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:
[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.