Creating Dimension Through TI Scripts

[TM1Learner]

Hi Gurus,

I have got a business requirement wherein I need to create a dimension similar to this. Left most being most detailed . As we can see here there are many to many relationships between the levels. While I can copy and paste each of these and create dimension manually in dimension editor , but for large data I want to use a TI script ti create such dimension. When I use Dimension Insert and Dimension Component Insert functions, it result in wring dimension, as for eg both "Ami Yad and Jal Nano" will be inserted as the lower most level under Finance for both Delhi and Paris, which increases the lower most elements to be 12 rather then 10.

Please suggest a workaround for this using TI scripts, so that we can create dimensions involving many to many relationships between levels using TI scripts in TM1. I have this data in a csv as shown below and trying to import in TI and customize the scripts.


Employee Name Department City Total Employee
Arun Jain Sales Dubai Total Employee
Nib Rai Sales Dubai Total Employee
Zxesa Pan IT Delhi Total Employee
Ami Yad Finance Delhi Total Employee
Jal Nano Finance Paris Total Employee
Chi Yun Executive London Total Employee
Xun Yha Reserch Dubai Total Employee
Rit Kumar Sales Dubai Total Employee
Niharika Jain HR Delhi Total Employee
Cha Jhu IT Delhi Total Employee

Thanks,

[garry cook]

If the data looks like that, pull it in as a line into V1, use the SUBST and SCAN functions to chop it up into the three fields and then DimensionElementComponentAdd them together.

[TM1Learner]

Thanks for the help but, data is anyways in CSV, and is in .excel. Here on the post it is showing like a single line as formatting does not apply on the forum GUI.We do not want to pull it in a single line nor have it in single. Issue is not about pulling data, but the semantics. Please refer to the attached image as it has the contents in excel which needs to form a dimension having levels Employee Name -> Department -> City -> Total Employee

Now if I want to use Dimension Add and then Dimension Component add , it will attach Employee "Jal Nano" and "Ami Yad" to "Finance" under both "Delhi" and "Paris" , so I will have 4 elements for this instead of just two. This is die to many to many relationship between City and Department.

Actual Result (As seen in Dimension Editor in TM1 after execution of TI Scripts)
----------------------------------------------------------------------------------------------
Delhi --> Finance --> "Jal Nano" and "Ami Yad"
Total Employee ->
Paris --> Finance -->"Jal Nano" and "Ami Yad"

Expected Result
----------------------

Delhi --> Finance --> "Ami Yad"
Total Employee ->
Paris --> Finance -->"Jal Nano"

[declanr]

Thanks for the help but, data is anyways in CSV, and is in .excel. Here on the post it is showing like a single line as formatting does not apply on the forum GUI.We do not want to pull it in a single line nor have it in single. Issue is not about pulling data, but the semantics. Please refer to the attached image as it has the contents in excel which needs to form a dimension having levels Employee Name -> Department -> City -> Total Employee

Now if I want to use Dimension Add and then Dimension Component add , it will attach Employee "Jal Nano" and "Ami Yad" to "Finance" under both "Delhi" and "Paris" , so I will have 4 elements for this instead of just two. This is die to many to many relationship between City and Department.

Actual Result (As seen in Dimension Editor in TM1 after execution of TI Scripts)
----------------------------------------------------------------------------------------------
Delhi --> Finance --> "Jal Nano" and "Ami Yad"
Total Employee ->
Paris --> Finance -->"Jal Nano" and "Ami Yad"

Expected Result
----------------------

Delhi --> Finance --> "Ami Yad"
Total Employee ->
Paris --> Finance -->"Jal Nano"

This isn't really a lack of tm1 knowledge you just need to apply some basic logic, in order to get this to work you could prefix the department with something unique such as the city field.

vCity | ' - ' | vDept

[TM1Learner]

OK thanks..may be a bad design as any ways drilling up and drilling down would be an issue with many to many relationship between levels. Anyways doing prefix would be a stop gap fix that I have tried but then dimension elements which will be created will be like Deptt+City which is not acceptable. Probably I would split this into 2 dimensions instead. Just thought TM1 would have a way to deal with such problems.

[tomok]

Just thought TM1 would have a way to deal with such problems.

It does, it's splitting Department and City into different dimensions. I don't know of any tools out there that can build a hierarchy with man-to-many relationships in the tree. The only answer is to use concatenation to provide the unique parents needed or split Department and City.

[Duncan P]

If your employee names are unique then this would appear to be a place for alternate hierarchies in the same dimension. The employee dimension would have two hierarchies, employees by department under the 'All Departments' member, and employees by city under the 'All Cities' member.

To do this in TI set the 'Total Employee' column to ignore and create two new variables on the Variables tab. I called them AllDepartments and AllCities.

Variables tab

althier_vars.png (11.74 KiB) Viewed 8089 times

For the formulae (under the Formula button above) I put the expressions

Code: Select all

AllDepartments = 'All Departments';

and

Code: Select all

AllCities = 'All Cities';

Then on the Consolidations tab I assigned them as below

Consolidations tab

althier_consols.png (8.44 KiB) Viewed 8089 times

and then I get the following result

Resulting dimension

althier_result.png (22.9 KiB) Viewed 8089 times

The key to this is in making sure that you have a separate top element for each hierarchy. This will also make it easier to use one or other in a view without confusing the users.

I hope this is useful to you.

[TM1Learner]

OK thanks for the help ..So it will create the dimension but what I actually want is ..

1)Have users be able to select any combination of location, department and enter data for payroll. But because I do not have 1 to many relationship between location and department hence cannot force a drill up and drill down here. By the just above solution I can get the dimension straight away but still in the cube I do not have anything to make the HR number entry manageable by having emplyees filtered by both department and location.

2)I have instead created a Emp Name -> Department hierarchy and introduced Location as an attribute to Emp Name.So now if I can have any way to enable my user to filter the emp-deptt view on location attribute filter , the hr entry becomes much more manageable.

3)I have seen in other tools it is possible to put a drop down filter based on attribute values , but I am not able to locate such thing in TM1 Perspectives or Architect.

Is there any other way to achieve this , as this requirement will become more complex with introduction of employee grades. There are thousands of employees and I cannot fit Employee Names and Grade/Deptt in single hierarchy because of many to many relationships. But users who enter data want to filter employees based on grade and deptt and then manually enter through views. What do i do. Please advise.

Apologies for my ignorance as I am very new to TM1 and have just started learning things.

[lotsaram]

If using an active form report either in excel or web then your requirement seems rather simple to address. Just have Dept, City, Grade, etc, etc as attributes of the employee dimension and in the active form you can have selections for these attributes that feed a parameterized excel formula to generate a MDX statement that will in turn generate the employee rowset in the active form for the selected combination of attribute values.

Sorry if that sounds complicated but really it isn't!

You could or should also be using the employee attribute values to drive alternate hierarchies and subsets in the employee dimension.

[TM1Learner]

Can you please advise in detail in terms of
How do i create selections for these attributes in Active form in Excel.
Which MDX function I should use and how do I have a parameterized excel formula to generate a MDX statement and how that that will generate the employee rowset.

I already have created the Attributes.

I have seen a similar product called Control from KCI and in there it is pretty straight forward . You can just create attributes and create prompts based on the attributes. Do'nt we have similar UI based functionality in TM1 rather then having to do this in Excel.

thanks