To load the data at consolidation level

[sreesuku]

Hi All,

Is it possible to load the data for consolidation levels in TM1 for a specific measure ?
Eg : I have a Region dimension with the hierarchy Geo --> Country --> state. Is it possible to load some values for state and different values for Country and Geo for a specific measure ?

Regards
Sree

[Christopher Kernahan]

In TM1 you can only directly load data into an N level element (this is a common statement and refers to elements of type 'N' and 'S'). You can't load data into a C level (also type 'C') level element.

You can, however, set rules on C level elements that change their behaviour. An example is that you have values stored as percentages in N level elements, and set a rule on the C level to calculate the percentages appropriately instead of simply consolidating.

edit: Note you can load string data into C level elements.

[tomok]

A common strategy in this instance is to create a leaf-level element to hold data for country-level data and have it a direct child of the country, just like the individual states.

[lotsaram]

To elaborate on Tomok's comment a typical solution to your problem is to have summarized dimensions where a consolidated element in a dimension is a leaf element in another dimension and have a series of cubes for input at each level with rules directing data entry from the higher level data entry cubes into the more granular cubes.

An approach using "dummy" leaf elements to hold data entry for consolidated nodes within a single dimension can also work but I find in most cases that the multiple dimension, multiple cube approach is much better and actually much less complex to maintain.

In your case where you have region > state > country > geography > world you would have not only a region dimension but also state and country dimensions (and input cubes).

[jim wood]

Just to mix things up. You can load string data at the consolidated level. You could add a string element to your measure dimension. (make sure this is the last dimension in the cube) If you need that to consolidate or you need it for a calculation add a numeric version of the same element (Call the original element revenue_string and the numeric version revenue.) and use a rule at the C: level to convert the string to a number. If you then hide the text measure from the user base you have your entry at the consolidated level.

This is just another option and one with a little more over head than the holding element solution mentioned above. I would avoid using it in a heavily rule based model.

[sreesuku]

Hi All,

Thanks for the replies. I will check them and give an update.

[sreesuku]

Just to mix things up. You can load string data at the consolidated level. You could add a string element to your measure dimension. (make sure this is the last dimension in the cube) If you need that to consolidate or you need it for a calculation add a numeric version of the same element (Call the original element revenue_string and the numeric version revenue.) and use a rule at the C: level to convert the string to a number. If you then hide the text measure from the user base you have your entry at the consolidated level.

This is just another option and one with a little more over head than the holding element solution mentioned above. I would avoid using it in a heavily rule based model.

HI Jim,

I have tried doing this, but I am getting the error while data loading , that 'cell type is consolidated'

I am just listing down the steps which I am doing. Just validate the same.
1) I have created a dummy measure which is of type string. I have a numeric version of the same as well
2) In TI, I have changed the type of data and and the element as string ( I tried making only data as 'string' but still not working)
3) In 'Maps - Dimensions' I made the element type as 'String' ( Its not working with element type 'Numeric as well)
4) In 'MapS - Data' I changed the measure element type as 'String'

[Steve Rowe]

It sounds like you are still writing to the consolidated numeric element rather than its equivalent string value. Make sure that you have updated the measures bit of the mapping.
Then go click in each tab of the advanced tab just to be sure that the code gets updated before you save it.
Depending on how serious you want to get with using TI you'll need to stop using the wizard and write your own script, it's pretty limiting for all but the most simple process.

Cheers

[Michel Zijlema]

I am just listing down the steps which I am doing. Just validate the same.
1) I have created a dummy measure which is of type string. I have a numeric version of the same as well
2) In TI, I have changed the type of data and and the element as string ( I tried making only data as 'string' but still not working)
3) In 'Maps - Dimensions' I made the element type as 'String' ( Its not working with element type 'Numeric as well)
4) In 'MapS - Data' I changed the measure element type as 'String'

Please note that the cell type in a cube is determined by the element type in the last dimension in the cube.
So if the element that you changed the element type for from N to S is not in the last dimension that is part of the regarding cube, than this change will have no effect on the cell type.

Michel

[sreesuku]

Hi Steve/Michel

I had added a string measure corresponding to the actual numeric measure. During data mapping I was able to map the column to this value.
I had clicked on the advanced tabs to ensure code udation. Still I was getting the same error.
Michel : My measure dimension is the last dimension in the cube and I have changed the type of one element to string in this dimension

I am attaching my TI tabs for your reference. Let me know if I need to change anywhere.

Here 'Test' in my string measure, for which I have changed the type as 'String'. 'Public' is consolidation level element for which I am trying to do load this string value

[Steve Rowe]

Hi Can you post the code that is on advanced tabs too please?

[sreesuku]

Hi Steve,

Please check the attached TI advanced tabs