Facts Concatenation

[nareshthavidishetty]

Hi,

We are having two fact tables of huge dataset.

Fact1:

Supplier_ID,Plant_ID,Calendar_Date,Material_ID,Sales

From XXX;

Cancatenate(Fact1)

Fact2:

Plant_ID,Material_ID,Calendar_Date,Count_of_Parts_Consumed;

From XXX;

When i try to Pull Sullpier_ID and count(Count_of_Parts_Consumed) in a straight table I haven't get any detail.

Fact2 Table is aggregated by month in backend and Fact1 is day level.

Our datmodel is strict to use concatenation when we have two fact tables.

Thanks..

[nareshthavidishetty]

Hi Jonathan,

After using the approach as said earlier  we are getting the values as below attached.

Thanks..

[amarnath_krishn]

Count(Parts_consumed) as stated above comes from a different fact unrelated to Supplier ID.

You might have to get the fact2 values aggregated at supplier level in the fact, if you have to represent the values at supplier level.

[nareshthavidishetty]

Hi,

Is there any smart way.

Rules to be followed in data model.

Due to more data(Ex: for only 6 months we have 30 million it might be increase more day by day) if we have two facts we need to do concatenation instead of join.

Thanks..

[amarnath_krishn]

Based on the information shared above, the 2 facts are having just a relationship based on the keys:

[Plant ID], [Material ID], and [Plant Material ID].

However, the second fact is an aggregated one at a bit higher level and for a said month, there could be multiple suppliers involved. So, you cannot use applymap or join to get supplier level parts count. 

So, if you really want the straight table built as stated above  - please get the second fact aggregated at supplier level in the backend for each month and concat it to the first fact.

[Anonymous]

Hi,

It seems to be two fact tables with different granularity. You can refer below link if it is helpful to you.

Fact Table with Mixed Granularity

Thanks,

Harshit

[nareshthavidishetty]

Hi,

All were might be correct.

But for final luck..

Please find the attached Sample data.If any one got answer please provide script.

I don't have access to open QVW's.

Thanks..