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>"