Joining two fact tables with common dimensions

Question

I get the concept on how to join dimensions to a fact table. However, I am having some difficulties in joining multiple (two) fact tables through common dimension tables.

To be specific, I have a fact table with actual sales figures, and a fact table with budget figures. Each fact table share two dimension tables (a customer table and an item table)

My question is, how do I join the two fact tables through the two dimensions tables?

Please see the attached, simplified example below, which illustrates the result set I am looking for.

Table 1 and 2 are fact tables and table 3 and 4 dimension tables. Table 5 the desired result set.

Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer — , Apr 23 '18 at 18:35
Possible duplicate of [Required to join 2 tables with their FKs in a 3rd table](https://stackoverflow.com/questions/23839392/required-to-join-2-tables-with-their-fks-in-a-3rd-table) — philipxy, May 05 '19 at 19:57

score 0 · Answer 1 · answered Apr 23 '18 at 18:29

You join them on the common keys:

select . . .
from actualsales a join
     budgetsales b
     on a.customer = b.customer and a.item = b.item;

You may want some sort of outer join, if you want to keep rows missing in one table or the other.

This is a very unrealistic example. Even as an example for a class, the columns are very poorly named. More importantly, there is no time dimensions -- something that almost all fact tables have.

Wyatt Shipman · Answer 2 · 2018-04-23T18:41:17.923

You can start with the Customer (dimension) table and join the fact tables to it:

SELECT C.NAME Customer,
    iT.Name Item,
    SUM(a.Amount) Actual_Amount,
    SUM(b.Amount) Budget_Amount
FROM Customer C
INNER JOIN BUDGET B
ON C.CUSTOMER_ID=B.CUSTOMER_ID
INNER JOIN ITEM iT
ON B.ITEM_ID=iT.ITEM_ID
LEFT JOIN ACTUAL A /*THIS WILL RETURN BACK ROWS ON ACTUAL IF THEY EXIST*/
ON A.CUSTOMER_ID=B.CUSTOMER_ID
AND A.ITEM_ID=B.ITEM_ID
GROUP BY C.NAME, iT.nAME

You could also union the list of customers and item_ids from the Budget and Actual tables to get the unique list of customers and items as your starting point. That would save you from dropping items that didn't exist on one or the other fact table.

WITH CTE_CUST_ITEM_FACT AS (
SELECT CUSTOMER_ID, ITEM_ID FROM ACTUAL
UNION
SELECT CUSTOMER_ID, ITEM_ID FROM BUDGET
)
SELECT * 
FROM CTE_CUST_ITEM_FACT
/* JOINING ACTUAL, BUDGET, ITEM, CUSTOMER BACK TO THIS LIKE ABOVE */

score -1 · Answer 3 · answered May 05 '19 at 18:10

-1

Fact to fact joining is not advisable. Create a fact object or write two seperate queries and finally create a logical set

answered May 05 '19 at 18:10

Karthick Raj

1
2

The question says, "joining multiple (two) fact tables through common dimension tables". Not "Fact to fact joining". Also, this answer is too brief to understand--please explain. Also, there are many approaches/methods for fact-dimension models--reference yours. – philipxy May 06 '19 at 03:24

Joining two fact tables with common dimensions

3 Answers3