SSIS – Process Cube with XMLA and Analysis Services DDL

Generating XMLA Script and data from Cube

What I was looking to do, was to use XMLA and the Analysis Services Execute DDL Task in SSIS to dynamically process just a required partition that has already been created in SQL Server Analysis Services (SSAS). I found that doing this in SSIS was a quick and easy way in order to get my data processed.

UPDATE (06 Aug 2013): I have found a way to complete the process of using the XMLA Script without having to put any files on the server or in a physical location. So this has been updated to reflect this below.

With doing this in SQL Server 2012, I could then use the new functionality in SSIS as well as the additional reporting.

Below is the example based on the Adventure Works DW2008R2 Analysis Services Cube.

1.       To generate the XMLA script, log into your SSAS instance and then browse down to the cube that you want to process.

1.       In our example we have partitioned our cube so we want to only process the current month’s partition.

2.       We drilled down to the following level below so that we could get to the partition

i.    

1.       Now right click on the Partition and Select Process

i.      In the Process Partition Window make sure that you change the Process Options to Process Data

ii.      Then at the top click on Script

iii.      NOTE: You can make any other changes you want in the Process Options Window

iv.      This will now then script the details into an XMLA file

1.       Now you have got your XMLA Script

2.       This is what the details look like

3.      

Generating your Partition Name using TSQL and putting it into a variable in SSIS

1.       As you can see from step 2a above we have got the PartitionID, this is what will change each month which we will need to process.

2.       So next you need to create your Partition Name, and to do this we use TSQL to dynamically change the Monthly partition as we move through the months.

3.       So here is the sample script below that we used:

1.       NOTE: This can also be put into a PROC

Declare @MonthNumber as Int

Set @MonthNumber = (SELECT DATEPART(MONTH,GETDATE()-1))

DECLARE @YearNumber AS INT

Set @YearNumber = 2005

— NOTE: You can use this below to get the current Year — SET @YearNumber = (SELECT DATEPART(YEAR,GETDATE()-1))

— ==================================================================================================

— 2. The second parts then adds a leading zero to your month number, this is because by default — it escapes or leaves out

— the leading zero

— ==================================================================================================

Declare @LeadingZeroForMonth as varchar(2)

Set @LeadingZeroForMonth = (select RIGHT(REPLICATE(‘0’,5)+CONVERT(VARCHAR(2),@MonthNumber),2))

— ==================================================================================================

— 3. In the final part we put in the table name and then put in our month value as well as the year

— ==================================================================================================

Declare @SSASPartitionName as varchar(40)

Set @SSASPartitionName = convert(varchar(28),‘Fact Internet Sales – ‘) +convert(varchar(4),+ CONVERT(VARCHAR(4),@YearNumber))

select @SSASPartitionName as SSASPartitionName

1.       As you can see with the above script we are just getting the same name as what will be required in our XMLA Script

                         EG:

1.       Now go into your SSIS Project and into your Package.

1.       Drag in an Execute SQL Task, and set it up so that you can populate your variable with the output of the Proc or TSQL in Step 3 above.

2.       NOTE: In our example we had the variable name of SSASPartitionName

2.       So now you have got your Partition Name and put it into a variable in SSIS

Using the XMLA Script and variable to output it to a variable in our SSIS Package

1.       We do this by creating a variable query in our SSIS Package, which is explained on how to create this below.

1. NOTE: The Variable query is used to dynamically create a query which can then be used in later steps within the SSIS package.

2.       We are going to create a new variable which will contain our dynamic query by doing the following:

1.       Click on Add Variable and configure it with the following:

1.       Name: XMLAQuery_ProcessData_CurrentPartition

2.       Data Type: String

1.       NOTE: It has to be string because we will be passing the entire XMLA Script which we generated above.

2.       Then click on the Ellipses under Expression, which is on the far right hand side of the Variable details.

1.       This will then open the Expression Builder.

2.       Now paste the following into the Expression Builder:

