Well that’s a wordy title isn’t it?
I’m in the midst of writing a new QlikView Document Optimization course to be delivered at Q-On Training . This work has reminded me of a not-so-obvious issue I sometimes see in Performance Tuning engagements with customers.
You might be thinking I’m going to write today about how heavy a calculation can be as a result of the cartesian product of disconnected fields in an expression . No…that’s not what I’m thinking of.
What I’m thinking about today is the impacts of selecting a field that is not used by any expression on the sheet. For example, a Currency listbox may be present on the sheet. The Currency field is not connected (“Data Island”) to other tables in the model . Some, or none, of the objects on this sheet may reference that Currency field.
What happens when you click a Currency value? Everything on the sheet gets recalculated. E v e ry t h i n g. Whether it uses Currency or not. Why? Because the data has changed.
Since the data used in my chart has not changed, the results will be fetched from cache, right? Probably not. Let’s look at an example:
Dimension: Customer
Expression: Sum(Sales)
The current selection in Currency is “USD”. The chart has been calculated and the results stored in cache, available for speedy retrieval if the same expression is calculated over the same set of data.
Select “EUR” in Currency. The cached result will not be used even though no change has been made in the data used by the chart. Cache evaluation considers the entire data model, not just data referenced by the object. If you now select “USD”, the previously cached result will be used.
If your application is large enough that you consider this behavior to be a problem, a leaner alternative for the Currency example is to use a variable. When a variable changes, only objects that reference the variable get recalculated. Another great alternative is to put the Currency listbox in a different Alternate State.
Let’s look at another case, the idea of a “universal listbox” that has been published by a several authors. I think it’s a very cool idea and I use it myself for data exploration. The common idea is that you let the user pick any field and then make selections in that field. This can be built using the system fields $Table and $Field.
Let’s consider the app is idle and all the sheet objects are occupying what I’ll term “relative cache slot #1” – the first cache entry for each object. Now we’ll use the universal listbox.
1. Select a $Table value. Everything is recalculated, consuming CPU resources and the results are stored in “cache slot #2”.
2. Select a $Field value, “ProductGroup”. Everything is recalculated, consuming CPU resources and the results are stored in “cache slot #3”.
3. Make some selections in “ProductGroup”. Everything is recalculated, consuming CPU resources and the results are stored in “cache slot #4”. This result, with the new data, is what the user is interested in.
We have used three times the CPU and cache resources to achieve the end result. A leaner alternative would be to place the universal listbox on it’s own sheet, thus avoiding extra recalculations. You can move back and forth with buttons to make it feel like it’s integrated with each sheet.
I don’t mean to discourage the use of these data island techniques. They can be incredibly useful to the #1 goal — getting accurate information to your business in a timely and cost efficient manner. However, when you are dealing a specific performance concern in larger apps this is an area you may want to examine and optimize.
-Rob
Watch the Q-On Training site or subscribe to our Q-On newsletter to be notified when the new Performance course is available. For more performance related discussions and tips, join me at the Masters Summit for QlikView Sept 23-25 NYC or 29 Sep-1 Oct Copenhagen.
Great post Rob.
I think alternate state is a option. I believe only objects of the same state are recalculated.
Alternate States is an excellent suggestion Jonas. Perhaps even easier to use than a variable.
Indeed.
We’ve switched to use alternative state for data islands something like a year ago and performance improvement is visible in our applications.
Another problem with Data Islands is that their selected values are saved in every bookmark created by a user. Then, if you modify or remove a Data Island (e.g.: a scorecard selector) those affected bookmarks will not apply anymore. I think that using variables you can avoid this problem. What do you think?
I have not experienced this problem — that is, removing a field from the model causes other fields in the bookmark to not apply. I have seen the problem where adding a new “Always One Selected” field will break a bookmark.
https://community.qlik.com/thread/48318
Very interesting. I did not know about that problem with bookmarks previously.
But I’ve checked exactly that scenario in the test application just now and it seems to me that our applications are not subjects for that error.
I can reproduce unwanted behaviour with that steps
1. Create test application, insert a test script from Edit script menu.
2. Populate sheet with listboxes and Current selection control.
3. Set selection on value C of Dim1
4. Add Bookmark named C with that selection.
5. Clear selections.
6. Add an island table to load script
LOAD * INLINE [
IslandValue
1
2
3
]
7. Add listbox for IslandValue, set value 3 and set “Always one selected value” for it.
8. Apply bookmark C
Bingo – Dim1 is not selected.
Now I deselect “Always one selected value”, add an alternate state “Island” and make the listbox use that
state. Select value and apply “Always one selected value”
Now I apply Bookmark C and: Bingo again C is selected in Dim1.
So I conclude that as far as you use alternate state for your data islands (we always do) – you do not subject your applications to that sort of problems with bookmarks
Hi Vadim,
In our data model we have the Currency table as a data Island which has the values GBP, EURO, USD. By using this field in expression, it impacts the performance of the objects. Can you please let me know how to deal with this scenario.
Thanks,
Bhavik Mandaliya
Can you post the expression you are using?
Thanks Rob,
I use a lot of data islands for customisable charts and user help (to name but a few reasons). I’ll look to switch them to alternate state following this advice.
Richard
I’ve updated the post to include the Alternate States idea in case someone doesn’t read the comments.
Hi Rob,
please could you explain me the “universal listbox” idea? I have not found any article in the web about this argument.
Could you do some practical examples about how I can replace the “Data Islands” use?
For example, in Currency case I think is good and easy for the user select the currency from a field.
If I use a variable how can I give this possibility to the user?
Thanks for you answer and for you job, you are a legend on QlikView.
Regards
Luca Jonathan
Sorry for hijacking the question, but as I understand it from Robs description of “universal listbox” you can see it in action in my https://github.com/inqlik/QvdExplorer (namely listboxes to choose field to filter and dimension selection listbox).
Also in that application you can see “Data Island + Alternate state” approach in action.
As for using variables instead of plain Data Island – we were toying with that idea too before finding “Data Island + Alternate state” solution. We’ve tried various modes of Input box, ad-hoc control based on sets of text boxes, and web extension control for selection one or multiple values for variable. We were inclined to use web extension control, but current approach serve us better still.
Thanks for posting the example Vadim. Here’s another example of the “Universal Listbox” idea.
http://qlikcentral.com/2014/10/08/advanced-filters-in-flat-design/
This example shows how to use variables for UI controls like currency:
http://qlikviewcookbook.com/recipes/download-info/how-to-choose-an-expression/
I think Vadim’s very interesting example demonstrates the Alternate State approach. The fundamental idea is to place your currency listbox in an Alternate State (for example “UI”) and then reference fields from that state as required in your expression like:
=sum(Sales * only({UI}CurrencyRate))
Rob,
Great considerations that really solved my performance problem. Just to help: there are some problems with alternate state and group hierarchies (drill down/up), but Benedikt Horneber posted an interesting way to bypass it. https://community.qlik.com/thread/45560
Dear sir,
I’ve two measures in the fact table, i.e. Net_Quantity and Net_Amount. I’m using a Data Island with exactly two fields, i.e. AdhocTable:
Load * inline
[
Selection_Id, Selection_Name
1, Quantity
2, Amount
];
Now I’m showing Selection_Name in a List Object and using “always select one value” and in the charts I’m using Conditional option under expression tab.
e.g. to show Quantity related expressions I’m using Selection_Id=1 at conditional option.
So my question is how can I remove Data Island in this type of scenarios? Also I’m not getting how to apply Alternate State in this type of cases. Any example would be very helpful.
Rob,
Do data island have the same impact on hide/show dimensions?
Yes.
An easy alternative to “alternate states” & data island is using : “variable input” type “button” with “dynamic values” if($(v_variable_dim1)=1,0,1) for each value of the dimensions (measures) lists