February 18, 2010 at 2:28 pm
hi!
i have 6 tables that contain data that has to be sum or counted: t1, t2, t3, t4, t5, t6
All tables relate to t1.
How do i join them to form 1 Fact Table?
t1:
t1.id - Needs to be counted
t1.Amount - needs to be sum
t2:
t2.id - Needs to be counted
t2.Amount - needs to be sum
t2.t1id
t3:
t3.id - Needs to be counted
t3.Amount - needs to be sum
t3.t1id
and so on...
iv'e tried to join them with Full Outer but then the cube sums and counts double and triple and more on some records.
thanks!
February 19, 2010 at 12:48 am
Can you try the following query:
SELECT tt1.ID, tt1.ID_Count, tt1.Amount, tt2.ID_Count, tt2.Amount, tt3.ID_Count, tt3.Amount -- and so on...
FROM
(SELECT ID, COUNT(ID) AS ID_Count, SUM(Amount) AS Amount
FROM t1
GROUP BY ID) tt1
INNER JOIN
(SELECT t1ID, COUNT(ID) AS ID_Count, SUM(Amount) AS Amount
FROM t2
GROUP BY t1ID) tt2
ON tt1.ID = tt2.t1ID
INNER JOIN
(SELECT t1ID, COUNT(ID) AS ID_Count, SUM(Amount) AS Amount
FROM t3
GROUP BY t1ID) tt3
ON tt1.ID = tt3.t1ID
INNER JOIN
-- and so on...
I used Inner Joins, but depending on how you want to treat missing values you can use Left Outer Join where appropriate.
Let me know if it works...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 19, 2010 at 6:50 am
Koen Verbeeck:
i need to be able to filter on all the tables so i can't sum or count in the view.
i need all the records in the cube.
thanks for your reply!
February 19, 2010 at 7:09 am
Not sure I completely follow your requirement to 'filter every table' but one approach, that will give you the full recordsets from each table, could be written like ...
SELECT ID, COUNT(ID) AS ID_Count_1, SUM(Amount) AS Amount_1, 0 AS ID_Count_2, 0 AS Amount_2 FROM t1 GROUP BY ID
UNION ALL
SELECT t1ID, 0 AS ID_Count_1, 0 AS Amount_1, COUNT(ID) AS ID_Count_2, SUM(Amount) AS Amount_2 FROM t2 GROUP BY t1ID
So, notes on this code:
- it is obviously working across only the first two tables (i got lazy), but the logic holds for all six, simply add more columns to each query, making sure you zero out the columns *not* related to the current table
- you could leave it 'as is' and allow SSAS to aggregate on the [t1]ID, alternatively, wrap the entire thing in another query, where you group on ID and then sum every field. This has the effect of 'merging' the records, per ID, but keeping the totals values for each particular 'section' (ie 2, 3, 4 etc).
HTH,
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply