locked
Parallel Processing in Separate Transactions RRS feed

  • Question

  • Hi all.

     

    Is it possible to process partitions in parallel with a separate transaction for each partition using AMO?

     

    I am currently using the CaptureXML method to capture the XMLA sent to SSAS and then using ConcatenateCaptureLog(True,True) to put it all into a batch as a single transaction using parallel processing. This is then executed using ExecuteCaptureLog(True,True).

     

    The problem is that when I process a large number of partitions using this method and one of them fails (which under Murphy's law is always the last partition ), it rolls back all the changes made by the processing, which is a waste of time. I would rather it process each partition in its own transaction so that if it fails, it only rolls back that partition but still make use of the benefits of parallel processing.

     

    I have tried using ExecuteCaptureLog(False,True) but I get the following error:

    Error: Errors in the OLAP storage engine: Element Parallel is not supported in non-transactional batch.

     

    Thanks

    William.

    Tuesday, September 2, 2008 6:24 AM

All replies

  • I got these defination from MSDN :

     

    Transactional

    If the Transaction attribute of the Batch command is set to true, the Batch command run commands all of the commands contained by the Batch command in a single transaction—a transactional batch.

    If any command fails in a transactional batch, Analysis Services rolls back any command in the Batch command that ran before the command that failed and the Batch command immediately ends. Any commands in the Batch command that have not yet run are not executed. After the Batch command ends, the Batch command reports any errors that occurred for the failed command.

    Nontransactional

    If the Transaction attribute is set to false, the Batch command runs each command contained by the Batch command in a separate transaction—a nontransactional batch. If any command fails in a nontransactional batch, the Batch command continues to run commands after the command which failed. After the Batch command tries to run all the commands that the Batch command contains, the Batch command reports any errors that occurred.

     

    Try setting the Transaction Attribute To "True", Something like this :

    <Command>
       <Batch Transaction="True"

     

    Let me know if it works or not..

     

    Regards..

    Girija Shankar

    Tuesday, September 2, 2008 12:14 PM
    Answerer
  • Thanks for replying.

     

    As I am using the CaptureXML() method, the transaction attribute you talk about is controlled in the XMLA using the ExecuteCaptureLog() method.

     

    The way I understand that ExecuteCaptureLog works is that it takes three boolean parameters. The first indicates whether execution will be in transactional mode (true = one transaction for all commands, false = each command in a separate transaction), the second indicates parallel mode (true = parallel, false = not parallel) and the third indicates whether affected objects will be processed as well.

     

    From printing out the XMLA that is produced I can see that the above is true. Given this, I have tried to run ExecuteCaptureLog(False,True) to indicate multiple transactions in parallel, but I get the following error:

     

    Errors in the OLAP storage engine: Element Parallel is not supported in non-transactional batch.

     

    Have you any other thoughts on what I could try?

    Thanks

    William.

    Wednesday, September 3, 2008 4:11 AM
  • I think the : Server.ExecuteCaptureLog Method (Boolean, Boolean)

     

    The first parameter is for "transactional" and second is "parallel"

     

    Have you tried running ExecuteCaptureLog Method setting transactional as true (the first parameter).

     

     

    Regards..

    Girija Shankar

    Wednesday, September 3, 2008 7:07 AM
    Answerer
  • Yes I have and it works fine except that it is not what I want. It is not what I want because it runs everything inside one transaction. This means if I am processing 25 partitions it will roll back everything if any of these partitions fail. This can be a huge waste of time if I have waited over a day for these partitions to process and then one of them fails.

     

    Thanks

    William.

     

    Thursday, September 4, 2008 3:36 AM
  • It seems that parallel processing is not supported in non transactional process. Take a look at this link :

     

    http://www.ssas-info.com/VidasMatelisBlog/8_ssis-package-to-process-all-dimensions-in-ssas-2005-db-continuing

     

     

    so options for you are :

     

    Hope this helps..

     

    Regards..

    Girija Shankar

     

    Thursday, September 4, 2008 9:58 AM
    Answerer