Master Calendar Question

[#1]

I was studied the Master Calendar function last night.  I completely understand the logic and what the script creates.  I created a new application with the following information only:

            LET vDateMin = Num(MakeDate(2000,1,1));

            LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

            LET vDateToday = Num(Today());

            TempCalendar:

            LOAD

             $(vDateMin) + RowNo() - 1 AS DateNumber,

             Date($(vDateMin) + RowNo() - 1) AS TempDate

            AUTOGENERATE 1

            WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

            MasterCalendar:

            LOAD

             TempDate AS CalendarDate,

             Day(TempDate) AS CalendarDay,

             WeekDay(TempDate) AS CalendarWeekDay,

             Week(TempDate) AS CalendarWeek,

             Month(TempDate) AS CalendarMonth,

             Year(TempDate) AS CalendarYear,

             'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

             WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,

             Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

            RESIDENT TempCalendar ORDER BY TempDate ASC;

            DROP TABLE TempCalendar;

            LET vDateMin = Num(MakeDate(2000,1,1));

            LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

            LET vDateToday = Num(Today());

From there a created a list box for each of the date data.  Now what I need help on is a couple good business reasons why I would use the Master Calendar.  I know I can search and find examples but I want to see what the members have done.  Any ideas would be awesome.

[Peter_Cammaert]

Master Calendars are mainly created and used for two good reasons (but those aren't necessarily business reasons, it's more to do with usability I think):

• use a Master Calendar if you don't want timelines with holes in them. If you just use the order date field to base your timeline upon, dates on which no orders were placed won't be there... Master Calendar = continuous timeline
• use a Master Calendar (or a Link Table with dates and additional dimension fields) if you want to connect multiple facts tables that have differing date granularity (for example Actuals = day or weekly, budget = monthly) and neither one of them may filter the available dates/periods.

Of course, YMMV.

Peter

[hic]

As soon as you have a date in your data, it makes sense to load a Master Calendar. You don't want to clutter your fact table with a bunch of calendar fields like Week and Month.

A comment on your script: You do not need a resident load. Instead you should use a preceding Load. It's both cleaner and faster. E.g.

LOAD   Date AS CalendarDate,

             Day(Date) AS CalendarDay,

             WeekDay(Date) AS CalendarWeekDay,

             * ;

LOAD  Date($(vDateMin) + RecNo() - 1) AS Date

            AUTOGENERATE $(vDateMax) - $(vDateMin) + 1;

HIC

[#4]

Like HIC says - Then you dont need to fill your Fact tables with Week,Month,Year and so on.

But many BI consultants also create a Date Dimension in their date warehouse which includes,Year,Month,Quarter and so on.

Normally I tend to use both, because I can use the MasterCalendar as a template across all document i might develop.

[#5]

Thank you.  That makes sense. 

[#6]

Lets say, we have the Fact table with 100K rows from Last one Year history.

Generally we have only 365 Date fields in one year. If you don't have calender table, you run the Year , Month, Week and some flags (YTD) on 100K rows and it take longer reloads time.

If you have the small table having 365 rows and created all the Year, Month, Week & other flags in this table, it take very less time to create the Date dimensions.

[#7]

Thank you.   So here is my problem:

I have two dates one in the AcctSetup table and One in the Bill Table:

1. FiscalYearBeginDate which could be 7/1/2012, 8/1/2012, 9/1/2012, etc. (AcctSetup Table)
2. BillStartDate which could be any date so as an example 2/1/2013 (Bill Table)

I load my AcctSetup Table:

AcctNum

FiscalYearBeginDate

......

I load my Bill Table

AcctNum

BillStartDate

....

What I need to know is the following:

If FiscalYearBeginDate = 7/1/2012 then BillDate 2/1/2013 is Month 7 of Fiscal Year 2012, 8/1/2012 is Month 6 of Fiscal Year 2012, 9/1/2012 is Month 5 of Fiscal Year 2012, etc.

So for each BillStartDate I need to know which month and fiscal year that date falls into.

The AcctSetup Table will look like:

AcctNum         FiscalYearBeginDate

123                    7/1/2012

456                    8/1/2012

The Bill Table will look like:

AcctNum           BillStartDate                             So my results need to be

123                    2/1/2013                                   Month = 7 and Fiscal year = 2012

456                    2/1/2013                                   Month = 6 and Fiscal year = 2012

Does this make sense?

David

[#8]

Henric,

Thank you for your suggestion.  I am still learning about Master Calendars and so forth.  It sounds to me whenever you have a date field in a table it might be good to create a Master Calendar.  I am going to continue to experiment with them.  Let me ask you a question

I have a table with CreateDate and ChangeDate loaded.  I need a two charts one to show how items were created based on the CreateDate field.  I need another Chart to show how many items changed based on the ChangeDate.  The charts will be independent of each other so what I want is two list boxes.  One with the Months(Jan, Fed, etc) and the other Years(2011,2012,etc).  Can you make it so if a person selects a Year the chart shows how many items were created and the other chart shows how many items were changed?  If some selects a Month they get the same type of results and finally if they select both they get the same type of results.  Does this make sense?  I am guessing one calendar but have to combine the two date fields together.

David

[hic]

Yes you can. You need to create a common date to represent both dates. This you do in the script by the following two Load statements:

GenericDate:

Load TransactionID, CreateDate as Date, 'Create' as DateType resident Facts;

Load TransactionID, ChangeDate as Date, 'Change' as DateType resident Facts;

Then you connect your master calendar to this new date. You need to use Set Analysis in the charts. The reason is that a item will link to two dates - it will be counted twice unless you use Set Analysis. Hence:

Sum({$<DateType={'Create'}>} Amount)

Count({$<DateType={'Create'}>} ItemID)

HIC

[#10]

Hi HIC,

Sorry to be asking so many questions.  I still have to create a Master Calendar along with the information you provided in the email above correct.  I don't know why I am so confused with the Master Calendar concept but for some reason I am.  I want to make sure I use the best code when building a Master Calendar and would love to have and example of the load statement.

David