Peculiar performance behaviour with dim reordering (BI/TM1)

[qml]

Guys, here's a good one for ya. I'm hoping this will turn into a discussion that will let us understand better what TM1 does "behind the scenes".

We have raised a ticket with IBM recently that goes something like this:

Problem description
Our Business development team are investigating performance issues
between Cognos BI 8.4.1 and TM1 9.5.1 from a number of angles, one of
which involved the use of the TM1 dimension re-order tool in Architect
/ Server Explorer. We have found that opening the "Re-order dimension"
toolbox and then clicking OK (without changing anything, as the
dimensions are already in the optimal order) can significantly improve
the performance of some BI reports. For example, one report takes 23
seconds to generate before the re-order process is run, then takes 7
seconds after it has been run.

The problem is, the performance improvements seem to "wear off" once
the cube is re-loaded with data. The BI report climbs back to the 23
second run time. Opening the re-order dimension tool, then clicking OK
again (without changing anything) seems to re-optimise the cube and the
BI report drops back to 7 seconds.

We're finding it hard to explain this functionality and would like to
be able to maintain these performance improvements consistently
(without having to manually apply each day). So, were hoping that you
could answer the following questions:

1. Specifically what steps are performed internally when a user clicks
OK in the Re-order dimension window without changing anything? ie. What
step in that process is causing the performance improvements?
2. Can you please provide any insight into why this optimisation seems
to "wear off" after data is re-loaded? Could we avoid this some how?
3. Most importantly... Is there any way of programmatically running the
dimension re-order / optimisation process after daily data updates
(via the API, TI or other means)?

After raising the ticket we figured there was actuallly a programmatic way of "optimising" these queries by using the following undocumented API calls:
TM1CubeDimensionsOrderGet
TM1CubeDimensionsOptimalOrderFind
TM1CubeDimensionsOrderSet
Using these seems to result in the same temporary performance improvement we're seeing when using the GUI and the effect "wears off" when data are re-loaded (the exact same data!).

To reiterate - in our case, the dimensions in our cube are already in the intended order, and when we open the dimension re-order tool, we are simply clicking OK without changing anything. There is no waiting time and the result is instantaneous. We see no change in memory usage before/after we run the dimension re-order tool. The benefit we are seeing is purely to do with performance of the cube in our Cognos BI reports.

Does anyone have any insight or theories to share on what is going on there? IBM have not been very helpful thus far, sending us drone-generated notes on how dimension reordering can improve memory usage and performance, but this is obviously irrelevant.

[lotsaram]

Sounds fascinating, but I have no insights. Would be interested to hear when you finally receive a sensible sounding explanation though.

[tomok]

I can tell you what their answer is going to be....upgrade to Cognos 10.

[John Hammond]

Thanks for sharing this QML

I have a couple of questions

1. Are you reloading the cube or recreating (calling CubeCreate) and then reloading the cube?
2. Does the memory usage drop after you enter and leave the reorder dimension dialog? (Tried this myself and it didnt but I am on TM1 90)
3. Is the improvement only apparent in C8 or is it apparent in TM1. (Export as ASCII data with no selection on the view is a very good way to test this)?

[David Usherwood]

Just a thought - is your server fully held in RAM? If it wasn't then the resequencing might pull that cube into RAM and performance would be better.

[qml]

1. Are you reloading the cube or recreating (calling CubeCreate) and then reloading the cube?

We just reload the data using a pretty basic TI script (ViewZeroOut, CellPutN). There is no cube recreation.

2. Does the memory usage drop after you enter and leave the reorder dimension dialog? (Tried this myself and it didnt but I am on TM1 90)

There is no detectable change in memory consumption in either direction.

3. Is the improvement only apparent in C8 or is it apparent in TM1. (Export as ASCII data with no selection on the view is a very good way to test this)?

We have actually noticed an improvement in pure TM1 as well, but the difference isn't as clearly measurable as when it goes through the BI layer. Tasks like exporting data to a text file or, say, expanding a hierarchy in Cube Viewer are faster, but it's not a case of making the query 3 times faster like with the BI reports.

Just a thought - is your server fully held in RAM? If it wasn't then the resequencing might pull that cube into RAM and performance would be better.

Yes, I believe it's stored fully in RAM. We have a pretty powerful server with a safe memory margin running multiple instances of TM1 and it's all held in memory, not swapped to disk. As a rule we set the parameter LockPagesInMemory to "T" and we played with it as well to see if it made any difference - it didn't.

[Steve Vincent]

Am i being deliberately simple here?

Is it not just the data cached in RAM has changed and the next time you report the data is has to re-cache it, exactly the same way that any change in TM1 will force the same thing?

Re-ordering the cubes is probably having the effect of checking all the data and caching it, just as opening a cube would. That would explain the time differences on calculation and certainly why the cube “goes back” to 23 seconds once it’s reloaded.

[qml]

Steve,

Quite frankly we were thinking along the same lines - that data is being cached when we run the reordering tool.

However, the fact that memory usage doesn't grow and that both opening the reorder window results in no waiting time at all, seems to cotradict this.

Also, I was under the impression that these versions of TM1 and Cognos BI talk to each other via pure MDX without using any optimisations on the TM1 side like cache usage etc. Hence the generally poor performance of BI reports on top of TM1 cubes. I could be wrong here though.

Also, one last note: if it was the normal cache that played a role here then after running a report for 23 seconds once, subsequent runs should be faster because of the caching that would have been done. We haven't observed this sort of behaviour; it stays consistently above 20 seconds unles the fake reordering takes place.

[John Hammond]

Your follow up much appreciated qml

It looks like it is something that C8 is doing on the basis of changed information rather than TM1.

You should be able to review C8's retrieval strategy before and after by setting it to trace any MDX passed to TM1 Iirc in Report Studio (the non basic one). I would be interested if you saw a difference and could post it if you found it.

Best Regards