I have a Qlikview app with 14 million rows in the fact table. Above is the data model. I find that the report opens in a reasonable amount of time. However after I make the first selection, from the ProvId listbox, it takes about 20 seconds for the chart to generate and about 30 seconds for another chart to generate (which is on another tab). Making subsequent selections (from the ProvId listbox) however, updates the charts instantaneously.
I have tried modifying the datamodel by combining the 4 tables selected in orange into one table. I saw a slight performance degradation after doing this.
What could be causing the issue and how to I resolve it? Is it an issue with the datamodel or do we need to run this of a faster server. Currently developing on a 4core server. Testing the report on a 6 core server give about a 20% improvement in the initial response time.
Once this issue is resolved I need to test the report with a dataset about 40 to 50 times bigger than this. I'm assuming the fact table will be between 500 mill and 600 mill rows.