Ralph introduced the concept of slowly changing dimension (SCD) attributes in 1996. Dimensional modelers, in conjunction with the business’s data governance representatives, must specify the data warehouse’s response to operational attribute value changes. Most Kimball readers are familiar with the core SCD approaches: type 1 (overwrite), type 2 (add a row), and type 3 (add […]
Dimension Table Core Concepts
Childhood guessing games sometimes rely on the distinction of “person, place or thing” for early mystery-solving clues. Some modelers use these same characterizations in their data models by creating abstract person, place and/or thing (typically referred to as product) tables. While generalized tables appeal to the purist in all of us and may provide flexibility and reusability advantages for […]
How do you deal with changing dimensions? Hybrid approaches fill gaps left by the three fundamental techniques. Unlike most OLTP systems, a major objective of a data warehouse is to track history. So, accounting for change is one of the analyst’s most important responsibilities. A sales force region reassignment is a good example of a […]
It’s not unusual to identify dozens of different dates, each with business significance that must be included in a dimensional design. For example, in a financial services organization you might be dealing with deposit date, withdrawal date, funding date, check written date, check processed date, account opened date, card issued date, product introduction date, promotion begin date, customer birth […]
As with most things in life, change is inevitable with dimension attributes. Most Design Tip readers are familiar with the three basic slowing changing dimension (SCD) techniques: Type 1: Overwrite the attribute Type 2: Add another dimension row Type 3: Add another dimension attribute If this is news to you, take a look at Ralph’s April […]
When developing a dimensional model, we often encounter miscellaneous indicators and flags that don’t logically belong to the core dimension tables. These unattached attributes are usually too valuable to ignore or exclude. Designers sometimes want to treat them as facts (supposed textual facts) or clutter the design with numerous small dimensional tables. A third, less obvious but preferable, solution […]
The acronym, SCD, is a keyword in a dimensional modeler’s vernacular. As most of you know, SCD is short-hand for slowly changing dimensions. There are several well-documented techniques for dealing with slowly changing dimension attributes. Briefly, with SCD Type 1, the attribute value is overwritten with the new value, obliterating the historical attribute values. For example, when the […]