Design Tip #164 Have You Built Your Audit Dimension Yet?

One of the most effective tools for managing data quality and data governance, as well as giving business users confidence in the data warehouse results, is the audit dimension. We often...

Design Tip #156 An Excel Macro for Drilling Across

  Drilling across separate business processes is one of the most powerful applications in a data warehouse. We often describe drilling across as magic: separately open connections to the dimensional...

Design Tip #152 Slowly Changing Dimension Types 0, 4, 5, 6 and 7

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...

Design Tip #147 Durable “Super-Natural” Keys

One of the tasks of the ETL system’s customer dimension manager is to “assign a unique durable key to each customer.” By durable key, we mean a single key value that...

Design Tip #145 Timespan Accumulating Snapshot Fact Tables

In Design Tip #140, I discussed the challenges of designing dimensional schemas for processes of indeterminate length such as a sales pipeline or insurance claims processing. We concluded they are best...

Design Tip #140 Is it a Dimension, a Fact, or Both?

For most subject areas, it’s pretty easy to identify the major dimensions: Product, Customer Account, Student, Employee, and Organization are all easily understood as descriptive dimensions. A store’s sales, a telecommunication...

Design Tip #133 Factless Fact Tables for Simplification

Factless fact table are“fact tables that have no facts but captures the many-to-many relationship between dimension keys.” We’ve previously discussed factless fact tables to represent events or coverage information. An event-based factless...

Design Tip #130 Accumulating Snapshots for Complex Workflows

Accumulating snapshots are one of the three fundamental types of fact tables. We often state that accumulating snapshot fact tables are appropriate for predictable workflows with well-established milestones. They typically have...

Design Tip #128 Selecting Default Values for Nulls

Design Tip #43 Dealing with Nulls in the Dimensional Model describes two cases where null values should be avoided in a dimensional model; in these situations, we recommend using default values...

Industry Standard Data Models Fall Short

Industry-standard data models are an appealing concept at first blush, but they aren’t the time savers they are cracked up to be. What’s more, these prebuilt models may inhibit data warehouse...

Kimball design Tips