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..

[Anonymous]

Hi Naresh,

    As a first thing try to add some identifier (flag)  in bot the tables. And pull the required fields and check the data by selecting flag fields.

some thing like below

Fact1:

Supplier_ID,Plant_ID,Calendar_Date,Material_ID,Sales,1 as Flag

From XXX;

Cancatenate(Fact1)

Fact2:

Plant_ID,Material_ID,Calendar_Date,Count_of_Parts_Consumed,2 as Flag;

From XXX;

Also  convert the Date in to Month level if you need

-Jai

[madhumitha]

Hi,

In the above example, you have only Plant_id as common field.

if you pull Plant_ID and  count(Count_of_Parts_Consumed), you will see values.

However, all the values of Sullpier_ID will be null for Count_of_Parts_Consumed.

Since there is no evaluated expression the straight table wont show any values.

Hope this helps.

Thanks!

[Anil_Babu_Samineni]

Can you share full version of expression you used?

[nareshthavidishetty]

DIm : Supplier_ID

Exp: =Sum(Sales) / count(Count_of_Parts_Consumed)*1000000

[devarasu07]

Hi

try to create composite key like below way and it should work.

Fact1:

Supplier_ID,

//Plant_ID,

//Calendar_Date,

//Material_ID,

Sales,

Plant_ID &'|'& Date(Calendar_Date)  &'|'& Material_ID as %Key

From XXX;

Cancatenate(Fact1)

Fact2:

//Plant_ID,

//Material_ID,

//Calendar_Date,

Count_of_Parts_Consumed,

Plant_ID &'|'& Date(Calendar_Date)  &'|'& Material_ID as %Key

From XXX;

in addition also check this article,

Fact Table with Mixed Granularity

Thanks,

Deva

[Anil_Babu_Samineni]

And your data model ??

[PrashantSangle]

Hi,

as suggested by Madhumita both field i.e. supplier id and Count_of_Parts_Consumed are coming from 2 different fact table.

So they are not interrelated in your data model, hence you are getting 0.

Regards,

[jonathandienst]

>> DIm : Supplier_ID

>> Exp: =Sum(Sales) / count(Count_of_Parts_Consumed)*1000000

The Supplier_ID dimension will only associate with the records loaded as Fact1. The records containing Count_of_Parts_Consumed are excluded. Perhaps partial totalling would work:

Sum(Sales) / count(TOTAL <Plant_ID, Calendar_Date, Material_ID> Count_of_Parts_Consumed)*1000000

If that does not work, you may have to relook your datamodel

[nareshthavidishetty]

Hi,

This is how the fact tables got concatenated.

Fact1 is to get get the details of parts which are not good.

Fact2 is for how many parts consumed.

[Fact Quality Parts]:

LOAD text(Notification_Id) as [Notification ID],

text(Disposition_Code) as [Disposition Code],

Conformed_Cause_Code as [Conformed Cause Code],

Conformed_Location_Code as [Conformed Location Code],

text( Plant_Id) as [Plant ID],

Disposition_Code_Group as [Disposition Code Group],

text( Material_Id) as [Material ID],

text(Plant_Material_Id) as   [Plant Material ID],

text(QNR_Reporting_Unit_Code) as [QRU Code],

text(Supplier_Id) as [Supplier ID],

Manufacturer_Id as [Manufacturer ID],

date(Calendar_Date,'DD-MMM-YYYY') as [Calendar Date],

month(Calendar_Date) as Month,

Nr_Of_Quality_Parts as [# QNs]

FROM [..\5_QVD\Notification.qvd] (qvd);

Concatenate([Fact Quality Notification])

[Fact Material Parts]:

LOAD Calendar_Month as [Calendar Month],

Calendar_Year&''&Calendar_Month as [Year Month],

Calendar_Year as [Calendar Year],

Date(Floor(MonthEnd(Date#(Calendar_Year & '-' & Calendar_Month, 'yyyy-MM'))), 'DD-MMM-YYYY') as [Calendar Date],

Month_Material_Movement_Count as [Monthly Material Movement Count],

Conformed_Movement_Type as [Conformed Movement Type],

text(Plant_Id)  as [Plant ID],

text(Material_Id) as [Material ID],

Plant_Material_Id as  [Plant Material ID]

FROM [..\5_QVD\Movement.qvd] (qvd);

Thanks..