Highly Summarized Dimensions

From the inbox today, a question about aggregate design:

Q:  "If you roll up a dimension that is hierarchical, but you are now left with the highest level of the hierarchy (no further roll-up would be possible in that dimension) - would you maintain a separate dimension for this in the aggregated star?

"Or would you join a number of these unrelated top hierarchy levels in a junk dimension?"

- Dublin, Ireland

The reader has a potential aggregate that will summarize more than one dimension at a very high level.  For example, an aggregate of sales that summarizes the customer dimension by country and summarizes the product dimension by category.

As a general rule of thumb, try to set up aggregate dimensions that preserve the original separation of the base dimension tables.  So rather than have country and category combined as a sort of "junk" dimension, create a rollup of customer called "country" and a rollup of product called "category." 

This gives the aggregate star a kind of "symmetry" with the original star.  It tends to be the most workable solution with aggregate-aware query tools, databases, etc.  It also is the easiest to manage when no tools are aggregate-aware, as it minimizes the effort required to rewrite SQL.

If the summary is very small, it may be workable to create a "big wide table" that stores the dimension values directly with the summary facts.  This works best if you are using a tool to implement the aggregate that is able to exploit the query rewrite function of a database, like a materialized view or materialized query table.  In this case, applications can still query the "base" star, but receive the benefit of the aggregate.

But do you really need this aggregate?  The more highly summarized an aggregate is, the fewer queries or reports it is able to accelerate.   It is usually possible to construct aggregates that are not quite so highly summarized, giving a performance boost to a wider range of queries.  For example, summarizing a customer dimension by region, rather than country, will allow the aggregate to work with more reports--including those that focus on country.

- Chris

Image credit:  ZapTheDingbat

More on Surrogate Keys

This post addresses a common follow-up question regarding surrogate keys:

"My source system already tracks changes. In this case, do dimension tables really need their own unique identifiers?"

Yes. There may be exceptions, but they are far less common than you may think, and they limit your future options.

Recommended Books on the Data Warehouse Lifecycle

Recommended Reading: A new book by Laura Reeves, and a revised edition of the classic Lifecycle Toolkit.

If you've been to any of my classes, you already know that I am a fan of Laura Reeves. She has a pragmatic, get-things-done approach to data warehousing.

You may also know her as co-author of the original edition of The Data Warehouse Lifecycle Toolkit, a book she wrote with Ralph Kimball, Margy Ross and Warren Thornthwaite. (For more on that book, see below.)

Laura has a new book out, which I highly recommend: A Manager's Guide to Data Warehousing.

In this book, she provides a practical guide to planning and executing data warehouse projects. It is written for managers (I.T. and business) who do not necessarily have a technical background in data warehousing.

Laura touches on each phase of the data warehouse lifecycle, providing useful advice without over-burdensome methodology, detailed task lists or the like. This makes it easy to fit her advice into your own organization's development style.

Even if you already have a strong background in dimensional design, you will find this book to be quite useful. You can get it at Amazon.com.

Also Recommended
If you have a dimensional data warehouse, I also urge you to check out The Data Warehouse Lifecycle Toolkit, Second Edition by Ralph Kimball, Margy Ross, Warren Thornthwaite, Joy Mundy and Bob Becker.

This fully revised version of the classic book contains detailed tasks and deliverables to help you manage all phases of the data warehouse lifecycle.

It is an excellent reference for data warehousing professionals. Read more about it at Amazon.com.

The original edition has been a long time recommendation on this blog, and the new edition carries on the standard. (Apologies to Warren Thornthwaite, whose name was previously misspelled here.)