1 Reply Latest reply: Aug 9, 2016 5:20 AM by MARCO HADIYANTO RSS

    Data Model

    Govind Ramchetty

      HI

      Can you please suggest me how can i achieve my requirement.

      MY Requirement is

       

      INVOICE.IN_KEY=Invoice_Line.IN_KEY.

      and INVOICE.IN_KEY=GL_FACT.IN_KEY

      and INVOICE.IN_KEY=EX_Fact.IN_KEY

       

      But my default data model loader taking circular join.How can achieve above one.

       

      [GL_Fact]:

      LOAD [GL_Fact_Invoice_No] AS IN_KEY,

        [GL_Total],

        [Year]

      FROM [lib://Datasources/WO_Expense.xlsx]

      (ooxml, embedded labels, table is GL_Fact);

       

      [EX_Fact]:

      LOAD [Invoice_Key_Expense] AS IN_KEY,

        [WO_Key_1] AS WOKEY,

        [Exp_amt]

      FROM [lib://Datasources/WO_Expense.xlsx]

      (ooxml, embedded labels, table is EX_Fact);

       

      [Invoice ]:

      LOAD [DIM_Invoice_No] AS IN_KEY

      FROM [lib://Datasources/WO_Expense.xlsx]

      (ooxml, embedded labels, table is [Invoice ]);

       

      [Invoice_Line_Item]:

      LOAD [Expese_Fact_Invoice_No] as IN_KEY,

        [WO_Fact_Key]

      FROM [lib://Datasources/WO_Expense.xlsx]

      (ooxml, embedded labels, table is Invoice_Line_Item);

        • Re: Data Model
          MARCO HADIYANTO

          Hi,

          I think you should join invoice line item and invoice, then concatenate with ex_fact.

           

          [Invoice_Line_Item]:

          LOAD [Expese_Fact_Invoice_No] as IN_KEY,

            [WO_Fact_Key]

          FROM [lib://Datasources/WO_Expense.xlsx]

          (ooxml, embedded labels, table is Invoice_Line_Item);

          left join

          [Invoice ]:

          LOAD [DIM_Invoice_No] AS IN_KEY

          FROM [lib://Datasources/WO_Expense.xlsx]

          (ooxml, embedded labels, table is [Invoice ]);

          concatenate

          [EX_Fact]:

          LOAD [Invoice_Key_Expense] AS IN_KEY,

            [WO_Key_1] AS WOKEY,

            [Exp_amt]

          FROM [lib://Datasources/WO_Expense.xlsx]

          (ooxml, embedded labels, table is EX_Fact);

           

          [GL_Fact]:

          LOAD [GL_Fact_Invoice_No] AS IN_KEY,

            [GL_Total],

            [Year]

          FROM [lib://Datasources/WO_Expense.xlsx]

          (ooxml, embedded labels, table is GL_Fact);

           

          Regards,

          Marco