TM1 Forum

Hi all,

Is there a concept of Event Handlers in TM1, where it is possible to detect a change in value of a cell and run some rules/processes?

For example, when a user updates a cell value, i want to update the "Status" measure to "pending approval", and update the "Last Update User" measure with who updated the cell value.

Is there a way to capture the last_update_userID and last_update_timestamp when a value is changed? My users typically expects this to be a very basic functionality that most systems would have.

Thanks.
Melvin

melvinleng wrote: Is there a concept of Event Handlers in TM1, where it is possible to detect a change in value of a cell and run some rules/processes?

No.

melvinleng wrote: For example, when a user updates a cell value, i want to update the "Status" measure to "pending approval", and update the "Last Update User" measure with who updated the cell value.

See above.

melvinleng wrote: Is there a way to capture the last_update_userID and last_update_timestamp when a value is changed? My users typically expects this to be a very basic functionality that most systems would have.

Perhaps they should switch to a classic RDBMS with triggers etc. OLAP engines don't normally support this (but I'll be interested if others have found any that do).

I see David beat me to it but anyway ...

melvinleng wrote:Is there a concept of Event Handlers in TM1, where it is possible to detect a change in value of a cell and run some rules/processes?

No. But you wouldn't be the first one to have requested this as an enhancement.

melvinleng wrote:Is there a way to capture the last_update_userID and last_update_timestamp when a value is changed? My users typically expects this to be a very basic functionality that most systems would have.

You can build your own solution to this by processing the transaction logs but typically you wouldn't want to capture this information on a cell by cell basis as usually the granularity that is relevant is which product manager / cost center owner updated brand X or cost center Y during planning cycle Z. Building your own workflow where this kind of information is written to a status cube as the user clict a "submit" button on their forecast is a good way to manage this.

@lotsa, be interesting to set out your approach to processing the logfiles, given they are split by last commit and also hold updates to all cubes, thus different column counts. Or maybe Steve Rowe can recall what he did for that large firm in West London that is no more (the Bedhead, aaarrgh )

Changes to cubes are recorded in the Transaction Log, provided you have logging turned on.

It sounds like you are looking to implement workflow (submission, approval/rejection) which requires a bit more work, and which tends to be handled with Excel rather that in the Cube Viewer. Use a staging cube to capture submissions and then have the submission move to reporting through TI or Rules, once 'approved'.

David Usherwood wrote:@lotsa, be interesting to set out your approach to processing the logfiles, given they are split by last commit and also hold updates to all cubes, thus different column counts. Or maybe Steve Rowe can recall what he did for that large firm in West London that is no more (the Bedhead, aaarrgh )

I have rolled my own workflow plenty of times but I wouldn't ever want to trawl through log files and capture each individual cell change to log somewhere, that would be like a transaction log for the transaction log. I don't think it's a very practical or efficient solution, although it is definitely possible and not too difficult, in much the same way as you would have a generic TI to re-enter or back out transactions. The variable number of fields isn't such a big issue given that the cube name, old value and new value are always in the same place and once you have the cube name you can iterate TABDIM to find the number and order of dimensions and then you know how to deal with each of the element address fields.

Hmm..
seems to me there would be 2 viable solutions, or rather workarounds:

1. Have a staging cube, and have the user to trigger a TI process to submit the values to another cube, where the TI would be able to capure UserID.
2. Simply use the transaction logs.

Melvin