What's the Grain in the context of DW

Question

According to The Data Warehouse Toolkit by Kimball

"The grain must be declared before choosing dimensions
or facts because every candidate dimension or fact must be consistent with the grain."

I'm so confused about this concept .Could some one illustrate what's the meaning of Grain giving an analogy or metaphor in real life to clarify the concept .

Level at which the facts are measured – Channa Jun 17 '20 at 19:21 — Channa, Jun 17 '20 at 19:21

score 9 · Accepted Answer · answered Aug 27 '16 at 12:29

9

Some examples:

"The sales table has a grain of DAY, STORE, PRODUCT"
"The sales table has a grain of DAY, STORE, PRODUCT, CUSTOMER"
"The sales table has a grain of HOUR, STORE, CUSTOMER"
"The sales summary table has a grain of DAY, STORE, PRODUCT_CLASS"

Hence the time dimension must support HOUR and DAY, and the product dimension must support PRODUCT and PRODUCT_CLASS.

answered Aug 27 '16 at 12:29

David Aldridge

51,479
8
68
96

1

Just to add to David's post, I found this to be quite a helpful resource: http://www.kimballgroup.com/2003/03/declaring-the-grain/ – Navik Hiralal Aug 27 '16 at 13:33
Hi, are not them the candidate Keys? – Enrique Benito Casado Jul 24 '18 at 16:00
1

@Enrique They would be if they combined to form a unique key, but in a DW fact table it would not be necessary that they do. – David Aldridge Jul 25 '18 at 13:30

score 9 · Answer 2 · answered Feb 22 '17 at 23:03

The grain (or granularity of the fact) refers to the 'level' at which you're taking a measurement. A fact table describes a measurement taken of a business process, so the best way to describe the grain is to describe what you get for each row. The classic example for a supermarket checkout is 'one row for every beep/scan'. This is better than saying 'one row for every day, product and store' (i.e. naming the dimensions) because it grounds it in reality.

The grain/level element is that you might be storing a row at a level of product, or you might be storing it at some grouping of products. This matters as it will determine whether you can use the product-level dimension or the group-level dimension with it.

score 2 · Answer 3 · answered Sep 15 '16 at 20:07

To add to David's examples. What if your Date dimension lowest grain is a week and Sales grain is a day? That means your DIM and FACT grain is inconsistent. Also, your sales table could point out a product and it's color, but what if the lowest grain of your Product dimension is just the product and no colors? Again there is an inconstancy in your DIM and FACT lowest grain.

What's the Grain in the context of DW

3 Answers3