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,
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.
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.
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.
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();
Select * from Table
Where UpdateTime >= $(vLastExecutionTime) and UpdateTime <= $(vCurExecutionTime);
from Table.QVD where Not Exists(Primary Key);
Store Data into Table.QVD(qvd);
Let vLastExecutionTime = vCurExecutionTime;
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.
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.
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..
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.