6 Replies Latest reply: Jun 1, 2017 2:25 PM by Abhay Sudhakaran RSS

    Joining Multiple Fact Tables

    Abhay Sudhakaran

      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

      ];