We currently have two different charts of accounts referencing the same nth elements. At present each chart is listed vertically one under the other with two sets of consolidations of the nth elements. The current structure creates issues in terms of consolidations as it is difficult to check that the second chart is appropriately rolled up. Also it is easy for nth elements to be missed from the second chart.
Is the vertical representation of these charts the best approach?
An alternative we are looking at is creating the second chart horizontally (next to the first chart) but this creates issues in that many nth elements can roll up to the same description in the second chart meaning that a duplication of the second chart description at the nth level would occur (which I don't think is allowed).
Any thoughts on the best way to construct multiple charts in TM1 would be most appreciated.
The most wasted of all days is one without laughter.
e e cummings (1894-1962)
There are two ways of maintaining the dimensions and their structures that come with TM1, both have their pros and cons but IMO neither are 100% perfect.
The first is the older style dimension worksheets.
Advantages.
Can control the order in which the elements appear in the dimension
Disadvatages.
Cannot be used for large dimensions due to the row limit in Excel.
Hard to use for multiple consolidations for the reasons you describe.
Dimension Editor
Advantages.
Can be used for large dimensions as the row limit does not exist
Disadvatages.
Can not (or very hard to) control the order in which the elements appear in the dimension.
Hard to use for multiple consolidations for the reasons you describe.
They both fail (again IMO) at the fairly basic requirement of being able to easily manage multiple complex structures. This is basically because both approaches are trying to do two things at the same time.
1. Display the structure/consolidation of the dimension in an intuitive way for the end user
2. Define the dimension.
I think they have 1 mostly right but at the cost of making 2 weak as this requires that the elements be repeated in the definition as many times as you have hierarchies and as you have discovered this rapidly leads to a maintenance nightmare.
For example in one of the systems I look after a P and L code needs to be a member of 5 different consolidations. This would be very very hard to maintain in a safe way using the vanilla methods.
In order to solve this issue you need to separate the definition of the dimension from it’s graphical representation.
In very simple terms the solution is as follows, define the structure of your dimension in an excel spreadsheet or a SQL database and read it into the system using a TI process.
The columns and data in your spreadsheet / DB will be something like this.
The key to this is that every element in the dimension appears once and once only in the child column, even if it is a consolidated element (very few C level elements are not themselves children of another C level). That way it is easy to look at an element and establish where it is mapped into in the dimension and if it is mapped into all the hierarchies it needs to be. You would also add the alias and attributes and so on into this database.
The TI process would (amongst other things) add the child to each of the parents in the row. The above definition would give you the following structure.
Acc01 is a child of Hier1 01 is a child of Hier1 02
Acc01 is a child of Hier2 01 is a child of Hier2 02
This is the best way of managing complex structures I have found as I can look at a single row and see the complete definition of an element within the dimension, but it does have some downsides.
1. You still can’t control the order in which the dimension elements appear in the subset editor in controlled way. For me though this comes a distant second to making sure that my 5 versions of the P and L all produce the same result.
2. It’s non-trivial to update the spreadsheet / database automatically when a new account code is encountered. It requires a good controls / VBA / SQL skills to join the dimension maintenance to the data load. Your approach to loading actual data and your maintenance of the dimensions that can change during the data load need to be considered to together. If the two tasks are not “joined up†in a logical way then you can make the dimension maintenance hard for yourself.
3. It’s not as intuitive for end-users to define their structures in this way as they will still need to see the graphical representation in the subset editor to understand the definition. It can be hard to look at the data base and picture the final structure, but since the TI process will build the dimension for very quickly I’ve found the users soon get the hang of it.
Anyway the above are my thoughts which I was going to write more formally and wiki for discussion, but since you posted the question this seems as good a place as any to start the discussion off.
Thanks very much for your quick and indepth response to Cheng's question. I am new to TM1 but have a reasonable background in VB/VBA and SQL. I think I understand your approach but please excuse me if my question is stupid.
The two charts we have start at different levels - i.e. Chart 2's bottom level is one up from Chart 1.
Using your approach I've created the below example which assumes the following:
"P...." = Chart 1
Weighting is ignored for the example
Does the below rollup make sense?, and if so, we seem to get stuck when duplicates appear in a column e.g. P1040, P2000 & Acc14020 are not accepted when trying to save the xdi file (obviously for the full listing we would probably need to use a TI process).
You need constructe an excel table in the way I describe, save this as a text file and the read it into the system using a TI process. It looks like you have the right idea though.
HTH
