|A Perfect Split!!!|
So this time, the question was..
When do you Split the Fact tables?
This is quite an interesting question, and tricky to me at the same time.
Don’t you think so?? Let me tell you then why..
Imagine, a Product being manufactured at any time in a factory, and a Product being Sold in a Store at any time. So, the measurements here are
Products Sold : A measure that could be stored at Product x Period level.
# of Products Manufactured : A measure that could again be stored at Product x Period level.
Just because, both of these measures are at the same level, the question is; Is it correct to store both these measures in the same Fact??
And my answer is, NO!! Just because, If you have noticed by now, the grain of the measures are different.
Yes, the Grain of a fact table is not a combination of Foreign Keys to dimensions. Instead, it identifies the context of the measurement; In this situation, A Product Being Sold in the Store at a time, and a Product Being Manufactured at a time which are completely different.
So my answer ultimately to the question being asked was, the Fact Tables have to be split based on Business Workflows.
So, have you ever been asked such a question too? If you were, could you share with us what was your take on it?