Hi,
I have a big QVW file (~ 1 GB file size) and a star schema (fact joined to date, sold to customer dim, ship to customer dim and product dim). The analysis is being performed in an adhoc fashion where a user can select attributes / metrics on left and a straight table shows the results. This straight table has N conditionally enabled dimensions & M conditionally enabled metrics, which get enabled on user selection.
The ideal way would have been to use dimension limits and show the first N values. However, according to this article and as observed on the Qlikview server, the table would first figure out the data required and only after that, will "display" the first N values, which takes a long time (in the orders of minutes) !
Therefore, the only possible solution seems to have a calculation condition. I am evaluating two expressions:
1. Count(Distinct $(=Concat(_AttributeName, '&'))) <=Threshold, which turns into Count(Distinct Field1 & Field2 & Field3...) <= Threshold. Although I have observed this calculation to be multi threaded , the RAM usage shoots up terribly and the calculation fails with Object memory exceeded error.
2. Sum(Aggr(1, $(=Concat(_AttributeName, ',')))) <= Threshold, which turns into Sum(Aggr(1, Field1, Field2, Field3))<=Threshold. This calculation is single threaded, but the RAM usage does not go up and the calculation finishes up in about 1 min, 36 seconds.
The machine configuration is: Intel(R) Xeon(R) CPU L5640@2.2GHZ (24 core), 48 GB RAM, 64 bit OS. Can someone kindly suggest if there is any other better way to count the number of rows to be possibly created due to these dynamic columns in the straight table ?
Thanks,
Puneet.
