Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Synthetic Field Performance Issue

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

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

8 Replies
hic
Former Employee
Former Employee

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

Not applicable
Author

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?

Not applicable
Author

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, <...>

Not applicable
Author

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.

hic
Former Employee
Former Employee

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

     x,

     y,

     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

sujeetsingh
Master III
Master III

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

Not applicable
Author

Is there some theory to support this?