Annotations in TM1 Cube

[gbehel]

Hello all,

Can someone suggest if there is a possibility of adding annotations to each data cell of TM1 cube similar to excel? I found a useful post from this forum ;

www.tm1forum.com/viewtopic.php?p=7320 where Lotsaram suggested the following :

"6) Ability to add commentary to the data values enteredYes TM1 can hold string data (comments).
Usually this is better done in a separate cube to the budgeting cube holding values but it can all be presented in the same interface.

7) Ability to view/edit commentary added to the data values during review cycle
Yes, goes without saying."

One way is to have the string data element in the last dimension of the cube and let users enter the comments. However, can you throw some light on the 2 cubes approach that is suggested above or any other method which wil allow users to add annotations and also generate a report of the comments entered.

Regards
Gbehel

[BariAbdul]

You might find this useful:
http://goo.gl/coRJnj Thanks

[mattgoff]

It used to be problematic to mix numeric and string elements in your measures dimension. This is no longer the case; we've had it for years for exactly the reason you have. Unless you really want a 2nd cube, I'd just add the element. Unless I'm overlooking something, any advantages to two cubes is outweighed by the simpler schema.

Matt

[Wim Gielis]

It used to be problematic to mix numeric and string elements in your measures dimension. This is no longer the case; we've had it for years for exactly the reason you have. Unless you really want a 2nd cube, I'd just add the element. Unless I'm overlooking something, any advantages to two cubes is outweighed by the simpler schema.

Matt

My understanding is that the second cube would have less dimensions. To not make an unnecessary high number of cross-sections that will never be used.
And text strings do not add up

[lotsaram]

In a few versions there have been some quirky things known to happen when numeric and string elements are mixed in the same dimension but typically these bugs have been fixed, TM1 is designed to allow holding of numeric and string data in the same cube after all.

Often in a planning or other data entry application commentary isn't required at cell level but is better with less dimensionality but the main technical reason not to include commentary in the same cube is model efficiency as by holding numeric and string data in the same cube you effectively fix the last dimension of the cube which vastly limits the effectiveness of any background cube optimization through dimension reordering as the optimal placement of the last dimension is responsible for about 90% of the possible optimization (cube memory space and calculation speed). For any smallish cube there is really no issue mixing in string data but for any large cube it could be better to have a separate commentary cube if performance optimization is important.

Note if you want to do it all in the same cube and go to cell level then it is best to include a separate dimension in the cube "data type" with just 2 elements "string" and "number", otherwise if there are multiple numeric elements in the measure dimension that might require commentary then you might need to add multiple comment measures. (That is for situations where all measures are otherwise numeric, if there are string measures as well then this approach gets problematic and a separate cube could be easier.) For a separate cube approach just have this extra dimension in the comment cube only.

It is pretty simple to show comments all in the same interface if that interface is Excel as you can use VBA to pull the comments and write them to Excel cell comments with the corresponding intersection. For other UIs some custom report development might be needed.

Note with 10.1+ it is possible to create an "Annotations" cube in the background whereby the commentary is automatically displayed in TM1 Application Web, Cognos Insight and CAFE. This woudl be my preferred approach going forward.

[gbehel]

Thank you all,

the cube im creating does not have a measure dimension. It has other dimensions like region, channels, division, employees etc and only one measure (sale) is being loaded. Hence there was no need to have a seperate Measure dimension.

The approach suggested by Lotsaram (i.e to have a separate measure dimension, with 2 elements OR have a separate cube to be pulled in excel and use VBA) sounds good.

I would like to go with the additional dimension in the cube. My only concern is some of the dimensions have thousands of leaf level elements and hence introducing an additional measure with "String and Numeric" elements will double the number of cells in the cube. Hope that should not hit the performance.

Given the above case which approach could be the best?

Kind Regards
Gbehel

[mattgoff]

Your last dimension is effectively your measures dimension. But, as others have said, if you don't need comments at the same resolution as your data cube, it is to your advantage to have a separate cube. However, if you do need to have the ability to comment on every intersection of your data cube, I'd still just add an element to your last/measures cube.

Can you provide the dimensions of the cube and the required granularity for comments? That will help us make a recommendation.

Matt

[Wim Gielis]

the cube im creating does not have a measure dimension.

Almost always and invariably, foresee a measures dimension in any cube...
Be consistent and name the measures dimension like: 'name of the cube'_Measures, or 'name of the cube'_Msr, or even 'name of the cube'_m

I would like to go with the additional dimension in the cube. My only concern is some of the dimensions have thousands of leaf level elements and hence introducing an additional measure with "String and Numeric" elements will double the number of cells in the cube. Hope that should not hit the performance.

Empty cells do not exist in TM1 and are not stored.
So go ahead and add the measures dimension

[gbehel]

Thank you all for quick responses. Things are much easier to understand with the help of this forum.

Kind Regards
Gbehel

[BariAbdul]

Code: Select all

For any smallish cube there is really no issue mixing in string data but for any large cube it could be better to have a separate commentary cube if performance optimization is important.
For a separate cube approach just have this extra dimension in the comment cube only.

Hi lotsaram,Apologies for not getting correctly I understand one dimension would be the extra dimension but do we need to also have all the dimension that exists in the orginal cube on which we want to have comments on.Thanks

[declanr]

Code: Select all

For any smallish cube there is really no issue mixing in string data but for any large cube it could be better to have a separate commentary cube if performance optimization is important.
For a separate cube approach just have this extra dimension in the comment cube only.

Hi lotsaram,Apologies for not getting correctly I understand one dimension would be the extra dimension but do we need to also have all the dimension that exists in the orginal cube on which we want to have comments on.Thanks

This would all depend on the level of granularity you wanted to store the comments at; if it's one per numeric cell then you want all of them - if it was at a higher level then you could drop dimensions. It just all depends on the use case.