alternative to a master calendar

[dadumas]

I have a master calendar, and it works great.  I have several fact tables, each with multiple date "roles".  Everything works fine.  However, my master calendar link table is large at 500k rows, due to all the combination of dates and roles.  I am very well versed in creating link tables, so I know it is created properly.  While the Master calendar link table has 500k rows, the density on almost all of the columns are at less than 5% (good).  I currently see no issues with performance, and have several million rows across fact tables  I also have individual role based calendars (tied separately to each fact), which allow for comparative analysis.

As an alternative to the above, I executed an alternative design and created a master calendar as a date island, and eliminated the Master calendar link table.  The master calendar, and all  individual role based calendars only have 1400 rows, so very efficient.  I created triggers on each of the user interface exposed selectable columns of the master calendar (Year, month, etc).   I then created "on change" and on select triggers on each of those columns, which set the corresponding Year/Month, etc columns in the individual role based calendar to those selections made on the date island master calendar.  This eliminated the need for the large Master Calendar link table. 

However, as an undesired affect, when master calendar selections are cleared, the above triggers do not seem to fire to clear selections in the individual fact specific date columns (bad).

Is this a QlikView bug?  Also, I not see any option for any ability to set an "on clear" trigger.

Dave

[Gysbert_Wassenaar]

That's not a bug. How should Qlikview know that when you clear a field you also want to clear totally unrelated other fields? Qlikview has no way to know that those other fields are supposed to follow selections in the master calendar fields. You will probably need to create more actions for the OnAnySelect trigger and check if a field should be cleared. That's the disadvantage of using a data island plus necessary trigger 'magic' instead of a link table.

[Oleg_Troyansky]

Hi David,

based on your description, you know your way around creating sophisticated solutions - whether it's a multi-fact data structure with link tables, or creating an island table, with triggers and actions.

While all of the above might fly on a small-to-medium dataset, these complex solutions hide may underwater stones that may result in poor performance on a larger dataset.

My recommendation would be to look for a possible simplification of the overall solution, that would make the data model more maintainable and more scaleable. Perhaps I'd consider creating a Concatenated Fact model, that could eliminate some of these issues and allow a single Master Calendar.

I'm describing both Link Tables and Concatenated Facts in a lot of detail in my new book QlikView Your Business. Also, you can learn the Data Modeling techniques and their Performance implications at the Masters Summit for Qlik, which is now going to include a lot of new and redesigned material.

cheers,

Oleg Troyansky

[Peter_Cammaert]

IMHO there is only one solution for your "issue": create your own Clear button(s), and attach as many actions as necessary to get the selection state back to normal.

Good start of 2016, Peter. Getting stoned to death by the others.

[dadumas]

Within the triggers of the data island fields, I do have clear fields on the the corresponding non data island fields, so I would expect them to execute those clears properly.  The link table does work, and I have used it in many project designs, so I will stick with that, thanks.

[dadumas]

Thanks, I agree the data island may not scale - wanted to do a POC to see the possibilities.  The link table does work, and I have used it in many project designs, so I will stick with that, thanks. 

I have your book - great job!

Dave

[dadumas]

Thanks Peter.  This turrned out to be a POC, to see the possibilities.  Looks like the link table is the tried and true way to go here.

Dave

[Oleg_Troyansky]

David,

thanks for the kind words, appreciate it! Keep in mind that with large data sets, link tables don't scale very well either. They carry a few fundamental problems that become acute in large data sets:

1. QlikView works well with data structures that only have one large table. Keeping multiple facts may lead to carrying multiple large tables, which at some point impacts performance.

2. Keeping measures in multiple fact tables can cause slow performance if you ever need to aggregate measures from different tables in one expression, like this: sum(Fact1*Fact2). The process of collecting measures from multiple Fact tables is very slow, causing longer calc times.

3. Finally, the Combo key that needs to be calculated for the Link table, becomes the biggest memory consumer in the whole app. In some cases, the combo key alone takes up to 70% of the whole application's memory footprint - all because of the large number of distinct values that this field inevitably carries. You can easily measure the impact of the Link Table on your application's footprint by using the Document Analyzer tool (free tool by rwunderlich‌, can be downloaded from www.qlikviewcookbook.com)

If your applications with Link Tables become too slow, consider converting them to a Single Fact models.

cheers,

Oleg Troyansky

Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!

[dadumas]

Thanks Oleg for the tips.  The combo key uses autonumberhash128() to generate, so is an integer, so that should help with memory.

In my early days I always used a concatenated fact design, but then over time and more complex projects, a multi-fact with link table (to all dimensions) designed has always yielded far richer usability.  At least in my experience, the data selectivity in a concatenated fact design cannot achieve the same levels of a multi-fact and link design, especially as you add more fact tables as the application grows. In addition, I have never seen a performance issue with fact to fact hops, across a linked dimension table.  But again, just my experience.

Dave

[rwunderlich · Accepted solution]

I think your trigger concept should work. Attached is a simple example that works for me, using on the OnSelect trigger. Can you try it out?

-Rob