Ordering a dimension using Aliases
- alexhancock
- Posts: 16
- Joined: Thu Oct 21, 2010 3:27 pm
- OLAP Product: TM1
- Version: 10.1 + 10.2
- Excel Version: 2010
- Location: London, UK
Ordering a dimension using Aliases
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?
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?
- qml
- MVP
- Posts: 1095
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: Ordering a dimension using Aliases
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?
Kamil Arendt
-
- MVP
- Posts: 3683
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Ordering a dimension using Aliases
Maybeqml wrote:Might be me being thick ...
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.
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 )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?
- 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: Ordering a dimension using Aliases
Maybe the SwapAliasWithPrincipalName function can help here: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 )
- 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
-
- MVP
- Posts: 3683
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Ordering a dimension using Aliases
Brilliant idea. It deserves to work.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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Ordering a dimension using Aliases
I can confirm it does. You just need a new TI with no data source and 4 lines of code:lotsaram wrote:Brilliant idea. It deserves to work.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
Code: Select all
==Prolog==
sDimName = 'Report';
SwapAliasWithPrincipalName ( sDimName , 'Description', 0 ) ;
DimensionSortOrder ( sDimName, 'ByName', 'Ascending', 'ByHierarchy', '' );
==Epilog==
SwapAliasWithPrincipalName ( sDimName , 'Description', 0 ) ;
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?
Robin Mackenzie
-
- MVP
- Posts: 352
- Joined: Wed May 14, 2008 1:37 pm
- OLAP Product: TM1
- Version: 2.5 to PA
- Excel Version: Lots
- Location: Sydney
- Contact:
Re: Ordering a dimension using Aliases
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
From http://www-01.ibm.com/support/docview.w ... wg21571068
Not that I do a backup or restart, but I have had it fail on me once.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.
Andy Key
- alexhancock
- Posts: 16
- Joined: Thu Oct 21, 2010 3:27 pm
- OLAP Product: TM1
- Version: 10.1 + 10.2
- Excel Version: 2010
- Location: London, UK
Re: Ordering a dimension using Aliases
Thanks very much guys, I wasn't even aware that such a function existed - could be extremely useful in the future.
- Alan Kirk
- Site Admin
- Posts: 6622
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Ordering a dimension using Aliases
That technote is dead but it appears that IBM conceded that there was a bug in the function, as discussed here.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
Not that I do a backup or restart, but I have had it fail on me once.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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.