Page 1 of 1
Ordering a dimension using Aliases
Posted: Fri Mar 23, 2012 2:46 pm
by alexhancock
Hello,
We would like to order a dimension (not a Subset) by Hierarchy using Aliases rather than element names (the element names have to be codes whereas everyone needs to see the English name of the code).
We need the dimension ordered as users often navigate through various levels, expanding consolidated elements as they go. When they expand an element, the elements are presented according to the hierarchy sort of the element (code) names.
Has anyone done this before, as I can't see an easy way to do it? I'm guessing some sort of TI process would need to be used?
Re: Ordering a dimension using Aliases
Posted: Fri Mar 23, 2012 4:12 pm
by qml
Might be me being thick, but I really don't understand how you can be talking about ordering a dimension "by hierarchy using aliases". Hierarchy is independent of element names (or aliases), so I don't really see how you can mix these concepts like that. Elaborate?
Re: Ordering a dimension using Aliases
Posted: Fri Mar 23, 2012 4:53 pm
by lotsaram
qml wrote:Might be me being thick ...
Maybe
I think the OP is talking about ordering elements within consolidations by name, i.e. CompSortType = "ByName" , CompSortSense = "Ascending " , ElSortType = "ByName" , ElSortSense = "Ascending"
BUT rather than ordering by the principal name ordering by an alias.
alexhancock wrote:Hello,
We would like to order a dimension (not a Subset) by Hierarchy using Aliases rather than element names (the element names have to be codes whereas everyone needs to see the English name of the code).
We need the dimension ordered as users often navigate through various levels, expanding consolidated elements as they go. When they expand an element, the elements are presented according to the hierarchy sort of the element (code) names.
Has anyone done this before, as I can't see an easy way to do it? I'm guessing some sort of TI process would need to be used?
I can't see any really easy way to do this without some extra steps. For an individual subset you could use the MDX ORDER function, but although this would display the original subset in the desired order the moment users start expanding and contracting elements then it would revert back to the original by name order. The only way I can think of to achieve what you want is to have the dimension order set to manual and presort the elements by alias name in the data source. I can this of 2 ways to do this 1/ laborious coding looping through each consolidation or 2/ cheat with a hidden "helper dimension" that has the alias to order by as the principal name and automatic dimension order sorting applied, then just process from the helper dimension into the main dimension. You only need the work around if you can't pre-sort the initial feed but option 2 seems like a pretty simple work around (if I don't say so myself
)
Re: Ordering a dimension using Aliases
Posted: Fri Mar 23, 2012 5:05 pm
by Michel Zijlema
lotsaram wrote:
I can't see any really easy way to do this without some extra steps. For an individual subset you could use the MDX ORDER function, but although this would display the original subset in the desired order the moment users start expanding and contracting elements then it would revert back to the original by name order. The only way I can think of to achieve what you want is to have the dimension order set to manual and presort the elements by alias name in the data source. I can this of 2 ways to do this 1/ laborious coding looping through each consolidation or 2/ cheat with a hidden "helper dimension" that has the alias to order by as the principal name and automatic dimension order sorting applied, then just process from the helper dimension into the main dimension. You only need the work around if you can't pre-sort the initial feed but option 2 seems like a pretty simple work around (if I don't say so myself
)
Maybe the SwapAliasWithPrincipalName function can help here:
- swap alias with principal name
- (re)save the dimension with the right ordering settings
- change the ordering to 'By Input'
- swap alias with principal name again
Michel
Re: Ordering a dimension using Aliases
Posted: Mon Mar 26, 2012 11:03 am
by lotsaram
Michel Zijlema wrote:
Maybe the SwapAliasWithPrincipalName function can help here:
- swap alias with principal name
- (re)save the dimension with the right ordering settings
- change the ordering to 'By Input'
- swap alias with principal name again
Michel
Brilliant idea. It deserves to work.
Re: Ordering a dimension using Aliases
Posted: Tue Mar 27, 2012 4:39 am
by rmackenzie
lotsaram wrote:Michel Zijlema wrote:
Maybe the SwapAliasWithPrincipalName function can help here:
- swap alias with principal name
- (re)save the dimension with the right ordering settings
- change the ordering to 'By Input'
- swap alias with principal name again
Michel
Brilliant idea. It deserves to work.
I can confirm it does. You just need a new TI with no data source and 4 lines of code:
Code: Select all
==Prolog==
sDimName = 'Report';
SwapAliasWithPrincipalName ( sDimName , 'Description', 0 ) ;
DimensionSortOrder ( sDimName, 'ByName', 'Ascending', 'ByHierarchy', '' );
==Epilog==
SwapAliasWithPrincipalName ( sDimName , 'Description', 0 ) ;
Having the second swap in the Epilog is important as you need a commit to occur between the two. This works on my dimension perfectly; before the operation the leaf elements were alphabetically ordered and aliases were out of alphabetical order. After the operation, the original leaf elements were out of alphabetical order, but the aliases were in alphabetical order.
For other people, you'll need to check out what your current order is to correctly put in your own DimensionSortOrder(... statement. I tend to use the one in the code example a lot as it alphabetically orders consolidated and leaf elements. However, the
documentation is less than stellar describing in detail what the 'ByHierarchy' order does...
I was tempted to parameterize this and stick it on Bedrock (obviously with full credit to Michel) but I am concerned about the cases of dimension sort orders other than mine - maybe I got lucky? Anyone else want to give this a try on one of their dimensions?
Re: Ordering a dimension using Aliases
Posted: Tue Mar 27, 2012 8:20 am
by Andy Key
I use SwapAliasWithPrincipalName often enough to have set up a utility process to run it, but it should be noted that IBM don't seem to be 100% confident of this function...
From
http://www-01.ibm.com/support/docview.w ... wg21571068
IBM Technote wrote:
...
10- Save and run the TI process (that contains the call to SwapAliasWithPrincipalName).
Element names should now have the name of the alias assigned.
Please ensure to back up your TM1 server before executing, restart the TM1 service after changing the element names, and ensure TI processes/Rules are updated to reflect the changes.
Not that I do a backup or restart, but I have had it fail on me once.
Re: Ordering a dimension using Aliases
Posted: Tue Apr 10, 2012 4:40 pm
by alexhancock
Thanks very much guys, I wasn't even aware that such a function existed - could be extremely useful in the future.
Re: Ordering a dimension using Aliases
Posted: Mon Nov 30, 2015 1:40 am
by Alan Kirk
Andy Key wrote:I use SwapAliasWithPrincipalName often enough to have set up a utility process to run it, but it should be noted that IBM don't seem to be 100% confident of this function...
From
http://www-01.ibm.com/support/docview.w ... wg21571068
IBM Technote wrote:
...
10- Save and run the TI process (that contains the call to SwapAliasWithPrincipalName).
Element names should now have the name of the alias assigned.
Please ensure to back up your TM1 server before executing, restart the TM1 service after changing the element names, and ensure TI processes/Rules are updated to reflect the changes.
Not that I do a backup or restart, but I have had it fail on me once.
That technote is dead but it appears that IBM conceded that there was a bug in the function, as discussed
here.