CODEWITHSUNDEEP
sqljoindimensionsfact-table
Thomas Ejlerskov
Thomas Ejlerskov

Reputation: 21

Joining two fact tables with common dimensions

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.

Upvotes: 0

Views: 13490

Answers (3)

Karthick Raj
Karthick Raj

Reputation: 1

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

Upvotes: -1

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

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 */

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270763

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.

Upvotes: 0

Related Questions