Many to many between fact tables
2 posters
Page 1 of 1
Many to many between fact tables
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
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
Re: Many to many between fact tables
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.
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.
Re: Many to many between fact tables
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
Re: Many to many between fact tables
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.
Similar topics
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum