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 are often the primary workhorse schema for many organizations. […]

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 fact table is student attendance information; the grain of the fact table is one row per student each day. A typical coverage factless fact […]

Many transaction processing systems consist of a transaction header “parent” with multiple line item “children.” Regardless of your industry, you can probably identify source systems in your organization with this basic structure. When it’s time to model this data for DW/BI, many designers merely reproduce these familiar operational header and line constructs in the dimensional world. In this Design […]

Meaningless integer keys, otherwise known as surrogate keys, are commonly used as primary keys for dimension tables in data warehouse designs. Our students frequently ask us – what about fact tables? Should a unique surrogate key be assigned for every row in a fact table? Although for the logical design of a fact table, the answer is no, […]

An overarching false statement about dimensional models is that they’re only appropriate for summarized information. Some people maintain that data marts with dimensional models are intended for managerial, strategic analysis and therefore should be populated with summarized data, not operational details. We strongly disagree! Dimensional models should be populated with the most detailed, atomic data captured by the source […]

In this Design Tip, we return to a fundamental concept that perplexes numerous dimensional modelers: text facts (also referred to as fact indicators, attributes, details or notes). Some of you may be rightfully saying that text facts are a dimensional modeling oxymoron. However, we frequently field questions from clients and students about indicator, type or comment fields that […]

Atomic fact tables are the core foundation of any analytic environment. Business analysts thrive on atomic details because they can be easily rolled up “any which way” by grouping on one or more dimension attributes. The robust dimensionality of atomic data is extremely powerful as it supports a nearly endless combination of inquiries. However, business […]

Factless fact tables appear to be an oxymoron, similar to jumbo shrimp. How can you have a fact table that doesn’t have any facts? We’ve discussed the basics of factless fact tables several times in our books and articles. In this design tip, we use a factless fact table to complement our slowly changing dimension strategies. As you […]

We are often asked about degenerate dimensions in our modeling workshops. Degenerate dimensions cause confusion since they don’t look or feel like normal dimensions. It’s helpful to remember that according to Webster, “degenerate” refers to something that’s 1) declined from the standard norm, or 2) is mathematically simpler. A degenerate dimension (DD) acts as a dimension key in […]