Scripting Knowledge

[kakani87]

Dear Folks ...

I want to know about the scripting part to improvise and reduce the load time

We follow three tire Architecture (Correct me If am wrong)

Tire1-Actually we do extract data day wise into QVD's

Tire2-Now load the extracted qvd and perform transformations and summarize month wise using monthend() and store the transformed qvd

Tire3-Finally using transformed qvd's we build data model

Every day all the data for the current year will be loaded from min date(start date) to till date.

Apart from this is there any other approach to follow.

Now can we do something to reduce the load time

Our current server configuration as follows

64 gb Ram and

Processor Intel(R)  Xenon(R) cpu E5 2609 0@ 2.40GHz 2.40GHz

OS-64-bit

application size 360 mb, 350 mb

mbaeyens‌

stalwar1‌

tresesco‌

Regards,

Kakani.

[Miguel_Angel_Baeyens]

There is nothing wrong with that approach.

The server configuration is missing, but anyway it would need the context of how often those reloads happen, which scripts are loaded every time, how those scripts look like, etc, etc.

You will now have to check all your scripts for unneeded JOINs or CONCATENATEs, use of alphanumeric values instead of numeric only, and all the other best practices you can find, plenty, on the Qlik Community and many other valuable forums about Qlik.

[kakani87]

I heard something like using subroutines to generate fact data for incremental load procedure like loading data for only based on last executed date but not entire data to load daily.

If so kindly explain this process to follow

[Anonymous]

Hello Sai Kishore,

which tire (Tier-1, Tier-2, Tier-3)  is taking more time?

and what are the transformations you are using?

[kakani87]

A document size is 360 MB it takes Almost 1 minute to display on opening

Lets say in this dashboard data is being summerized with monthend()

and calculations like below for salesvalueYTD, salesqtyYTD,tgtvalueYTD,tgtqtyYTD

if

(

itemcode=Previous(itemcode) and mrcode=Previous(mrcode) and Yr=Previous(Yr) and (MonthNum-1=Previous(MonthNum) OR (MonthNum-1=0 and Previous(MonthNum)=12)),

salesvalue+peek('salesvalueYTD'),salesvalue

)as salesvalueYTD,

All the above things with summarized is being handled in one layer

Where the avg load time of this dashboard in QMC shows as 100 mins to 120 mins

Before this FY it was 75 to 80 mins of execution time.

Contains of data from 2014 to till date

[Miguel_Angel_Baeyens]

How are those subroutines created in the script? Do they call to external macros (not in the script)? Loops are perfectly fine, and sometimes required to load the data the way you need.

Again, there are plenty of recommendations here in the Qlik Community (for one, the entire Qlik Design Blog with entries mainly from HIC), related to loading multiple files, like use a DO WHILE or FOR NEXT loop instead of LOAD *. But there are many more, and very likely some of them cannot be applied because your source data is like it is, and you need to transform this data to be usable in Qlik.

[Miguel_Angel_Baeyens · Accepted solution]

Opening time is related to the data but mostly, to the front-end of the application, meaning triggers, variables, objects, expressions, conditions and the like, and section access.

As a rule of thumb, the more objects you have, the more variables must be calculated at all times and the more complex the expressions and dimensions are, the longer it will take to open.

With section access, if it is set to reduce, the reduction happens when the user first logs in. If the size is big in memory, reducing can take some time.

Also, it is linked to the DSC service, in case you are using QlikView Server and the size of the .Meta and .Shared (or .TShared) files for that QVW. To a lower extent, even .PGO files as well.

Similarly, the bigger these files are, the longer it will take to open. If the DSC is set to resolve groups and the security directory has a lot, or if it is slow resolving group membership (or because the connection is not good), the opening time will be increased.

[kakani87]

Thank's a lot Baeyens for guiding me.

Not implemented subroutines in our current dashboard, but i heard from some others like can be done using subroutines.

And that can be handled based on last execution like to load > last execution date, as  historical data avail and want to add the data > last update.

I do not have the knowledge of writing subroutines

I may not be able to explain properly, hope you understood what my concern is.

Intention behind this is to improve my knowledge on scripting part to be able to handle script on my own.

[Miguel_Angel_Baeyens]

What I meant is that even if your script is perfect, there are other factors to take into account for performance, in particular for opening times.

With the server and size of the application, it should be running fine. The size of the application though is not what matters, but the size in memory. If in memory it also uses 300MB you will still need to check all those external factors.

As an example, if the DSC is set to resolve groups for another domain because the environment in your company is multi domain, and this action takes 10 seconds, there is nothing in your application you can do to improve it.

But if you add more data because you need more data, expect the size to increase and also the response times opening and browsing, which is normal.

If you need to take over an application you did not develop from the beginning, you will need to understand how the data is stored after extraction and how this data is loaded into the final application, but also how the expressions are working.

For example, in the front-end application can you replace some If() statements in expressions by set analysis? Or can you move some of the calculated dimensions to the script and create a new field?

For the reloads, in the tier 2, can you create another layer of QVDs which store data for the range of dates you want to use only so for example, you only load dimensional data relevant for that period of time? Probably using EXISTS() will help.

However, it's rather impossible to say which one of these you have to use without knowing the environment, usage and how the applications were built in the first place.

If you see a serious performance degrade I would suggest to engage with Qlik Consulting or a trusted partner (there are also plenty around here in the Qlik Community) to help you address your specific needs, not with generic recommendations.