TI - Add new element in alphabetical order under consolidation

Post Reply
whitelillie
Posts: 3
Joined: Mon Feb 06, 2017 12:42 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

TI - Add new element in alphabetical order under consolidation

Post by whitelillie » 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 :)

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: TI - Add new element in alphabetical order under consolidation

Post by Alan Kirk » Tue May 07, 2019 8:10 am

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

Wim Gielis
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI - Add new element in alphabetical order under consolidation

Post by Wim Gielis » Tue May 07, 2019 10:32 am

Alan Kirk wrote:
Tue May 07, 2019 8:10 am
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 ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

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: TI - Add new element in alphabetical order under consolidation

Post by Alan Kirk » Tue May 07, 2019 11:18 am

Wim Gielis wrote:
Tue May 07, 2019 10:32 am
Alan Kirk wrote:
Tue May 07, 2019 8:10 am
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".
"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.

Wim Gielis
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI - Add new element in alphabetical order under consolidation

Post by Wim Gielis » Tue May 07, 2019 12:29 pm

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 :lol: 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

MGrain
Posts: 13
Joined: Wed Nov 15, 2017 11:36 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: TI - Add new element in alphabetical order under consolidation

Post by MGrain » Tue May 07, 2019 2:59 pm

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
Posts: 22
Joined: Mon Oct 01, 2018 1:50 pm
OLAP Product: TM1
Version: 10.2.20500.75
Excel Version: 2016
Location: Montreal, Canada

Re: TI - Add new element in alphabetical order under consolidation

Post by CellPutN » 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');
Last edited by CellPutN on Tue May 07, 2019 10:43 pm, edited 2 times in total.

Wim Gielis
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI - Add new element in alphabetical order under consolidation

Post by Wim Gielis » Tue May 07, 2019 3:40 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

CellPutN
Posts: 22
Joined: Mon Oct 01, 2018 1:50 pm
OLAP Product: TM1
Version: 10.2.20500.75
Excel Version: 2016
Location: Montreal, Canada

Re: TI - Add new element in alphabetical order under consolidation

Post by CellPutN » Tue May 07, 2019 4:16 pm

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
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI - Add new element in alphabetical order under consolidation

Post by Wim Gielis » Tue May 07, 2019 4:33 pm

CellPutN wrote:
Tue May 07, 2019 4:16 pm
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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

CellPutN
Posts: 22
Joined: Mon Oct 01, 2018 1:50 pm
OLAP Product: TM1
Version: 10.2.20500.75
Excel Version: 2016
Location: Montreal, Canada

Re: TI - Add new element in alphabetical order under consolidation

Post by CellPutN » Tue May 07, 2019 5:10 pm

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
MVP
Posts: 2260
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: TI - Add new element in alphabetical order under consolidation

Post by Wim Gielis » Tue May 07, 2019 6:29 pm

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 model with AutoHotKey as a service or application

lotsaram
MVP
Posts: 3318
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TI - Add new element in alphabetical order under consolidation

Post by lotsaram » 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).
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

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: TI - Add new element in alphabetical order under consolidation

Post by Alan Kirk » Tue May 07, 2019 7:32 pm

lotsaram wrote:
Tue May 07, 2019 7:17 pm
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.
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).
Or alternative eg... when you exit the Metadata tab, on which you can stick this function at the bottom.
"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.

tomok
MVP
Posts: 2631
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TI - Add new element in alphabetical order under consolidation

Post by tomok » Tue May 07, 2019 8:21 pm

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).
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

whitelillie
Posts: 3
Joined: Mon Feb 06, 2017 12:42 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TI - Add new element in alphabetical order under consolidation

Post by whitelillie » Wed May 08, 2019 12:32 am

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

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

whitelillie
Posts: 3
Joined: Mon Feb 06, 2017 12:42 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TI - Add new element in alphabetical order under consolidation

Post by whitelillie » 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_Namechris-smith
CurrentYes
Descriptionchris smith
Alt Description 1chris.smith
Alt Description 2christopher smith
Alt Description 3christopher.smith
Alt Description 4christopher-smith
Alt Description 5SMITH Chris
Alt Description 6SMITH Christopher
Alt Description 7SMITH, 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. :)

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: TI - Add new element in alphabetical order under consolidation

Post by Alan Kirk » Wed May 08, 2019 3:28 am

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_Namechris-smith
CurrentYes
Descriptionchris smith
Alt Description 1chris.smith
Alt Description 2christopher smith
Alt Description 3christopher.smith
Alt Description 4christopher-smith
Alt Description 5SMITH Chris
Alt Description 6SMITH Christopher
Alt Description 7SMITH, 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.
"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