I have a question related to the actual benefit of having a Master Calendar. The discussion came about because one of my colleague recently created a Master Calendar not based on date, but based on a TimeStamp field. And after creating the calendar, he did a left keep to drop of the new timestamps he just created. I argued with him that what is the point of creating an extra table when he is going to do a left keep. He can do the same within the fact table. To this he argued that all the date, month, year calculations can be separated out in a different table (makes sense), but aren't we taking extra steps for something we can do it within the fact table?
Real Question: I have been using master calendar for a while and I don't use left keep, but what is the benefit of creating a master calendar? We don't even use the dates which are not available in our fact table, do we?? Is there a hidden benefit that we are missing by having all dates in our application.
Agree with MRKachhiaIMP 100%. Unless you have a one-table "data model" and you know for sure that you'll never use date as dimension in charts, you have nothing to loose using Master Calendar. To repeat the advantages:
1. Have the full list of dates in the Calendar range. Speaking of range (and going back to your "left keep" - typically we create Calendar before loading Facts data, and when loading Facts we use Calendar range as condition, hence filtering out the facts outside the required range.
2. Avoid multiplication of the same data in Fact table.
I have been usually creating the master calendar using the max and min date from the fact table. I thought that was the way to do it. But you brought another interesting thing forward: To create the master calendar before the fact table. So far this is what I have been doing:
Temp: //Using the fact to extract min and max date
LOAD Min(Date) as minDate,
Today() as maxDate
LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(vMinDate) + IterNo() - 1 as Num,
Date($(vMinDate) + IterNo() - 1) as TempDate
1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
TempDate as Date,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
'Q' & ceil(month(TempDate) / 3) as Quarter,
'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,
MonthName(TempDate) as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear
Order By TempDate ASC;
DROP Table TempCalendar;
This might be whole another topic (and I am cool with starting another discussion for that), but is there a benefit of bringing the master calendar before the fact table?
Thanks Manish, Marcus_Sommer, msolomov for sharing your knowledge and expertise. From the above discussion, I draw the conclusion that more often then not, it is useful to have a master calendar in your data structure. All your inputs are valuable which makes it hard for me to decide whose answer should I mark as correct. I think I am going to randomly assign one of the answers as correct.
Once again thanks for your time and valuable insights.