Use of Master Calendar

[sunny_talwar]

Hi Qlikers,

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.

All responses are greatly appreciated.

Best,

Sunny

[Anonymous]

Great question...I wonder the same thing whenever I create master calendar. In fact when we don't create master calendar and keep date, month, years etc. in the fact table itself, the method will be less CPU intensive. I am curious to see expert's take on this issue.

[MK_QSL]

Sunny... Excellent Question... There could be different answers on advantages/disadvantages on creating/use of Master Calendar. To create and use Master Calendar is completely depends on what type of data model and domain it is and what are the requirements.

Consider that you have Sales table where you have all Invoice related information and Customer ID. We usually prefer to associate Sales Table with Customer tables via Customer ID to get all customer related information rather than keeping Customer information in Sales table to avoid duplication of data. (i.e. One Invoice belongs to a single customer could have 10 lines, so all information of Customers would get duplicated)... The same way I would prefer to create separate calender from InvoiceDate field to get Year, Month, Quarter, Week, MonthYear, YearMonth informations.

Sometimes client want to show 0 sales value when you don't have sales on particular day (usually happens on weekends or holidays). To show 0 sales on Bar Chart, what we want to create those missing dates, so I think Master Calendar is the best and easiest option in this case.

How you will show Daily Sales Growth compare to previous day if you don't have data for previous day? You first need to create those missing Dates ! There are also alternative way to get the result but Master Calendar could be the easier one.

Hope we would have more replies from some experts !

Happy Qliking !

EDIT 02/04/2015 (09:30 UTC)

Completely forgot to mention the use of Canonical Date. Without creating a separate calendar/master calendar, it's very difficult to show data for table having two different date fields.

Order Table having OrderDate and Delivery Date.

If one want to show Daily/Monthly/Weekly/Quarterly/Yearly number of Orders received vs Number of Orders Delivered...or Amount instead of Order Count... we need to create a Common Calendar with Flag Order and Delivered...

[sunny_talwar]

Hi Manish,

Thank you for taking out some time to responding back to this post. I really appreciate you listing out advantages of having a master calendar and I am almost tempted to mark your answer as correct, but I just want to wait a little longer before I close this thread.

Coming back to the three advantages you listed, I feel that 2 and 3 seems to be the true advantage of having the master calendar. Like you said that comparison with a previous day would make your life so much harder without a master calendar. and whenever we want to show sales for days where we did not have any sales, it wouldn't be possible without a master calendar. 

But to your first 1st point relating to duplication of information, it makes sense for client, employee, supplier, manufacture where each of them will be linked to just one address phone no. etc. In case of master calendar we will still have the same duplication with even more set of dates. For example if I do all the manipulations in the fact table it would be in just one table and number of rows will be less

Transaction ID	Date	Month	Year
100	02/02/2015	Feb	2015
101	02/04/2015	Feb	2015

but if I do the same in the master calendar without left keep, I will have something like this:

Date	Month	Year
02/02/2015	Feb	2015
02/03/2015	Feb	2015
02/04/2015	Feb	2015

I would think that if you don't have a requirement like the 2nd or 3rd point you mentioned, it would make more sense to do the date field manipulations in the fact table itself or if you really wish to have a separate table you can always create it like this (instead of regular method)

MasterCalendar:

LOAD Date,

          Year(Date) as Year,

          Month(Date) as Month,

          and so on...;

LOAD DISTINCT Date

FROM Fact;

Once again, I feel you introduced some great reasons to have a Master Calendar and I really want to thank you for your time and effort to clarify things.

I hope that other experts will also contribute to this thread.

Best,

Sunny

[MK_QSL]

Regarding my 1st Point...

What if you have One invoice having 10 lines of data.

InvoiceDate, InvoiceNumber, LineNo, Item, Price, Qty

01/01/2014, INV001, 1, A1, 100, 10

01/01/2014, INV001, 2, A2, 110, 12

01/01/2014, INV001, 3, A3, 120, 9

01/01/2014, INV001, 4, A4, 10,   8

and so on..

If you will create Month, Quarter, Week, Year field in the same table, there would be duplication of data in each line but in Master Calendar particular for 01/01/2014, data will come for once only....

I also want not to close this thread so that other experts like below would also comment..

gwassenaar

swuehl

jagan

tresesco

mby

maxgro

its.anandrjs

ashfaq_haseeb

msolomov

hic

stevedark

dvqlikview

Kind request to all of you to share your thoughts on this...

Your reply would be highly appreciated... Thanks !

[sunny_talwar]

Yes that definitely makes sense. I did not think of that. I guess in that case it would make sense because Fact table could have millions of rows of data vs Master Calendar which would be only 10's of thousand.

[marcus_sommer]

We had had a quite similar discussion in my session (User Group Session "Re-Using von Scripten, Formeln und Daten") by the central usergroup-meeting DACH last year in beforehand from the VYWK in Frankfurt and discussed this topic with swr. In short, the result was that the use from a master-calendar is recommended then there are a lot of advantages:

Advantages from a master-calendar against creating period-fields within the fact-tables are:

- structure of data-model (facts linked to dimensions)

- less RAM will be needed (amount of fields and distinct values will be the same (unless date-holes) but the efforts for the pointer will be greater by large fact-tables)

- no holes within the dates

- it's easier to create "extended" date-fields like continuing month- or week-counter or counting the working-days and so on

- using the same master-calendar for all applications (maybe with where-clauses over minDate/maxDate from facts or rolling years or fixed data-ranges)

- using the master-calendar several times for a single application (canonical date)

- Marcus

[sunny_talwar]

Hi Marcus,

Thanks for your response. I am unclear about some of the advantages you listed here, would you be able to elaborate on these a little:

- less RAM will be needed (amount of fields and distinct values will be the same (unless date-holes) but the efforts for the pointer will be greater by large fact-tables) - Do you mean if we create all dates regardless of if there is a fact available in the fact table vs. doing it in a master calendar, the RAM consumption will be more if its within the fact table??

- it's easier to create "extended" date-fields like continuing month- or week-counter or counting the working-days and so on - what is "extended" date field (I know you mentioned continuing month- or week-counter, does it literally means assigning a some kind of flag for a required duration?)

- using the same master-calendar for all applications (maybe with where-clauses over minDate/maxDate from facts or rolling years or fixed data-ranges) - Don't understand the part in brackets (why do we need a where clause when we use it in other application, may be I don't understand the context)

Once again I really appreciate your time and effort on helping me understand this better. I am sure this will prove helpful for not just me, but many other in the community as well.

Best,

Sunny

[marcus_sommer]

To the point RAM - exampl: if you create to your date two further fields month and year you will have two additional fields with nearby the same number of distinct values if create these fields within the fact-table or you linked it per master-calendar. But these fields needs within large fact-tables more pointer to link they to the date. It's not a easy topic which I could explain well therefore have a look here Symbol Tables and Bit-Stuffed Pointers and in the book Mastering QlikView from stephen.redmond.

To "extended" date-fields: Yes, with them are flags, pre-calculations and simplifications meant. MonthCounter for examples is a continuning month-number, started with 1 and after a year it will be increased from 12 to 13 and so on. Use is the simplification of expression which query date-ranges over years - sum{< Monthcounter = {">=$(=max(Monthcounter) - 6)"}>} Value).

To where-clauses: It was meant to use one master-calendar for all applications but not each application need to cover the complete data-range from the master-calendar. Mine covered more than two decades but the most applications included only a few years.

- Marcus

[sunny_talwar]

Thanks Marcus for providing clarification. Your help on the topic is greatly appreciated.

Best,

Sunny