Optimizing Qlikview Performance Tuning 1z5420

Optimizing Qlikview Monitoring Memory Consumption at Object Level: Memory utilized by the QlikView objects and the calculations time can be monitored from the document properties. This will helps to identify which object is consuming more memory and finding the reason for delay in loading time of the Qlikview application.

Steps for improve performance QVW

1. If the chart is too large, then implementing forced selection by the will minimize the chart calculation time. 2. Showing frequencies in list box can be avoided if not necessary. 3. During sorting, it is recommended to sort numerical fields numerically than alphabetically. 4. Remove Synthetic Keys if any 5. Drop Temporary Tables if used in script , which consumes unnecessary space and memory. 6. Always Load QVD’s with Optimized Load What is the QVD Optimized ? What is the Non-Optimized ?

7. Load at least 3 years’ or required data into application. 8. Settings -> save -> compression and there it will be high, normal , low. by default, it is already high. So, it is already compressed up to the max possible limits.

Continue ..

9. Convert Timestamps & Phone field If you have a phone number field that stores 13 chars (001 212 555 1234) and has 1000 distinct values, you will need 13 x 1000 = 13000 RAM you need only for 1000 phone numbers. However, QlikView does not store the same value twice, so if instead of that you have one field for the international prefix of 3 chars, another area code of another 3 chars, and the phone number in a third field of 7 chars, and assuming all your numbers are for NY, the rough math's would be: 001 is stored only once: 3 bytes 212 is stored only once: 3 bytes 1000 different unique phone numbers: 7 x 1000 = 7000 bytes Summing up a total of 7006 RAM. Almost one half with the very same amount of rows and values in the source, and having three fields instead of only one! Now think of applying this logic to dates (day, month, year), timestamps, addresses, name prefix... That takes some work in the load script but it's worth it.

10. Minimized Chart Vs Maximized Chart: Minimized chart objects will consume less memory comparing to the maximized one, hence use of auto minimize option & conditional enable will be a good practice in this case.   Continue ..

11. LOAD always from QVD Files And keep a file to pull from the database, do all needed transformations and store into the QVD will be loaded later. And LOAD only those fields you really need (pretty obvious, but "LOAD *" or "SELECT *" are usual). You don't need to have the Customer Name in both Invoices and Customer table, just linking "Invoices" to "Customers" through "Customer Code" will do.

12. Star Schema ( Data Model) Use a fact table with all dimensions required trying to follow a star schema, instead of a snowflake schema (this is quite theoretical, but say that in your data source you have a table with Customers, another one with Group of Customers, another one with Addresses of Customers, well, them so you have one big "Customers" table with all required data). When concatenating to create this fact table, rename names so the final table has the same name and number of fields.

13. Avoid IF conditions Avoid IF() conditionals in any object expression, use instead set analysis. 14. Use of numeric values instead of string values in key When linking one table to another by means of renaming a field in both alike (we could say these are the key fields), use a numeric value instead of a string (usually the AutoNumber() function will do).

Continue ..

15. Use built-in actions instead of macros ( Triggers ) 16. Flags Create flag fields for those analysis or filters you want to use in the expressions, the simpler the expression the faster the chart is rendered.

18. Avoid RESIDENT loads. If needed, load twice from a QVD. 19. Use of Section Access 20. Avoid Complex Expressions Complex calculation within a dimension or expression or in any of the Qlikview objects will give poor performance and it will be better to use the complex calculations within the script of the QVW. Replacing with simpler calculations will help and its a good practice too in scripting. Also try to use straight tables instead of pivots