Related Links

Web Seminars

History requirements need to be addressed when designing reporting systems.

For example, if Bob, our customer, moves five times in the last year, we can choose to capture only the most current information where Bob lives today, which is known as a Type 1 slowly changing dimension. Or we can store all history and, therefore, all of Bob's address changes, which is known as a Type 2 SCD. Or we can store some of the history, such as Bob's original or previous address, which is known as a Type 3 SCD. Type 3 is frequently chosen for purely technological reasons, such as simplifying development, saving storage space or increasing query performance. Is the decision to store some history (Type 3) over all history (Type 2) ever driven by business needs, or is it always dictated by technology? In other words, what business reasons are there for modeling a Type 3?

Most Design Challengers agree that choosing a Type 3 is a technology-driven decision by IT. Business requirements, however, can also favor Type 3 for these reasons:

Choose Type 3 when Type 2 can violate legal regulations or privacy rights. Within certain industries, storing substantial history for some subject areas can violate laws. Often these regulations and privacy rights have to do with knowing too much about an individual. Doug Jones, information architect, gives credit history as one example where too much history may not be required or allowed. Jeff Dallara, data management lead, includes mortgage applications as an area where only the current and previous addresses may be permitted in gauging the mortgagee's financial stability.

Choose Type 3 when Type 2 can violate company policy. Even if laws are not broken by storing full history, internal company practices may limit the storage of histories. Data Architect Moshe Japha says a company's privacy policy may dictate that they won't store more than the current and next previous customer addresses. "The rationale would be that they need the previous one in cases where a user made the change online or by phone and confirmations are sent to both addresses (as is common in the financial industry). Older addresses no longer are the company's business." Chris Ragan, global functions data architect, provides this human resources example: "Each year our employees are given a performance rating. The business has chosen to keep the current year plus two previous year ratings. Older ratings are not an indicator of current performance, so the business has no need of older history."

Choose Type 3 when Type 2 can produce meaningless results. Sometimes too much history can be confusing or worthless to the business. If Type 3 has been chosen for a particular subject area, it is possible that other subject areas related to the one chosen will also need Type 3 because Type 2 would produce meaningless results. Building on the performance rating example mentioned earlier, if we are only storing three years of performance ratings, it may not be of value to store full history on all of the assignments the employee has had. Architect Vibha Shrivastava provides this manufacturing example: "In one situation when I was creating a data warehouse for a steel manufacturing company, they needed the original heat of the material and the current heat of the material. It was immaterial what other heats the steel would have had in the past." Jennifer Prichard, data modeler, provides this HR example: "The only business reason for this that I can think of in general terms is a piece of data that only exists in two states: current and past. One I can think of is marriage status - single, married, etc. A current/past value would be useful for seeing if someone was ever married at a glance, and dates could be used to track multiple events."

Choose Type 3 if the values never change more than once. Building upon the previous example of HR marriage status indicators, the person's last name may change only one time (though exceptions exist), and therefore, would be good for Type 3. Chan Beauvais, database analyst, provides another example: "At our firm, we need to know if a location has relocated, and if so, what was the previous location number. This is used for comparative tracking of the previous and new location sales data. So we only need the current and previous location numbers."

It is rare to choose Type 3 for business reasons, yet the examples offered are proof that it can happen and that there are exceptions to every rule.

Steve Hoberman is currently a data modeling consultant and instructor. He taught his first data modeling class in 1992 and has educated more than 10,000 people about data modeling and business intelligence techniques since then. Steve balances the formality and precision of data modeling with the realities of building software systems with severe time, budget, and people constraints. In his consulting and teaching, he focuses on templates, tools, and guidelines to reap the benefits of data modeling with minimal investment. Steve is the author of five books on data modeling, the founder of the Design Challenges group, and inventor of the Data Model Scorecard.