GRAIN
Last updated
Last updated
Declaring the grain is the pivotal step in a dimensional design.
The grain establishes precisely what a single fact table row represents.
Minute-by-minute weather.
Daily sales.
A scanner device measures a line item on a customer’s retail sales ticket.
An individual transaction against an insurance policy.
What is the lowest level data? Grain is defined in business terms, not as rows / columns.
Based on the image, what do you think as data frequency?
It can be anything, but that has to be decided first.
Ralph Kimball says, "The grain must be declared before choosing dimensions or facts".
The grain must be declared before choosing dimensions or facts because every candidate dimension or fact must be consistent with the grain.
This consistency enforces uniformity on all dimensional designs, that is critical to BI application performance and ease of use.
When changes are made to Grain (adding Per customer to lowest level)
Rolled-up summary grains are essential for performance tuning, but they pre-suppose the business’s common questions.
Each proposed fact table grain results in a separate physical table; different grains must not be mixed in the same fact table.
Making one more change
Helps in Better Reporting. Increases query performance. It helps in getting aggregated / summary view.
