Page 1 of 1
Suppress a consolidation
Posted: Mon Sep 26, 2011 2:16 pm
by pmakulski
Is there a way to suppress a level or a specific consolidation point? I'm used to this feature in Cognos 7.4, but I can't seem to duplicate the functionality in TM1.
What I want to do is have the grandchildren of a point show up under the grandparent without the parents.
But if I have level 2 1 and 0, if I make a level subset of 2 and 0, I get the level 2 as an expandable item (which expands to level 1) and all of the level 0s.
If I make a subset that deletes the parent it also turns into a holy mess.
Is there a trick to do this or is it only possible if I make an alternate hierarchy?
Re: Suppress a consolidation
Posted: Mon Sep 26, 2011 2:47 pm
by qml
pmakulski wrote:If I make a subset that deletes the parent it also turns into a holy mess.
How do you mean?
I think that generally you should be choosing from among the following 3 options:
a) alternative hierarchy;
b) dynamic MDX subset(s);
c) static subset(s).
But the choice really depends on the interface you're using/providing and a few other considerations.
Re: Suppress a consolidation
Posted: Mon Sep 26, 2011 6:59 pm
by Christopher Kernahan
C Grandparent
C Parent
N Child1
N Child2
I think what you're saying is you'd like to be able to show Child1,Child2, and Grandparent, but not the Parent.
If you use ElementSecurity you should be able to hide the Parent consolidation, such that Grandparent will still have the + to expand it in the Subset editor, but won't, and the Children will be available. But, I can't say I like the look of this. Plus, it doesn't really solve your 'holy mess', and I don't think an alternate hierarchy will help with what you're trying to do here, because the Grandparent will probably consolidate to a different value.
Re: Suppress a consolidation
Posted: Mon Sep 26, 2011 8:46 pm
by qml
Christopher Kernahan wrote:I don't think an alternate hierarchy will help with what you're trying to do here, because the Grandparent will probably consolidate to a different value.
Not sure why you think that, we're probably thinking of two different solutions. If the alternate hierarchy is constructed properly without the Parent then GrandparentAlt will consolidate to the same value as Grandparent. Using your example:
Base hierarchy:
C Grandparent
C Parent
N Child1
N Child2
Alternate hierarchy:
C GrandparentAlt
N Child1
N Child2
If the weights are the same there's no reason for this not to work. However, it does not mean this is necessarily the best solution in this case. I sure would love to hear more about the requirements, what the reporting tool is (Cube Viewer, Excel Slice, Active Form, ...) etc. Is it a static report or is the whole solution meant to work dynamically. Maybe, if the Parent level is not needed, it can be eliminated from the dim altogether?
Re: Suppress a consolidation
Posted: Wed Sep 28, 2011 4:06 pm
by pmakulski
Sorry, been away for a while. Glad to see the discussion carrying on.
The big culprit is our G/L organization hierarchy. The dang thing is 9 levels deep and it is fully populated (ie. all 9 levels are present whether needed or not ... and lots of parents have only one child). What we are usually interested in is the 0 level under a particular parent, regardless of level.
Cognos 7.4 had a nice "push down to a lower level" feature in its browser implementation (as distinct from "nest a lower level"). One could keep pushing to lower and lower levels of descendents.
It also had in its transformer a "suppress a consolidated item" feature. While a bit cumbersome, it did allow a rollup point to do everything except show up.
So, in TM1, I'd like a subset that shows
___+ Grandparent
that expands like:
___- Grandparent
________Child 1
________Child 2 ...
A level subset looks like this
___+ Grandparent
___Child 1
___Child 2
and expands like this (the holy mess I referred to in the original posting)
___- Grandparent
______- Parent
_________Child 1
_________Child 2
___Child 1
___Child 2
If it were possible to build a "suppressed parent" subset, I'd use it in a lot of places. An mdx would be even better.
Re: Suppress a consolidation
Posted: Wed Sep 28, 2011 11:30 pm
by rmackenzie
pmakulski wrote:The big culprit is our G/L organization hierarchy. The dang thing is 9 levels deep and it is fully populated (ie. all 9 levels are present whether needed or not ... and lots of parents have only one child).
Yep, common problem. I have the same issue at the moment and the users want to see exactly what you are describing. Firstly, the MDX for this isn't too complex:
Code: Select all
{[YOUR_ACCOUNT_DIMENSION_NAME].[YOUR_ROOT_ELEMENT],{TM1FilterByLevel({TM1SubsetAll([YOUR_ACCOUNT_DIMENSION_NAME])},0)}}
Where the expression is basically the root element concatenated (using the comma) with the standard MDX definition of an all 0-level subset. The gotcha here is that any consolidated elements with no children will still appear in the 'child' list due to them being level 0 but not actually n-level.
There is a question mark over whether you need to use a dynamic subset though. I guess that your account dimension is a) unlikely to move during the day and b) is going to be refreshed no more than daily and probably overnight. If my guesses are true, then you could build the subset as a static subset with TI in your overnight jobs, or even follow qml's suggestion of constructing a alternate hierarchy in your account dimension. The TI is reasonably simple (and can be quickly repurposed to build an alternate hierarchy as opposed to a static subset):
Code: Select all
# object names
sDimName='YOUR_ACCOUNT_DIMENSION_NAME';
sSubName='YOUR_SUBSET_NAME';
# initialise subset
IF ( SubsetExists ( sDimName, sSubName ) = 0 );
SubsetCreate ( sDimName, sSubName );
ELSE;
SubsetDeleteAllElements ( sDimName, sSubName );
ENDIF;
# insert root element
nIndex = 1;
sRootElement = 'YOUR_ROOT_ELEMENT';
SubsetElementInsert ( sDimName, sSubName, sRootElement, nIndex );
# insert level-0 children
nCounter = 1;
nMaxCount = DIMSIZ ( sDimName );
WHILE ( nCounter <= nMaxCount );
sElemName = DIMNM ( sDimName, nCounter );
nElementLevel = ELLEV ( sDimName, sElemName );
IF ( nElementLevel = 0 );
nIndex = nIndex + 1;
SubsetElementInsert ( sDimName, sSubName, sElemName, nIndex );
ENDIF;
nCounter = nCounter + 1;
END;
Except if you are using TI, you can use this test instead
Code: Select all
sElemName = DIMNM ( sDimName, nCounter );
sElementType = DTYPE ( sDimName, sElemName );
IF ( sElementType @= 'N' );
nIndex = nIndex + 1;
SubsetElementInsert ( sDimName, sSubName, sElemName, nIndex );
ENDIF;
This test is preferred because you will exclude the 'empty-nest' parents.
HTH
Robin
Re: Suppress a consolidation
Posted: Fri Sep 30, 2011 6:48 pm
by pmakulski
Awesome reply Robin. Thanks.
Yes, the org structure is fairly static; it wouldn't change during the day, and doesn't change often. Of course, occasionally it changes a lot, as management (as Dilbert says) know only how to do 2 thing: reduce headcount, and reorganize.
I'll give your static subset suggestion a spin.