Memory problems (?) with point-in-time data: need to adjust load process?

[#1]

I am currently evaluating QlikView (QVPE x64) to use as dashboard. I am running into load problems really quickly and I was wondering whether this is attributable to the structure of my dataset, a limitation of QlikView or just my (incorrect) way of importing the information into the project.

My dataset has this structure:

1. Table ACCOUNTS with columns REPORT_DATE, ACCOUNT_ID, ... (+- 400 items per report date)

2. Table ASSETS with columns REPORT_DATE, ASSET_ID, ... (+- 50 items per report date)

3. Table ACCOUNTS_CASHFLOWS with columns REPORT_DATE, ACCOUNT_ID, CASHFLOW_DATE, ... (+- 15.000 items per report date)

4. Table ASSETS_CASHFLOWS with columns REPORT_DATE, ASSET_ID, CASHFLOW_DATE, ... (+- 16 items per report date)

So basically for every report date the entire set of accounts, assets and their cashflows is added to the dataset. As a result many duplicate values exists (except for the reportdate, that is) though values for ACCOUNT_ID and ASSET_ID are consistent over time.

I can create and succesfully load this information in an empty project if my sample dataset contains data of a few working days. However, as the number of report dates increases to around 10, I get the "Execution of load script failed" error - this always happens when linking information in tables (e.g. $Syn 1 = ..., $Syn 2 = ...). I do understand there's a flaw in my data model, but we're talking about 160.000 records in total; even MS Access handles this easily 😉

My data load process is very simple (perhaps too simple?): I just press the "Table files" button from the Data from Files group in the script editor, which automatically generates basic LOAD statements for me.

What should I do to get QlikView working without having to resort to measures that limit the amount of data imported? My options of changing the data structure are limited, as these are just dumps of existing processes.

Thanks

[rbecher]

Hi,

you shout try to omit synthetical keys ($Syn1..) in your case. Especially $Syn3 (REPORT_DATE+ASSET_ID+ CASHFLOW_DATE) is very long and will take some memory.

You can do this in different ways. Best way could be the usage of surrogate keys. You can create this by autonumber.

- Ralf

[#3]

Thanks for your reply! I've manually added surrogate keys. I didn't know this had such a huge impact - larger files do now load.

QlikView says that I have a loop in my data, probably through the CASHFLOW_DATE columns in the CASHFLOW tables and REPORT_DATE in the ACCOUNT and ASSET tables (I've removed REPORT_DATE entirely from the CASHFLOW tables). What's the best way to approach this?

[edit]

After reading about some 'workarounds' (concatenates and joins), I think I don't really grasp the nature of the limitation. Yes, there is a loop in my data - but I WANT that loop to be there, as the data is very tightly connected: individual accounts and assets directly relate to their cashflows (based on their now new surrogate keys), but the cashflow schema also relate with eachother, as they can occur at the same point in time. Furthermore I also want to compare data from different reporting dates (basically 'states' of our processes) with eachother.

Do I really need to split my connected dataset into disjoint views to observe relationships between different aspects of the data?

[rbecher]

Can you upload an example qvw?

[rbecher · Accepted solution]

If I understand it right:

you should put the both CASHFLOW data into one table by concatenate including REPORT_DATE and COUNTRY. Those two fields should not be in ASSETS or ACCOUNTS. You need to join COUNTRY to the CASHFLOW data.

- Ralf

[#6]

Basically you're saying I need to create a separate view for each component of my report, taking good care that field names don't overlap between views. That is not really elegant and does add quite some work. But alas - I understand that's how Qlikview works.

Thanks for your help!

[rbecher]

No, you can have everything in one view, maybe even in one chart..

- Ralf