You have 3 different dates :
- Produced Date
- Ordered Date
- Stock Level Date
This sounds like a classic use case for a Canonical Date as described by HIC in his blog post.
ps. I'd look at concatenating your 3 Fact tables as opposed to joining them.
Am not missing but My Dates are literally of different attributes, hence if I do it as a join, I will be treating them as the same which is not.
Canonical suggest, creation of a Master calendar, which will allow users to select Product quantity produced on certain Date, and sold on certain date. That way I can get all the 3 plotted in the same axis, other wise the join treats the dates as if they are of same attribute.
No, no unique date field. As for example some products are produced on same date but ordered on different dates and stock level taken on different dates.
While some have same order dates but different production dates and different stock level taking dates..
So the dates are not the same at all (not unique in anyway)
But some are actually the same.
Andrea: Your are correct, as I suggested to Michael earlier I'd look at concatenating your 3 Fact tables as opposed to joining them, and HIC's blog post Canonical Date suggests multiple calendars.
Michael: The first time I read the blog post it took me a few reads to fully understand it, so maybe make yourself a cup of tea to sip whilst you are sussing it.
Am still struggling with creation of Master calendar gives me an error on load. Says DateID not found yet I defined it
Script for first Table master calendar. Anybody can help with this
All my Dates have to be converted with Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [RecordDate]
LIB CONNECT TO 'GetWarehouse';
FROM JSON (wrap on) "root";
LOAD [itemDirectoId] AS [Product_Id],
Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00')) AS [RecordDate],
[summedQuantity] AS [summedQuantity]
//Creation of master calendar starts here
min(Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00'))) as [MinDate],
max(Timestamp(ConvertToLocalTime((25569 + Round([ts]/1000) / 86400),'GMT+02:00'))) as [MaxDate]
LET Start = floor(YearStart(peek('MinDate')));
LET End = floor(YearEnd(peek('MaxDate')));
Drop Table tmp;
LET NumOfDays = End - Start + 1;
$(Start) + Rowno() -1 as [DateId]
date(DateId) as [Date],
day(DateId) as [Day],
week(DateId) as [Week],
month(DateId) as [Month], // simple month name; it is dual - numeric and text
dual(month(DateId) & '-' & year(DateId),
year(DateId) & num(month(DateId), '00')) as MonthYear, // Month-Year format, dual
year(DateId) as Year,
weekday(DateId) as Weekday,
'Q' & ceil(month(DateId)/3) as Quarter // in format Q1, Q2, Q3, Q4
Drop Table Date_src;
DROP TABLE Stockshit;