Create SQL Server Analysis Services Partitions using AMO

Create a partition according to the current date. For example, if today is September 21, 2012 the T-SQL script will create a partition named Internet_Sales_2012 as shown.

 

If it were the year 2013, the partition name would be Internet_Sales_2013. The idea for this tip is to create partitions automatically using XMLA and variables. In this tip we are going to use SSIS and Visual Basic, but you can also use C# to automate the partition process in SSIS.

Create a Script Task with code to create the partitions dynamically. To start open the SSDT or BIDS and create a new SSIS project. Once you have a new project started, in the Toolbar choose Script Task and drag and drop to the design pane.

 

 

 

 

 

 Create two variables, go to the SSIS menu and choose Variables

 

 

 

 

 

 

 

Create a connection to the SSAS Server. Go to Connection Manager > New Connection Manager

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Rename the connection from "ssas.conmgr" to "ssas" in order to match with the variable that was created

 

 

 

 

 

 

 

 

 

 

 

 

 

Select Add Reference from the Script task

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

VB.NET Code

Imports Microsoft.AnalysisServices

Shared myServer As New Server()

 

Public Sub Main()

Dim olapConnection As ConnectionManager

olapConnection = Dts.Connections(CStr(Dts.Variables("Connection").Value))

Dim Server_Name As String = CStr(olapConnection.Properties("ServerName").GetValue(olapConnection))

Dim Database_Name As String = CStr(olapConnection.Properties("InitialCatalog").GetValue(olapConnection))

Dim db_name As String = CStr(Dts.Variables("Database").Value)

 

Dim objCube As Cube

Dim objMeasureGroup As MeasureGroup

Dim oPartition As Partition

Dim oDataSourceView As New DataSourceView

Dim sdate, syear As String

sdate = Format(Now,"yyyyMMdd")

syear = Format(Now,"yyyy")

myServer.Connect(Server_Name)

 Dim db As Database = myServer.Databases.GetByName(db_name)

oDataSo urceView = db.DataSourceViews("Adventure Works DW")

objCube = db.Cubes.FindByName("Adventure Works")

objMeasureGroup = objCube.MeasureGroups("Fact Internet Sales 1")

oPartition = objMeasureGroup.Partitions.Add("Internet_Sales_" + syear)

oPartition.Source = _New QueryBinding(oDataSourceView.DataSourceID, _"SELECT * FROM [dbo].[FactInternetSales]  _WHERE OrderDateKey = '" + sdate + "'")

oPartition.StorageMode = StorageMode.Molap

oPartition.ProcessingMode = ProcessingMode.Regular

oPartition.Update(UpdateOptions.ExpandFull)

oPartition.Process(ProcessType.ProcessFull)

myServer.Disconnect()

Dts.TaskResult =ScriptResults.Success

End Sub

Run the SSIS package and verify the results