TM1 Active Form Performance Issue

[mail_kupi]

Hi Guys,

I'm using Cognos TM1 10.1.1, with 24 Core and 16 GB Ram on Hardware ( share with another application )
i have a report using excel where i build the report from active form using DBRW formula.
the report is using one cube with 23 dimension, where consist of 16 row dimension, 2 column dimension and 3 title dimension.
when generating report with 1 year and 1 month data ( 3673 row ) it takes almost 15 - 20 minutes.

but when using a cube viewer its only takes 5 s to generate data, and 8-10 s to slice it.

is there any idea or recommendation what can i do to imrove the performance?

thanx a lot before.

[tomok]

Make sure none of your DBRW formulas point to a hard-coded Server:Cube reference. They either need to point to a cell that contains a VIEW or TM1RptView formula. This is the number one error I see when diagnosing slow TM1 workbooks.

[mail_kupi]

Thanks for Reply Tomok

My DBRW Formula point to a cell that contain a View TM1RPTVIEW formula, so i dont think its not the problem.
Any suspect and suggestion?

[cgaunt]

TM1 runs predominantly on a single core, it certainly does for data queries. During the time that you experience poor performance on the active form, look at the server CPU / page file and the client CPU / page file. You will see if there is the limiting factor in either of those. You can also use TM1TOP to determine what other activity is happening at the same time that you experience this poor performance. You may be able to identify the bottleneck that you are hitting.

Apart from that, you could pay further heed to Tomok's suggestion and perhaps go back to basics and start again with another simple active form, perhaps from a cube with less dimensions. Is this true for all active forms, or just the form that you currently have? When you cut the active form, limit the pages to a single element so that you are not having to load the entire view into memory. Begin to build the form back up a piece at a time until you hit an action that hinders performance.

by all means post the result of all of the above for further consideration, especially if any of them yield a result.

[tomok]

Another thing I should mention is that comparing the load times for a view to an active form report is meaningless. When you use the cube viewer it only shows a preview of the rows (like first 20 or so) so it will always come up faster than an active form report that has 3673 rows. The cube viewer only calculates the other rows as you scroll down.

[lotsaram]

Without going into too much detail an active form with 16 row dimensions doesn't sound like good design. Especially not if any of these are using large lists of unregistered subsets on hidden AR sheets and even more so if the dimensions are large.

[mail_kupi]

Hi Guys,

sory for late response and thanks a lot for your reply.

cGaunt,
in CPU PF there is no significant usage, but in client CPU PF usage increase dramatically.
in TM1TOP there is no paralel activity that happen same time when i hit TM1REFRESH.
i just only have a problem in current active form, and does not have any problem with another active form
in my current active form, im reduce to 4 row dimension, but still dont have any effect, just only reduce a few second.

Tomok,
u're right, im just realize it when i scroll down the cube its like recalculate again the new row

lotsaram,
actually i have already give a complain to functional team when they bring this report design, they say that client want to bring down the data so they cant keep it offline and make pivot table whenever they want. My Team suggest that user can use Cube Viewer and when they want it offline, user can use snapshot. but still they dont want that alternative.

[lotsaram]

If there is a requirement to dump thousands of rows of data so that users can build pivot tables then I would humbly suggest that users don't understand OLAP or how to use TM1 properly. I would also humbly suggest that it is the functional team's job to educate the users. However I understand the type of organization that you work for and realize that the functional team probably at best have a flimsy grasp of OLAP and TM1 themselves. In which case I hope you have a strong technical lead prepared to argue with and educate the functional team.