TM1 Forum

Hey guys, got one for ya. My head is developing a small bald patch from scratching.

Is there a way to programmaticaly prevent a TI process from committing data changes? In other words, is there a way to programmatically force a rollback?

Background:

There are two specific points in a TI's execution when changes are committed:
- directly after the Metadata tab all metadata changes are committed;
- directly after the Epilog tab all data changes are committed.

Let's simplify the problem and only talk about data changes. Those can be made on any of the four tabs (e.g. you can pot a CellPutN statement in any tab) and are only merged with the base data at the very end of the process. However, there doesn't seem to be a way to skip that step. Neither of the usual suspects seem to prevent committing:
- ProcessQuit
- ProcessError
- ItemSkip
- ItemReject

Sure, the above functions let you stop processing and jump out of a tab or even the whole process entirely, but any of the transactions already collected by the process get committed anyway.

I want to effectively be able to say 'something's gone wrong in the process, roll back all the changes instead of committing them' and achieve atomic change control - either all changes go in or none do. As it stands I can't find a way to do it. Sure, there are workarounds like kicking off another TI process that will undo the unwanted changes based on some transaction record (a file, a logging cube or the system transaction log). But that is not really achieving the real goal. Is it me or does it seem like a fairly standard thing to want? Do you know of a good trick?

I agree, it would be useful. To the best of my knowledge what you're looking for can't be done right now. You could try submitting an RFE (Request For Enhancement) with IBM.

I have in the past run an initiation process which tests for errors in the data where possible and on successful completion of this process then run the main process.

Not atomic change control but something is better than nothing!

Hi Qml

Another post on here triggered a possible idea. I wonder if you could use the Sandbox functions to write to a Sandbox. If you don't want to commit, then delete the Sandbox. If you do, then commit the Sandbox to base data.

Regards

Paul Simon

Thanks guys for your input.

Paul, I like your idea. I actually thought about it too, but the problem with this approach is that there seems to be no TI function to commit a sandbox (or is there?). It seems I would have to jump out of pure TI and do some API work to merge the sandbox with base data. It sounds perfectly doable, but a bit convoluted.

qml wrote:It seems I would have to jump out of pure TI and do some API work to merge the sandbox with base data. It sounds perfectly doable, but a bit convoluted.

You could set a TI to use the sandbox and export all relevant cube data to flat file or a DB then have another TI/Set of TIs import that data back to the base model... a bit convoluted but probably less messy than having to go through the API route.

QML did you read this post:

http://www.tm1forum.com/viewtopic.php?f=3&t=11755

I'm not sure if it'll be of any use but it does cover a process used within an application to commit data,

Jim.

Thanks Jim

That was the post that triggered the idea for using Sandboxes in the first place.

I looked at Declan's idea. It would be nicer if you could do it without having to write a file. However, the documentation on the Sandbox functions doesn't make it clear as to whether or not it is possible to swap between sandbox and base in a process ie can you read from a sandbox and write to base - might need to swap back to sandbox again before the next record is read, although I believe that in general TM1 will prepare a view completely before reading starts. Anyway it would be one to try.

I looked at the function }tp_workflow_save_node. I am not convinced that IBM gave the right function. That one seems to be about saving a node in a planning application, not committing a sandbox.

The other option might be the API route. At least in 10.2.2 you can now call Java from TI.

Regards

Paul Simon

There are TI functions for managing sandboxes http://www-01.ibm.com/support/knowledge ... ns?lang=en. It wouldn't surprise me if there were some other undocumented ones too. For example, I did see a post on this forum about a "ServerSandboxCreate" function that I can't find in any of the docs.

Big thanks for your valueable input, guys.

I have tried a few obvious keywords, but didn't stumble upon any undocumented TI functions that would commit a sandbox.

Also, I am keen to find a single-pass solution where each source record is only processed once, not twice or more. Data volumes I'm working with are on the chubbier side of the spectrum.

I will post an update if I have any interesting results with the API route, or any other one.

Try Obviously since this is undocumented YMMV.

Brian - thanks for the tip, this TI function works.

The sandbox route seemed very promising until we realised while prototyping a solution that there is a maximum user sandbox size and it's by default 0.5 GiB on a 64-bit server and can only be increased to 1 GiB (this upper limit is not documented, but setting it higher doesn't work). With our data volumes this is not even close to being enough.

So at the moment it's looking like we won't be able to go down that route. For someone with small data volumes this could still be an option though.

If anyone comes across this thread looking for a solution to the same problem, in another thread I discuss one, possible to implement with TM1 10.2.2 FP3 or newer.

Wouldn't having a top process with called process steps in the prolog with conditional process quits between the steps. Commit only occurs at the end of the top processes' epilog

Hi John, thanks for the idea. I don't think it really solves my problem though. I might also be misunderstanding your proposal. However yopu define the 'steps', once the subprocesses implementing these steps have run, the changes are either already committed (metadata changes) or are waiting to be committed at the end of the parent process (data changes) and there is nothing in standard TI you can do in the parent process (like ProcessError, ProcessQuit etc.) that would prevent that commit from happening.

I would like to do single-pass processing (e.g. due to large volume of data) while simultaneously being able to jump out and do a rollback in case any exceptions are encountered. Sure, I could validate all data in one pass and then do a second pass to load, but that does not meet my criteria.

Ideally, the solution would work with multi-threaded data loads too. Think 40 or 80 instances of a TI process running in parallel, loading data into the same cube. If even one of them encounters an issue it should be able to do a rollback, but also communicate to the other processes (e.g. via a flag file) that they should terminate and roll back too.

There are a few workarounds that get me quite close to what I want, but nothing so far has fully satisfied the geek in me.

If you wanted N multi-threaded processes to commit or rollback in coordination (if such functionality were possible) you would have to have them counting success/failure flags and only commit when all N (including themselves) have been counted. This is known in the trade as a "two phase commit protocol".

Thanks Duncan, good link. Methinks that if I had a good rollback method for a single TI then I could easily implement 2PC (Two-Phase Commit) myself. Each of the parallel processes would report its status back in the Epilog and then wait for others. If all processes report success, they are all allowed to commit; if at least one is unsuccessful they all roll back (using whatever method is adopted). That kind of synchronisation is quite easy to implement, actually.

One other idea I had for a rollback was to terminate the process via the TM1 Top API 'Cancel Thread' call before it has a chance to commit, but that's not too pretty either. I think I'll play with the SpreadErrorInTIDiscardsAllChanges (link 4 posts above) hack for now.