How to 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





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”)

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

Dts.TaskResult =ScriptResults.Success
End Sub

Run the SSIS package and verify the results


Tags: Microsoft BI
previous post: Steps to consider before implementing CRM solution next post: Role Center in Dynamics SL 2011