Hi All,
Most of the people feel that designing the data model in sql/oracle and qlik is pretty much same... but actually it has a lot of difference.
As everyone knows qlik engine works based on in-memory concept. Here are some basic best practices to keep in mind while developing the dashboard,
1. Joining two or more huge tables and linking the same does make a huge impact in system performance.
2. Avoid using the fields which is used for linking in UI expressions in charts.
3. Using apply map instead of joining tables improves the system performance.
4. Always use variables to refer the path of the file in load statement instead of absolute of directory path.
5. Drop fields & tables when it's not required for the final data model.
6. Follow the naming conventions such as 'Link_', '%key_' etc., for the linking fields, so that it can be hidden by using HidePrefix variable.
7. Avoid Syntetic keys & circular loop, as it will cause the inconsistency in the data model.
8. Use linked objects in UI wherever it's required such as primary filters, calendar objects.
9. Drop/ Comment all the unused fields in the final data model to reduce the load.
10. Try to accomplish most of the calculation via script unless it should be dynamic.
11. Use set analysis instead of if condition where ever it is possible in UI.
12. Though qlik can execute the SQL queries, it is always recommended to convert the script in qlik format to reduce the load and support the in-memory concept.
13. Always have a data in qvd where ever there is a possibility in reusing the data, so that the number of times we hit the actual data source will be reduced.
14. Follow the DAR principle in the dashboard design.
15. Use Exit Script command or execute the script in debug mode to find the issues and resolve it quickly by checking block by block.
Here I have attached the simple dashboard which explains the difference in the results when the link field and non-link field used in the same expressions.
Warm Regards,
Karthikeyan.
