Script Batch XMLA Commands Using T-SQL

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.

SELECT DISTINCT CAST(LEFT(CAST([dbo].[FactInternetSales].[OrderDateKey] AS VARCHAR(100)), 4) AS INT)
FROM [dbo].[FactInternetSales]
WHERE OrderDateKey >= '20050101' AND OrderDateKey <= '20081231'

DECLARE @sql nvarchar (MAX)

OPEN io_cursor

SET @firstRun = 1

FROM io_cursor
INTO @year

WHILE (@@Fetch_Status = 0)
IF (@firstRun = 1)
SET @firstRun = 0
SET @sql = '<Batch xmlns='''' Transaction=''true''>'
PRINT @sql
SET @sql =
'<Delete xmlns="">

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

PRINT @sql
FROM io_cursor
INTO @year

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

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

2014-10-06 14_23_36

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

2014-10-06 14_33_25

After executing this, no errors should appear.2014-10-06 14_26_57

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

2014-10-06 14_28_02


Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

One thought on “Script Batch XMLA Commands Using T-SQL”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s