02/02/2014

SSIS - How to process SSAS cubes

In this post I will show how to create, configure and monitor a processment of cubes using Microsoft SSIS package.
I'll start from the beginnig:
1. Create a new SSIS project:
Automatically a SSIS package is created with a name "Package.dtsx". If you want you can rename it.

2. Create Data Sources. You need to create the data source of the cubes (where are located your cube/s). And also my recomendation is to create the data source of the log database - MS SQL Server Database. In this database will be stored a log of all actions that the SSIS package will perform. This is one way to monitor the SSIS package activities and also keep history and even create statistics.

2a. Create SSAS Cubes Data Source:
You can check this check box in order next time to not appear this kind of "warning" message.













2b. Create SQL Server Database Data Source
In the same way like the creation of the Cube Data Source, just using the SQL Server Database connector and connecting to a SQL Server database.

3. Creation of Analysis Services Processing Task:


We will use just this type of task to process cubes and/or dimensions.

4. Configuring the Dimensions processment

4a. You can rename the Task:

4b. Add connection to the Cubes SSAS database:

4c. Add the cube first:

4d. Add all related dimensions to this cube:

4e. Remove the cube (leave only the dimensions):


5. Add another Analysis Processment task (you can copy paste the previous) and configured it in the same way like the dimensions, but this time just leave the cube (steps 4b - 4c).

6. Connect the 2 tasks. When the first task is completed successfully the execution flow will execute the second task:


7. Test the package (execute it):



Done.

No comments:

Post a Comment