AUTOMERGE

I started writing this post four and one half years ago. I'm finally getting around to finishing it because I had to use it in real life this week.

I ran into what I thought was strange behavior in ASO the other day. I'd submit data to a cell, then submit a zero. I'd see a zero. Then I'd submit data to a cell, then submit a zero. I'd see a #MISSING. I'd repeat this over and over again. The first time I'd get a zero, the second time I'd get a #MISSING. At least that was consistent!

I had a hunch it had to do with ASO data slices and the merge process. In 11.1.2.4 the default ASO behavior is for slices to be automatically merged after some number of slices have been created. The two essbase.cfg settings that control this are AUTOMERGE and AUTOMERGEMAXSLICENUMBER.

So the first send of a number created slice 1, the send of the zero created slice 2, the send of the next number created slice 3, the send of the next zero created slice 4. Four happens to be the default value for the AUTOMERGEMAXSLICENUMBER setting. ALWAYS happens to be the default value for the AUTOMERGE setting. So when that fourth slice was created, it triggered Essbase to automatically merge the slices back together.

The documentation states that the merge happens after the slice number is exceeded, so it should happen on the fifth load of data. I observed that it was happening on the exact number, so on the fourth load of data.

The one thing you can't control here is that when the merge happens it will turn your zeros into #MISSINGs. In most cases this is fine, but in certain circumstances when you need an actual zero, it could be a problem. Merging the data slices using MaxL allows you to control whether or not the zeros are removed.

alter database ASOsamp.Sample merge all data remove_zero_cells;

I'm curious to know if anyone has run into any problems with this setting causing performance problems. Depending upon how many users are sending data to the cube I would imagine running so many merges could be an issue. Then again, it might help since you reduce query cost after merging slices.