AUTOMERGE

AUTOMERGE

DrFosterman
DrFosterman

November 17th, 2016, 7:18 pm #1

If we make this setting "Never" does anyone know what the pitfalls are? What we are trying to achieve is having our spreadsheet sends not take so long to occur due to the overhead of automatic merges. We have a daily job that merges all slices at the end of the day.
Quote
Share

Pete
Pete

November 21st, 2016, 2:55 am #2

I didn't realise that smatview submissions were treated as slices? Thought it was only data loads (haven't spent a lot of time with users submitting directly into ASO via smartview). I suppose that makes sense though - otherwise smartview load performance would be horrific.

And for the pitfalls - fundamentally query performance will start to suffer as you have more slices sitting between the baseline data and the query. I'm not sure anybody would be able to quantify exactly what it will look like in your specific case - would seem to heavily depend on the data intersections that were loaded and the associated optimisations (and aggregate views) that already existed on the system.

You do seem to have the option of using the SELECTIVE option - could potentially tweak that have it occur less frequently?

SELECTIVE—Specifies to activate the incremental data slice auto-merge process when the number of incremental data slices specified in the AUTOMERGEMAXSLICENUMBER configuration setting is exceeded. If the number of incremental data slices in the data load does not exceed the value of AUTOMERGEMAXSLICENUMBER, the auto-merge process is not activated.

Default is 4 - maybe try upscaling that to 10 - 20 - 30 and running some test cases over it. Would definitely make an interesting case study - can't seem to find anyone writing much about it.

Quote
Share

TimG
TimG

November 23rd, 2016, 1:16 pm #3

If we make this setting "Never" does anyone know what the pitfalls are? What we are trying to achieve is having our spreadsheet sends not take so long to occur due to the overhead of automatic merges. We have a daily job that merges all slices at the end of the day.
Just for my own education, how hard does this hit you in performance terms? I assume in your case you definitively tied poor send performance directly to those that trigger the auto-merge. The auto-merge is only merging multiple incremental slices into a single incremental slice, right - i.e. it never does the (really costly) full merge in to the "primary" slice?

Obviously Oracle had a reason to make this feature available, but as is often the case the docs provide a technical description without much practical guidance...
Quote
Share

DrFosterman
DrFosterman

November 25th, 2016, 1:45 pm #4

What we have seen is, when a cube is aggregated (in our case 10gb +) and has incremental slices with incremental aggregated cells, a send of 300K-500K cells can take betwen 10-20 mins as the automerge kicks in for every line of the send. The way the send is formatted also seems to make a difference as the more columns you have the more data is being sent and automerged (it appears to go line by line)


In testing we found that when we merge all slices into the base and then load the same file through the Excel Add-in, it takes a matter of minutes.
Quote
Share

Pete
Pete

January 10th, 2017, 12:20 am #5

Interestly, on PBCS they seem to have set the automerge limit significantly higher. One of my guys was complaining about performance issues while trying to do data validation. On checking in Calc manager there were 13 incremental slices created, with a volume of data around twice what the base data set was!

You can merge the slices back quite easily within calc manager, but I can't find a way to add that into an overnight process via EPMAutomate \ REST API - it doesn't look like the merge function is available yet.

Quote
Share


Confirmation of reply: