7 Replies Latest reply: May 10, 2013 8:13 AM by Bill Britt RSS

    Qv Data refersh at mulitple times

    Udit Kumar Sanadhya

      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

        • Re: Qv Data refersh at mulitple times
          vikas mahajan

          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

            • 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.

              • Re: Qv Data refersh at mulitple times
                Udit Kumar Sanadhya

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

                  • 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.

                    • Re: Qv Data refersh at mulitple times
                      Michael Jordan

                      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

                        • Re: Qv Data refersh at mulitple times
                          Nilesh Gangurde

                          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

                    • Re: Qv Data refersh at mulitple times
                      Bill Britt

                      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