Analytical Grain, Facts, Dimensions, and the Star Schema
5 min read
Lesson objectives
- State a clear fact-table grain before building measures.
- Distinguish fact and dimension roles in a simple star schema.
- Detect an error caused by mixed levels of detail and propose a fix.
Before aggregating, decide what one row represents
One of the most important early modeling decisions is grain, or level of detail: what event or observation does one row in the fact table represent? A row might be one maintenance request, one line item within a request, or a daily snapshot of request status. These levels are not interchangeable. If grain is not defined before measures and relationships are added, values from different levels can be combined into results that look plausible but do not represent the intended question.
Fact and dimension tables
In a star schema, the fact table stores measurable events or observations, while dimension tables describe the business entities used for filtering and grouping. In our case, FactRequestLine might contain request ID, part ID, quantity, and cost, while dimensions such as DimService, DimTeam, and DimDate describe service, team, date, and their attributes. This separation supports questions such as: What was part cost by service type? By team? Across months? It also makes row meaning and keys clearer.
| Table | Type | Example fields | Analytical role |
|---|---|---|---|
| FactRequestLine | Fact | RequestID, PartID, Quantity, PartCost | Aggregate cost and quantity at line grain |
| DimService | Dimension | ServiceKey, ServiceName, Category | Filter and group by service |
| DimTeam | Dimension | TeamKey, TeamName, Region | Filter and group by team and region |
| DimDate | Dimension | DateKey, Date, Month, Year | Provide consistent time analysis |
The word “fact” does not mean every column is a number to be summed. Fact tables can contain keys, flags, or dates. Some measures are not meaningfully additive across every dimension. An end-of-day balance, for example, is not summed across dates in the same way as sales value. The foundational habit is to ask two questions before any SUM: What is the row grain? Does addition across this dimension make business sense?
Test the grain with one sentence
Before building the table, write: “One row represents …” For example: “One row represents one part line used in a maintenance request.” Then test each field: can it logically belong to that line? If we store a team’s annual contract value on every part line, the value repeats for every request and part and inflates when summed. It belongs at a more appropriate grain or in a calculation that does not repeat it. This simple sentence catches many modeling mistakes early.
Lesson summary
Grain defines what a row represents. A star schema uses facts for measurable events and dimensions for description and filtering. Mixing order and line grains can turn mathematically correct counting or summing into an analytically wrong result.