SSIS – Dropping Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL
Below are the steps that we have integrated into SSAS using SSIS so that we can then drop our old SSAS Partitions using SSIS and XMLA.
· We are going to drop our oldest partition from Measure Group called Fact InternetSales 1, which is in our Adventure Works cube.
· The actual Cube partition name is called:
You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330
· I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012
Getting SSAS Partition Details
You can reference the following section in Analysis Services Quick Wins and go to the following section which will explain how to get down and insert our SSAS Partition details.
Getting Partition Name into Variable in SSIS
Below is how we will then drop our oldest SSAS Partition as per our example above.
1. The first thing that we need to do is to find out our oldest Partition for our Measure Group called:
a. Fact Internet Sales 1
2. Once we have this we are then going to put this into a query, which we will then put into a variable in SSIS
3. This is the query that we are going to use
Selecttop 1 ID as CubePartitionID
FROM [AdventureWorksDW2012].[dbo].[Mart_TD_SSAS_PartitionDetails] with (nolock)
Where [Parent_MeasureGroupID] =‘Fact Internet Sales 1’
a. NOTE: For your particular Partition scheme you might have to change your query to get back the partition ID you expect
b. As per above we get the following result in SQL Server Management studio (SSMS)
4. Next in SSIS we will create the following variable as shown below:
5. Next what we do is to assign our query from step 3 above to our variable.
a. NOTE: Ideally you would want to put your query into a Stored Procedure.
b. We drag in an Execute SQL Task, and we rename it to the following:
c. Then we configured the General Tab with the following
d. Then click on the Result Set on the left hand side and configure it with the following Result Name and Variable Name as shown below:
e. Then click Ok.
f. Now you can run and test to make sure that you can get your variable correctly.
Getting XMLA to drop SSAS Partition and put into SSIS
In the following steps we will generate our XMLA and then using this put it into SSIS so that we can then automate this.
1. Go into SSMS and go to your SSAS Cube.
2. As with our example this was our Adventure works cube.
3. We then navigated to the following as shown below:
4. Now right click on the Internet_Sales_2005, Script Partition as, Delete To, New Query Editor Window as shown below.
5. You will now see the following in SSMS
6. Next we now need to go into SSIS and create the following variable
7. Next we need to take our XMLA delete statement and put this into a TSQL Query syntax so that we can then use this to populate our variable (XMLAQuery_DropSSASPartition)
8. This is the how we did it:
a. The first thing to do is where you have your script from step 5 above, go into find and replace and do the following:
b. NOTE: This is so that when we put this into SSIS and load it as an expression it will not invalidate it due to the double quotation.
c. Now we put in our TSQL Query syntax as shown below
9. Now go into your SSIS package and next to the variable XMLAQuery_DropSSASPartition click on the Ellipses button
a. Now we configured our expression with the following shown below:
b. As you can see above we have encapsulated this in our SSIS Expression
c. What we have also done is to insert our CubePartitionID variable into our expression
i. It is highlighted in RED above.
d. You can click on Evaluate Expression to ensure that everything is correct.
e. Click Ok to insert our expression
10. Next what we need to do is to assign our variable we created above into a variable so that this can then be passed to an Analysis Services Execute DDL Task to actually drop the partition, but doing the following below:
a. First we need to create a variable which will hold our XMLA syntax once it has been populated from step 9 above.
b. We gave it the following name:
d. Next what we then need to do is to use an Execute SQL Task to then get our XMLA script populated.
e. We dragged in an Execute SQL Task and gave it the following name:
f. Then we configured our Execute SQL Task with the following to get our data from our variable in step 9 above.
ii. NOTE: AS you can see we set the SQLSourceType to a variable.
1. And then used our variable name from step 9
g. Then click on Result Set and we configured it with the following:
h. Then click Ok.
11. Now the final part is to drag in our Analysis Services Execute DDL Task and configure it to connect to our cube, and then use our script from step 10 above.
12. Now we just need to configure the Analysis Services Execute DDL Task by doing the following.
a. Drag in the Analysis Services Execute DDL Task, double click to go into the Properties
i. Click on DDL
b. In the right hand side where it says Connection you are going to have to create a connection to your SSAS, and Database
i. Click on New Connection
c. As with our example we created our connection, which you can configure to your SSAS Cube.
d. Now where it says SourceType, click on the Drop down and select Variable
e. Then next to Source click on the Drop down and select the Variable that we populated in the previous steps.
f. As with our example we selected the following:
g. It should look like the following:
i. Then click Ok.
Order of Control Flow Items
The final step is to now order everything correctly in our Control Flow.
1. Below is how we have ordered our SSIS Package
3. NOTE: You will see that we are truncating our Mart_TD_SSAS_PartitionDetails table.
a. This is because we want to keep it up to date.
4. NOTE 2: You will see that even though we started with how to get our PartitionDetails, we still put this at the end, so that once our SSAS Partition has been dropped we have the correct details.
5. Finally run your SSIS package and it will then drop your last partition