Many to many relationship in a dimensional model

View previous topic View next topic Go down

Many to many relationship in a dimensional model

Post  pran_ita on Mon Aug 11, 2014 1:50 pm

Hello everyone,

This is my first post here. I'm looking for an answer to the following question:

I am aware of the bridge table being the technique used to model many to many relationships between a fact and a dimensional table.

I have a fact table called Claims. This is linked go several dimensional tables, one of them being the drug dimension. I, now have to model the drug ingredients. One drug can have many ingredients and many drugs can have the same ingredient.
Will the bridge table be the only way i can model this.

The reporting requirements are:
1) list of ingredients for a drug (this one is fairly simple)
2) list of all drugs which a particular kind of ingredient
3) members who are taking a particular kind of infredient in their medications and in what quantity?

The third requirement is the most imp one. Member is a dimension which is linked to the claims fact table.
So, we can take a particular member, and then get all the drugs for that member. We then need to see, of all the drugs the member is prescribed, how much quantity of a particular drug (say for eg. Acetaminophen) does the member take??

I needed help in knowing the best possible way this can be modeled which is effeicient even in queries for reporting.

Thanks,

pran_ita

Posts : 2
Join date : 2014-08-11

View user profile

Back to top Go down

Re: Many to many relationship in a dimensional model

Post  ngalemmo on Mon Aug 11, 2014 2:22 pm

A bridge table between drug and ingredient will handle all 3. It is the most efficient way of handling it.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Many to many relationship in a dimensional model

Post  pran_ita on Thu Aug 21, 2014 9:52 am

Thanks for the reply @ngalemmo.

pran_ita

Posts : 2
Join date : 2014-08-11

View user profile

Back to top Go down

Have a very similar problem

Post  martaoliveira on Fri Sep 05, 2014 6:22 am

Hi @ngalemmo, thanks for the help.

I have a similar problem and for what i have read in the forum, the answer i need is also a bridge table.

I have a fact table with services, a dim table with companies and another dim table with coverages that the companies have. One company may have many coverages and, on the other hand, a coverage can be held by many companies.

The additional problem is that my fact table also has other information and i cannot base the numbers on the info contained in the bridge. That is, i can have services that may not need a coverage. If the connection is made solely to the bridge table (without connecting the company to the fact table), i will only have info on my fact table about services that required coverages.

So, the bridge table is naturally the answer.

But, How should i connect the tables to the fact table?


Thanks a lot in advance for all the help,
Marta

martaoliveira

Posts : 1
Join date : 2014-09-05

View user profile

Back to top Go down

Re: Many to many relationship in a dimensional model

Post  nick_white on Fri Sep 05, 2014 10:30 am

Hi - it depends on the grain of your fact table and the relationship between a fact record and a dimension record.

If a service fact record can be related to only 0 or 1 Companies then you join the fact record and the Company Dim directly (your Company Dim will contain 'dummy' records to allow for the unknown/not applicable situation where the service fact is not related to a company)
The same logic applies to service fact/coverage Dim relationship.

Bridge tables are normally used to resolve many-to-many relationships between Dimensions. So you would have your bridge table between your company and coverage tables; the bridge table does not join directly to your fact table. But you only need to do this if one of your dimensions does not have a direct relationship to a fact; if your service fact record can only have 0/1 company and only 0/1 Coverage then both join directly to your fact table and you don't need a bridge table.

Hope this helps?

nick_white

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

View user profile

Back to top Go down

Re: Many to many relationship in a dimensional model

Post  ngalemmo on Fri Sep 05, 2014 11:03 am

What is it you are trying to model? Services or coverages?

If you have a service event where a coverage applies, then coverage is a dimension of the service event. If multiple coverages apply to the event then you either have multiple rows or one row and a bridge to the coverage… depending on how you can treat the measures.

If you are modeling coverages a customer has, this can be done with a factless fact table, or it may be a byproduct of an invoicing fact...
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Many to many relationship in a dimensional model

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