Confused about many to many

View previous topic View next topic Go down

Confused about many to many

Post  Ikaros on Wed Nov 05, 2014 5:03 am

Hello folks,

I am working on a modeling problem involving a many to many relationship for which I would be grateful to receive insights.

The business process i am modeling is the administration of funding applications, each of which has up to five science branches as attributes. The analytical interests are in finding out the average count of science branches listed for the applications, on which applications certain branches co-exist etc.  The data warehouse feeds SSAS cubes as user interface.

So, I guess the standard solution offered for this situation is to create two "helper tables", science branch group and science branch bridge and model the relation as application -> science branch group <- science branch bridge -> science branch dimension

However, someone suggested that you could drop the science branch group -table from the model altogether, instead modeling the data like this:

application <- science branch bridge -> science branch dimension

Are both of these models plausible and are there some pros and cons involved with either of these designs?

Best Regards,

Antti

Ikaros

Posts : 4
Join date : 2013-10-11

View user profile

Back to top Go down

Re: Confused about many to many

Post  ngalemmo on Wed Nov 05, 2014 12:56 pm

Both will work.

However, some clarification… Science Branch Group is a junk dimension, not a helper table. It is not actually used in queries, but serves as a means to identify unique combinations of branches. Doing so reduces the size of the bridge table when it can be expected that specific clusters of branches re-occur frequently.

The alternate method is you assign a unique key to each application row and have a bridge entry between the application and the branches. This is easier to load.

The difference between the two approaches is the size of the bridge table and load complexity. Wither that is a problem depends on how many applications you deal with and how often specific clusters of branches repeat.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Confused about many to many

Post  Ikaros on Thu Nov 06, 2014 6:18 am

Thanks for your help ngalemmo, it clarified my thoughts on the subject As the data volumes in our DW are very manageable, I think we will just link the bridge table with the surrogate key of the application, and drop the group table...

Ikaros

Posts : 4
Join date : 2013-10-11

View user profile

Back to top Go down

Re: Confused about many to many

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