TM1 Forum

whitelillie wrote: ↑Tue May 07, 2019 8:06 am I'm a pretty new user of TM1 (using version 10.2.4)

I have a dimension that is list of employee names with a single consolidation called "All Employee Names". The dimension looks some this like this:

All Employee Names
adam-jones
brett-smith
david-collins

Each time a new employee name is added to the dimension by a TI process, it is just adding the new name to the bottom of the consolidation and looks like this:

All Employee Names
adam-jones
brett-smith
david-collins
aaron-henry

To clean this up I am currently going into the dimension structure once a week and manually removing the consolidated element, resorting the n-level elements alphabetically and then adding the consolidated element back in.

Is it possible to add the new element so that it is added in alphabetical order as part of the TI process?

Thank you for any help you can provide

Alan Kirk wrote: ↑Tue May 07, 2019 8:10 amNormally you would use an artificial key for this; something like an employee ID code from the HR module. You can use the name as an alias. You can then use MDX to create sorted subsets which put the elements into the order that you want.

Wim Gielis wrote: ↑Tue May 07, 2019 10:32 am

Alan Kirk wrote: ↑Tue May 07, 2019 8:10 amNormally you would use an artificial key for this; something like an employee ID code from the HR module. You can use the name as an alias. You can then use MDX to create sorted subsets which put the elements into the order that you want.

True. But start using consolidations to collapse and expand and we are screwed.
Which is not to say that people's names should be the element names, but this is to something that is not optimal - unless I am missing something. Or is there a away to insert elements such that an alias is the leading way to sort element names ?

This function sets a sort type and sense for dimension elements and for components of consolidated elements within a dimension.
This is a TM1® TurboIntegrator function, valid only in TurboIntegrator processes. The sort order defined byDimensionSortOrder determines how the subset All dipsplays in the Subset Editor.

DimensionSortOrder sets properties for a dimension; the dimension is not actually sorted until it is saved on the server.

CellPutN wrote: ↑Tue May 07, 2019 4:16 pmI never had perfomance issue with SaveDataAll

Not 100% clear documentation wrote: DimensionSortOrder sets properties for a dimension; the dimension is not actually sorted until it is saved on the server.

lotsaram wrote: ↑Tue May 07, 2019 7:17 pm Sheesh this ran a little off topic.

lotsaram wrote: ↑Tue May 07, 2019 7:17 pm But while I'm here ...

Not 100% clear documentation wrote: DimensionSortOrder sets properties for a dimension; the dimension is not actually sorted until it is saved on the server.

You don't need a SaveDataAll or for that matter CubeSaveData('}DimensionProperties')

What the documentation is saying, albeit clumsily, is that the new dimension sort order is applied not when changing the dimension properties via the DimensionSortOrder function but when the dimension is next saved (the dimension not the properties cube).

The dimension save and change in order will happen the next time the dimension is updated (e.g. open the dimension in the dimension editor and click save).

lotsaram wrote: ↑Tue May 07, 2019 7:17 pm Sheesh this ran a little off topic.
But while I'm here ...

Not 100% clear documentation wrote: DimensionSortOrder sets properties for a dimension; the dimension is not actually sorted until it is saved on the server.

You don't need a SaveDataAll or for that matter CubeSaveData('}DimensionProperties')

What the documentation is saying, albeit clumsily, is that the new dimension sort order is applied not when changing the dimension properties via the DimensionSortOrder function but when the dimension is next saved (the dimension not the properties cube).

The dimension save and change in order will happen the next time the dimension is updated (e.g. open the dimension in the dimension editor and click save).

CellPutN wrote: ↑Tue May 07, 2019 3:26 pm Create a TI with a dimension parameter and copy the following code in the prolog. It will solve all your problems !

Code: Select all

###################################################
#
# This TI sort order for the dimension entered as parameter.
# All dimension elements are sorted in ascending by level,
# and any components of consolidations are sorted in 
# ascending alphabetical orde
#
###################################################

#==================================================
# Paramater validation
#==================================================

IF(DIMIX( '}Dimensions', pDim ) = 0);
	ProcessQuit;
ENDIF;

#==================================================
# Sort dimension
#==================================================

DimensionSortOrder ( pDim , 'ByName' , 'Ascending' , 'ByHierarchy' , 'Ascending');

DimensionSortOrder (pDim, 'ByInput', 'Ascending', 'ByName', 'Ascending'); 

Alan Kirk wrote: ↑Tue May 07, 2019 8:10 am That said, I would not necessarily recommend using people's names as principal element names. If someone changes their name (as some newly married women have an annoying (from a system point of view) habit of doing), you'll need to export all of the data relating to that element and create a new one, then reimport the data. Changing a name is the same as deleting the old element and creating a new one, which is why aliases came in back in version 7; that was a real problem until then.

Normally you would use an artificial key for this; something like an employee ID code from the HR module. You can use the name as an alias. You can then use MDX to create sorted subsets which put the elements into the order that you want.

whitelillie wrote: ↑Wed May 08, 2019 2:52 am

Alan Kirk wrote: ↑Tue May 07, 2019 8:10 am That said, I would not necessarily recommend using people's names as principal element names. If someone changes their name (as some newly married women have an annoying (from a system point of view) habit of doing), you'll need to export all of the data relating to that element and create a new one, then reimport the data. Changing a name is the same as deleting the old element and creating a new one, which is why aliases came in back in version 7; that was a real problem until then.

Normally you would use an artificial key for this; something like an employee ID code from the HR module. You can use the name as an alias. You can then use MDX to create sorted subsets which put the elements into the order that you want.

Agree that using people's names as principal element names is not the best approach. Unfortunately in my current workplace it's the only solution that has worked for us.

We have so many systems that we draw data from (financial, HR, IT, travel, compliance, assets etc) and they all record employee names in a different format. The way that we have gotten around this is by creating an employee name dimension that has the employee's active directory name as the main element and then any other formats as alias'.

AD_Name	chris-smith
Current	Yes
Description	chris smith
Alt Description 1	chris.smith
Alt Description 2	christopher smith
Alt Description 3	christopher.smith
Alt Description 4	christopher-smith
Alt Description 5	SMITH Chris
Alt Description 6	SMITH Christopher
Alt Description 7	SMITH, Chris

It's not the cleanest solution but for us, it solves more problems than it creates when capturing employee name data across the multiple systems.