0

I try to build a galaxy-schema in Amazon Redshift. I started with all the dimensions and with a few workarounds I got them working with SCD2 - Kimball style.

Now I'm trying to get some fact tables working. The facts in our company are very complicated to compute, so I did one "view" for every KPI. I try to illustrate it in an easy way, so you can maybe help me to solve my problem on an easy example: Lets assume these are 3 different views, lets name them: They're already summed up by time.

enter image description here fact_revenue

enter image description here fact_licenses

enter image description here fact_costs

What I'm now trying to achieve is to merge them all together in one big fact table, that contains the information of all three. But I really don't know how to do that:

enter image description here fact_all

Would by nice if you can help me out with this. Thank you so much in advance.

Hawtin
  • 101
  • 10

2 Answers2

2

I'm not sure what syntax redshift supports, but this is the standard pattern

SELECT  
Time,Customer,
MAX(Revenue) As Revenue, MAX(Licences) As Licences,MAX(Costs) As Costs
FROM 
(
SELECT Time,Customer,Revenue,NULL as Licences,NULL As Costs
FROM FactRevenue
UNION ALL
SELECT Time,Customer,NULL,Licences,NULL
FROM FactLicences
UNION ALL
SELECT Time,Customer,NULL,NULL,Costs
FROM FactLicences
) As MyTable
GROUP BY Time,Customer
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
0

Do you have any relationships between the tables? I assume that time and customer columns are foreign keys but lets say if you have one more data with the same values, your select will be duplicate . If you dont have data with same values you can use this statement :

select * from fact_revenue rev 
left join fact_licences li on li.time=rev.time and li.customer=rev.customer
left join fact_costs co on co.time=rev.time and co.customer =rev.customer
bsk
  • 46
  • 2
  • 1
    Hi and thank you for your answer. A left join is only an option if the main fact table holds all of the possible values for any dimension foreign keys. In your statement, I would have lost the information about the date "2020-03-31". Thats actually my main problem. I don't hold a table with all the possible combinations of my dimension keys - so I need to set them together form all the fact tables I have – Hawtin Apr 21 '20 at 13:05