Conditional cumulation of values between two differently dimensioned cubes

Post Reply
jdbw
Posts: 2
Joined: Fri Sep 22, 2017 11:43 am
OLAP Product: TM1
Version: PAL 2.0.0
Excel Version: 2016

Conditional cumulation of values between two differently dimensioned cubes

Post by jdbw »

First of all, thanks to all of you for your great work! I've been working with TM1 for three months now and this forum has become a valuable source for answering my (admittedly often rather basic and even naïve) questions and made me start to understand the concepts of TM1 (at least that's what I think ;)). However, I've just encountered a problem I haven't found a satisfying solution so far. I hope you can help me and/or give some tips or hints. So, here it is:

I have two cubes, 'accountParameters' and 'accountGroupParameters', which solely exist to store some parameters (obviously) in a calculation model.
The dimensions are as follows:

accountParameters: company, year, month, version, currency, accountParams, accounts
accountGroupParameters: company, year, month, version, currency, accountGroupParams, accountGroups

The cubes share the first five dimensions, the other two, handling either the accounts themselves or their respective groups, naturally differ from each other.

The 'accounts' dimension contains 102 elements.
The only elements in the 'accountParams' dimension that are interesting for this problem are 'Value' and 'AccountGroup'.
The 'accountGroups' dimension corresponds with the entries in the 'accounts' cube, i.e. it contains the elements 'AccountGroup01', … 'AccountGroup20'. Furthermore, there's a text attribute that stores the account group's clear name called 'GroupName'.
Once again, the only element of the 'accountGroupParams' that matters right now, is 'CumulatedValue'.

The idea is to cumulate the values from all accounts belonging to an account group in the 'accountGroupParameters' cube.

My current rule on the 'accountGroupParameters' cube states:

Code: Select all

['CumulatedValue'] = N:	If(
 				DB( 'accountParameters', !company, !year, !month, !version, !currency, 'AccountGroup', 'Account001' ) @= ATTRS( 'accountGroups', !accountGroups, 'GroupName' ),
 				DB( 'accountParameters', !company, !year, !month, !version, !currency, 'Value', 'Account001' ),
 				0
 			) +
 			… +
			If(
				DB( 'accounts', !company, !year, !month, !version, !currency, 'AccountGroup', 'Account102‘ ) @= ATTRS( 'accountGroups', !accountGroups, 'AccountName' ),
 				DB( 'accounts', !company, !year, !month, !version, !currency, 'Value', 'Account102' ),
 				0
 			);
Of course, this gets the work done, but is an awful mess to maintain being a conditional sum of 102 hard coded elements. Come the day someone changes the 'accounts' dimension, it gets quite hard to do the proper changes in the code. Is there any other way to accomplish the same result with a more elegant rule? A TI would get the job done fast and nicely, but I don't want to force users to execute a TI for each minor change in the 'accountParameters' cube. Inserting additional consolidations by account group doesn't work either, since the 'accounts' dimension is used by a bunch of cubes, where another bunch of TIs run, that fail if the dimension structure is altered (something I had to learn the hard way…).

If there's additional info you need or something is unclear (I'm not that good at explaining stuff...), please ask.

I'm looking forward to your replies!

Cheers,

Jan
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Conditional cumulation of values between two differently dimensioned cubes

Post by Michel Zijlema »

Hi Jan,

I was just going through some unread posts in the forum and noticed your question.
If I understand your requirement correctly, you want to pull the on an accountgroup level consolidated account values from the accountParameters cube to the accountGroupParameters cube. In that case you would make life much easier for yourself when the accounts dimension has an accountgroup rollup hierarchy included (so all accounts aggregating to the respective accountgroup) - where the names of the consolidated accountgroup elements in the accounts dimension correspond with the leaf level accountgroup element names in the accountGoups dimension.
In that case you could write the CumulatedValue rule like this:

Code: Select all

['CumulatedValue'] = N:	DB( 'accountParameters', !company, !year, !month, !version, !currency, 'Value', !accountGroups );
Michel
jdbw
Posts: 2
Joined: Fri Sep 22, 2017 11:43 am
OLAP Product: TM1
Version: PAL 2.0.0
Excel Version: 2016

Re: Conditional cumulation of values between two differently dimensioned cubes

Post by jdbw »

Thank you for your reply, Michel!

Adding roll-up consolidations would indeed be an elegant solution to solve the problem, although I'm not too sure if it meets the - admittedly rather odd - requirement of the model: The mapping of the accounts to their respective groups has to be dynamic, i.e. if someone decides that there's need for another account group from this day on, thus resulting in a new mapping, everything calculated before has to remain unaltered. If the calculation is rule based, the old values change as well, when the consolidations change, right?
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Conditional cumulation of values between two differently dimensioned cubes

Post by Michel Zijlema »

Hi Jan,

If the mapping changes from period to period it indeed makes no sense to use the dimension rollup.
What you could do is build an intermediate accumulation link cube that contains the company, year, month, version, currency, accountParams, accounts and accountGroups dimensions. This cube pulls it's data from the accountParameters cube, where the mapping from account to accountGroup is looked up from an account to accountGroup mapping cube (per period) - so in this cube the data is stored on a combination of account and accountGroup. The accountGroupParameters cube then can pull it's values from the intermediate cube, selecting the top level total element on the accounts dimension.

Michel
Post Reply