Handling many-many relationship between fact and dimensions.

View previous topic View next topic Go down

Handling many-many relationship between fact and dimensions.

Post  Hemapr on 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

View user profile

Back to top Go down

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

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

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

Post  Hemapr on 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

View user profile

Back to top Go down

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

Post  Hemapr on Wed May 30, 2012 1:16 pm

any suggestions?

Hemapr

Posts : 12
Join date : 2012-05-15

View user profile

Back to top Go down

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

Post  LAndrews on 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

View user profile

Back to top Go down

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

Post  Hemapr on 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

View user profile

Back to top Go down

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

Post  Hemapr on 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

View user profile

Back to top Go down

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

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