32 Replies Latest reply: Aug 9, 2016 12:43 PM by Vladimir Komarov RSS

    QMC data reload Performance Issue

    Rohit Kumar

      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
          Vladimir Komarov

          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
            Vladimir Komarov

            Adding Binary load should help as well.

             

            VK

              • Re: QMC data reload Performance Issue
                Rohit Kumar

                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
                    Vladimir Komarov

                    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
                  Rohit Kumar

                  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
                      Vladimir Komarov

                      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:

                      Screen Shot 2016-06-29 at 8.19.41 AM.png

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

                       

                      Here is how you control other users' access:

                      Screen Shot 2016-06-29 at 8.29.38 AM.png

                      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?

                       

                      VK

                        • Re: QMC data reload Performance Issue
                          Rohit Kumar

                          Hi Vald,

                           

                          I am attching the snapshot below:

                          Domain: UIC

                           

                          Administrator Name : Administrator

                           

                          User Name: RohitkTk.PNG

                           

                          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

                           

                          tk2.PNG

                            • Re: QMC data reload Performance Issue
                              Vladimir Komarov

                              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.

                               

                              VK

                        • Re: QMC data reload Performance Issue
                          Rohit Kumar

                          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

                           

                          Err2.PNG

                          • Re: QMC data reload Performance Issue
                            Rohit Kumar

                            Hi Vlad,

                            Thanks for continuously replying. The point here is , In your interface option is coming "User Access" and in my interface it is coming "USER" . So my  question is, Why there is no option of "User Access" at my end. I attached snapshot of security rules to show you that these are my default rules. As what you said click one Secuty rules ,I clicked on it and attaching the snapshot , it is giving me by default four rules not the window what do you haveCapture.PNG

                            • Re: QMC data reload Performance Issue
                              Rohit Kumar

                              Hi Vlad ,

                               

                              Its really great that you are trying and keep responsive until now , Really I must appreciate you.

                              I am attaching the snapshot of my current installed Qlik Sense Server Version , hope this will make you know what you need Capture.PNG

                                • Re: QMC data reload Performance Issue
                                  Vladimir Komarov

                                  That explains it..

                                  You are using a year old version (2.0) - from June 2015.

                                  I am using 2.2.4 release (March 2016) and  Qlik has released  3.0.1 version just few days ago.

                                   

                                  Any reason you did not upgrade to at least 2.2? I am holding on 3.0 upgrade because of the issues in the original release...

                                   

                                  Vlad

                                    • Re: QMC data reload Performance Issue
                                      Rohit Kumar

                                      Hi vald

                                      Hope you are doing well .

                                      As I heard Qlik sense 3.0 server is not stable or in beta phase that's why I was afraid to lose my current content . But as I believe in your suggestions, I will upgrade my Qlik sense to latest version ,then I will let you know if I will still face any issue.

                                      Vald one more concern. , I  placed a logo or image as PNG format on sheet and try to export it but when I export the sheet as PDF it not comes in PDF, if I am right  same old version doesn't have this feature. Please correct me if I am wrong.

                                       

                                      One last question: if someone update security rules which by default with installation around 40, how to reset them as by default stage as reinstall is not a solution as it could make lose lots of content

                                        • Re: QMC data reload Performance Issue
                                          Vladimir Komarov

                                          I would wait for few more days and monitor the forum for 3.0.1 (SR1) release issues (the 3.0.0 one was bad).

                                          The 2.2.4 was pretty stable...

                                          Let me know if you will decide to go for 2.2.4. There are few steps recommended to do before the installation.

                                           

                                          For your second question: are you trying to export the story (built from your page's objects)?

                                          Or just a page?

                                           

                                          I've tried both cases and I was able to see the image (from "Text/Image" control in the exported PDF file).

                                          Here is the page's screenshot:Screen Shot 2016-07-28 at 11.57.57 AM.png

                                           

                                          The image control contains "the lock" picture.

                                          Here is the PDF:

                                          Screen Shot 2016-07-28 at 11.57.33 AM.png

                                           

                                          Please note that the Calendar Object and Advanced Text box objects are not displayed.

                                          But they are both custom Extensions... (it's a different story how to handle them in reports).

                                          But the image is visible...

                                           

                                          What kind of error your PDF is displaying? How you are storing your images? In the QVF? Or in Repository?

                                           

                                           

                                          And I did not get the question about Security rules changes after the upgrade. Could you please elaborate on it?

                                           

                                          Regards,

                                          Vlad

                                    • Re: QMC data reload Performance Issue
                                      Rohit Kumar

                                      Thanks Vlad, pleas update me when you done with testing. Second, you are the best person on this community Vlad,always comes with best solution