topic Re: QMC data reload Performance Issue in App Development

QMC data reload Performance Issue

Thu, 09 Jun 2016 05:06:45 GMT

Hi,

I am facing an scenario where database is oracle , tables count is 150 and data size is around 500 GB, Now, I am executing it 4 times is or data refresh cycle is 4 times in day but each time it takes like 15 to 20 min to reload or refresh, Can anyone help me how to Increase the performance , less data refresh time ,

Point here is I am looking for an approach , how to start analyzing , ETL part, Modeling, App creation, So I will have a way to follow to increase performance ...

This is a very common requirement comes by client

Re: QMC data reload Performance Issue

vlad_komarov — Thu, 09 Jun 2016 14:08:18 GMT

Are you using a full reload every time? If not, you can organize your script to do an "incremental" reload (loading only rows that were updated after the last reload was completed).

It should be the simplest way to improve your performance....

Regards,

Vladimir

Re: QMC data reload Performance Issue

vlad_komarov — Thu, 09 Jun 2016 14:09:24 GMT

Adding Binary load should help as well.

Re: QMC data reload Performance Issue

Fri, 10 Jun 2016 04:42:57 GMT

Hi Vlad,

Thanks for the suggestion, See what I understand by Binary load , It just for access the qvf or qvw file. Can you please help me to understand how do binary load improve performance or data refresh ?

Re: QMC data reload Performance Issue

vlad_komarov — Fri, 10 Jun 2016 13:16:17 GMT

Hi Rohit,

Binary load is useful when you are loading all your data into main (UI-based) application .

I do not know if you are using 2-3 tier architecture in your application (or using data load from database to your app directly), but this is what I would recommend in your case:

Tier 1: Organize your data extraction to handle incremental load(s):

Data:

LOAD

;

SQL SELECT *

FROM "Oracle.Data.Table"

WHERE period_start_date > '$(MaxDateTime)'

;

Store Data into _LastLoad.qvd;

Tier 2:

Do necessary transformations

....

Tier 3 (could be combined with or used instead of T2)

