topic Re: Performance Issue I can't explain in QlikView

Synthetic Field Performance Issue

Tue, 04 Dec 2012 08:02:50 GMT

Hi there,

I am attempting to create a QV model from an ERP-grade application. For this, I have "delegated" joins to QV, thinking it was the best approach. I am running this model on a laptop (with 4GB dedicated memoy to the Windows instance I am working from).

Basically, I am attempting to create a sort of a star-schema. I am simplifying the issue, this is what my script looks like:

qualify * 
unqualify A, B, C, D 
// Transactional data
[Dataset1]: 
SQL SELECT A, B, C, D, <...>
qualify * 
unqualify A, B
// 'dimension'
[MasterData1] 
SQL SELECT A, B, <...>
qualify * 
unqualify A, C
// 'dimension'
[MasterData2] 
SQL SELECT A, C, <...>

FYI, this is the data yield:

DataSet1: 100k records
MasterData1: <50 records
MasterData2: <5k records

I am satisfied with the result, it's extracting and processing in less than 5 seconds with a very low processor/memory footprint.

Connecting to XXX
 Connected
 DataSet1 << AAA 112,026 lines fetched
 MasterData1 << BBB 29 lines fetched
 MasterData2 << CCC 4,096 lines fetched
 $Syn 1 = A+B
 $Syn 2 = A+C
 $Syn 3 = $Syn 1+$Syn 2

Next I am adding one more MasterData to the script:

// 'dimension'
[MasterData3] 
SQL SELECT A, D, <...>

I have tried with a MasterData yielding 1 record or 1M records, I get to the same result: I am maxing out ma machine's memory.

Connecting to XXX
 Connected
DataSet1 << AAA 112,026 lines fetched
MasterData1 << BBB 29 lines fetched
MasterData2 << CCC 4,096 lines fetched
MasterData3 << DDD 1 lines fetched
 $Syn 1 = A+B
 $Syn 2 = A+C
 $Syn 3 = A+D

What could explain the sudden explosion in memory usage?

Edit:changed the title for more visibility

Re: Performance Issue I can't explain

hic — Tue, 04 Dec 2012 08:09:54 GMT

The synthetic keys. These are created when you have more than one key between two tables and are usually a sign of an incorrect data model. Probably you should have just one key between each master table and the fact table. So try to remove keys (by renaming the fields) or try to combine two keys into one (by concatenating them: A & '|' & B as Key).

HIC

Re: Performance Issue I can't explain

Tue, 04 Dec 2012 08:12:53 GMT

Thanks Henric for taking the time to answer.

I have read everything and its contrary about synthetic vs. composite keys.

Composite keys are fine with me, it's just that it's making the data model harder to read.

I will give it a try though a report back.

These are created when you have more than one key between two tables and are usually a sign of an incorrect data model

You've ever worked with ERP systems?

Re: Performance Issue I can't explain

Tue, 04 Dec 2012 08:34:09 GMT

Interestingly, turning to a composite key model solved the performance issues.

This is what I have done:

qualify * 
unqualify A&B, A&C, A&D 

// Transactional data
[Dataset1]: 
SQL SELECT A&B, A&C, A&D, <...>

qualify * 
unqualify A&B

// 'dimension'
[MasterData1] 
SQL SELECT A&B, <...>

qualify * 
unqualify A&C

// 'dimension'
[MasterData2] 
SQL SELECT A&C, <...>
qualify * 
unqualify A&D
// 'dimension'
[MasterData3] 
SQL SELECT A&D, <...>

Re: Performance Issue I can't explain

Tue, 04 Dec 2012 08:42:39 GMT

Now, how is that possible to delegate the composite key creation to QV instead of the ODBC driver? This is a concern as the QV document may connect to ODBC to various RDBMS.

Not all support convert(varchar, A) + '|' + B as CompositeKey.

Re: Performance Issue I can't explain

hic — Tue, 04 Dec 2012 09:35:08 GMT

Oh, yes, I have worked with many ERP systems...

My point is that you should not let the ERP system dictate which fields you should use in QlikView. You are in charge and decides what the fields should look like. If the ERP system has two fields that are used as keys in many tables, then you have to create your own QlikView key, by concatenating the two.

Tip1: use <ctrl>-T to see what you have done.

Tip2: use preceding Load to create the keys: e.g.

Load

x & '|' & y as Key,

Amount;

SQL SELECT

Amount

FROM ...

The result from the SELECT will then be piped into the Load. The SELECT is evaluated by the ODBC and the Load is evaluated by QlikView.

HIC

Re: Performance Issue I can't explain

sujeetsingh — Tue, 04 Dec 2012 10:11:35 GMT

Guill ,

Composite keys are good when the data mode is simple..

As the structure will gwt complex they will really create problem.

Even sometimes it make reload to fail

Re: Performance Issue I can't explain

Tue, 04 Dec 2012 23:02:19 GMT

Is there some theory to support this?

Re: Performance Issue I can't explain

sujeetsingh — Wed, 05 Dec 2012 07:04:41 GMT

go to below thread-

http://community.qlik.com/thread/66977

http://www.quickintelligence.co.uk/perfect-your-qlikview-data-model/

http://michaelellerbeck.com/2008/08/22/qlikview-synthetic-keys/

Go to all you will learn a lot!!