Very recently I had to delete a 600 partitions from a single measure group in a cube. This is a high number of partitions, but to make matters worse, it was 600 random partitions out of 1,000 or so partitions already there. So, I could’ve spent the best part of a day picking through which partitions to delete, or I could use SQL to script out the xmla for me and then execute the xmla manually. Ideally I would have done this in PowerShell. And I probably will if I have to do something like this again. But seeing as it is a one shot, I decided to write it in T-SQL. At any rate, I’ve posted the T-SQL below.

I’ve created an example using the Adventure Works 2012 enterprise cube that has partitions already created. Beneath the code I have some screenshots showing the script in action. The SELECT statement used for the cursor was taken directly from the partition; the Internet Order partition is partitioned by year on the OrderDateKey column in the dbo.factinternetsales table. So to modify this for any partitions you want to drop in your own cubes you need to alter the cursor in the FOR SELECT statement and the xml nodes for the Database, Cube, MeasureGroup and Partition.

DECLARE io_cursor CURSOR
FOR
SELECT DISTINCT CAST(LEFT(CAST([dbo].[FactInternetSales].[OrderDateKey] AS VARCHAR(100)), 4) AS INT)
FROM [dbo].[FactInternetSales]
WHERE OrderDateKey >= '20050101' AND OrderDateKey <= '20081231'
ORDER BY 1 DESC

DECLARE @year INT
DECLARE @firstRun BIT
DECLARE @sql nvarchar (MAX)

OPEN io_cursor

SET @firstRun = 1

FETCH NEXT
FROM io_cursor
INTO @year

WHILE (@@Fetch_Status = 0)
BEGIN
IF (@firstRun = 1)
BEGIN
SET @firstRun = 0
SET @sql = '<Batch xmlns=''http://schemas.microsoft.com/analysisservices/2003/engine'' Transaction=''true''>'
PRINT @sql
END
SET @sql =
'<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

AdventureWorksDW2012Multidimensional-EE
Adventure Works
Internet Orders
Internet_Orders_'+CAST (@year as nvarchar (5))+'

</Delete>'
PRINT @sql
FETCH NEXT
FROM io_cursor
INTO @year
END

CLOSE io_cursor
DEALLOCATE io_cursor
SET @sql = '</Batch>'
PRINT @sql

I’ve added a few extra partitions to the Internet Orders measure group:

So say we decide to delete the 5005-2008 partitions for whatever reason. Using the script above, we can create the batch delete xmla.


Refreshing the Object Explorer will show that the partitions have been dropped.