“Select ‘<Batch xmlns=\”http://schemas.microsoft.com/analysisservices/2003/engine\“>

       <Parallel MaxParallel=\”64\“>

              <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\“ xmlns:ddl200=\”http://schemas.microsoft.com/analysisservices/2010/engine/200\“ xmlns:ddl200_200=\”http://schemas.microsoft.com/analysisservices/2010/engine/200/200\“ xmlns:ddl300=\”http://schemas.microsoft.com/analysisservices/2011/engine/300\“ xmlns:ddl300_300=\”http://schemas.microsoft.com/analysisservices/2011/engine/300/300\“>

                     <Object>

        <DatabaseID>AdventureWorksDW2012</DatabaseID>

        <CubeID>Adventure Works</CubeID>

        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

        <PartitionID>“+ (DT_WSTR, 100) @[User::SSASPartitionName]  +”</PartitionID>

                     </Object>

                     <Type>ProcessData</Type>

                     <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

              </Process>

       </Parallel>

</Batch>‘ as XMLAScript_CurrentPartition_ProcessData”

3.       From above you can see where we put in our Variable for the SSAS PartitionName highlighted in RED

4.       You can click on the Evaluate Expression to ensure that the expression is correct.

5.      

3.       Next we are going to create a variable which will contain the entire XMLA Script generated in the previous step, by doing the following below.

1.       Click on Add Variable and configure it with the following:

1.       Name: XMLAScript_ProcessData_CurrentPartition

2.       Data Type: String

4.       Now drag in an Execute SQL Task and put it below where you generated the Table Name.

1.       We gave our Execute SQL Task the following name:

1.       Get XMLA Query for Process Data into Variable

5.       Next we are going to configure our Execute SQL Task to get its data from our Query we created in steps above, by doing the following:

1.       Go into the Properties of the Execute SQL Task and on the General page configure it with the following:

2.       Under Result Set changed this from None, to Single Row.

3.       Then configure the following under SQL Statement.

1.       Connection Type MUST be set to OLE DB

2.       Next to Connection, select your OLE DB Connection.

1.       In our example this was: SQL-AdventureWorks2008R2-ADO

3.       Next to SQLSourceType change this from Direct Input to Variable

4.       Next to SourceVariable, click on the Drop down and select the following Variable:

User::XMLAQuery_ProcessData_CurrentPartition

1.       NOTE: This is our Variable query which we created earlier which contains the entire XMLA Script to Process Data our current partition.

4.       The above should look like the following:

1.      

5.       Now in the left hand side click on Result Set and configure it with the following:

1.       Click on the Add Button

2.       Now where it says Result Name change this to the following:

XMLAScript_ProcessData_CurrentPartition

1.       NOTE: The Result Set Name MUST match the column name that is outputted from your SQL Syntax otherwise the Result Set will fail.

3.       Now where it says Variable Name, click on the drop down and select the following:

User::XMLAScript_ProcessData_CurrentPartition

6.       The above should look like the following:

1.      

7.       Click Ok to complete configuring the Execute SQL Task.

Configuring your Analysis Services Execute DDL Task to use your Variable and Process Data on your Current partition.

1.       Now we just need to configure the Analysis Services Execute DDL Task by doing the following.

2.       Drag in the Analysis Services Execute DDL Task, double click to go into the Properties

3.       Click on DDL

1.       In the right hand side where it says Connection you are going to have to create a connection to your SSAS, and Database

2.       Click on New Connection

3.       As with our example we created our connection

4.       Now where it says SourceType, click on the Drop down and select Variable

5.       Then next to Source click on the Drop down and select the Variable that we populated in the previous steps.

6.       As with our example we selected the following:

User::XMLAScript_ProcessData_CurrentPartition

7.       It should look like the following:

1.      

8.       Then click Ok.

Now finally run the package and it should run and process the partition

NOTE: If you want to check if it worked, go down to the partition level and select Properties. Then look under the Status, and Last Processed date time

Finally here is my entire SSIS Package from start to end so you can see how it all pieces together.