Incremental load from QVD is not being optimised with single where clause

[#1]

Is anyone able to spot the issue with my load script? Everything is loading as expected, but the Fact_Sales load from QVD is not optimised. I've read through other posts and they all refer to ensuring a single where clause is included in the load script. One of the loads I'm doing uses about 5 million rows of data and it is quite noticeable.

Note: I'm forcing an audit record of 1 in the incremental data load for testing purposes.

I've tried using a Where Exist, Where Not Exists.I have also tried using Concatenate instead of same table name with no luck.

LET vStore = 'Store1';

LIB CONNECT TO '$(vStore1DB)';

// Grab Max Audit

$(vStore)_MaxAudit:

LOAD MAXAUDIT

FROM [lib://QVD/$(vStore)_MaxAudit.QVD]

(qvd);

Let $(vStore)_Audit = peek('MAXAUDIT',-1,'$(vStore)_MaxAudit');

DROP TABLE $(vStore)_MaxAudit;

//Load incremental data

$(vStore)_FACT_SALES:

LOAD

DATEID&TIMEID&PRODUCTID&STOREID&REGISTERID&CLERKSHIFTID&PROMOTIONID&CUSTOMERID&TRANSACTIONNUMBER&SALESEQUENCE as PRIMEID,

DATEID,

TIMEID,

DATEID&'-'&TIMEID AS DATETIMEID,

'$(vStore)'&'-'&PRODUCTID AS PRODUCTID,

'$(vStore)'&'-'&STOREID AS STOREID,

'$(vStore)'&'-'&REGISTERID AS REGISTERID,

'$(vStore)'&'-'&CLERKSHIFTID AS TRANDATEID,

'$(vStore)'&'-'&CLERKSHIFTID AS CLERKSHIFTID,

'$(vStore)'&'-'&PROMOTIONID AS PROMOTIONID,

'$(vStore)'&'-'&CUSTOMERID AS CUSTOMERID,

'$(vStore)'&'-'&TRANSACTIONNUMBER AS TRANSACTIONNUMBER,

'$(vStore)'&'-'&SALESEQUENCE AS SALESEQUENCE,

AUDITID,

'$(vStore)'&'-'&EJHEADERID AS EJHEADERID,

'$(vStore)'&'-'&EJPLUOBJECTID AS EJPLUOBJECTID,

ISSECONDPRICESALE,

ISCUSTOMPRICESALE,

ISRETURNED,

QUANTITY;

SQL SELECT

DATEID,

TIMEID,

PRODUCTID,

STOREID,

REGISTERID,

CLERKSHIFTID,

PROMOTIONID,

CUSTOMERID,

TRANSACTIONNUMBER,

SALESEQUENCE,

AUDITID,

EJHEADERID,

EJPLUOBJECTID,

ISSECONDPRICESALE,

ISCUSTOMPRICESALE,

ISRETURNED,

QUANTITY

FROM "FACT_SALES"

WHERE AUDITID > 1//'$($(vStore)_Audit)'

ORDER BY AUDITID;

LET vNoOfRows = NoOfRows('$(vStore)_FACT_SALES');

IF  $(vNoOfRows) > 0 THEN

// Update Max AuditID from latest increment.

Let $(vStore)_Audit = peek('AUDITID',-1,'$(vStore)_FACT_SALES');

$(vStore)_MaxAudit:

LOAD * INLINE [

MAXAUDIT

$($(vStore)_Audit)

];

Store * from $(vStore)_MaxAudit into [lib://QVD/$(vStore)_MaxAudit.QVD];

DROP table $(vStore)_MaxAudit;

// Loading STORE_FACT_SALES Data from QVD

$(vStore)_FACT_SALES:

LOAD

PRIMEID,

DATEID,

TIMEID,

DATETIMEID,

PRODUCTID,

STOREID,

REGISTERID,

TRANDATEID,

CLERKSHIFTID,

PROMOTIONID,

CUSTOMERID,

TRANSACTIONNUMBER,

SALESEQUENCE,

AUDITID,

EJHEADERID,

EJPLUOBJECTID,

ISSECONDPRICESALE,

ISCUSTOMPRICESALE,

ISRETURNED,

QUANTITY

FROM [lib://QVD/$(vStore)_FACT_SALES.QVD]

(qvd)

WHERE NOT EXISTS (PRIMEID);

[#2]

Connected

Store1_MaxAudit << Store1_MaxAudit

(QVD columnar optimized)

Lines fetched: 1

Store1_FACT_SALES << FACT_SALES

Lines fetched: 600

Store1_MaxAudit << INL268A

Lines fetched: 1

Store1_FACT_SALES << Store1_FACT_SALES

Lines fetched: 56,323

Store1_MaxProduct << Store1_MaxProduct

(QVD columnar optimized)

Lines fetched: 1

[petter]

According to the the online QlikSense documentation (and the QlikView Reference Manual in Chapter 28 - QVD Files):

Optimized mode can be utilized only when all loaded fields are read without any transformations (formulas acting upon the fields), though the renaming of fields is allowed. A WHERE clause causing QlikView to unpack the records will also disable the optimized load.

[#4]

Thanks for the comment Petter, I've ensured that when loading the QVD the same fields are loaded without transformation.

In relation to the Where clause, I was under the impression that a single where clause can be utilised with the load remaining optimised as referenced here: http://www.quickintelligence.co.uk/qlikview-optimised-qvd-loads/

[Anonymous]

Simon,

Handle all of your transformations in a resident table load after your initial optimized load. 

Not all single where clause's will keep the optimization.  From what I have found, Exists() is the only where clause that can be used.

[#6]

Ok Darrin, so your suggestion here is to load the QVD without any where clause and then use a resident load with a where not exist clause to create my new Store1_FACT_Sales.QVD?

[Anonymous]

Simon,

No, you can do the not exists() on the initial load and keep it optimized.  Once you have reduced the majority of your data down that you can with an exists you can then reduce and transform with the resident table.

I always look to see what's the biggest bang for my buck I can get on my exists when reducing my data.  If it's a date, then load all dates into a temp table you can use with exists, etc.  You get the idea.

I hope this helps.

Remove:

LOAD * Inline [

CustKey

10

11

12

13];

tmpCustomer:

LOAD CustKey,

     CreateDate,

     CustAcctNum,

     CustTypeKey,

     CustTypeDesc,

     CustFullName,

     TotalSales

FROM

[..\..\Customer.qvd](qvd)

where not Exists(CustKey);

DROP Table Remove;

NoConcatenate          //Just in case

Customer:

Load

     CustKey & '-' & CreateDate as PrimaryKey,

     CustKey,

     CreateDate,

     Month(CreateDate) as CreateMth,

     Year(CreateDate) as CreateYr,

     CustAcctNum,

     CustTypeKey,

     CustTypeDesc,

     CustFullName,

     TotalSales

resident tmpCustomer

where TotalSales > 5000;

drop table tmpCustomer;

[#8]

Thanks for the comment there Darrin but that exact scenario your talking through doesn't work in an "Optimised fashion" on my setup.

The tmpCustomer load from QVD you refer to above is not loading with the "Optimal flags", do you have a DataLoad log that shows it working?

I am running in essence a three step process.

Step 1: DB Query pulling data since the last data collection

-- There are some situations where historical data has been corrected and may already exist in the QVD file.

Step 2: Load from QVD everything that wasn't in the DB Query and join it together

-- The "not exist" clause is used to retain the latest information for a sale record i.e. catering for the above scenario

Step 3: Save the joined data back to a QVD

[Anonymous]

So sorry about that. I was looking at the wrong part of your load statement with the transformations.

[#10]

No Drama's, but I gather you have the same expectation I did.

It might just be a QlikSense specific restriction? No optimal loads if using any where clause.