How to prevent a commit / force a rollback
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
How to prevent a commit / force a rollback
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?
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?
Last edited by qml on Tue Jun 23, 2015 5:34 pm, edited 1 time in total.
Kamil Arendt
-
- MVP
- Posts: 264
- Joined: Mon Nov 03, 2014 8:23 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2 PA2
- Excel Version: 2016
Re: How to prevent a commit / force a rollback
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.
-
- Community Contributor
- Posts: 248
- Joined: Tue Nov 01, 2011 10:31 am
- OLAP Product: TM1
- Version: All
- Excel Version: All
- Location: Manchester
- Contact:
Re: How to prevent a commit / force a rollback
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!
Not atomic change control but something is better than nothing!
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: How to prevent a commit / force a rollback
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
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
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: How to prevent a commit / force a rollback
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.
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.
Kamil Arendt
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: How to prevent a commit / force a rollback
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 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.
Declan Rodger
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: How to prevent a commit / force a rollback
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.
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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: How to prevent a commit / force a rollback
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
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
-
- MVP
- Posts: 264
- Joined: Mon Nov 03, 2014 8:23 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2 PA2
- Excel Version: 2016
Re: How to prevent a commit / force a rollback
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.
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: How to prevent a commit / force a rollback
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.
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.
Kamil Arendt
-
- MVP
- Posts: 264
- Joined: Mon Nov 03, 2014 8:23 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2 PA2
- Excel Version: 2016
Re: How to prevent a commit / force a rollback
Try
Obviously since this is undocumented YMMV.
Code: Select all
ServerSandboxMerge( 'source sandbox name', 'target sandbox name (or empty for base cube)' );
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: How to prevent a commit / force a rollback
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.
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.
Kamil Arendt
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: How to prevent a commit / force a rollback
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.
Kamil Arendt
-
- Community Contributor
- Posts: 295
- Joined: Mon Mar 23, 2009 10:50 am
- OLAP Product: PAW/PAX 2.0.72 Perspectives
- Version: TM1 Server 11.8.003
- Excel Version: 365 and 2016
- Location: South London
Re: How to prevent a commit / force a rollback
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
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: How to prevent a commit / force a rollback
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.
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.
Kamil Arendt
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: How to prevent a commit / force a rollback
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".
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: How to prevent a commit / force a rollback
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.
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.
Kamil Arendt