How to Group Customers by Number of Purchases Quickly!

This is a great little sql statement that packs a nice punch for the number crunchers out there...

A disclaimer first: make sure you don't do this on a live OLTP table with millions of rows unless you want your customers to wait. Thus, run it on your slave :).

Onto business...

The Request

"I need to know the number of people who made one purchase, two purchases, three purchases, and so on. Right now."

The Answer

SELECT total, COUNT(1) FROM (SELECT customer_id, COUNT(1) AS total FROM purchases_table GROUP BY customer_id) derived_table GROUP BY total ORDER BY total;

And would you know it, the result was there in seconds....

Until next time, (I do hope to put up some juicy warehousing how-to's soon)
David

MySQL Conference: The Week After

Wow... I had a great time at the annual MySQL Users Conference in Santa Clara.

Highlights:

• Received my CMDBA designation. Woohoo!
• Sun announced the possibility of an open source mobile phone;
• All day mysql proxy tutorial with Jan Kneschke and Giuseppe Maxia;
• Amazon's Power of Infrastructure as a Service - great premise; glitches remain;
• Great food and a nifty side trip to Santa Cruz;
  
• MySQL Workbench - nifty tool for database development;
• Use multiple sockets to run queries in parallel to speed up web pages (from Digg.com);
• Best Keynote: Who is the Dick on My Site? by Dick Hardt
• A chant that Marty Widenus required us to repeat regarding MySQL's development future: "We don't want crippleware";
• Discovered how Fickr scaled heavy concurrent writes in real time;
• Intriguing discussion panel on database warehousing: Sun/MySQL, Kickfire, Infobright, NitroSecurity, and a data warehouse contractor;
• Chocolate covered strawberries and ice cream goodies at the exhibit hall.
  

I hope to be more of a responsible blogger and share some very insightful ideas to those of you who were not able to make it to the conference.

Until next time,
David

Data Warehousing 101: Pitfalls to Avoid

Sometimes technology gets in the way of getting things done, and us tekkies must do what we can to make our data warehouse work for the purpose it is intended. Ralph Kimball makes 10 good points about the common pitfalls to avoid when implementing a data warehouse, as listed in his book "The Data Warehouse Toolkit", published by Wiley.

• Become immersed with technology rather than the requirements and the goals of business;
• Fail to recruit an influential and accessible management visionary to sponsor the data warehouse;
• Turn this into a multi-year project instead of pursuing manageable iterative development;
• Run out of budget while creating a normalized data structure, before building a viable presentation area;
• Pay more attention to back-room ease of development over front-room ease of use and query performance;
• Make queryable data in presentation area too complex, thus causing users to refer to developers;
• Populate dimensional models on a standalone basis without regard to the data architecture that ties them together;
• Load only summarized data into presentation area's dimensional tables;
• Presume that the business, its requirements and analytics and the underlying data and technology are static;
• Neglect to acknowled that data warehouse success is tied directly to user acceptance.

Next: using MySQL and php to extract, transform and load data (ETL) into the warehouse

Until Next Time,
David

Data Warehousing 101: Requirements for a Data Warehouse

Yes, it has been a while since I added an entry in my blog. I have been working on creating a dynamic data warehouse system reliant on the traditional LAMP stack (and a very nifty graphical plug-in - please comment below if you wish to know what it is!).

Firstly, I must make these qualifications before you read further:

1. I consider Bill Inmon and Ralph Kimball the pioneers of data warehousing;
2. Data warehousing has been a relatively recent concept, since 1983 when Teradata introduced a database decision support system, and most current day concepts we understand about data warehousing have been taking shape over the past 10 years;
   
3. I am new to this as well, but am very satisfied with the results I am obtaining.

OK... What does a data warehouse look like? This is a succinct high-level view as aptly described on the data warehouse entry on wikipedia:

• Subject-oriented - data is organized so that all data elements relating to the same real-world event or object are linked together.
• Time-variant -changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time.
• Non-volatile - data in the database is never over-written or deleted - once committed, the data is static, read-only, and retained for future reporting.
• Integrated - database contains data from most or all of an organization's operational systems and this data is made consistent.

This premise draws us to what appears to be a centralized solution for a data warehouse. However, you can approach it on a smaller scale by identifying the basic bones of your database structure, thus enabling you to produce results in a shorter span of time. Note: the building blocks of data is not done according to departments - for example, sales, marketing, customer service, and so on.

Instead, you can establish what your basis for your data is through fact tables and dimension tables.

A fact table is the "primary table where the numerical performance measurements of the business are stored" (The Data Warehouse Toolkit, second edition, p.16). The most useful facts of this table are numeric and additive.

Daily Sales Fact Table

Date Key (FK)
Product Key (FK)
Store Key (FK)
Quantity Sold
Dollar Sales Amount

A dimension table, the companion to the fact table, is the user interface to the data warehouse.

Production Dimension Table

Product Key (PK)
Product Description
SKU Number (not to be used as a key in the warehouse)
Brand Description
Category Description
Department Description
...more

Bringing together facts and dimensions becomes easier across all levels of business, as users agree that the dimensional model is their business. In our examples above, store operators use the store dimension table to relate to the number of products sold daily; marketing and product development refer to the attributes of the product dimension table to make conclusions about which products to promote and develop further.

Next I will relate common pitfalls known in the early stages of data warehousing and how to overcome them.

Until Next Time,
David

Don't Ever Use NOW() - `datetime field`

***All point news bulletin***

Do not ever use NOW() - `datetime field`! Broadcast this from the mountaintops of open source to the valleys of companies who dabble in it.

Instead, use the following:

TIME_TO_SEC(TIMEDIFF(Now(),`datetime field`))

There are other ways to do the above, just as long as you do not use NOW() - `datetime field`.

Wanna see proof?

mysql> SELECT RIGHT(NOW(),8) `Now`, RIGHT(datetime_inserted,8) `Inserted`, NOW()-datetime_inserted Incorrect, TIME_TO_SEC(TIMEDIFF(NOW(),datetime_inserted)) Correct from my_table;
+----------+----------+-----------+-------+
| -- Now - | Inserted | Incorrect | Correct
+----------+----------+-----------+-------+
| 11:16:59 | 10:59:45 | 5714.0000 | 1034
| 11:16:59 | 10:59:51 | 5708.0000 | 1028
| 11:16:59 | 10:59:55 | 5704.0000 | 1024
| 11:16:59 | 11:03:17 | 1342.0000 | 822
| 11:16:59 | 11:03:19 | 1340.0000 | 820
| 11:16:59 | 11:03:20 | 1339.0000 | 819
+----------+----------+-----------+-------+

Fun, eh?

Til next time,
David

Data Warehousing 101: The purpose of a data warehouse

When your company decides that "it is time to build a data warehouse", what thoughts come to mind?

1) A magical fairy ice cream land where data is presented in chocolate shells for everyone to digest perfectly;
2) A big literal warehouse in the industrial section of town with rusty old containers;
3) Another place to put data, which means another place for you to track and monitor additional activity;
4) A place to put a pared-down representation of your OLTP database and call it OLAP.

