SSAS Processing-SSIS

SSIS has a component to process SSAS cubes, it is called the Analysis Services Processing task. This task allows one to specify the cubes/measures groups that need to be processed. This task requires a connection string, the connection string specifies the Analysis services databases that need to be connected to. In the properties of the Analysis Services Processing task, there is a property called ConnectionName, this has the connection name alias for the connection string. The main aspect of this task to process a SSAS cube, there are two ways of specifying the cube. The first method is to use the user interface to specify the cube, this can be done by choosing Edit on the task. In the task editor, specify the Analysis services connection manager, once that is done, in the next step choose the Add button in the Object list window.

In this example i connected to the Adventure works cube on Analysis services, one can see the list of cubes and the measure groups within the cube. Choose the cube that needs to be processed, the cubes gets added to the Object list window. Once this is done choose OK and the task can be executed to process the cube.

The second method is to use Expressions, within this property there is an attribute called ProcessingCommand. To take advantage of this attribute, create a string variable called sProcess at the package level. An XMLA command can be assigned to this variable. XMLA is a XML string which caters to the processing of SSAS cubes. The XMLA string for the processing of the cube can be generated by going into the SQL Server Management studio, connecting to Analysis services. Select the cube within the Analysis Services database and right click process, the process window comes up. In this window choose the script option and copy the script to a new query window.

Switch back to SSIS, choose the variable sProcess, copy and paste the  XMLA string into the variable. One word of caution is to make sure that the whole process string gets pasted, if this doesn't happen remove the carriage returns and then paste it.

Once this operation is complete, choose the ProcessingCommand property with the Expressions for this task and then assign the variable sProcess, evaluate the expression, one should be able to see the XMLA string. On completion of the expressions execute the task, the SSAS cube will get processed. Using a variable and XMLA one can automate the processing of SSAS cubes and since the XMLA is stored in a string variable, this can be built up by concatenating different string variables.