Data Model

[#1]

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);

[marcohadiyanto · Accepted solution]

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