Parallel dimension processing from different batches. Possible? (2023)

Ask a question

Search related threads

  • Remove From My Forums

Answered by:

Archived Forums 361-380

>

SQL Server Analysis Services

  • Question

  • Parallel dimension processing from different batches. Possible? (1)

    Sign in to vote

    Hi,

    I'm trying to process two different dimensions from two different connections using XMLA-Batches. Each batch on it's own works fine, but if I try to launchtwo batches simultaneously, runs only the first one, the second onealways terminates with DEADLOCK error.

    Ís it even possible to processtwo or moredimensions from different batches parallel? Maybe there are any XMLA-tags to set isolation level or someone else?

    Thanks!

    Wednesday, February 2, 2011 8:06 AM

Answers

  • Parallel dimension processing from different batches. Possible? (3)

    Sign in to vote

    Ah okay, thanks. Just to be certain though, is this an Analysis Services deadlock issue or has Analysis Services failed due to a relational database deadlock error that has effectively been logged? (Can you post the actual error?) I tried processing a couple of dimensions in different batches here where the first appears to wait for the second to complete, no deadlocks.

    1. Okay so that seems like a standard dimension processing XMLA script. I guess you don't need the parallel tag there for one dimension but that shouldn't be an issue.

    2. At first glance that seems quite inefficient but possibly more information required. See point 3 below...

    3. I understand. However I strongly suspect that your complex ETL processes are going to get even more complex going this route. I have a similar situation, but rather than go the route you are going I have serialised the cube processing aspect. Remember that Analysis Services doesn't really yet have fine-grained locking and as such the process to commit writes (i.e. processing) requires instance and database level locks. Any queries currently running (read locks) will prevent AS from committing the processing of objects hence you have the settings CommitTimeout and ForceCommitTimeout. Constantly processing provides little window for the interaction between read and write locks.

    You also need to be careful that your ETL processes aren't updating dimension tables whilst Analysis Services is processing that dimension else you can get issues with referential integrity in AS. Also, unless you have AS2008R2 you will have scalability issues if you are using a large number of partitions as the time taken to build the processing schedule grows exponentially the more partitions you have. Thus, multiple processing batches will each need to build their own processing schedule and in the end state could take longer to process than a single batch doing it in parallel.

    I don't want to be a prophet of doom, though I guess I have just done a good job of that, but I would really advise you to seriously consider serialising aspects of this. For example, process all dimensionsrelated to a cube in parallel and within a single batch and then the cube partitions and then do the next set of dimensions but only where shared dimensions have been updated since the last process. Rinse and repeat.

    Beyond this you probably need to look at a profiler trace to analyse why you are getting deadlocks when doing this and then go from there.

    http://bi-logger.blogspot.com/

    • Proposed as answer by Jerry Nee Monday, February 7, 2011 7:11 AM
    • Marked as answer by Jerry Nee Tuesday, February 15, 2011 2:13 AM

    Wednesday, February 2, 2011 1:54 PM

  • Parallel dimension processing from different batches. Possible? (5)

    Sign in to vote

    Sure, that's what the <Parallel> element is for. So something like the following would process both the Adventure Works dimensions "Customer" and "Date" in parallel. The "maxParallel" limits the number of dimensions to be processed in parallel to 4. The default (not specified) is "let the server decide".

    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel maxParallel="4"> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"> <Object> <DatabaseID>Adventure Works DW</DatabaseID> <DimensionID>Dim Customer</DimensionID> </Object> <Type>ProcessUpdate</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"> <Object> <DatabaseID>Adventure Works DW</DatabaseID> <DimensionID>Dim Time</DimensionID> </Object> <Type>ProcessUpdate</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel></Batch>

    http://bi-logger.blogspot.com/

    • Proposed as answer by Jerry Nee Monday, February 7, 2011 7:10 AM
    • Marked as answer by Jerry Nee Tuesday, February 15, 2011 2:13 AM

    Wednesday, February 2, 2011 4:26 PM

