none
DDL Task in Sequence Container, run in Parallel?

    Question

  • Hello,‌ If I place several AS Exec DDL Task objects that load various data partitions inside a Sequence container, on a 1200 Tab cube, will all the tasks run in parallel? Thanks
    Tuesday, July 10, 2018 9:46 PM

All replies

  • No, I'm pretty sure they will get serialized. The reason is that each DDL task will start it's own implied transaction so the first task received by the server will start running and the others will be blocked until that first one commits, then the next one will run and so on.

    To run multiple processing tasks in parallel they should be submitted to SSAS as a single parallel batch XMLA command. This way they will run inside a single transaction. The downside of this is that if one operation fails they will all roll back, but if you want parallel execution there is no other option.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, July 11, 2018 12:07 AM
    Moderator
  • Hi JP_Quinn,

    Thanks for your question.

    >>>Hello,‌ If I place several AS Exec DDL Task objects that load various data partitions inside a Sequence container, on a 1200 Tab cube, will all the tasks run in parallel? Thanks
    From SSIS side, the tasks will run in parallel. However, just like Darren said that each DDL task will start it's own implied transaction so the first task received by the server will start running and the others will be blocked until that first one commits, then the next one will run and so on. Thus, they will get serialized.

    In this scenario, I would suggest you to use Analysis Services Processing Task. There is a setting called processing order, you can change it from Sequence to Parallel. Please see below screenshots:


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Visakh16MVP Wednesday, July 11, 2018 7:23 AM
    Wednesday, July 11, 2018 7:21 AM
    Moderator
  • Unfortunately, when I click on the change settings, my option to select Parallel is grayed out, and after pokng around I can't seem to make a change to get the option available. I'm open to ideas as to why mine is grayed and how to get parallel in. Thanks

    Wednesday, July 11, 2018 1:52 PM
  • Wednesday, July 11, 2018 1:59 PM
  • This is the design I'm working with, where after the fact tables load, I have the first step load all the dims as a full process, then the next step is the first of what many be many, when I have a scripted task to load a target data partition. In both these, I get the grayed out option of selecting parallel. Hope this helps clarify.
    Wednesday, July 11, 2018 2:01 PM
  • Hi JP_Quinn,

    Thanks for your response.

    >>>Unfortunately, when I click on the change settings, my option to select Parallel is grayed out, and after pokng around I can't seem to make a change to get the option available. I'm open to ideas as to why mine is grayed and how to get parallel in. Thanks
    In this scenario, you might want to try to upgrade your SSAS Server to latest version. As you can see from the screenshot by me, I can change it from Sequential to Parallel. 

    >>>This is the design I'm working with, where after the fact tables load, I have the first step load all the dims as a full process, then the next step is the first of what many be many, when I have a scripted task to load a target data partition. In both these, I get the grayed out option of selecting parallel. Hope this helps clarify
    In this scenario, you would follow Darren's idea, submit the task to SSAS as a single parallel batch XMLA command. See sample code below:

    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
     <Parallel>  
      {
       "refresh": {
         "type": "automatic",
         "objects": [
           {
             "database": "YourCube",
             "table": "Usage",
             "partition": "Your partition"
           }
         ]
       }
     }
     </Parallel>  
    </Batch>  

    For more information about Batch processing, please refer to below document:

    https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/batch-processing-analysis-services?view=sql-server-2017#bkmk_xmla

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 12, 2018 7:46 AM
    Moderator