Data Warehousing and Business Intelligence

30 June 2012

Not All Surrogate Keys Define the Fact Table Grain

Filed under: Data Warehousing — Vincent Rainardi @ 5:04 pm

Not all surrogate keys in the fact table define the fact table grain.

For example, if you have if you have daily account balance table, the grain of the fact table 1 row for each account, every day. So there are only 2 surrogate keys that define the grain of the fact table:

  1. Snapshot Date Key
  2. Account Key

If we have Customer Key and Product Type Key in that fact table, they don’t form part of the grain:

  1. Snapshot Date Key – part of grain
  2. Account Key – part of grain
  3. Customer Key – not part of grain
  4. Product Type Key – not part of grain

Say that for 27th June 2012 we loaded 1000 rows, and ditto for 28th June 2012. In the BI tool (say Cognos) if we put Snapshot Date, Account Number and Account Balance, we will get 1000 rows. If we add Customer Number, we will still get 1000 rows. If we add Product Type, we still get 1000 rows.

So my point here is: not every surrogate key in the fact table is part of the fact table grain.

Advertisements

2 Comments »

  1. Hi,so I see that not all surrogate keys in a fact need to be part of grain, but would it make sense if only 1 surrogate was part of grain? All other keys not being part of grain? Therefore, 1 to many for all other dimensions to fact, but 1 to 1 for 1 particular dimension?

    Comment by Bryan — 12 September 2012 @ 6:19 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: