Hold Consolidate and Hold Release in TI

Post Reply
deepu_sree
Posts: 29
Joined: Tue Jun 22, 2010 4:01 pm
OLAP Product: IBM TM1
Version: 9.5
Excel Version: 2007

Hold Consolidate and Hold Release in TI

Post by deepu_sree »

Hi All,
We need to do Hold Consolidate and Hold Release in TI processes.

Please pour in your suggestions.

TM1 - version 9.5
Excel - 2003

Regards,
Deepu.
User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: Hold Consolidate and Hold Release in TI

Post by rkaif »

When you place HOLD on a cell in any cube TM1 creates a Control cube with name }Hold_UserName_CubeName. The cells which are on Hold will have a value of H in this control cube. See the attachment for details.

If you will write a TI Process which writes H in the }Hold_UserName_CubeName for the required intersection then you can place Hold on these cells. To remove/release the Hold you will have to erase the value of H from the control cube - once again you can do this within a TI Process.

Hope it helps!
Attachments
Hold Consolidate in TI Process
Hold Consolidate in TI Process
TM1_HoldConsolidate.jpg (153.61 KiB) Viewed 9027 times
Cheers!
Rizwan Kaif
deepu_sree
Posts: 29
Joined: Tue Jun 22, 2010 4:01 pm
OLAP Product: IBM TM1
Version: 9.5
Excel Version: 2007

Re: Hold Consolidate and Hold Release in TI

Post by deepu_sree »

Many Thanks Rizwan - Certainly helpful!

Just wondering how/when does this Control cube get deleted?

Regards,
Deepu
deepu_sree
Posts: 29
Joined: Tue Jun 22, 2010 4:01 pm
OLAP Product: IBM TM1
Version: 9.5
Excel Version: 2007

Re: Hold Consolidate and Hold Release in TI

Post by deepu_sree »

The first time round, if a hold has never been applied on a cube, how would the control cube '}Hold_UserName_CubeName' be created.
TI throws an error as 'Cube not found' while trying to write 'H' into the control cube.

Thanks,
Deepu.
User avatar
rkaif
Community Contributor
Posts: 328
Joined: Fri Sep 05, 2008 6:58 pm
OLAP Product: IBM Cognos TM1
Version: 9.1 or later
Excel Version: 2003 or later

Re: Hold Consolidate and Hold Release in TI

Post by rkaif »

If you will place/release a hold in a cell then }Hold_UserName_CubeName will be created automatically. This will be easy to do.

I have not tried it personally but you could use CubeCreate() function in TI to create }Hold_UserName_CubeName by yourself. But this way could be tedious.
Cheers!
Rizwan Kaif
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Hold Consolidate and Hold Release in TI

Post by lotsaram »

deepu_sree wrote:The first time round, if a hold has never been applied on a cube, how would the control cube '}Hold_UserName_CubeName' be created.
TI throws an error as 'Cube not found' while trying to write 'H' into the control cube.

Thanks,
Deepu.
When the user applies a hold through the UI the }Hold cube is automatically created and the hold values are inserted. What possible requirement could you have for creating this cube manually? It is not too difficult to train your users to apply a hold (just right-click)! If you are building your own interface to apply holds quite honestly I can't see how it would be any easier for your users then what comes out of the box (without you having to code or design anything). Just teach your users how to spread and hold!
User avatar
jpm_de
Posts: 22
Joined: Thu Jun 10, 2010 5:19 pm
OLAP Product: TM1
Version: 10.2.2 FP3
Excel Version: 2010

Re: Hold Consolidate and Hold Release in TI

Post by jpm_de »

Actually, I came across a user requirement, where centrally/automatically administering holds is exactly, what the client might need.

And yes, you could and should train users to use holds. Power users will love this feature during the prep or adjustment of any initial/final plan.
But setting holds centrally in combination with a limited UI interaction could be a very powerful way of managing certain dummy user interaction.

And by the way: Being able to handle parallel interaction more nicely, I would propose an (optional config-parameter-based) redesign of }Hold cubes, replacing the }Hold dim with }clients and using just one hold cube for each data cube for all users.

Advantages
1) Often, a lot of users do adjustments/planning/forecasting once or a dozen times a year.
These dummy users will prefer some simple action buttons or even predefined hold constellations, where they need not or should not be allowed any changes to holds.
2) And especially where dummy users are concerned, disabling the hold context menu could even reduce the need for a complex security and/or rules/TI to limit and manage the scope of a user's changes to a larger set of data.

Requirement
Given some cube with typical hierarchies (dims A, B, and C), the client first wants to edit Dim A as follows:

Dim A
Total A
- A1
- A11
- ...
- A1x
- A2
- A21
- ...
- A2y
- A
- AN1
- ...
- ANz
- A Other

The planning process starts with adjusting the Total, lets say increase 5%, which is the organization's new top target.
Afterwards, the Total and the next level (A1...AN, A Other) should be fix. A1's descendants are editable by user 1, A2's ones by user 2, and so on.
By placing holds centrally, in combination with element security, this would be a very very nice way of restricting the effects of a dummy user's interactions within the area of their responsibility. This effectively allows for adjustments within the given responsibility, without any change to a higher level's target.
And yes, it would be nice to allow these users to use holds on the other dimensions to get the advantage of adjusting the allocation within their domain more easily.
I will try and see, what happens, when I limit write access to part of a hold cube.

And the requirement even states, that some users, lets say user 3 with A3, are allowed to use "A Other" as a bucket for any changes to A3 itself by releasing/not holding A3 and A Other. In any case, this would reduce the need for reconciliation processes/reports/forms and functionality, and allow for some clearing mechanism using A Other.

What I see, would be two changes I would love to see
1) Allow more explicit TI handling of Holds/Releases and its cube(s).
2) Add Hold/Release to the list of manageable user capabilities or even better, disable holds in the context menu, based on security or a new status for the hold cube.
Why TM1? Because ...with great dimensionality there must also come -- great responsibility!
(http://www.quotecounterquote.com/2012/0 ... great.html)
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Hold Consolidate and Hold Release in TI

Post by Wim Gielis »

deepu_sree wrote: Sun Nov 14, 2010 8:07 amThe first time round, if a hold has never been applied on a cube, how would the control cube '}Hold_UserName_CubeName' be created.
Update: it seems that the name of Holds cubes has been changed, I don't know what TM1 version.
Now it is: }Hold_USERNAME_}}_CUBE
Where:
USERNAME is the Client's principal name (not the }DefaultDisplayName alias) and
CUBE is the cube name where the Hold is applied.

Version 2.0.6
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply