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.