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

Fact Tables

Fact tables are the foundation of the data warehouse. They contain the fundamental measurements of the enterprise, and they are the ultimate target of most data warehouse queries. Perhaps you are wondering why it took me so long to get to fact tables in DM Review? Read More