Script to CREATE and DELETE multiple SSAS cube partitions

Here is how you can create a script to create and delete multiple SSAS cube partitions.

1. Right click on cube partition --> Script Partition As--> CREATE TO
2. Right click on cube partition --> Script Partition As--> DELETE TO
3. Embed the Delete Partition and Create Partition XMLA in the T-SQL Script below
4. Run the T-SQL script and copy the result and paste it in the scriptlet below to run these commands as a batch.
5. 
   

   Copy paste the script that is generated from the script above.. Here .....

   
   

Detailed instructions follow

T-SQL Script that dynamically generates the partitions

DECLARE @StartDate DATE = getdate() - 360;


DECLARE @EndDate DATE = getdate();

DECLARE @StartYearMonth INT = CAST(CONVERT(CHAR(6),@StartDate,112) AS INT)

DECLARE @EndYearMonth INT = CAST(CONVERT(CHAR(6),@EndDate,112) AS INT)

DECLARE @YYYYMM VARCHAR(6)

DECLARE @CreatePartition NVARCHAR(max)

DECLARE @DeletePartition NVARCHAR(max)

DECLARE @FinalPartitionDef NVARCHAR(MAX)

SELECT @DeletePartition = '





'

SELECT @CreatePartition = ''



Adventure Works DW 2008R2

Adventure Works

Internet Orders







Internet_Orders_YYYYMM

Internet_Orders_YYYYMM



Adventure Works DW

SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber] FROM [dbo].[FactInternetSales]
WHERE OrderDateKey <= YYYYMM



Molap

Regular



-PT1S

-PT1S

-PT1S

-PT1S












SELECT LEN(REPLACE(REPLACE(@CreatePartition,CHAR(13),''),CHAR(10), ''))

SELECT LEN(REPLACE(REPLACE(@DeletePartition,CHAR(13),''),CHAR(10), ''))



WHILE @StartYearMonth <= @EndYearMonth

BEGIN

SELECT @FinalPartitionDef = REPLACE(@DeletePartition,'YYYYMM',CONVERT(CHAR(6), @StartYearMonth, 112))

SELECT @FinalPartitionDef = @FinalPartitionDef + REPLACE(@CreatePartition,'YYYYMM',CONVERT(CHAR(6), @StartYearMonth, 112))

PRINT @FinalPartitionDef

SELECT @StartDate = DATEADD(month, 1, @StartDate)

SELECT @StartYearMonth = CAST(CONVERT(CHAR(6), @StartDate, 112) AS INT)



END

--------Now copy the script generated by running this script and paste in the command below----------------








Copy paste the script that is generated from the script above.. Here .....










Sample script looks something like this…

















Adventure Works DW 2008R2

Adventure Works

Internet Orders







Internet_Orders_201106

Internet_Orders_201106



Adventure Works DW



SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]

FROM [dbo].[FactInternetSales]

WHERE OrderDateKey <= 201106





Molap

Regular



-PT1S

-PT1S

-PT1S

-PT1S





















Adventure Works DW 2008R2

Adventure Works

Internet Orders







Internet_Orders_201107

Internet_Orders_201107



Adventure Works DW



SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]

FROM [dbo].[FactInternetSales]

WHERE OrderDateKey <= 201107





Molap

Regular



-PT1S

-PT1S

-PT1S

-PT1S