Will new hierarchy on dimension affect the performance?

[comma]

Hi everyone,

Need your advice here.
I have an Accounts dimension like this:
- All accounts
- Account 1
- Account 2
- Account 3
- Account 4
- Account 5
- Account 6
- Account 7

Then, I want to add a new root on the dimension, like this:
+ All accounts
- All accounts by function
- G & A
- Account 1
- Account 2
- Account 3
- Account 4
- CA
- Account 5
- Account 6
- Account 7

Will adding this new root affect the performance of my model even if just a little?

[Alan Kirk]

Need your advice here.
I have an Accounts dimension like this:
- All accounts
- Account 1
- Account 2
- Account 3
- Account 4
- Account 5
- Account 6
- Account 7

Then, I want to add a new root on the dimension, like this:
+ All accounts
- All accounts by function
- G & A
- Account 1
- Account 2
- Account 3
- Account 4
- CA
- Account 5
- Account 6
- Account 7

Will adding this new root affect the performance of my model even if just a little?

A little, but not so much that you'd notice in most models I wouldn't think. If someone fully expands the tree, then they'll see an extra (in the example above) three values that will need to be calculated and stored. (Probably more; I assume that what you're showing here is just an example.) That means more calculation time and more memory used; there's no way around that.

However calculations are only done on demand and if someone just looks at the "All Accounts" total without expanding the tree, then there will be no more calculations done now as were done before the change. (Unless something has changed in recent versions, TM1 doesn't calculate the intermediate consolidations if you calculate the top level one.)

The consolidation calculation system in TM1 is very efficient, so (assuming that there are no rules involved) you probably won't take much of a performance hit. And unless you're on 32 bit and really close to your memory ceiling (and I mean really, REALLY close) I doubt that the extra memory of a handful of extra consolidated elements would make much difference either. It's certainly not something that I'd expect to be a performance killer; the sort of hierarchy that you describe would be typical of most chart of account dimensions in most models.

[comma]

Thanks Alan for your explanation.

Yes, those above are just examples and there're many more elements inside (around 300), and there are some rules involved as well.
But, the thing is I need to consolidate these accounts by their function (G&A, CA, Finance, etc) and I also need these accounts to be NOT consolidated by their function.

Is there any other way around to solve this?
I was thinking to only have one root, which is the one consolidated by the functions. While the other root (the unconsolidated one) created using subset.
But it seems to be impossible.

[Alan Kirk]

Thanks Alan for your explanation.

Yes, those above are just examples and there're many more elements inside (around 300), and there are some rules involved as well.
But, the thing is I need to consolidate these accounts by their function (G&A, CA, Finance, etc) and I also need these accounts to be NOT consolidated by their function.

Is there any other way around to solve this?
I was thinking to only have one root, which is the one consolidated by the functions. While the other root (the unconsolidated one) created using subset.
But it seems to be impossible.

You can create two hierarchies if you want to (one which contains the function consolidations, and one which has the Total only); you can certainly do that. (Though the top level consolidations in each hierarchy would need to have different names, like "Total Accounts" and "Total Accounts By Function". They'd return the same value, but the names would have to be unique since any consolidation can be defined with only one set of children.) But unless you have a need to allow users to iterate through the N level elements using the DNext() worksheet function (or something similar), there probably isn't any point.

You could simply create the hierarchy which contains the function consolidations as your sole hierarchy, then create a dynamic (MDX) subset which consists of the top level consolidation (the grand total) and all of the N level elements. When users select that subset, it'll be just as if they were looking at a hierarchy which didn't have the function consolidations in it.

To do that, you just use the Tools -> Record Expression option in the subset editor. Select all elements, filter it by the two hierarchy levels that you want, stop recording, save the subset as a public one. (There's more guidance on creating MDX subsets this way in the manuals; just search for "Record Expression".)

(NB: I'm assuming that the top level of the hierarchy will always be the same; level 4, level 5, whatever. If it won't, things get a smidge more complex but there would still be workarounds.)

[comma]

You could simply create the hierarchy which contains the function consolidations as your sole hierarchy, then create a dynamic (MDX) subset which consists of the top level consolidation (the grand total) and all of the N level elements. When users select that subset, it'll be just as if they were looking at a hierarchy which didn't have the function consolidations in it.

Hi Alan,
Just to make sure, it means that I cannot hide those N level elements by collapsing the Grand Total consolidation, right?
Because when I expand the Grand Total, I can see the function consolidations and the N level elements inside, while another set of N level elements also exist outside of the Grand Total consolidation.

(NB: I'm assuming that the top level of the hierarchy will always be the same; level 4, level 5, whatever. If it won't, things get a smidge more complex but there would still be workarounds.)

I don't really understand this one. What do you mean by the top level will always be the same?
Thanks so much for your help btw. Really appreciate it.

[Alan Kirk]

You could simply create the hierarchy which contains the function consolidations as your sole hierarchy, then create a dynamic (MDX) subset which consists of the top level consolidation (the grand total) and all of the N level elements. When users select that subset, it'll be just as if they were looking at a hierarchy which didn't have the function consolidations in it.

Hi Alan,
Just to make sure, it means that I cannot hide those N level elements by collapsing the Grand Total consolidation, right?
Because when I expand the Grand Total, I can see the function consolidations and the N level elements inside,

If you mean can you stop the users from expanding the consolidation, no you can't. (Unless obviously they have no security access to the child level elements, but that doesn't seem to be a practical solution. They'd still see the correct total in that case, but I assume that they need to see the N level elements as well.)

while another set of N level elements also exist outside of the Grand Total consolidation.

If I'm reading you correctly it's not really another set of N elements; it's just that the elements will be shown in two places. This won't affect the total, though. We'll come back to that in a moment.

(NB: I'm assuming that the top level of the hierarchy will always be the same; level 4, level 5, whatever. If it won't, things get a smidge more complex but there would still be workarounds.)

I don't really understand this one. What do you mean by the top level will always be the same?

OK, what I mean is this. Let's suppose that you have a single hierarchy like so:

02_Accounts.jpg (32.96 KiB) Viewed 8360 times

As I'm guessing you know, N elements are at level 0, the functional consolidations will be level 1, the All Accounts By Function one is at level 2, and the All Accounts one is at level 3. This enables you to record arguably the simplest dynamic subset expression there is, select by levels:

03_Accounts.jpg (59.78 KiB) Viewed 8360 times

This gives the expression (and subset) that we can see here (I've tuned the Expression Window on from the View menu so you can see the MDX code at the bottom):

04_Accounts.jpg (50.2 KiB) Viewed 8360 times

Where this will fail is if, say, you added a new consolidation in to add elements 1 and 2 together and added that consolidation to the G&A one. That would push All Accounts up to level 4 and the simple expression would no longer work. But as long as All Accounts stays at level 3 (or whatever), the expression will work just fine.

Now as I suspect you've discovered, if the users get snoopy and expand the consolidation then yes, you're correct, they will see the N elements twice (as well as the functional consolidations):

05_Accounts.jpg (53.42 KiB) Viewed 8360 times

If that's a real problem, the only choice you have is to maintain two hierarchies, one of which has the functional groupings and one of which is just a total element with the N level ones below it.

[comma]

Thanks Alan for your really comprehensive explanation, I understand better now.
I will maintain two hierarchies on my dimension then.

[rikard78]

Hi,

I have a similar question. I'd like to make a dynamic subset that always shows a sorted list of leaf elements in my dimension which I have under a single consolidation: "All Counterparties". The problem is that when I apply the sort using MDX it sorts everything including the consolidation. This is what it looks like:

Sorting All Counterparties.png (30.21 KiB) Viewed 8234 times

and this is what I'd like:

Sorting All Counterparties_desired.png (24.94 KiB) Viewed 8234 times

Is there a way of writing this in MDX?

thanks,

Richard

[tomok]

Is there a way of writing this in MDX?

Sure, just use the recording feature of the subset editor and it will write the MDX for you. Basically you want to filter for the name of the parent element, do a drill down member on that, and then filter the result to only level 0. Then just save that subset, making sure to check Save Expression.

[rikard78]

Thanks Tomok.

Sorry, I realise I've missed something from my request.

I would like to show the Consolidation as well as the leaf level elements so I can't apply the final filter step to remove the consolidation.

e.g. at the end what I'd really like is to have:

--All Counterparties
A
B
C
..
Z

I think the following nearly does the trick:
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [zCounterparty] )}, 1),TM1FILTERBYLEVEL( TM1SORT( {TM1SUBSETALL( [zCounterparty] )}, ASC),0)}

