Context
Let’s take the example of a fact table: Order_facts, which is designed to compile information relating to orders.
Fact table granularity is highly important because it enables us to understand the nature of different facts. More specifically, granularity defines the nature of a given row in a fact table. In the example shown above, the facts Quantity Ordered, Order Dollars, Cost Dollars and Margin Dollars are calculated per product (PRODUCT), per salesperson (SALESPERSON), per customer (CUSTOMER), and per date of sale (DAY).
Definition
Granularity, or grain, is what enables the definition of the degree of detail of the information included in a row of a fact table. It is defined by a minimal set of dimensions.
The granularity of a fact table therefore refers to its degree of detail:
- It defines the specific context of the different facts.
- It guarantees that all facts are recorded in the same degree of detail.
Granularity can be expressed in two different ways:
- Via a dimensional definition listing the associated dimensions:
PRODUCT,SALESPERSON,CUSTOMER,DAY. - Via a business definition, using terms derived from the business field without an explicit reference to the dimensions: sales of products made by a salesperson to a given customer on a given date.
It is important to note that the grain corresponds to a subset of the set of dimensions linked to the fact table: this is not necessarily all of the dimensions as shown in the example above.
A fact table has several facts. All facts must correspond to a single context. For example, for one product, the quantity ordered and the total amount could be stated. In this case, the quantity and the amount relate to the same context: the order and the product.
When two facts correspond to two different grains, it means that they are not involved in the same process, and that they must be stored in two different fact tables.
When two facts in two different fact tables have the same definition, the same unit of measurement, and the same calculation method, they are said to be conformed facts, even if they are not defined as having the same grain.
Reading Fact table granularity: Star Schema. Christopher Adamson. pages 42, 67-69
Reading Fact table granularity: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. pages 8-9, 233
Reading Event granularity: Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema Laurence Corr, and Jim Stagnitto. page 54