Circular Reference-Actual and Target fact tables

[#1]

Hello! my question is about circular references in loading script . I have 2 fact table from excel (Actual and target) and 1 dim table from sql,  by loading data the circular reference error appear and table viewer shows such diagram:

i have seen posts about circular references but it is not effective for me on this model . i need simple data model to solve this problem .

Thanks in advance for any help on this!

[marcus_sommer]

I would very probably concatenate the target- and actual-tables and adjust the YearMonth from the target to a date with something like:

makedate(Year, Month, 1) as Date8

you might need some further functions to extract year and month from the YearMonth field.

- Marcus

[Anonymous]

Create a link table by pulling out Date8 field from DimTime & Actual , OrgID field from Target & Actual table .

Then drop these fields by dropping from original table .

Or you can rename the fileds .

[marjan_it]

you can use left join in order to your basic fact in your script and then loop error will be solved!

[#5]

My dummy method: Rename the columns in two of your three tables, and they will not link automatically! Hope this helps.

[jansen28]

I would probably choose to concatenate both fact tables considering the volume. And use date function to convert Yearmonth. You could also create a flag to differentiate Actual Vs Target.

[#7]

Thanks for the prompt reply .

I think this approach will work , but i am really confused  in this case . can you help me about writing link table script.

Thanks in advance,

masood

[Anonymous]

Please see the Script ,

DimTime:

LOAD YearMonth,

     Date8,

     CalenderQuarter,

     CalenderYear

FROM

(ooxml, embedded labels, table is Sheet1);

Target:

LOAD OrgID,

     YearMonth,

     OrgName,

     TargetAmount

FROM

(ooxml, embedded labels, table is Sheet2);

Actual:

LOAD Date8 as Date_8,

     OrgID,

     Amount

FROM

(ooxml, embedded labels, table is Sheet3);

LinkTable:

load YearMonth as YearMonth,

  Date8 as Date8

Resident DimTime;

Concatenate

LOAD OrgID

Resident  Target;

drop Field YearMonth from DimTime ;

drop Field OrgID from Target;

Hoping that your Fields are distinct & common between the tables .