Data persistence in Datasphere

[jeanramon]

Hello, I am using the data persistence in Datasphere. Based on the log, the number of records inserted in the table inflates to about 178x so the initial number of records which is 750k becomes 134M. This also inflates the amount of memory being used. Has anyone encountered this? Anyone with data persistence knowledge can share anything more about it? Thank you!

[appel_solar_dk]

Have you tried to count the records? Are you sure that you don't have associated 1:n so that your base data is exploded into multible rows? You will only see that when you start to use the associated fields.

[jeanramon]

@appel_solar_dk I tested this with a SQL view. I counted the rows when persisted and it had indeed 134M rows but when I counted the rows with the virtual I get the correct 750k rows. Any association should also show up in the virtual version right?

[appel_solar_dk]

If you count(*) on a view it will not "open" the association and also left outer joins are not opened. If you persist the view it must as I see it open all associations and left outer joins and that is where you could have a 1:n relation that explodes your data

[jeanramon]

@appel_solar_dk I have only left joins onto the original source of 750k rows so they should not be multiplying e.g. original source left join with Master Code so then the original source will have Master Code description from Master Code view. That would not multiply the records right?

[appel_solar_dk]

It sounds correct. You will need to create some more sql counting or maybe create a select minus / except query to find out what is the difference between the two sets

[IGPRIETO]

Hello,

this persistence problem happened to me several times. It´s usually caused by the wrong use of join cardinalities that can multiply rows.

Join cardinality settings are used for optimization and can lead to unexpected behaviors if the data doesn’t match the stated cardinality

Hope it helps!

[Snehi_Kaushik]

I was facing a similar issue in one of our graphical views where a left outer join with the fiscal calendar table was causing record inflation. When DISTINCT was applied in the DSP graphical view left outer join, fyi- I kept the cardinality to default (blank), the record count matched the expected output. Try checking the distinct check box in the join to get the distinct values based on the keys