plsqlamazon-redshiftdata-warehousefact

Merging Fact-Tables in Amazon Redshift

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.

Solution

  • 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