Kimball University: Handling Arbitrary Restatements of History - InformationWeek
Software // Information Management
10:13 PM

Kimball University: Handling Arbitrary Restatements of History

How do you cope with an executive's request to "bring back a time series of activity for all subscribers who were in platinum status as of X date," or "show me a time series of orders by sales region according to the sales organization as of Y"? Here's how data warehouse pros can cope with the common requirement to look back in time.

Most business users of the data warehouse/business intelligence system are content with looking at their information in one of two ways: by the current state of affairs, or by tracking history. For example, a sales manager developing a sales forecast wants to see sales for his or her region as that region is defined today. But that same sales manager confirming the compensation plan must correctly associate all sales with each salesperson, even if they used to report into a different region. Today's best-practice BI systems are designed for these alternative approaches.

But sometimes it's more complicated than that. Do our existing design techniques enable a business user to restate history as of an arbitrary date? As we describe in this article, the answer is "yes, but." Yes we can, but it's a challenging query for business users to construct. Luckily, a modest design change in the data model can improve the situation.

Manage Changes to Dimension Attributes

The Kimball Method advocates three main techniques for managing changes to dimension attributes:

Type 1: Restate history by updating the dimension attribute in place.

Type 2: Track history by adding a new row to the dimension table that contains the new view of the dimension member.

Type 3: Snapshot history by adding new columns to the dimension table that holds the attribute's values at a specific date, often year-end.

These techniques have stood the test of time. They meet the vast majority of business users' requirements for reporting and analysis. Dimension tables are joined to the fact table by a single column key, usually an integer, that's managed by the data warehouse ETL process. This simplicity pays us back at query time when the database engine has a relatively simple task — a task for which most database vendors have invested in tuning their engines. Dimensions with Type 1 and Type 2 attributes are smoothly incorporated into OLAP cubes as well, for high-performance querying and analysis. Sometimes, though, users need to report using dimension attributes as of an arbitrary date. For example, "Bring back a time series of activity for all subscribers who were in platinum status as of December 12," or "Give me a time series of orders by sales region according to the sales organization as of July 15."

This requirement isn't directly met by the three classic dimension attribute change types. Type 1 gives us the dimension attributes as of today. Type 3 is not sufficiently flexible; we need to modify the data model, adding columns to the dimension table to encode the attributes for the alternative history. Type 2 is our best bet; the Type 2 attributes actually contain the information we need to reconstruct the dimension as of a past date.

1 of 4
Comment  | 
Print  | 
More Insights
Newest First  |  Oldest First  |  Threaded View
How Enterprises Are Attacking the IT Security Enterprise
How Enterprises Are Attacking the IT Security Enterprise
To learn more about what organizations are doing to tackle attacks and threats we surveyed a group of 300 IT and infosec professionals to find out what their biggest IT security challenges are and what they're doing to defend against today's threats. Download the report to see what they're saying.
Register for InformationWeek Newsletters
White Papers
Current Issue
IT Strategies to Conquer the Cloud
Chances are your organization is adopting cloud computing in one way or another -- or in multiple ways. Understanding the skills you need and how cloud affects IT operations and networking will help you adapt.
Twitter Feed
InformationWeek Radio
Archived InformationWeek Radio
Join us for a roundup of the top stories on for the week of November 6, 2016. We'll be talking with the editors and correspondents who brought you the top stories of the week to get the "story behind the story."
Sponsored Live Streaming Video
Everything You've Been Told About Mobility Is Wrong
Attend this video symposium with Sean Wisdom, Global Director of Mobility Solutions, and learn about how you can harness powerful new products to mobilize your business potential.
Flash Poll