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.
3. Using the same Calendar for many Fact dates.
Hi msolomov ,
Thanks for your response.
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?
This is as simple as that:
- If you are interested in the data only in the specific time range (e.g. 2 years back and one year forward), start with the calendar.
- If you need all the existing facts data (for me it's quite rare), create calendar after loading facts.
Besides, I don't like calendars that start or end with something like March 13 . So I typically make start date as the start of the year, and end date as end of the year. In your example it would be:
LET vMinDate = Floor(YearStart(Peek('minDate', 0, 'Temp')));
LET vMaxDate = Floor(YearEnd(Peek('maxDate', 0, 'Temp')));
(Or at least month start/end)
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.
I put some of my thoughts on Master Calendar in this post:
I tend to incorporate date break downs into the main fact table, rather than hanging a calendar off the side. There are advantages and disadvantages of this approach.
The article looks at some of these, and there is a fair amount of conversation about the topic in the comments after.
Sorry for posting this link on your post but its really urgent please do have a look towards my one big problem.
I hope you will be the one who can help me out with the same.
Urgent Help !