Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the fact table with 252 million rows and it is joined with other dimension tables.
I have a combo chart with product and month as the dimension and count(distinct id) as the expression.
I have a listbox with month and product selection. If i select few months in a listbox, then the chart takes nearly 40 - 60 secs to calculate the count. And when i check the value in Calctime of chart in sheet properties, it gives the value in millisecs as 41340 or 60432.
How to improve the performance of the chart?
How looked your datamodel?
- Marcus
It has two big fact table with one table with 251 million rows and another with 175 million rows, 4 dimension tables and 2 dimension tables joined to other dimension tables. Those fact tables are linked with two ids id1 and id2, so it has created one synthetic key bw two fact tables.
Can you send us a screen shot of your data model. It is most of the time good to avoid synthetic keys as they give incorrect results unless it is necessary to have them.
With a synthetic key between such large fatc-tables it's no surprise that the response-times are slow. You need to remove them and quite probably you will need more changes and trying to merge both fact-tables to get a fast gui.
- Marcus
I agree with Marcus Sommer.
If you have 2 fact tables. As per data modelling principle, we should never associate 2 facts directly and moreover , if synthetic keys exists in the model, the performance will be still poor.
See if the below link helps to resolve the data model issue.
https://community.qlikview.com/thread/89056
If you still want 2 fact tables, i would recommend to associate the dimension tables to the lowest grain level fact and not the other fact with high grain level...
But the fields i am using in the chart are from a single fact table.
The dimensions and count(distinct id) are all from a single fact table, so will it still affect the performance of the dashboard because of the synthetic key bw two fact tables, even if i use the fields from a single fact table.
If really all fields comes from one table a change from the table-associations might not effect the calculation-times but I would at first eliminate all obviously weaknesses of the datamodel before going further. If you are on this step then take a look here: Symbol Tables and Bit-Stuffed Pointers - a deeper look behind the scenes.
- Marcus
Hi Uday,
Below are few best practices you can follow to improve the performance of your Dashboard
- Using set analysis instead of if-else conditions
-Use a link table to join all the Fact tables (Star Schema). This will create a single fact table and reduce the number of synthetic keys.
-Use less number of variables which consume CPU time
-Use the flags for the filters which are used in the expression. The simpler the expression the faster the chart is rendered
-Move all complex calculations to the script level and minimal at object level
-Avoid RESIDENT loads. If RESIDENT has to be used then there are 2 ways to work on it so that the performance is not affected.1. Use no concatenation and 2. use DROP table.
-Use built-in functions instead of macros
Hope this helps.
Regards,
Laxmi
One addition: with count(distinct id) you are not calculating within one table then id is one of your key-fields and part from the synthetic key (and in general don't count a key-field).
- Marcus