All replies

  • Parallel dimension processing from different batches. Possible? (7)

    Sign in to vote

    Hmmm, 3 questions first...

    1. Are the two batches only processing dimensions? i.e. None of the batches are processing cubes, measure groups or partitions. Attempting to process partitions (and thus inclusive of measure groups and cubes) at the same time as dimensions can cause deadlocks as both processes can end up referencing resources out of order.
    2. Are you attempting to process the same dimension in both batches?
    3. Why do you need to process dimensions on multiple connections rather than processing dimensions in parallel within a single batch?

    http://bi-logger.blogspot.com/

    Wednesday, February 2, 2011 8:32 AM

  • Parallel dimension processing from different batches. Possible? (9)

    Sign in to vote

    1. These batches process only dimensions, the batch looks like:

    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">
    <Object>
    <DatabaseID>SIS_DWH_E</DatabaseID>
    <DimensionID>SISDWH DIM Minerwas Stichtag</DimensionID>
    </Object>
    <Type>ProcessUpdate</Type>
    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
    </Parallel>
    </Batch>

    2. No, one dimension - one batch.

    3. Wehave a very complex and heavyweight ETL process - many tables being imported and converted from many sources simultaneously. Now we process our cubesjust after all transfers and converts are done. The main idea is to process some OLAP objects just after the necessary data are here.

    Wednesday, February 2, 2011 9:06 AM

  • Parallel dimension processing from different batches. Possible? (11)

    Sign in to vote

    Ah okay, thanks. Just to be certain though, is this an Analysis Services deadlock issue or has Analysis Services failed due to a relational database deadlock error that has effectively been logged? (Can you post the actual error?) I tried processing a couple of dimensions in different batches here where the first appears to wait for the second to complete, no deadlocks.

    1. Okay so that seems like a standard dimension processing XMLA script. I guess you don't need the parallel tag there for one dimension but that shouldn't be an issue.

    2. At first glance that seems quite inefficient but possibly more information required. See point 3 below...

    3. I understand. However I strongly suspect that your complex ETL processes are going to get even more complex going this route. I have a similar situation, but rather than go the route you are going I have serialised the cube processing aspect. Remember that Analysis Services doesn't really yet have fine-grained locking and as such the process to commit writes (i.e. processing) requires instance and database level locks. Any queries currently running (read locks) will prevent AS from committing the processing of objects hence you have the settings CommitTimeout and ForceCommitTimeout. Constantly processing provides little window for the interaction between read and write locks.

    You also need to be careful that your ETL processes aren't updating dimension tables whilst Analysis Services is processing that dimension else you can get issues with referential integrity in AS. Also, unless you have AS2008R2 you will have scalability issues if you are using a large number of partitions as the time taken to build the processing schedule grows exponentially the more partitions you have. Thus, multiple processing batches will each need to build their own processing schedule and in the end state could take longer to process than a single batch doing it in parallel.

    I don't want to be a prophet of doom, though I guess I have just done a good job of that, but I would really advise you to seriously consider serialising aspects of this. For example, process all dimensionsrelated to a cube in parallel and within a single batch and then the cube partitions and then do the next set of dimensions but only where shared dimensions have been updated since the last process. Rinse and repeat.

    Beyond this you probably need to look at a profiler trace to analyse why you are getting deadlocks when doing this and then go from there.

    http://bi-logger.blogspot.com/

    • Proposed as answer by Jerry Nee Monday, February 7, 2011 7:11 AM
    • Marked as answer by Jerry Nee Tuesday, February 15, 2011 2:13 AM

    Wednesday, February 2, 2011 1:54 PM

  • Parallel dimension processing from different batches. Possible? (13)

    Sign in to vote

    Thanks Philip,

    unfortunatelly we work with AS2005 :( Maybe it is our problem.

    Our ETL process (a lot of SSIS packages) takes a lot of times and resources. But during the ETL processes ourSSASenvironment do nothing. After the ETL process is ready we start to process our cubes - the ETL environment do nothing, but SSAS servers are full loaded. We thought, we can process the cube dimensions during the ETL process, just after the dimensions data are loaded. And after ETL is finished the only we have to do is to process our cubes. But unfortunately this idea doesn't work - we have the DEADLOCK error in case of parallel dimensiion processing from different SSIS tasks.

    Our next idea is to create a "processing queue" - a stack of processing requests, which can be processed by the background task.

    Wednesday, February 2, 2011 3:18 PM

  • Parallel dimension processing from different batches. Possible? (15)

    Sign in to vote

    Philip,

    is it possible to process a single dimensions in parellel, i.e. i have seperate jobs trying to process a single cube and as you said one awaits the 1st job to completedbefore the second josb starts the job processing.

    Thoughthe same is not the case with measures where we can proces the cubes based on partitons, is there a way where we can do dimension processing in parellel as well?

    Abhinav

    Wednesday, February 2, 2011 3:56 PM

  • Parallel dimension processing from different batches. Possible? (17)

    Sign in to vote

    Sure, that's what the <Parallel> element is for. So something like the following would process both the Adventure Works dimensions "Customer" and "Date" in parallel. The "maxParallel" limits the number of dimensions to be processed in parallel to 4. The default (not specified) is "let the server decide".

    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel maxParallel="4"> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"> <Object> <DatabaseID>Adventure Works DW</DatabaseID> <DimensionID>Dim Customer</DimensionID> </Object> <Type>ProcessUpdate</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"> <Object> <DatabaseID>Adventure Works DW</DatabaseID> <DimensionID>Dim Time</DimensionID> </Object> <Type>ProcessUpdate</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel></Batch>

    http://bi-logger.blogspot.com/

    • Proposed as answer by Jerry Nee Monday, February 7, 2011 7:10 AM
    • Marked as answer by Jerry Nee Tuesday, February 15, 2011 2:13 AM

    Wednesday, February 2, 2011 4:26 PM

  • Parallel dimension processing from different batches. Possible? (19)

    Sign in to vote

    Is this going to work if I use this in SSIS Process task to process the Dimension?

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, January 18, 2019 3:51 AM

Top Articles
Latest Posts
Article information

Author: Amb. Frankie Simonis

Last Updated: 12/30/2022

Views: 6111

Rating: 4.6 / 5 (56 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Amb. Frankie Simonis

Birthday: 1998-02-19

Address: 64841 Delmar Isle, North Wiley, OR 74073

Phone: +17844167847676

Job: Forward IT Agent

Hobby: LARPing, Kitesurfing, Sewing, Digital arts, Sand art, Gardening, Dance

Introduction: My name is Amb. Frankie Simonis, I am a hilarious, enchanting, energetic, cooperative, innocent, cute, joyous person who loves writing and wants to share my knowledge and understanding with you.