topic Advice on load optimization in QlikView

Advice on load optimization

Thu, 08 Jan 2015 13:26:34 GMT

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:

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.
QVDTransformation: In this folder, I put QVWs where I create generic QVDs for SAP objet (Customer_MasterData.qvd, Product_MasterData.qvd etc).
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.

Re: Advice on load optimization

marcus_sommer — Thu, 08 Jan 2015 15:10:10 GMT

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

- Marcus

Re: Advice on load optimization

Thu, 15 Jan 2015 13:12:31 GMT

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

Re: Advice on load optimization

Clever_Anjos — Thu, 15 Jan 2015 13:45:19 GMT

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])