Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

udit_kumar_sana
Contributor II

Qv Data refersh at mulitple times

Dear All,

There is one requirement of  Refreshing QV data at least at 9 am , 2 PM , 4 PM and 6 PM every day for my client.

Currently we are donig only once at  9am.

The issue scenario is as follows:

---The extractor reload time can be minimized by incremental process for this mulitiple times data refresh.

---But the issue comes for all Qvw files refresh.Nearly some qvw files takes min time of 15 min to refresh and some qvw files takes around max 45 min to refersh data.


----With this we are also doing development of other reports on same server and users are also accessing different report from access point.Due to this Ram utilization will very high throught the day & will create issues.

Please guide me for best possible solution for this whole above scenario ,so that we can provide mulitple times QV data refresh every day.

Thanks & Regards,
Udit

7 Replies
vikasmahajan
Honored Contributor III

Re: Qv Data refersh at mulitple times

Dear Mr.Udit ,

As per Sql Schedular and reload times you need to set reload option QMC  as per desired

times also as you said development is on same server then

You need to increase capacity of RAM in server

Also you can implement Incremental Load concept so that the load time you can minimize.

Hope this helps you.

Vikas

Not applicable

Re: Qv Data refersh at mulitple times

1. Increase RAM

2. Recommend cust to make available a new server, where the QVD data can be stored and reload operation of the QVW application can be done, the production live server shld be kept only for user level performances

u van adjust reloads on both the servers without the consent of the client.

this idea will reduce the load which will help you.

also parallel way optimize the reports.

udit_kumar_sana
Contributor II

Re: Qv Data refersh at mulitple times

But how can we reduce refersh time from qvd to qvw data refersh.

Not applicable

Re: Qv Data refersh at mulitple times

Arrange the applications in such a way that u fulfill the following steps,

First get all the RAW Qvds on the staging area.

secondly get all the Model Qvds

lastly Run the live applications.

****###****

Following this steps are subjet to market risks.

amars
Valued Contributor

Re: Qv Data refersh at mulitple times

Hi Udit,

If u wish to use increamental load, u will need to have some Timestamp in ur Database and a primary or a unique key.

Now u can implement it this way at the begining of ur load u can store the current Timestamp

Let vCurExecutionTime = Now();

Data:

Select * from Table

Where UpdateTime >= $(vLastExecutionTime) and UpdateTime <= $(vCurExecutionTime);

Load *

from Table.QVD where Not Exists(Primary Key);

Store Data into Table.QVD(qvd);

Let vLastExecutionTime = vCurExecutionTime;

Explanation:

1. First Step stores the Current TimeStamp

2. Loads only data from LastExecution Time to Current Time

3. Load Previously loaded data where there is no record which is later updated  and is available with data at step 2.

4. Store QVD for next execution

5. Store Current Execution Time as vLastExecutionTime for subsequent reloads to happen correctly.

Now if it reduces ur Reload Time u can set ur reload to Hourly interval and can achieve the desired result.

Thanks

Amar

nilesh_gangurde
Valued Contributor

Re: Qv Data refersh at mulitple times

Hiiiii Udit,

You are syaing that your extractors are taking 15 to 45 mins to run means you are dealing with huge data set. so you can do the following things to optimised the extractor.

1. Find out the which part of the Script is taking max time to load. From my view if you are using increamental load the MAX date calculation will ulilized the 60 % of the time.

The solution for the above prob is to have a separete table of MAX DATE for different tables which will store the Table name and the date of data or timestamp for last load.

2. there is option to control the RAM usage on server.

Go to QEMC => System => Setup => Working Set.

3. If you are doing any of the calculations on Feilds while taking data from database. Do it at the Qvd level. Means first store base data and then aply logics if any.

Hope this will Help you.

-Nilesh

Employee
Employee

Re: Qv Data refersh at mulitple times

Most of the time you are doing a reload to just update a few tables and not all the data. If that is the case you can use the replace option and do a partial reload..

The replace prefix can be added to any Load, Select (SQL) or Map ... using statement in the script. The replace load/replace select statement

has the effect of dropping the entire QlikView table, for which a table name is

generated by the replace load/replace
select
statement, and replacing it with a new table containing the result

of the replace load/replace
select
statement. The effect is the same during Partial Reload and full reload. The replace map...using statement causes mapping to take place

also during partial script execution.

Bill