TM1 Active Form Performance Issue

Post Reply
mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

TM1 Active Form Performance Issue

Post by mail_kupi » Tue Apr 23, 2013 12:45 pm

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
MVP
Posts: 2704
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 Active Form Performance Issue

Post by tomok » Tue Apr 23, 2013 2:50 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: TM1 Active Form Performance Issue

Post by mail_kupi » Tue Apr 23, 2013 3:04 pm

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
Posts: 33
Joined: Tue Jan 29, 2013 2:52 pm
OLAP Product: TM1
Version: 9.0 SP3 9.5.1 10.1.1
Excel Version: excel 2010 2007 2003

Re: TM1 Active Form Performance Issue

Post by cgaunt » Tue Apr 23, 2013 3:23 pm

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
MVP
Posts: 2704
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 Active Form Performance Issue

Post by tomok » Tue Apr 23, 2013 3:39 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

lotsaram
MVP
Posts: 3359
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 Active Form Performance Issue

Post by lotsaram » Tue Apr 23, 2013 3:58 pm

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
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: TM1 Active Form Performance Issue

Post by mail_kupi » Wed Apr 24, 2013 2:33 pm

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
MVP
Posts: 3359
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1 Active Form Performance Issue

Post by lotsaram » Wed Apr 24, 2013 4:03 pm

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.

Post Reply