Dates in multiple Fact tables

[Anonymous]

Hi All

I have few different dates coming from different fact tables in my Data model and I am trying to link those dates with the Master calendar.

Can anyone please give the solution on linking those dates.

[sunny_talwar]

Check here

Canonical Date

[jonathandienst]

Assuming that date is one of several possible dimensions that might apply to multiple facts tables, I think you would need a link table.

[Anonymous]

Can you please elaborate on date is one of several possible dimensions that might apply to multiple facts tables?

[jonathandienst]

Well you said you had multiple fact tables. I don't know what dimensions you have other than date, but you may have something like an organisation dimension, or a people or a product dimension shared by more than one fact table. Then you need a link table.

[Anonymous]

That's true, the fact tables are linked on different dimensions for instance, Purchase Order table is linked to Vendor Dimension on Vendor Id and to Requisition Dimension on Requisition Id where Purchase Order has PO Date and Requisition Dimension has Requisition Date.

So, how these 2 dates can be linked to the master calendar?

also, can you please share the sample link table.

[bhaskar_sm]

Hi,

Please find below snap of code for implementing Date Bridge which will help you handle multiple date.

// Load different Date Fields from diff fact tables and combined into one table ..

//DT is a common date which is going to link with your Master Calandar

// DTTYPE is a field which is required for you to identify the  diff fact table days - in later you can use this in Setanalysis

[Date Bridge]:

LOAD

[Sales Order Date],

[Sales Order Date] as DT,

Month([Sales Order Date]) as Month,

'SaleOrderDate' as DTTYPE

Resident Table1;

Concatenate

LOAD

[Purchase Order Date],

[Purchase Order Date] as DT,

month([Purchase Order Date]) as Month,

'PurchaseOrderDate' as DTTYPE

Resident Table2;

Concatenate

LOAD

[Forecast Month],

makeDate(Date#([Forecast Month],'MMM'),2018,1) as DT,

Month(Date#([Forecast Month],'MMM')) as Month,

'ForecastDate' as DTTYPE

Resident Table3;

please let me know if you have any query.

if you found this helpful, please mark as helpful or correct.

Regards,

Bhasker Kumar

[Anonymous]

Bhasker I tried your solution but on testing it I found the problem with the Invoice Date Flag as it is not getting linked with the master calendar as attached.

Please help with this.

[bhaskar_sm]

Hi,

you need to check the date format of Invoice date that should be same as per your master calendar and other fact table dates.

if possible kindly share the sample data.

Regards,

Bhasker Kumar

[Anonymous]

I checked there is no date format problem. I used this format for all the dates:

Date("PO Date",'DD/MM/YYYY')