19 Replies Latest reply: Aug 9, 2017 12:35 PM by Ajay Sikharam RSS

    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

        • Re: Use of Master Calendar
          Rupesh Bharadwaj

          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.

          • Re: Use of Master Calendar
            Manish Kachhia

            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...

              • Re: Use of Master Calendar
                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 IDDateMonthYear
                10002/02/2015Feb2015
                10102/04/2015Feb2015

                 

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

                 

                DateMonthYear
                02/02/2015Feb2015
                02/03/2015Feb2015
                02/04/2015Feb2015

                 

                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

                  • Re: Use of Master Calendar
                    Manish Kachhia

                    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 !

                      • Re: Use of Master Calendar
                        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.

                        • Re: Use of Master Calendar
                          Michael Solomovich

                          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.

                            • Re: Use of Master Calendar
                              Sunny Talwar

                              Hi Michael Solomovich ,


                              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

                              Resident FactTable;

                               

                              LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

                              LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                              DROP Table Temp;

                               

                              TempCalendar:

                              LOAD

                                $(vMinDate) + IterNo() - 1 as Num,

                                Date($(vMinDate) + IterNo() - 1) as TempDate

                              AutoGenerate

                                1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

                               

                              MasterCalendar:

                              LOAD

                                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

                              Resident TempCalendar

                              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?

                               

                              Best,

                              Sunny

                        • Re: Use of Master Calendar
                          Veerendra Katikala

                          Thanks Manish for sharing your valuable suggestions on the master calendar. 

                        • Re: Use of Master Calendar
                          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

                            • Re: Use of Master Calendar
                              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

                                • Re: Use of Master Calendar
                                  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-x.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

                              • Re: Use of Master Calendar
                                Sunny Talwar

                                Thanks ManishMarcusMichael 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.

                                 

                                Best,

                                Sunny

                                • Re: Use of Master Calendar
                                  Rupesh Bharadwaj

                                  Thanks everybody for sharing your deep insight. I highly appreciate your efforts.

                                  • Re: Use of Master Calendar
                                    Steve Dark

                                    I put some of my thoughts on Master Calendar in this post:

                                    http://www.quickintelligence.co.uk/qlikview-calendar/

                                     

                                    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.

                                     

                                    Enjoy!

                                    Steve

                                    • Re: Use of Master Calendar
                                      Siddharth Gupta

                                      Hello Sunny,

                                      Sorry for posting this link on your post but its really urgent please do have a look towards my one big problem.

                                      I want to get Avg of a Ticket on every day basis.

                                      I hope you will be the one who can help me out with the same.

                                      Urgent Help !