Problem: These are the tables I have in the data model. What is the best approach to link them.
--------------------------- Salesforce -----------------------
OpportunityFact:
%Key1
%Key2
Opp Name
SF_FiscalYear
SF_FiscalMonth
Amount
Account:
%Key1
%KeyFact
Target Client Code:
Target Client
Product:
%Key2
Product Name
Competency
---------------------------------Revenue---------------------------
RevenueFact:
%KeyFact
Account Name
Head Client Name
R_Competency
R_Fiscal_Year
R_Fiscal_Month
Revenue
------------------------------------------------------------
I would like to link Salesforce Tables and Revenue Table based on composite key which includes
From Salesforce Tables:
%KeyFact + Competency + SF_FiscalYear + SF_FiscalMonth
From Revenue Fact Table:
%KeyFact + R_Competency+ R_Fiscal_Year + R_Fiscal_Month
Problem with various tried approaches:
Left Join: If I left join all tables from Salesforce, to create a single fact table it will create duplicates and result in double counting
Associated Link Keys: If I link them, I cannot create a composite key in Salesforce because each of the required fields is sitting in a separate table.
Any help is appreciated.
Sample Script For Testing:
----------------------------------------------------------------------------------------------------------------------------------------------
Opportunity:
Load * INLINE [
%Key1, %Key2, Opp_Name, SF_FiscalYear, SF_FiscalMonth, Amount
AA, 001, Opp1, 2017, Mar, 500
BB, 002, Opp2, 2017, Feb, 200
];
Account:
Load * INLINE [
%Key1, %KeyFact, Target_Client
AA, A001, BMO
BB, B001, Apple
];
Product:
Load * INLINE [
%Key2, Product_Name, Competency
001, Prod1, CompA
002, Prod2, CompB
003, Prod3, CompB
];
RevenueFact:
Load * INLINE [
%KeyFact, HC_Name, R_Competency, R_Fiscal_Year, R_Fiscal_Month, Revenue
A001, BMO, CompA, 2017, Mar, 200
A001, BMO, CompB, 2017, Feb, 400
];
