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

 

Tags: Microsoft BI | Comments Off

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

Comments are closed.

Archives

2016
2015
2014
2013
2012
Congruent Facebook Twitter Slideshare