TM1 Forum

I have an Excel file containing data that I want to load in TM1. Position Title is one of the many columns in the Excel.
The thing is there's lots of different Position Title in the Excel file (around 2000 or so) and we don't want to maintain all of that in TM1 as elements. So I'm thinking to simplify the list by mapping several Titles in the Excel to a single element in my Position Title dimension, thus reducing the number of elements in the dimension. And I also want my user to be able to maintain the mapping by themselves.

My thought is to create 2 dimensions, Full List Position Title and Simplified Position Title dimensions. The Full List will contain all possible Position Title in the Excel, while the Simplified will only contain the generalized ones.
Then I'll create Position Title Mapping cube, composed of Full List Position Title dimension and a Measure dimension containing just 1 element. And for the value that user can fill I'll use a picklist based from the Simplified Position Title dimension.

So in my other cubes that require position title, I'll use the Simplified Position Title dimension. When the values of those cubes have to uploaded from Excel, I'll first map it based on the Position Title Mapping cube.

Is this an acceptable approach? Or is there any better way doing this?

Have you considered using 1 dimension and building the hierarchy to incorporate the simplified/full (many to 1 ) list you require. Eg you would have a Position dimension - in it you have multiple positions (the full list ones) rolling up to particular consolidated (simplified) positions -

Position ABC
- Position ABC_1
- Position ABC_2
- Position ABC_3
Position DEF
- Position DEF_1
- Position DEF_2
- Position DEF_3

and so on. you can then report on the consolidated or the leaf elements - thus avoiding the need to have 2 dimensions containing basically the same elements (potentially making your cube less sparse).

You will also reduce the requirement for a separate mapping cube (depends on how you want to handle the mapping though). Based on the hierarchy above it may be an option to have a TI process handle the mapping and hierarchy rebuild which can be through an action button in excel - the user selects position and which parent it belongs to - these are passed as parameters in the TI process.

Hope this helps