Fact with different Grain

[nthumu88]

Hi All,
I know we must not create a fact table from tables with different gain.
but here I have a scenario where item ledger table stores shipment volume at the order level(i.e. it has company code, document type, document number, line number) and the same table stores inventory entry(but It has company code, document type which is different from shipment volume, doesn't have document number, its has line number which is again different from shipment volume entry). all these fileds company code, document type, document number, line number are present in sales detail table as composite PK. Now my question is can I include production volume and shipment volume/sales volume in same fact.

Thanks

[hkandpal]

Hi ,

the best options is to create a different fact table for different grain, if you have one fact with different grains then generating reports on that data will be a problem, especially when doing a count or other arithmetic functions.

Any reason why you are going with one fact ?

thanks

[nthumu88]

No Particular reason, People here want less number of facts.

[nick_white]

Hi,
rule 1 for designing a fact table is to define its grain and then ensure that everything you subsequently do doesn't violate that grain (unless you decide the original grain decision was wrong in which case you need to re-start the whole design process for that fact).
If you break this rule your dimensional model will not work.
If your design decisions are being driven by statements like "People here want less number of facts" then you'll almost certainly end up with a real mess of a dimensional model

[BoxesAndLines]

You can do anything you want. I've worked at many clients where they mixed grains. They even had the Kimball group come in and tell them it was a bad idea, paraphrasing, "you are going down a road from which you can never recover". Did they listen, nope. Did it work, if you're an expert on dimensional modeling and understand all of the implications of mixed grain fact tables and understand exactly how ETL is loading the mixed grain metrics, you'll be fine. If not, your users will complain, bad metrics get published, and you get blamed.

[nick_white]

I agree - nothing wrong with breaking the rules as long as you are doing it for a valid reason, you have a very good understanding of the impact and you have processes in place to deal with those impacts.
However I would suggest that if you are asking general questions like the one that started this topic you probably don't yet have the experience to successfully implement multi-grained facts - regardless of the support you might get from this forum - and you should avoid them if there is any other workable solution, such as creating multiple fact tables

[ngalemmo]

No Particular reason, People here want less number of facts.

By far one of the most absurd reasons to do this that I have heard.

A fact table must be of a single grain. In ER modeling terms, it is the equivalent of saying it needs to be in 3NF. Codd spent a lot of time working out normalization and his relational algebra to prove a relational model can handle any use case. From his work, it was clear that a model must be in 3NF as a minimum to ensure any situation can be dealt with. The same reasoning applies to the single grain rule.

If you mix grains there will be situations that will require very complex queries to resolve. Such queries are often well out of the scope of capabilities of a typical BI tool. Such situations typically require IT to create views to support the particular use case. The view would appear as another fact table to the user. The net result being the opposite of the intended outcome of fewer facts.

If you must, have two proper facts at the correct grain then build an aggregate fact at a common grain of the the two atomic facts. You wind up with three facts, but the users only need to deal with one. If they want to see the more granular data, you have the atomic level facts at you disposal.

[BoxesAndLines]

One of my favorite quotes from ngalemmo, 'Multi grain is good for bread, not for facts'.

[nthumu88]

No Particular reason, People here want less number of facts.

By far one of the most absurd reasons to do this that I have heard.

A fact table must be of a single grain.  In ER modeling terms, it is the equivalent of saying it needs to be in 3NF.  Codd spent a lot of time working out normalization and his relational algebra to prove a relational model can handle any use case.  From his work, it was clear that a model must be in 3NF as a minimum to ensure any situation can be dealt with.  The same reasoning applies to the single grain rule.

If you mix grains there will be situations that will require very complex queries to resolve.  Such queries are often well out of the scope of capabilities of a typical BI tool.  Such situations typically require IT to create views to support the particular use case.  The view would appear as another fact table to the user.  The net result being the opposite of the intended outcome of fewer facts.

If you must, have two proper facts at the correct grain then build an aggregate fact at a common grain of the the two atomic facts.  You wind up with three facts, but the users only need to deal with one.  If they want to see the more granular data, you have the atomic level facts at you disposal.

Yes I did same, i convinced my manager and created 2 facts at their own grain adn planing to build an aggregate fact at common grain i.e., day level data.

[Sponsored content]