3 Replies Latest reply: Jan 15, 2015 8:45 AM by Clever Anjos RSS

    Advice on load optimization

    Julien Vaillant

      Hello,

       

      I use Qlikview mainly with SAP, and I use the SAP connector to get data from the ERP.

      I use a three tier structure with:

      1. QVDExtractor: In this folder, I put the QVWs that I use to extract the raw data from SAP. I only copy and paste the script given by the Script Builder application. The extracted QVDs are also in this folder.
      2. QVDTransformation: In this folder, I put QVWs where I create generic QVDs for SAP objet (Customer_MasterData.qvd, Product_MasterData.qvd etc).
      3. QVW: This is where I put the final applications.

       

      I created an application with about 15 millions rows in the fact table, which takes approximately 30 minutes to reload. While looking for advice on optimization, I learned more about optimized loads.

       

      In this application, I have some code like the following one:

       

      Concatenate([FactTable])

      LOAD

        [Inv_Invoice Number] AS %InvNumber_Key,

        [InvLine_Sales Document] AS %SalesDocument_Key,

        [InvLine_Sales Document] &'/'& [InvLine_Sales Document Item] AS %SalesDocumentItem_Key,

        [Inv_Sold-to Party]&'/'&[Inv_Division]&'/'&[Inv_Sales Organization]&'/01' AS %Customer_Key,

        [Inv_Sold-to Party]&'/'&[Inv_Division]&'/'&[Inv_Sales Organization]&'/01' AS %Security_Match,

        Date([Inv_Billing Date]) AS %InvDate_Key,

        Date([Inv_Billing Date]) AS %MainDate_Key,

        [Inv_Document Condition]&'/'&[InvLine_Billing Item] AS [%InvLineCondition_Key],

        [Inv_Division] AS %Division,

        [Inv_Sales Organization] AS %SalesOrganization,

        Upper([Inv_Sales Organization Description]) AS %SalesOrganizationDescription,

        'Invoice' AS [%Type] 

      FROM $(vDirQVDTransformation)XXXX.qvd (qvd)

      WHERE Date([Inv_Billing Date])>=$(vStartDate);

       

      From what I understood, I have a lot of things to improve to have an optimized load. In the following case, does this mean that ;

      • I should change the format of [Inv_Billing Date] and other fields like that directly in the QVD Transformation folder, and not in the final application?
      • I should create keys directly in the QVD Transformation as well? For instance, %Customer_Key links my fact table to my customer table, and I was always advised to create keys in the final application; In this case, should I create a field like [Inv_Sold-to Party]&'/'&[Inv_Division]&'/'&[Inv_Sales Organization]&'/01' AS [Inv_LinkToCustomerTable] directly in my QVDTransformation, where I will make my calculations anyway?

       

      Thank you for your insight in advance.