but:

1. I get the consolidation appearing at the bottom so the results look like this:

A
B
C
..
..
Z
All Counterparties

Is there a way to force the consolidation to appear at the top?

2. If I'm using it in a View and I collapse the consolidation and re-expand it I've lost the sort order. It's like the view has 'forgotton' that I'm using the dynamic subset..

Is there a way to force it to retain the dynamic subset when being used in a view?

thanks,

Richard

[lotsaram]

Your MDX statement is only working because you have only a single level 1 consolidation in your zCounterparty dimension. To make it more flexible to account for possible changes in the dimension structure you should consider rewriting it. (You can also write the MDX directly. What the MDX recorder gives yo is quite limited much as the Excel macro recorder it only records what you do.)

If your dimension is ordered such that children of consolidations are automatically alpha sorted then all you would need is:
{ DRILLDOWNMEMBER( {[zCounterparty].[All Counterparties]}, {[zCounterpartyMonth].[All Counterparties]} ) }

To be more flexible to account for the consolidation having multiple levels and only showing the top node and level 0 you could use a union query:
{UNION(
{[zCounterparty].[All Counterparties]},
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[zCounterparty].[All Counterparties]}, ALL, RECURSIVE )}, 0)}, ASC)}
)}

As a shortcut to a union you coudl also use the "+" operator eg:
{
{[zCounterparty].[All Counterparties]}
+
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[zCounterparty].[All Counterparties]}, ALL, RECURSIVE )}, 0)}, ASC)}
}

