Design Tip #167 Complementary Fact Table Types

There are three fundamental types of fact tables in the data warehouse presentation area: transaction fact tables, periodic snapshot fact tables, and accumulating snapshot fact tables. Most DW/BI design teams are very familiar with transaction fact tables. They are the most common fact table type and Read More

Design Tip #145 Time Stamping 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 represented as accumulating snapshot fact tables characterized by one row per pipeline occurrence where each Read More

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

For must 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 company’s phone calls, and a college’s course registrations are all clearly facts. However, for some Read More

Design Tip #133 Factless Fact Tables for Simplification

It’s been awhile since we talked about factless fact tables in Design Tip #50. You may recall that a factless fact table is “a fact table that has no facts but captures the many-to-many relationship between dimension keys.” We’ve previously discussed factless fact tables to represent Read More

Design Tip #116 Add Uncertainty to Your Fact Table

We always want our business users to have confidence in the data we deliver through our data warehouses. Thus it goes against our instincts to talk about problems encountered in the ETL back room, or known inaccuracies in the source systems. But this reluctance to expose our uncertainties Read More

Exploit Your Fact Tables

My previous column described the three types of fact tables that are all you will ever need in your data warehouse (see December 2008 DM Review). The secret to this simple observation is adhering fanatically to the grain. A fact table records measurement events, and as long Read More