Dear All,
I am Sharing some important points while my experiance I have collected some importants facts about data model from community and my experiance while implementing projects
There are some guidelines that can be generally applied, although the best knowledge of the datamodel is obviously yours and sometimes some of those guidelines are not possible to follow. But in short, a few steps I always take into consideration are:
1.- 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.
(Because QVD IS 3 Times FASTER THAN OTHER DATA SOURCES)
2.- 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 souce you have a table with Customers, another one with Group of Customers, another one with Addresses of Customers, well, join 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.
3.- Avoid IF() conditionals in any object expression, use instead set analysis.
4.- 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).
5.- Note that QlikView is case sensitive, so when possible, set all your values to be in the same case.
6.- Use builtin actions instead of macros
7.- 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.
8.- Take to the script all formatting, conversions, mapping... all complexity you can.
9.- Avoid RESIDENT loads. If needed, load twice from a QVD.
10. Avoid MACROS use Qlikview Inbuild functionality
12 Use SQL Joins & FLAGS to achive certain results rather than more complicated set analysis and syntax.
13 IF you are using windows schedular for sending mails please check memory and cpu utilization some time we face
problem in memory and cpu usage.
Again, most of the above are obvious and very vague, since your application and requirements will make life more complex. These are not MUSTs, rather than STRONGLY RECOMMENDED.
In addition, note that the rendering engine of QlikView uses all your CPU and GPU and memory available in your computer, while the QlikView Server may be a shared computer, using version 9 doesn't use multithreaded extraction, and even so...
NOTE: Quoting QlikTech's Brad Peterman (from the "Large Data Volumes Roundtable" in Qonnections few weeks ago), make sure you do need all those records, because you might not need them depending on the analyses you want to perform, meaning that that your database has 35M records doesn't mean you need to load them all into memory if they are not going to be displayed or used according to the level of detail the customer wants...
Hope that helps.
Vikas Mahajan
Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
