The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence

Chapter 8. Fact Table Core Concepts

Dimensional models are directly tied to the measurement activities performed by an organization as the metrics captured or generated by these activities are unveiled in corresponding fact tables. This chapter focuses on the central figure of any dimensional model—the fact table.

Every fact table should have a single, explicitly stated granularity; we begin the chapter by encouraging you to tackle the most atomic details available from an operational source system. We then describe a relatively remarkable dimensional modeling phenomenon: All fact tables are either transactional, periodic snapshots, or accumulating snapshots. The treatment of transaction header versus line item details is further explored.

From there, we turn our attention to the pros and cons of fact table surrogate keys and the role of degenerate dimensions. Techniques to help reduce the width of voluminous fact tables are described. Finally, we close the chapter by discussing the handling of null, textual, and sparsely populated facts, along with metrics that sometimes more closely resemble dimension attributes.

Granularity

This first set of articles stresses the importance of precisely establishing the grain of a fact table early in the design process. They also argue that the fact table's granularity should correspond to the most detailed, atomic data captured in the operational source system for maximum analytic flexibility and extensibility.

8.1 Declaring the Grain

Ralph Kimball ...