Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Handling many-many relationship between fact and dimensions.

3 posters

Go down

Handling many-many relationship between fact and dimensions. Empty Handling many-many relationship between fact and dimensions.

Post  Hemapr Thu May 24, 2012 1:53 pm

We have a scenario where there is a many-many relationship between fact and dimensions.

One approach as Kimball mentioned in his articles such as creating a Bridge table between fact and dimension.

But, is it a good idea to maintain m-m relationship in base fact and then create another fact with distinct of lowest grain (such as flashnumber in our case.) and count?

Here is an example: Base fact with M-M between SplSvcs and Flashnumber
Factid SplSvcs Flashnumber date
--------------------------------------
1 s1 f1 5/21
2 s2 f1 5/21
3 s1 f2 5/21

Another Fact for counts with distinct of flashnumber:

Flashnumber count date
------------------------------
f1 1 5/21
f2 1 5/21

So, is it a good idea to go with this approach of another fact on the base fact?

Hemapr

Posts : 12
Join date : 2012-05-15

Back to top Go down

Handling many-many relationship between fact and dimensions. Empty Re: Handling many-many relationship between fact and dimensions.

Post  ngalemmo Thu May 24, 2012 2:51 pm

It depends on wither there are meaningful measures at the higher grain. If there are, it would make sense to create a fact table at the lower grain. If the many-to-many relation is simply providing greater context and does not affect the measures, then a bridge is appropriate.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Handling many-many relationship between fact and dimensions. Empty Re: Handling many-many relationship between fact and dimensions.

Post  Hemapr Fri May 25, 2012 7:42 am

Actually, both the facts are at the same grain (flash number). We consider 1 as a detail fact to store all the many-many relationship dimensions in that fact table where the flashnumber alone is not unique. Since it has m-m relationships so, to uniquely identify a row in that fact it is a composite key of flashnumber + dimension ids (where m-m exists). Because of this reason, we cannot have measures in this fact as the counts will not be correct.

So, we created another fact (master fact) where the flashnumber is unique and this will not have m-m relationship dimension ids. We maintain measures at this level.

Is it a good idea to have 2 facts at the same grain for these kind of M-M relationship between fact and dimensions?

Hemapr

Posts : 12
Join date : 2012-05-15

Back to top Go down

Handling many-many relationship between fact and dimensions. Empty Re: Handling many-many relationship between fact and dimensions.

Post  Hemapr Wed May 30, 2012 1:16 pm

any suggestions?

Hemapr

Posts : 12
Join date : 2012-05-15

Back to top Go down

Handling many-many relationship between fact and dimensions. Empty Re: Handling many-many relationship between fact and dimensions.

Post  LAndrews Wed May 30, 2012 2:21 pm


both facts are not the same grain.

If the grain of the fact is "flash_number", then effectively that means one record per flash. That equates with your "master-fact".

from your example, it looks like each flash can be associated with more than one SplSvcs.

A typical solution would include a bridge table between your fact and the SplSvcs dimension.



LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

Back to top Go down

Handling many-many relationship between fact and dimensions. Empty Re: Handling many-many relationship between fact and dimensions.

Post  Hemapr Wed May 30, 2012 5:16 pm

Instead of bridge table is it a good idea to create a factless fact table for special svcs ? and another fact for all other dimensions where they have 1-m relationships between dimensions - facts and measures ?

But, the grain for both factless fact and fact is the same "flashnumber".


Hemapr

Posts : 12
Join date : 2012-05-15

Back to top Go down

Handling many-many relationship between fact and dimensions. Empty Re: Handling many-many relationship between fact and dimensions.

Post  Hemapr Thu May 31, 2012 12:54 pm

I was reading Kimball's design tip 133 ( http://www.kimballgroup.com/html/11dt/DT133FactlessFactTablesSimplification.pdf) which says factless fact table is a better solution than bridge table if we have many-many relationship between facts and dimensions .

So, i'm really confused in what scenario we should use factless fact table if we have m-m relationship between fact and dimension and in what scenarios we should use the bridge table approach.

I really appreciate if someone can explain me on this as we need to take a decision on the design solution.


Hemapr

Posts : 12
Join date : 2012-05-15

Back to top Go down

Handling many-many relationship between fact and dimensions. Empty Re: Handling many-many relationship between fact and dimensions.

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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