Can we join to 2 fact tables directly ?

As per best practice for dimensional data modeling it is always suggested that 2 fact tables should never be joined directly (i.e PK and FK join) instead should only be joined through conformed dimensions. I want to understand the downside of joining to fact tables directly in Header - Line item scenario where header fact can be joined to line item fact.

I am asking this because I have seen many cases where detailed report required data coming from 2 separate fact tables where transaction captured in one fact happens before transaction captured in second fact. And detailed report require showing some attributes of first transaction (fact 1) and second transaction (fact 2) in same line.

Please clarify my understanding on such scenarios.

Regards,
Abhiraizada

From a best practice point of view, given any query that involves two fact tables, you must assume the relationship between those tables is many-to-many. The reason the two tables are aggregated on common dimensions before joining is to eliminate the possiblility of a many-to-many relationship.

You can avoid doing so only if you have specific knowledge of the query and content of the tables, which is not usually the case, particularly in an ad-hoc environment. So, the aggregate & join pattern will always work regardless of the table's contents.

I agree with Nick, but if you don't know the grain of the fact table(s), get out of my DW!

ngalemmo wrote:From a best practice point of view, given any query that involves two fact tables, you must assume the relationship between those tables is many-to-many. The reason the two tables are aggregated on common dimensions before joining is to eliminate the possiblility of a many-to-many relationship.

You can avoid doing so only if you have specific knowledge of the query and content of the tables, which is not usually the case, particularly in an ad-hoc environment. So, the aggregate & join pattern will always work regardless of the table's contents.

Thanks for this clarification..i understood the point of m:m relationship between facts, but the scenario i am talking about is slightly different its in similar lines to that of Header and line, where transactions in 2 fact tables are related by 1:m relationship. And this is never going to change. Two approaches which I have tried in my designs are -

1). De-normalizing all the necessary attributes from first transaction fact into second transaction fact which will remove all possibilities of joining these 2 fact tables in any scenario and will suffice all the detailed reports (line reports) which require data coming from these 2 fact tables.

2). Create a small dimension out of first transaction fact, which will result in 2 tables - transaction fact and transaction dimension ( having only limited set of attributes based on reports analysis - I know not the best ways to handle these req.) and this transaction dimension is connected to other fact tables as proper dimension. I have seen this working well in terms of performance.

Any thoughts ..

Regards,
Abhiraizada

BoxesAndLines wrote:I agree with Nick, but if you don't know the grain of the fact table(s), get out of my DW!

It would be helpful if you can explain your thoughts.

Regards,
Abhiraizada

Ok. Here is an example and I will use a very, very simplified view of a typical retail store chain. (This is just an example, any reference to 'you' is a fictious reader, not you personally.)

Customer goes into a store and buys a product. The customer purchases some quantity of the product and pays for the purchase. This is represented as a single row in a sales fact table. The measures captured from this business event is the quantity purchased and how much (value) the sale was worth. The dimensions for this fact are the date the sale occured, the customer who made the purchase, the store where the purchase occured and the product that was purchased.

Now, this chain has an inventory system that provides the cost of goods on any given day for any store. This fact table contains a single measure: the cost of goods. It has three dimensions, the date the cost of goods was calculated, the store, and the product. It is a snapshot table and calculates the averge cost for a single unit of the product once a month. The cost applies to any sale during the month specified in the date. This table is maintained to ensure there is only one row per month, per store, per product.

They then decide to create a gross net revenue report. Gross net revenue is calculated by subtracting the cost of goods from the value of the sale. The cost of goods is calculated by multiplying the cost stored in the cost of goods table and the quantity sold on the sales fact table.

So, since both facts are needed to generate the report, you decide to join the tables directly.

The first report you build shows net gross sales by month, by store by product. You check out the results and everything looks good. The numbers add up and everybody is happy.

The, the users ask for a new report, this time they just want it by month and product. They call it the "All Stores" report which is special and goes to the highest executive levels. So, you take the old report and change the join a bit (now just using date and product), do a quick lookover and fire it off to the executives. Well the, Sales VP is very surpised to see that sales have jumped exponentially since the previous month. The CAO is puzzled because the costs or revenue don't tie to any of GL numbers, and the CIO is getting ready to whoop a## in the IT department.

What happend?

The first report happened to match the grain of the cost table. So the join is only one to many: one cost row to many sales rows, so the numbers add up correctly.

The second report, however, doesn't match the grain of either fact. There are many rows in the cost table for any given month and product... one row per store, in fact. As well as many rows in the sales fact. So you wind up with a many to many relationship that inflates the totals relative to the number of stores that sell that product.

So, to avoid this issue, each fact is first aggregated to the grain of the report and then a do join (inner or outer as necessary) on the two aggregation subqueries. The aggregation of both facts on the same dimensions guarantees a 0/1:0/1 relationship between the two tables.

Hi,

@ngalemmo
excellent case scenario provided....However there is one concern that i have.
In case i have the two fact tables also connected to dimensions. Would'nt the resultant query cause a loop join?
In that case, how can we overcome the issue to satify the reporting requirement.

Y.allawala

How do you get a loop? What tool are you using?

After aggregating the facts, they are joined on the dimension keys. You would also join to the dimension if needed, but why would you join both facts to the same dimension?

Thank you.

I am using IBM Cognos 10 and modelling a purchase cycle.

Taking a similar scenario, we have
2 dimension tables
Time
Product

2 fact tables
Order (Date,OrderNo,Product,OrderQuantity)
Receiving (Date,ReceiptNo,OrderNo(FK),Product,ReceivedQuantity)

Relationships
Order (1..1) ------------------------ (1..n) Receiving

For the reporting Requirement to find out the following
1. 'Total Ordered Quantity in a Month by Product'
2. 'Total Received Quantity in a Month by Product'
3. 'Total Received Quantity against Ordered Quantity in a Month by Product'

We require to model our dimensions and facts as in the snapshot (attachment)
To fulfill the 3rd requirement, we query Order,Time,Receiving.
This forms the loop. Now, which path will dimension (Time) take to fill the requirement?
(Also note that there can be Products receiving against Orders that were generated last month)

(i am unable to attach snap shot image file)

Use aliases…

As far as a net revenue report goes, you would use revenue from invoicing and cost from inventory. Orders are not revenue and receipts in a month is not a correct method to calculate cost of goods sold (unless its build to order and materials are purchased for a specific order).

But regardless, you summarize revenue by month & product and summarize cost by month & product and combine the two.

In the case of build to order, you would tie the costs to the order (sum costs by order, sum revenue by order, join on order) and summarize on invoice date.

you are right, using separate aliases for dimensions for each of the fact tables will resolve the issue, but it would clutter the model and result in great confusion for the report author for ad-hoc reporting and for model maintenance. (suppose there are 10 dimensions and 4 facts (with each fact connected to each other), i would be required to create aliases for 10 dimensions for each of the 4 fact).... that's a lot to swallow...

Can there be a more direct approach?