Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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 fetchedMasterData1 << 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
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
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
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?
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, <...>
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.
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
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
Is there some theory to support this?
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!!