Q&A: Customers with No Sales in Fact Table

In this post, Chris answer a reader's question about reporting on customers with no sales in the fact table.

New Q&A Feature

I get a lot of questions about dimensional modeling from readers of the blog, my books, and people who attend my classes. I try to answer all of them. I thought it would be interesting to post some of them here, for all to read.

This week, I'm answering a question about reporting on customers with no sales.

In general what is the recommended approach for reporting customers that have no sales in the fact table for MTD or YTD? Or, for that matter, any dimension member where no fact exists for some time period?

- J. Dolan

Usually, something like “customers with no sales for a period” is computed as part of the reporting process. Developers often bristle at this kind of report, because it requires using a correlated subquery. This makes it difficult to do with a SQL-generating BI tool, and can be slow. And if you want to use the list of customers with no sales as a filter for another query, things can get really hairy.

An alternative is to pre-compute this information, storing the results in a table that summarizes reporting periods. This is likely to be a factless fact table, with foreign keys to dimension tables representing the period summarized and the dimension in question--customers in your case. If you require monthly and yearly slices, you will want to multiple summaries. This approach may simplify reporting, but adds complexity to the ETL process.

If current period is the only period of concern, as in your question, then it is also possible to pre-compute the information and store the result directly to the customer dimension table, as a flag. The flag would specify whether the customer has placed an order during the current period. In your case, it would be two flags -- one for month to date and one for year to date.

This kind of attribute is called a behavioral dimension because it takes behavioral activity recorded in the fact table and transforms it into a dimension. Behavioral dimensions are very powerful, because they can be used as simple filters for other queries.

Again, this concept trades ETL complexity for reporting flexibility and performance. But isn't that what data warehousing is all about?

Thanks to J. Dolan for agreeing to have his question posted. If you have a question, send it in. I try to answer all my email, though it sometimes takes a while!

Chris

Coming to TDWI New Orleans? Vote First!

If you'll be joining me at the TDWI conference in New Orleans during election week, make sure you vote ahead of time. Here's how.

This election day, I'll be teaching Advanced Dimensional Modeling at The Data Warehouse Institute World Conference in New Orleans. I was a bit reluctant to accept the invitation at first, out of fear that it would prevent my vote from being counted. But after doing a little bit of research, I accepted with confidence.

You can attend with confidence as well. All you need is an absentee ballot.
Absentee voting procedures vary by locality. To find out how it works where you live, you can go to GoVoteAbsentee.org.

Plug in your zip code, and they'll provide you with information on how to apply for an absentee ballot. They'll even give you a link to download the ballot application. If you prefer, you can contact your county or city government for information.

The process is simple, and you can be sure your vote will be heard. Some localities even permit residents to cast absentee ballots in person. In Fairfax county Virgina, where I live, you can personally feed your ballot into the optical scanner that tallies the votes.

And as an added bonus, you won't have to stand in line on election day!

I have already cast my absentee vote. If you will be traveling to The Data Warehouse Institute Conference in New Orleans, I urge you to do the same. Go to GoVoteAbsentee.org now, and find out how.

- Chris

Available Now: Designing a Performance Layer

My article "Designing A Performance Layer: Aggregates and Derived Tables" appears in the current issue of The Business Intelligence Journal (Vol 13, No. 1), available to members of The Data Warehouse Institute.

This article shows you how to create a performance layer for your dimensional data warehouse -- a set of cubes and tables designed specifically for performance.

The article covers two types of performance design: aggregate tables and derived tables.

• Aggregate Tables store partially summarized copies of existing data. These tables (or cubes) improve performance for queries that do not require granular detail. The article fully explains aggregates, including summarization of metrics, hierarchies, and implementation with or without aggregate navigators.
  

• Derived Tables store restructured copies of existing data. They do not necessarily summarize information, but they may. Derived tables deliver performance benefits by streamlining the query and reporting processes. In the article, I show how to supplement an existing schema with merge, pivot, and set operation fact tables (or cubes).

The full article appears in The Business Intelligence Journal, which is available in print and on line for TDWI members.