Note: TM1 doesn't care about the formatting the line breaks and spaces are just there for added legibility for human readers, this would work just as well
{{[zCounterparty].[All Counterparties]}+{TM1SORT({TM1FILTERBYLEVEL({TM1DRILLDOWNMEMBER({[zCounterparty].[All Counterparties]},ALL,RECURSIVE )},0)},ASC)}}

To answer your other question once you start drilling up and down you are no longer looking at the original subset of the view and the drill up/down will respect the hierarchy structure of the dimension. It wont revert to the original subset unless the view is reset. Ain't nothing can be done about this.

[tomok]

Is there a way to force the consolidation to appear at the top?

This MDX statement will give you the elements that you want and in the order that you want. It creates a subset of the top level node and concatenates the children of thet top level node sorted in Alpha order:

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [zCounterparty] )}, "All Conterparties")} +
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [zCounterparty] )}, "All Counterparties")}, ALL, RECURSIVE )}, 0)}, ASC)}

If I'm using it in a View and I collapse the consolidation and re-expand it I've lost the sort order. It's like the view has 'forgotton' that I'm using the dynamic subset. Is there a way to force it to retain the dynamic subset when being used in a view?

Not that I'm aware of.

[lotsaram]

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [zCounterparty] )}, "All Conterparties")} +
{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [zCounterparty] )}, "All Counterparties")}, ALL, RECURSIVE )}, 0)}, ASC)}

There's no need for the text pattern filter ("wildcard" filter in the subset editor GUI), if you are searching for a string that is an exact match to an element name you may as well just declare the element explicitly as part of a set. It's both more efficient and more legible. Thus the above becomes ...
{ {[zCounterparty].[All Conterparties]} + {TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[zCounterparty].[All Counterparties]}, ALL, RECURSIVE )}, 0)}, ASC)} }
... which if memory serves me correctly is what I had already posted directly above!