Sorry to dash anyone's hopes, but it is none of the above.

Before anyone starts shooting from the hip about what a data warehouse is, I recommend picking up the book "The Data Warehouse Toolkit" by Ralph Kimball, a very deserved pioneer in the specialty of data warehousing.

Let's start with the goals of a data warehouse. What do you hear through the walls of business when they are talking about data?

"We have oceans of data in this company, but we can't access it."
"We need to slice, dice, and thrice the data in every conceivable manner possible. "
"You've got to make it easy for business people to get at the data directly."
"Why do we have people present the same business metrics at a meeting, but with different numbers?"
"We want people to use infromation to support more fact-based decision making."

Because these concerns are widespread, they have driven the requirements for the data warehouse:

The data warehouse must make an organization's information easily accessible.
The data warehouse must present the organization's information consistently.
The data warehouse must be adaptive and resilient to change.
The data warehouse must be a secure bastion that protexts our information assets.
The data warehouse must serve as the foundation for improved decision making.
The business community must accept the data warehouse if it is to be deemed successful.

So what does a data warehouse look like? I am glad you asked. That will be the next thing I blog about. :)

Until next time,
David

What Do You Name Date Fields?

I have seen too many date fields that have been named after reserved key words that I need to make an entry for this... It's like naming your kid "Kid". Wait a sec... that's happened many times before as well.

I will make a "Proper Naming Conventions" page for mySQL after checking a few more sources first, but this date naming issue cannot wait another day.

Thus..

First Rule: Do not use a reserved word for your field name!
Second Rule: Your date-related field name can actually include the word date or datetime in it!

Examples:

datetime_inserted
datetime_modified
datetime_joined
date_joined
member_since
date_of_birth
dob

Yes, this was a short simple post. But freekin' necessary.

Until next time,
David