Binary [lib://QS Apps/aaaaaa-abbbbbb-cccc-dddddddddd];

// DataFull table will be loaded

DataFull:

concatenate load

FROM _LastLoad.qvd;

__________________________________________________________

This approach will allow you to save a lot of time during overall reload process.

In my case (20 M total records, application must be updated every hour) - direct load took ~20-25 mins.

Using incremental and binary loads allowed me to refresh the app within ~2-3 minutes.

Regards,

Vladimir

Re: QMC data reload Performance Issue

Thu, 23 Jun 2016 11:05:18 GMT

Hi Vlad,

Thanks for your inputs!

One point here is , Why do we need binary load , I agree Incremental process saves lots of time but creating a new application and then by Binary load access the previous application which has incremental code and transformation scripting. My question is how come Binary load saves time while refreshing ?

Re: QMC data reload Performance Issue

vlad_komarov — Thu, 23 Jun 2016 13:18:39 GMT

Rohit,

The Binary process was designed to use "optimized" load for all data from the "data model" application into your "UI-app".

It's like using QVD load without transformation, but not for individual QVD files, but for the whole data structure.

Qlik will load your data significantly faster in this case.

So the 2-3 tier approach requires additional development, but saves total reload time..

I hope I've answered your question.

Regards,

Vlad

Re: QMC data reload Performance Issue

Mon, 27 Jun 2016 10:12:41 GMT

Hi Vlad,

Thanks for the optimistic answer. So from now , I will make two applications for a project, First contains incremental and Scripting part and Second will access first one and all the visualization will be done on second application.

Hope you are saying same above.Please correct me if I am wrong

Re: QMC data reload Performance Issue

vlad_komarov — Mon, 27 Jun 2016 13:10:14 GMT

Rohit,

That's correct.

Vlad

Re: QMC data reload Performance Issue

Mon, 27 Jun 2016 13:34:14 GMT

Thanks ,

One more thing ,can you please tell me how to do this binary load on Qlik sense server ?

Re: QMC data reload Performance Issue

vlad_komarov — Mon, 27 Jun 2016 15:07:39 GMT

Please check here (https://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Binary.htm?q=binary), but the short version:

1. You need to add a Binary Statement to your script:

Binary [lib://Qlik_Apps_Connector/a4484de1-fud1-4954-b5t5-911100000000];

Please note that it should be the VERY FIRST line in your script (before SET definitions)

2. You need to use a App's ID (a4484de1-fud1-4954-b5t5-911100000000), not QVF file name there.

3. You can get this ID when open the app in the browser:

That's it.

Regards,

Vlad

Re: QMC data reload Performance Issue

Tue, 28 Jun 2016 06:54:14 GMT

Hi Vlad,

It is giving me an error of Data connection not found (Qlik_Apps_Connector). I think I need to create a new Data connection first with administrator login because when we install Qlik Sense server with administrator credentials the folder only accessed to Administrator where all the application saves same as in Qlik Sense Desktop ,where applications saves in

Documents=Qlik=>Apps.

Currently I am logged in with Non Administrator User.

Please confirm Do I need to apply some permission as when Binary load will work , It can access the data connection made by administrator ?

Re: QMC data reload Performance Issue

vlad_komarov — Tue, 28 Jun 2016 13:07:45 GMT

Hi Rohit,

Yes, you need to create your new connector (Folder type) to the directory where your applications are located.

I've used a Qlik_Apps_Connector connector as an example.

And yes, it's recommended to use a admin account to create this connector.

Vlad

Re: QMC data reload Performance Issue

Wed, 29 Jun 2016 09:29:30 GMT

Hi Vlad,

Thanks for your response.

I have created a new Folder connection with name Qlik_Apps_Connector but when I saved it the user name administrator comes with connection name like Qlik_Apps_Connector(Domain_administrator). then I logged out and logged in with USER1 and then name of connection become Qlik_Apps_Connector(Domain_administrator)(Domain_USER1) . Can you please explain why this is happening and for your knowledge USER1 has root admin rights. what id USER1 doesn't has root admin rights ?

Re: QMC data reload Performance Issue

vlad_komarov — Wed, 29 Jun 2016 13:39:26 GMT

Rohit,

If I understand you correctly, the Connector's name is updated as you log-in as different user.

Could you please provide a screenshot for that?

It should not happened. You can create a Connector with Admin User account, share it with all users and it should do it.

Here is my system:

As you can see the connector uses the original owner's name.

Here is how you control other users' access:

Make sure you give full access to Admins and System accounts (red rectangles)

Just curious: did you try to edit the first connector created by Domain_administrator account from Domain_USER1 account?

Re: QMC data reload Performance Issue

Fri, 01 Jul 2016 07:34:17 GMT

Hi Vald,

I am attching the snapshot below:

Domain: UIC

Administrator Name : Administrator

User Name: Rohitk

Note:Rohitk has root admin access. Do I need to care about the permission of data connection for Rohitk ?

Secondly , I am not sure for your last point (Just curious: did you try to edit the first connector created by Domain_administrator account from Domain_USER1 account?), May be I did because the connection with name Qlik_Apps_Connector(UIC_Administrator) [Initally showing when i logged with Rohitk account] ]was not working initially.

Third: Once i have tried to create a connection by Rohitk but when I tried to run the script with same connection it was failing , Can you please add your notes on this too.

Please explain in little explain so I can pick it with step by step

Re: QMC data reload Performance Issue

vlad_komarov — Fri, 01 Jul 2016 18:51:31 GMT

I never saw the case when the connector name was updated like you are showing.

You probably did something unusual to cause that...

But it should not really matter: you should be able to use this connector from any account you've granted access for.

Just re-create a connector from Admin account and try to use it (without modifying it from other accounts)

Based on your screenshot you gave Admin and yourself just a Read and Update access. Why not a full access (see my screenshots above).

Did you give an access to other people?

What kind of error(s) you are receiving while executing the script? What's your "binary load" statement?

You can attach a logs here so the issue will be more clear.

Re: QMC data reload Performance Issue

Tue, 12 Jul 2016 09:15:34 GMT

Hi Vlad,

I am logged in with a user which has root admin role granted. But I couldn't find this window which you mentioned above . Can you Please help me to know how you reached there ? What I understand whenever I create a new connection by Admin account same time I need to grant access to others no matter what role they has, no matter root admin. Please correct me if i am wrong

Re: QMC data reload Performance Issue

vlad_komarov — Thu, 14 Jul 2016 14:35:32 GMT

Here are steps:

In "Data Connections" (1) select your connector, click on "Edit" (2)

Find "Associated Items --> User Access " (3).

Use "Security Rules" option (4) to edit individual rules..

Regards,

Vlad

Re: QMC data reload Performance Issue

Fri, 15 Jul 2016 04:45:04 GMT

Hi Vlad,

Hope you are doing good!

I got the issue what exactly is ,In your screen User access option is coming and in my window User option is coming.

Please help me to find why in my account User access is not coming , even mine user has Root Admin Rights.

Where can be issue is ,Did i updated any rule by mistake ?

I am attaching a snap shot and coming option is highlighted.

in yellow