topic Re: Performance of multiple master calendars in App Development

Performance of multiple master calendars

valpassos — Sat, 16 Nov 2024 02:40:44 GMT

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!

Re: Performance of multiple master calendars

jcdatasax — Thu, 16 Apr 2020 02:11:09 GMT

Can you load a sample data set with the date fields? I currently use 3 canonical calendars on a data set over 49 million rows. It works great. You shouldn’t need to join the calendars but rather use a canonical calendar, using after mapping to join date fields into common date.

Re: Performance of multiple master calendars

valpassos — Mon, 20 Apr 2020 12:15:24 GMT

Hi @jcdatasax ,

My dataset is close to that as well, ~50 million rows, but it keeps growing day by day.

Can you share a sample of that solution of yours? How does that work for the dates in filters?

Thanks,

Lisa

Re: Performance of multiple master calendars

jcdatasax — Tue, 21 Apr 2020 20:47:57 GMT

Lisa: I tailored my solution based on this post by @thkarner :

https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Derived-Calendar-Fields/ta-p/1485683

Re: Performance of multiple master calendars

thkarner — Wed, 22 Apr 2020 08:32:03 GMT

If you want to use derived Autocalendar fields on date fields in a large fact table it might be very slow. The solution is to create a new table with a single date field and a join field to the date in the fact table. The new table only contains the distinct values per date (without duplicates).

Then you can apply the autocalendar on the new date field in the new table. It will extremly speed up the loading time of the app.

My technical explanation is:
Whenever the derived calendar fields are used, a complete reload of the table happens. In case of a large fact table it could take time. Using a small compact date table joined with the fact table is very quick.

- Thomas

Re: Performance of multiple master calendars

valpassos — Wed, 22 Apr 2020 09:08:52 GMT

Hi @thkarner ,

Thanks for the input! I'm not really interested in using autocalendar derived fields, though. Why would I use that instead of master or canonical calendars? My fact table is very large, as I said.

My initial question was if there was a more efficient solution to create the calendars I need instead of creating 7 master calendars and left keeping them with my fact table. Do you know one?

Thanks,

Lisa

Re: Performance of multiple master calendars

valpassos — Wed, 22 Apr 2020 09:10:36 GMT

Hi @jcdatasax ,

But what does that have to do with the canonical calendars solution you mentioned? It seems to me they are two very different solutions.

Appreciate the clarification 🙂

Lisa

Re: Performance of multiple master calendars

thkarner — Wed, 22 Apr 2020 09:43:12 GMT

Hi, not sure if I understood you fully. Please let me replicate:

a) You have a large fact table with 7 date fields.

b) You want to create a master calendar for each date field.

You have to options:

a) Create your own master calendar table with all necessary fields (Year, Quarter, Month, etc.)

b) Create your own master calendar table with date field only and derive other fields (Year, Quarter, Month, etc.). This is what I´d suggest (according to https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Derived-Calendar-Fields/ta-p/1485683)

In both cases you have to create a new table for each date field.

Instead of LEFT KEEP you can try like this

CalendarDate1: LOAD DISTINCT Date1 RESIDENT Facts;

Since DISTINCT is not very quick you can improve by loading Min/Max values for Date1 in an intermediate table and autogenerate all dates between Min and Max in a new Calendar table.

If Min/Max is still too slow also the FieldValue Function I´ve already used to detect min and max.

Finally the goal is to create a new master calendar table with the distinct dates only to ensure performance of the app.

If you´re familiar with scripting I´d suggest using the min/max approach. If this is not quick enough try to find out how it could be done with FieldValue function.

Re: Performance of multiple master calendars

marcus_sommer — Wed, 22 Apr 2020 10:49:19 GMT

I think your approach had two serious issues. One is limiting a calendar per keep respectively per join (the match-logic is the same and it differs only in the number of tables which remain) which means here that each date from the calendar must be checked against each record from the fact-table - and by 50 M of records and 7 calendars it will of course take some time.

Much more performant would be to use a where-clause with exists, like:

CreationCalendar:
LOAD * FROM Calendar where exists(&ID_Creation_Calendar, &ID_Date);

because here is the calendar-date compared against the distinct values of the system-table of the fact-table date and not against the whole fact-table.

But the second mentioned issue remained by this method - you won't probably have all calendar-dates between the min- and max-date of the fact-table because there are usually gaps from weekends, holidays and similar breaks. Often these missing dates are needed for various calculations and views within the UI.

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.

The min-date might start with yearstart(YourMinDate) and the max-date is often something as today() or yearend(today()). The means any read of the available dates from the fact-tables aren't really needed - and if it should be done against the system-tables. A good description could you find here: https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/

If you have multiple date-fields you could just repeat it for all of them - you need only to rename all calendar-fields properly to avoid synthetic keys and circular loops. In this case would be qualifying quite useful. If your date-fields contain connected events like an order/billing/shipping-date you may create an extra canonical calendar for them. This means the canonical calendar didn't replaced the other calendars else it provides additionally possibilities.

- Marcus

Re: Performance of multiple master calendars

valpassos — Wed, 22 Apr 2020 15:53:53 GMT

I get it now. Thanks for the clarification, @thkarner! Will give that solution a try 🙂

Lisa

Re: Performance of multiple master calendars

valpassos — Wed, 22 Apr 2020 16:35:09 GMT

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

Re: Performance of multiple master calendars

marcus_sommer — Thu, 23 Apr 2020 06:22:21 GMT

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

Re: Performance of multiple master calendars

valpassos — Fri, 24 Apr 2020 17:03:20 GMT

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 !

Re: Performance of multiple master calendars

marcus_sommer — Mon, 27 Apr 2020 07:41:42 GMT

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

Re: Performance of multiple master calendars

valpassos — Mon, 27 Apr 2020 10:48:08 GMT

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

Re: Performance of multiple master calendars

marcus_sommer — Mon, 27 Apr 2020 11:48:52 GMT

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

Re: Performance of multiple master calendars

valpassos — Tue, 28 Apr 2020 08:02:56 GMT

Ohhh got it! 🙂 Thank you, @marcus_sommer!