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

Many to many between fact tables

2 posters

Go down

Many to many between fact tables Empty Many to many between fact tables

Post  ddesjard Thu Aug 21, 2014 3:42 pm

We often see many to many relationship between a fact table and a dimension, but is it possible to have a many to many relationship between two fact tables? If so, how can we model it? I have a reflex of using a inetmediate table with the two fact tables keys, but in a case of a data warehouse, is it performant?
My case is a fact table with invoices related to dimensions (date/time, supplier, etc) and a payment fact table. I need to link both fact tables as an invoice can have many payment, an a payment can be applied to many invoices.
I hope it's clear.
Thanks in advance.
Denis Desjardins

ddesjard

Posts : 2
Join date : 2014-08-21

Back to top Go down

Many to many between fact tables Empty Re: Many to many between fact tables

Post  ngalemmo Thu Aug 21, 2014 9:51 pm

In a dimensional model you do not model relationships between fact tables.

Fundamentally, any relationship between fact tables is assumed to be many to many.  Facts are combined by aggregating the facts along common dimensions and joining the two aggregate sets.  An alternate method is to union the facts.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Many to many between fact tables Empty Re: Many to many between fact tables

Post  ddesjard Fri Aug 22, 2014 8:18 am

ngalemmo wrote:In a dimensional model you do not model relationships between fact tables.

Fundamentally, any relationship between fact tables is assumed to be many to many.  Facts are combined by aggregating the facts along common dimensions and joining the two aggregate sets.  An alternate method is to union the facts.

I'm not sure I understand the concept clearly. Suppose I have this structure:

Fact1: Invoice (Attributes: Invoice # and Amount)
Dimensions: Time (Invoice Date), Supplier and Ressouce (the employe who processes the invoice).

Fact2: Payment (Attributes: Invoice # and Amount)
Dimensions: Time (Payment Date), Supplier and Payment Method for example

In that case, I can join the two fact tables using the Invoice #, so I can know which payments apply to an invoice, an consequently a payment is applied to which invoices. But for performance isssue, do I need to have a physical aggregation table, or we do the join in querys when building the reports or the dashboards?

Thanks.

ddesjard

Posts : 2
Join date : 2014-08-21

Back to top Go down

Many to many between fact tables Empty Re: Many to many between fact tables

Post  ngalemmo Fri Aug 22, 2014 12:41 pm

You could do it either way. Usually you first test to see how a join query performs. If it doesn't suit your service levels you then consider creating an aggregate table. It really depends on how often such a query is performed. If it is a common, frequent query, an aggregate can be very useful.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Many to many between fact tables Empty Re: Many to many between fact tables

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