Ordering a dimension using Aliases

Post Reply
User avatar
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

Post by alexhancock » Fri Mar 23, 2012 2:46 pm

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?

User avatar
qml
MVP
Posts: 1074
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.6 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: Ordering a dimension using Aliases

Post by qml » Fri Mar 23, 2012 4:12 pm

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

lotsaram
MVP
Posts: 3316
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

Post by lotsaram » Fri Mar 23, 2012 4:53 pm

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 :) )

User avatar
Michel Zijlema
Site Admin
Posts: 705
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

Post by Michel Zijlema » Fri Mar 23, 2012 5:05 pm

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

lotsaram
MVP
Posts: 3316
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

Post by lotsaram » Mon Mar 26, 2012 11:03 am

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.

rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Ordering a dimension using Aliases

Post by rmackenzie » Tue Mar 27, 2012 4:39 am

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?
Robin Mackenzie

Andy Key
MVP
Posts: 341
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1 CX
Version: 2-5 to 10-2-x
Excel Version: 2003 2007 2010 2013
Location: Sydney
Contact:

Re: Ordering a dimension using Aliases

Post by Andy Key » Tue Mar 27, 2012 8:20 am

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.
Andy Key

User avatar
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

Post by alexhancock » Tue Apr 10, 2012 4:40 pm

Thanks very much guys, I wasn't even aware that such a function existed - could be extremely useful in the future.

User avatar
Alan Kirk
Site Admin
Posts: 5996
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Ordering a dimension using Aliases

Post by Alan Kirk » Mon Nov 30, 2015 1:40 am

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.
"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.

Post Reply