Qlik Best Practices

[karthikeyan1504]

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.

[Anonymous]

thanks for sharing

[william_allen]

Bookmarked, thanks

[v_petrenko]

karthikeyan1504‌, thanks for sharing, nice list. Could you please remove all other categories except App Development, Scripting and Developer Toolkit for not confusing people and keeping Resource Library clean and well-structured.

[ahaahaaha]

Very useful document. Thanks for sharing.

[#6]

Great help. Thanks alot for sharing.

[Lech_Miszkiewicz]

Hi,

I have a question re 4. Always use variables to refer the path of the file in load statement instead of absolute of directory path.

I am just curious why not using Directory statement and relative paths? Why always variables? Once you use variable there, you are loosing functionality of coming back to wizard by clicking on "little hammer" icon from script editor.

regards

Lech 

[Lech_Miszkiewicz]

I also disagree with this:

(12. Though qlikview can execute the SQL queries, it is always recommended to convert the script in qlikview format to reduce the            load and support the in-memory concept.),

especialy when you have to aggregate data (sum, max, count). This is in Qlik single threaded process and DB can deal with it much quicker, so SQL querry would return aggregated data much quicker than Qlik would perform Group by statement.

regards

Lech

[karthikeyan1504]

Hi Lech,

Thank you for your comments.

For 4th point, I have referred the below scenario,

Directory;

LOAD A,

     B,

     C,

     D

FROM

[..\Data File\Mapping.xlsx]

(ooxml, no labels, table is Sheet1);

It's always good practice to have a complete path(It's better if we declare it in the variable to manage efficiently) instead of using the relative path which is shown above.

For 12th point, Yes I agree SQL can handle the aggregation better than qlikview. But consider the scenario where multiple dashboards are accessing the same data set from the SQL/Oracle database. It is always best practice to create the raw data qvd and do the aggregation on it rather than multiple dashboards hitting the database to get the same data set.

By following the approach mentioned above, we are reducing the load on database and using the qlik engine capability effieciently.

Hope this answers your query.

Thanks,

Karthikeyan.

[Lech_Miszkiewicz]

Hi,

I agree that creating a QVD files/layers of RAW, Transformed qvd etc.. and reusing them for Qlik applications is a solution to go for.

However if there is an option where you can build views in SQL, for example to create keys, to aggregate data in SQL to lowest necessary granularity in DB then do it in SQL and use aggregated data as your source. You can still store and reuse them as a QVD files. The point is that you have think about whole picture and use common sense to decide which is the best way to go.

I would avoid saying "it is always recommended to convert the script in qlikview" as it is not always true.

Cheers

Lech

[luiskeyrus]

i think the same. Thank You karthikeyan1504