Advice on load optimization

[#1]

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.

[marcus_sommer]

With a big recommendation: https://www.packtpub.com/big-data-and-business-intelligence/mastering-qlikview.

- Marcus

[#3]

Well, that was a great read. Thanks. It is much clearer now.

[Clever_Anjos]

Answering your questions:

• 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?
  • Yes, all transformation should be done at transformation
• 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?
  • Yes, all transformation should be done at transformation

This clause

WHERE Date([Inv_Billing Date])>=$(vStartDate); will prevent an optimized load, but there´s a workaround

1) Load all dates that needs to be loaded to a table (search the community for a table

2) Change your WHERE clause to

WHERE exists(YouDateField,[Inv_Billing Date])