Stock date and sales date in data model

[PapaJapa]

Hi, I have some tables: sales, stock, purchases and products. In each of them (wo products of corse) I have transaction date (or stock date). How to combine these tables with the calendar? When I create three separate keys in the calendar, a loop is created in the data model 😞  Thanks.

[nisha_rai · Accepted solution]

Hi,

Create a Bridge date table/fact table  which will connect with the Master Calendar.

Example:

Link_Table_Temp:
Load
Distinct
%key_cloud ,
Date("Cloud_Date") AS Date,
Actual_DeptCode AS Department_code,
Actual_Department AS [Cloud Department],
Actual_Workload AS Workload,
Actual_Vendor AS Cloud_Vendor,
Actual_Account as account

Resident table1;

Concatenate (Link_Table_Temp)

Load
Distinct
%key_Forcloud 
Date(ForecAStDate) AS Date,
ForecASt_DeptCode AS Department_code,
ForecASt_Department AS [Cloud Department],

Resident table2

Concatenate (Link_Table_Temp)

Load
Distinct
[%key_hyperion]  ,
Date(perioddate) AS Date,
department AS Department_code,
account

Resident table3

Concatenate (Link_Table_Temp)

Load Distinct
[%key_expense]  ,
Expense_Vendor AS [Vendor Code],
GLACCOUNT as account,
Date(EXP_CLOSEDATE) AS Date,
EXP_GLDEPARTMENT AS Department_code

Resident table2;

now Date key should link with Master Calendar