performance degrade of reports after dimension reorder

[ub14]

Hi,

We have created TM1 cube(TM1 9.5.2) with 16 dimensions and cube service size is around 5 GB. 20 BI Cognos reports(IBM Cognos 10) built on and each report is taking around 30 seconds. In order to improve the performance, we have done re-order of dimensions In TM1 and there is decrease in cube service size to 3.5 GB. But after the dimension re-order in TM1, the cognos Reports are taking more time compared to previous , even after 5 minutes also the reports not coming out. If we change the dimension order to previous state, the reports working fine.

Does anyone have any insight of this peculiar issue.

regards
bhaskar

[jim wood]

Did you re-work the reports for the new order? Or did you expect them to automatically pick up the change? Have you tried replicating one of the reports?

[qml]

Did you re-work the reports for the new order?

Jim, can you elaborate on this? What kind of re-working do you have in mind? I think you've lost me there.

[jim wood]

I'm no BI expert but if you built a report on a cube based on the old structure would the report still try to use the old order? Would you need to refresh the data source for instance?

[qml]

To my knowledge dimension reordering only changes the internal order of dimensions; for all external purposes the order stays the same, so all rules, TI, reports etc should keep working with no change whatsoever. I would also assume the same holds true for Cognos BI packages, but I might be wrong on this one.

Not so long ago I posted an interesting story about our experience with dimension reordering and report performance in BI. I still have no response from IBM on this one (!), but maybe it's somehow related to the problem described by the OP (although in our case the weird behaviour was quite the opposite, i.e. performance improvement)?

[jim wood]

Very interesting indeed. Did you try rebuilding one of the reports to see if you got the same result?

[George Regateiro]

How did it affect your TM1 query times on that cube (though a view or excel template)? The issue with the dimension reorder is that the smallest cube size is not always the most efficient for queries. Normally such a huge difference is not expected but it is possible.

[ub14]

Hi,

Rebuilding the Cognos Report after dimension re-order in TM1 cube is not feasible solution. We have around 20-30 reports.
What are the best optimization steps for Cube. ? Can we follow optimization suggested by Cube Optimizer in TM1 Architect. ?
Eventhough cube service size reduces by 25% after optmization, the Cognos Reports Taking more time.(>5 min which is not acceptable).
TM1 View's resposnse is good. We tried restarting tm1 services and BI Services, there is not much difference in performance of the reports.

Regards
Bhaskar

[jim wood]

I wasn't offering it as a solution. I was looking to see if there was any difference as a test. If it behaves the same way then fine, if not then it's something else you can feedback to IBM support or us.

[ub14]

Hi,

How much free memory is required for 5 GB TM1 cube size. The available free ram in our Server(where TM1 is isntalled) is 6 GB. Is this enough for benchmark performance of the Cube/Cognos Reports. How can we correctly size the hardware requirement of server (particular RAM) ? .

[jim wood]

From a BI point of view I have no idea. If you have that and TM1 installed on the same box then you need to be aware that every view in TM1 is cached so that free memory will start be taken by this very quickly. As for the amount it will take is very hard to tell just by the cube size. (Sorry if you were already aware of this.)

Jim.

[ub14]

Hi Jim,

Thank you. We have Tm1 9.5.2 and Cognos 10 BI installed on the same server. Do you think still we need to add more RAM on this Server ?
What We noticed is, previously when the Cube size 1.5 GB, the reports are fine. But once we loaded more months data, the cube size is increased to 3.5 GB(initially it is 5 GB, we optimized to 3.5 GB by dimension reorder), the reports execution time doubled up.
Below are Hardware Stats on Server:
Total RAM : 16 GB
Avaialble RAM is : 6 GB
Cube Size : 3.5 GB

Regards
Bhaskar