TM1 web active form speed issue

Post Reply
Mark RMBC
Regular Participant
Posts: 162
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

TM1 web active form speed issue

Post by Mark RMBC » Fri May 04, 2018 1:46 pm

Hi all,

I have 2 cubes, one that holds staff budgets and one that holds staff actuals (they are in different cubes because of differing dimensionality for one thing)

I have created an active form based on the actuals cube. Employees are in the rows. In the columns are the period actuals for pay and a column for their total pay budget.

I want to give the user the option on the web to return employees with just actuals or employees with a budget for a given cost centre. Obviously the just actuals view will exclude any employees who don’t have an actual! Hence giving users both options.

The view to return employees with just actuals is a simple mdx filter by level 0 (referenced in the TM1RPTROW) and suppress zero rows. This works fine.

To return those employees with a budget I have switched off zero suppression and used mdx in the TM1RPTROW as follows:

Code: Select all

{Filter({TM1FilterbyLevel( {TM1SubsetAll( [F_Employee] )}, 0 )},
[StaffingBudget].( 
	[F_Employee].CurrentMember, [Version], [Year], [Analysis], [CostCentre], [JobNumber], [TotalPay] ) > 0)}
Each of the dimension elements in the above mdx, apart from employee, references a cell in the websheet.

The issue is that this mdx takes around 20 seconds to return data on the web.

I did add a view formula to the websheet, as follows:

Code: Select all

VIEW("TM1Model:StaffingBudget","!",$D$13,$D$17,$D$11,$D$16,$D$14,$D$18)
But this doesn’t seem to make much difference

Can anyone think of a way to make this more efficient?

Regards, Mark

User avatar
gtonkin
MVP
Posts: 630
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 web active form speed issue

Post by gtonkin » Sat May 05, 2018 5:51 pm

Hi Mark, If you are using an Active Form, the TM1RPTVIEW will replace the VIEW - not sure that even if you have other DBRWs referencing this, you will see gains. The first thing I always try and think about is what drives the Active Form - I then link the relevant DBRWs to the TM1RPTVIEW and create another reference to the cube and link all other DBRWs to that (have not tried making the reference a VIEW statement).

What I would do in your case is create a flag e.g. Active for Planning or just Active then rule derive a 0 or 1. Your Active Form can then have the first DBRW formula referencing the TM1RPTVIEW and the Active measure.

Give it a whirl.

User avatar
paulsimon
MVP
Posts: 635
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: TM1 web active form speed issue

Post by paulsimon » Sat May 05, 2018 6:43 pm

Hi Mark

I think your fundamental issue is that your active form is based on your actuals cube. You might somehow be referencing budget but it won't be efficient.

I would suggest a rule in the budget cube that pulls data up from the actuals cube to its own, presumably more summarised level.

We do something similar - we have a Budget Allocations cube that only splits data by Account and Programme for about 20 very summarised combinations as opposed to the several million potential combinations that exist in the Actuals. This works well for us.

The other alternative is to create an _Input element at whatever level you budget at, and pull data from the budget cube in to this. I am guessing that alongside your employee dimension you have an account dimension and you budget at a more summarised level than you collect actuals.

Otherwise, go with what George said. Get the key columns that determine zero suppression from the Active Form and the rest from a VIEW function.

Regards

Paul Simon

Mark RMBC
Regular Participant
Posts: 162
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 web active form speed issue

Post by Mark RMBC » Tue May 08, 2018 7:59 am

Thanks for the replies.

There are more dimensions in the actuals cube than the budget cube, but I think I will pull the data from budget to actuals because the actuals cube is more for reporting (with the occasional user updates) and the budget is for regular user updates, so I would prefer any extra overhead to be in the reporting cube.

Not sure yet whether to have a rule or a TI to pull the data but that is for me to sweat over!

cheers, Mark

User avatar
paulsimon
MVP
Posts: 635
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: TM1 web active form speed issue

Post by paulsimon » Tue May 08, 2018 8:24 pm

Hi Mark

If you do pull budget to the actuals cube and the actuals cube has more dimensions, then if you use rules, you want to avoid feeding from one to many. Therefore I would suggest that you adopt the _Input idea and for other dimensions pick one element to which you are going to feed.

Depending on how your budgeting works, then a TI is also an option.

In our cubes we have both Draft Budget and Curr Budget versions. All budget entry takes place in the Draft Budget version. It is only when Management Accounts are happy with the budget and it matches all top down budget allocations that the Draft Budget gets copied to the Curr Budget by a TI. All comparisons to Actuals and Forecast are made against the Curr Budget version. In this way we only need to make the Draft Budget read only for the duration of the copy to Curr Budget and if we want we can open the Draft Budget version immediately for further budget changes.

Regards

Paul Simon

Mark RMBC
Regular Participant
Posts: 162
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 web active form speed issue

Post by Mark RMBC » Wed May 09, 2018 11:06 am

Hi Paul,

thanks for the info. Will bear in mind.

Your method of having Draft Budget and Curr Budget versions is similar to how we do it, except we have draft and current cubes rather than a version and use TI to transfer the data. This means we can simplify the rules in the draft cube, though it does complicate the TI's somewhat!

And like your set up only approved data ends up in the curr budget

cheers, mark

Post Reply