Many to many between fact tables

View previous topic View next topic Go down

Many to many between fact tables

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

View user profile

Back to top Go down

Re: Many to many between fact tables

Post  ngalemmo on 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.
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 between fact tables

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

View user profile

Back to top Go down

Re: Many to many between fact tables

Post  ngalemmo on 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.
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 between fact tables

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