Performance of multiple master calendars

[valpassos]

Hi community,

I have the need in my model to create muitple master calendars, as I have many dates. I'm talking about 7 master calendars.

I'm loading them and LEFT KEEPing them with my FACTS table. Like this:

LEFT KEEP(FACTS)

CreationCalendar:
LOAD 
&ID_Date AS &ID_Creation_Calendar
...
FROM Calendar;

LEFT KEEP(FACTS)

AssignmentCalendar:
LOAD 
%ID_Date AS %ID_Assignment_Calendar
...
FROM Calendar;

But this is taking forever to load, like almost 20 minutes, just to load these tables. The application is very slow and I'm wondering if it has anything to do with this?

Do master calendars hinder performance?

Thanks in advance!

[valpassos]

Hi @marcus_sommer ,

I didn't know about that behaviour of left keep! I changed my logic to where exists and the loading time of the calendars immediately dropped from 20 to 2 mins 😅

I couldn't keep up with what you said after, though. You say "...Therefore I recommend to create a single master-calendar within a separate qvd which contains all possible calendar-fields of the last/future years/decades." 

But this is exactly what I have - I have a Calendar.QVD will all the possible and imaginary dates. I am generating a calendar to each one of my dates based on that .QVD, although I am not doing any MIN() and MAX() extraction for each of the dates. Doesn't the where exists solve that?

Thanks for you input on this - very appreciated the explanation!

Lisa

[marcus_sommer · Accepted solution]

My suggestion was not to limit the calendar (regardless in which way it's applied) to the available dates of the fact-tables which a keep/join/map or where-clause would do. Like mentioned have fact-tables often gaps in regard to a consecutive date - and this might not only happens to the "classical" missing dates like weekend and holidays else there might be also "normal" dates without a sale/production/billing/shipping caused through any reason.

Even if all dates are available within the calendars it could be quite difficult to show these gaps but without it's impossible. I don't know any real case in which you could get disadvantages through dimension-dates which aren't also fact-dates which couldn't handled - a classical case would be the count of dates but here you could use set analysis to consider the source, for example with something: count({< Sales = {'*'}>} date) which would only count dates which have a sale.

The more different date-fields you want to include the more likely would it be that they would have a different dataset within their calendars.

In the end it means that no real dependencies between the dimension- and fact-tables must be implemented. Just loading the calendar-dates between a manually set min/max-date. Usually the min-date of the fact is known and mustn't be read in any way and the max-date might be also known respectively it could be easily calculated with something like yearend(today()) or similar approaches.

With it you would also save the 2 minutes because the unoptimized load (because of the where-clause to the min/max-date - whereby even that could be bypassed with an appropriate pre-load which creates the needed dates to a where exists() clause) of 7 calendars would be finished in a few seconds.

- Marcus

[valpassos]

So, what you are saying is that I can keep my 7 calendars, but instead of doing a LEFT KEEP to my fact table or a WHERE EXISTS, I should retrieve the MIN() and MAX() of each of my 7 dates, and generate a master calendar (for each date) based on those min and max dates. Is that what you are saying?

I do am experiencing a CALCULATION TIMED OUT error for every line chart I'm using my dates, despite of how 'simple' the metric... How do you read that error?

Thanks, @marcus_sommer !

[marcus_sommer]

Quite usually you don't need to retrieve the min/max values from your fact-tables to limit the time-span of the calendars because like above mentioned they are mostly known - why should you calculate values if you already know the results.

Further I would tend to to hold the same the same time-span within all calendars because it simplifies the handling within the script and the UI and doesn't confused the users. Of course there may be requirements which speak against this approach but at least I would start with it.

If your simple line-charts run out of calculation time and/or memory it's probably caused from your datamodel in which your calendars / dates aren't connected properly to the other parts of your datamodel so that the chart creates any kind of cartesian calculation.

- Marcus

[valpassos]

So, I'm missing something from your explanation. When you say: " (...) Therefore I recommend to create a single master-calendar within a separate qvd which contains all possible calendar-fields of the last/future years/decades. Then in each application you could load the needed/specific fields with the needed time-span - which could mostly be set manually because the min- and max-date are usually known."

I already have the master calendar (7 of them), and I know the MIN and MAX dates from all of my dates (they are the same). How do I now filter my calendars to those dates, then? I thought the LEFT KEEP or the WHERE clause were doing that.

As for the calculation timed out issue, yeah, there is something wrong with my keys in my fact table that is mixing everything.

Thanks and regards,

Lisa

[marcus_sommer]

You limit the calendars with a where-clause but none which is directly related to your fact-table loads else like:

load * from calendar.qvd (qvd) where date >= 'MyKnownMinDate' and date <= 'MyKnownMaxDate';

Such a load wouldn't be optimized but the calendars have so less data so that this wouldn't have a significantly effect.

- Marcus

[valpassos]

Ohhh got it! 🙂 Thank you, @marcus_sommer!