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

[Martin_Kuma · Accepted solution]

Persistency will not open ASSOC. Assoc by definition are temporary and will be used by AM only. Not view persisting. 

It has to show 750k. Try to copy the view into a very new one. I have had the issue of obsolete version in runtime (I was deploying to PROD however) and I was reading such issues were not uncommon in DWC. What helped me was to copy the view... 

Try also without any associations if not sure. 

The inflation of 1:n View-Assoc happens in AM

[jeanramon · Accepted solution]

@Martin_Kuma I tried this copy and deleting and ran the persistence but still ended up with the multiplied number of rows

[Martin_Kuma · Accepted solution]

Nice issue you have there :). So you copied, but the view persistency is still inflated? OK., try to copy again and remove all ASSOCs. Then try to re-add some of the ASSOCs. I have Views with 50+ ASSOCs f.e. Also please go to Space Management \ Monitoring and check there how many entries has your persisted view (just as a double check). Also is it possible you have a parameter in the view which persists with different values? Just asking / checking

Last check -> Runtime metrics. What is shown there as count (*)?

[jeanramon · Accepted solution]

@Martin_Kuma The view does not have any associations only joins. In Data Monitoring tab, the persisted data has the multipled rows ~134M. I also have no parameters

[Martin_Kuma · Accepted solution]

OK. Got it. Then go for the left-joins. They will be the root cause. One of them is a 1:n, or maybe even n:m. Remove all left-joins and add one by one. Left join will inflate in case the key is not used. DSP views bypass SQL rules like unique key, not-null key, ... only DSP tables obey the SQL rules.

Possible null to null join 🙂

[jeanramon · Accepted solution]

@Martin_Kuma I found the issue. It appears that there are 2 views that are left joined to the source view but no values match the source view. I am not sure why but somehow removing all the left join and leaving this problematic view alone multiples the records. It doesn't affect the virtual version but somehow it does on persistence.

[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