Peculiar performance behaviour with dim reordering (BI/TM1)
Posted: Wed Jan 18, 2012 2:30 pm
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:
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.
We have raised a ticket with IBM recently that goes something like this:
After raising the ticket we figured there was actuallly a programmatic way of "optimising" these queries by using the following undocumented API calls: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)?
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.
