Wednesday, August 25, 2010

How to Process SSAS Dimensions Individually and Automatically

    Sometime there is need to process the cube dimensions individually, so you could create logs to trace the dimension that has been processed, or to recover from the failed point without restart the whole process. We can create a SSIS Task and schedule it in SQL Agent Job as following steps:
    • Create a new Integration Service project.
    • Add execute SQL task with OLE DB connect to the analysis service database
      • Query list of dimensions in a SSAS database using DMV as following and save it into a SSIS object variable
      SELECT DISTINCT DIMENSION_NAME 
      FROM  $SYSTEM.MDSCHEMA_DIMENSIONS
      WHERE LEFT(CUBE_NAME,1) = '$'
      AND [DIMENSION_UNIQUE_NAME] <> '[Measures]'
      ORDER BY DIMENSION_NAME

      • Create a ForEach Loop Container and set the collection to retrieve the dimension name from the above step

      • Add the Analysis Services Processing Task with a SSAS connection to the to be used in the container


      • Edit the task by using expression to set the XMLA dynamically in the ProcessingCommands property with the dimension that we want to process.

      • Here is the sample XMLA to be used in expression editor:
                  

      • You may use SSMS to connect to the SSAS and select a Dimensions in a database to use the Script function to create the XMLA for you as template. You may change the Process Options as needed.

      • Once you have the SSIS Package, go to SQL Server Agent and add a SQL Job with Job Type as SQL Server Integration Service Package. Select the Package Location and set the schedule option as per your requirement.
      That’s it. Now all the dimensions will be processed at the scheduled intervals.
      This way, any newly added dimensions will also get processed without modifying the SSIS package.

      "<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\ xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\">
      <Object>
      <DatabaseID>" + @[User::_DatabaseID] + "</DatabaseID>
      <DimensionID>" + @[User::_DimensionName] + "</DimensionID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
      </Process>
      </Parallel>
      </Batch>"

      3 comments:

      1. Very nice trying to duplicate this hope works out well

        ReplyDelete
      2. Hi,

        I have been using object as type for the variables, and ado net enumerator type in the foreach loop. This works.

        But the only thing still bugging me is the XMLA script, it doesn't parse, whether using my own script or fendies script.

        Someone ideas?

        Nice post fendy!

        Best regards

        ReplyDelete
      3. SELECT DISTINCT DIMENSION_NAME
        FROM $SYSTEM.MDSCHEMA_DIMENSIONS
        WHERE LEFT(CUBE_NAME,1) = '$'
        AND [DIMENSION_UNIQUE_NAME] <> '[Measures]'
        ORDER BY DIMENSION_NAME

        script above gets NAMES of Dimensions and at processing stage we must give DimensionId. When dimension name and dimension Ids are same it works well. but i got error when they are not same.

        How can i get dimensionIds?

        ReplyDelete