Fact with different Grain

View previous topic View next topic Go down

Fact with different Grain

Post  nthumu88 on Thu Feb 19, 2015 4:45 pm

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

nthumu88

Posts : 10
Join date : 2015-01-26

View user profile

Back to top Go down

Re:Fact with different Grain

Post  hkandpal on Thu Feb 19, 2015 5:03 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Fact with different Grain

Post  nthumu88 on Thu Feb 19, 2015 7:28 pm

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

nthumu88

Posts : 10
Join date : 2015-01-26

View user profile

Back to top Go down

Re: Fact with different Grain

Post  nick_white on Fri Feb 20, 2015 8:08 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Fact with different Grain

Post  BoxesAndLines on Fri Feb 20, 2015 10:56 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fact with different Grain

Post  nick_white on Fri Feb 20, 2015 11:26 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Fact with different Grain

Post  ngalemmo on Fri Feb 20, 2015 12:46 pm

nthumu88 wrote: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.

avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact with different Grain

Post  BoxesAndLines on Fri Feb 20, 2015 12:56 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fact with different Grain

Post  nthumu88 on Fri Feb 20, 2015 3:23 pm

ngalemmo wrote:
nthumu88 wrote: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.

nthumu88

Posts : 10
Join date : 2015-01-26

View user profile

Back to top Go down

Re: Fact with different Grain

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum