TI - Add new element in alphabetical order under consolidation

[whitelillie]

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]

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

Take a look at the function DimensionSortOrder.

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.

[Wim Gielis]

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.

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 ?

For example, let's take projects (level 0) belonging to a customer (level 1). They both have an ID (element name) and a Description (alias). The user wants to use the Description alias, but wants to see sorted elements - otherwise finding a project is not easy. Or reconciling a pivot table in Excel with a list of elements in TM1 (without additional copy/pasting of elements from Excel to the Subset Editor).

What would be the best way to deal with this rather basic requirement, knowing that MDX's will break whenever we collapse and/or expand a consolidation ? Or would that be the undocumented function DimensionElementInsertByAlias ?

[Alan Kirk]

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.

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 ?

I don't think you're missing anything; given that I like you most likely prefer to avoid undocumented functions unless critically necessary it's a limitation of the product. I suppose that you could Heath Robinson a method of stripping out all of the elements and rebuilding the dim, inserting the elements in alias order though I'm not sure it would work . In a case like you describe I've had the user select a consolidation, used Excel formulas to create MDX to drill down to get the elements in order then union the consolidation on top... but that's in active forms and I know that you frickin' HATE Active forms almost as much as I hate PAW, PAX and Docker.

In Cube Viewer it would not be quite as "easy".

[Wim Gielis]

About active forms: I don't hate them anymore. We will never become good friends but we accept each other.

For the dimension edits: I brought up the case of projects and customers because I inherited a TM1 model for keeping timesheets data (hours spent), which then generates invoices based on that. In fact, this Client dimension is part of that model, with projects at leaf level and rolling up into customers. My predecessor has created all kinds of stuff with aliases and swapping element names with aliases (another undocumented function). It works more or less, except that on a regular basis a dummy project needs to be created to get sorted aliases back. We're at dummy 100 or so I never took the time to really built it up again from scratch given the product limitation we discussed above. Avoiding brain damage and serious headaches following the analysis of my predecessor's TI code is the second reason if I'm honest.

If anyone has a reasonable workaround to this problem, be my guest ! Playing around with the insert point index as the 2nd element in the DimensionElementInsert function does not seem a viable plan either.

[MGrain]

I've had to solve similar problems in the past with a staging dimension and a published dimension, it's an extra level of complexity but if you are building the dimension with TI anyway it's not really an issue.

Some slightly more advanced features in DimensionSortOrder (like specifying an alias) would make it all much simpler.

[CellPutN]

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');

[Wim Gielis]

Thanks CellPutN.
What is the use of the SaveDataAll ? Could it be a CubeSaveData of the respective }ElementAttributes cube ?
In some bigger models I have in mind, I don't fancy a SaveDataAll. Or maybe a control cube for dimension properties.

[CellPutN]

Hi Wim,

I never had perfomance issue with SaveDataAll but I think it can be a CubeSaveData.
To be honest, I have no idea why we have to save on the server but that's what it said in the DimensionSortOrder documentation:

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.

[Wim Gielis]

I never had perfomance issue with SaveDataAll

Start recreating feeders file of about 10GB each and you will see savedata of about 30 minutes. During business hours this is unacceptable.

It will be a CubeSaveData of }DimensionProperties but not sure how this function will allow us to solve the problem though.

[CellPutN]

I never had a model with feeders file of about 10GB each.
I agree with you,

Code: Select all

CubeSaveData('}DimensionProperties');

is better than

Code: Select all

SaveDataAll;

[Wim Gielis]

whitelillie: apologies for hijacking your thread ! Whenever you might have a question or doubt, feel free to ask.

[lotsaram]

Sheesh this ran a little off topic.
But while I'm here ...

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

[Alan Kirk]

Sheesh this ran a little off topic.

To be fair my original reply was just the first line pointing to the DimensionSortOrder function.

Then I foresaw the impending pain that will inevitably come from using a description as a principal name, thought better of it, and added the suggestion that the original poster not do that.

But while I'm here ...

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

Or alternative eg... when you exit the Metadata tab, on which you can stick this function at the bottom.

[tomok]

Sheesh this ran a little off topic.
But while I'm here ...

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

I found that suggestion a little baffling as well. I have changed the sort order on a dimension many times and I can't ever remember issuing any type of cube save in order to make it live in the dimension.

[whitelillie]

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');

Thank you CellPutN!!!!!! - this solved my issue.

I tried using DimensionSortOrder before but had the CompSortType and ElSortType arguments wrong.

Code: Select all

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

[whitelillie]

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.

[Alan Kirk]

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.

Using the AD name as the principal name is less of an issue, since that at least enforces a certain level of uniqueness. Where you may encounter a problem (though this is not germane to the issue at hand) is with the aliases if your organisation is of any significant size. I have to deal with at least half a dozen cases in payroll reporting where two people have the same name. And some of them aren't even particularly common names. Annoyingly, given the total absence of error handling in TI, this can sometimes cause "cannot create alias" errors to spit even if you put in some code that should theoretically avoid them.

Suppose that you have another Chris Smith come to work for you. Maybe it's even Christine Smith this time.

Let's also suppose that the above Description 5 is coming out of the financial system, and that the new Chris Smith is set up as SMITH Chris in the travel system, which is Alt Description 7, say.

For the new employee Alt Description 7 won't be populated because it won't be unique. (The name has to be unique across ALL aliases.)

Thus when you do the lookup for the name SMITH Chris in the travel system interface... you'll be getting alt description 5 of the original employee, not alt Description 7 for the new one.

If you're a really small organisation of 20 people or so this is likely to be less of an issue. The odds are heavily in your favour that you won't get a duplicate name. If you have a few hundred, it will most likely be an issue at the time that you can least afford it to be.

It's up to you and how much of a risk you perceive this to be, but I would probably be inclined to build a lookup cube which has as one dimension, all of the possible names used, in another the source system for that name, and stored at the intersection the AD principal name.

(Actually upon further reflection I'd make that a 3 dimensional cube with a measures dimension consisting of 2 string elements; ADCode, and Comments so that you could make a note of any mapping issues that you have.)

In this way you can ensure that you pick up the correct principal element name in each interface. It would also allow you to streamline the number of aliases if you wanted to.