Cube Processing Using SSIS

This has been a long pending post from me, always thought of penning down my thought but somehow just misses out. Today would deal with it finally yaa MAYBE NOT, lets see how it goes. Pardon the Spellings though .

Now this topic is something which i don’t find as a readymade code available on googling maybe i will try to solve that issue and give the code as well today so that if at all someone like me wants the code, this post can be of quick assistance. So the topic for today is Cube Processing, you would think big deal not so complex task just do a full process of cubes each time, but it becomes a tad complex when we have huge cubes with partitions each partition needs to be create dynamically on the basis of some relational table column EX:

a) Consider a huge Retail Cube, which has 5 million transactions to be processed each day, think of how many monthly yearly, so partition the cube on the basis of Months or even dates.

b) Consider a huge cube for several countries, this would require us to create partition on the basis of COUNTRY identifier

So let’s begin, the SSIS task would be a simple SCRIPT Task, you can have your error handling in place am not going to define the same here on this post, will concentrate on the just the SCRIPT Task

I will start from what goes inside main, In this example am taking the example of partitioning via monthly periods, it can easily be changed to daily periods as well. I will start with sample where partition Name and Cube Name are passed, this can easily be modified for not passing the cube name and processing all cubes in catalouge

Things to be passed as variables, NOTE: These can be easily assigned inside a script task, by using a SQL configuration.

Server Name, Cube Catalogue Name, Period, CubeName, ConnectionString

————————————————————————————————————————————–

‘ Microsoft SQL Server Integration Services Script Task
‘ Write scripts using Microsoft Visual Basic 2008.
‘ The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

<System.AddIn.AddIn(“ScriptMain”, Version:=”1.0?, Publisher:=””, Description:=””)> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
‘ The execution engine calls this method when the task executes.
‘ To access the object model, use the Dts property. Connections, variables, events,
‘ and logging features are available as members of the Dts property as shown in the following examples.

‘ To reference a variable, call Dts.Variables(“MyCaseSensitiveVariableName”).Value
‘ To post a log entry, call Dts.Log(“This is my log text”, 999, Nothing)
‘ To fire an event, call Dts.Events.FireInformation(99, “test”, “hit the help message”, “”, 0, True)

‘ To use the connections collection use something like the following:
‘ ConnectionManager cm = Dts.Connections.Add(“OLEDB”)
‘ cm.ConnectionString = “Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;”

‘ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

‘ To open Help, press F1.

Dim oServer As New Microsoft.AnalysisServices.Server
Dim oDB As Database
Public Sub Main()

‘ Add your code here

Dim oConnection As ConnectionManager
Dim sCube As String, sMesGroupNm As String, sPartName As String

Dts.Connections(“Connections”).ConnectionString = Dts.Variables(“ConStr”).Value.ToString

sCube = Dts.Variables(“CubeName”).Value.ToString
sMesGroupNm = Dts.Variables(“MesGroupNm”).Value.ToString
sPartName = Dts.Variables(“PartName”).Value.ToString

oConnection = Dts.Connections(“Connections”)
‘Assing Server Name
Dim sServer As String = CStr(oConnection.Properties(“ServerName”).GetValue(oConnection))
‘Assing DB Name
Dim sDatabase As String = CStr(oConnection.Properties(“InitialCatalog”).GetValue(oConnection))
Try
‘ connect to the server
oServer.Connect(sServer)
‘Assing Catalouge Name
oDB = oServer.Databases.FindByName(sDatabase)

‘Strat Calling Subfucntions
CheckndProcessPartitions(sCube, sMesGroupNm, sPartName) ‘This functions calls the Creation for Valid Paritions

Catch ex As Exception

End Try

Dts.TaskResult = ScriptResults.Success
End Sub
Public Sub CheckndProcessPartitions(ByVal sCube As String, ByVal MesGrpName As String, ByVal partName As String)

Dim subCube As Cube = oDB.Cubes.FindByName(sCube)
Dim sPeriod As String
sPeriod = Dts.Variables(“Period”).Value.ToString
Try
If (Not PartitionExists(sCube, MesGrpName, partName)) Then
CreateNewPartition(sCube, sPeriod)
End If

‘Call the processing Code
ProcessPartitions(sCube, MesGrpName, partName)
Catch ex As Exception
Throw ex
End Try

End Sub

Public Function PartitionExists(ByVal sCube As String, ByVal sMeasureGroup As String, ByVal sPartition As String) As Boolean

Dim oCube As Cube = oDB.Cubes.FindByName(sCube)
If oCube Is Nothing Then
Return False
End If

Dim oMeasureGroup As MeasureGroup = oCube.MeasureGroups.FindByName(sMeasureGroup)
If oMeasureGroup Is Nothing Then
Return False
End If

Dim oPartition As Partition = oMeasureGroup.Partitions.FindByName(sPartition)
Return Not oPartition Is Nothing

End Function

Public Function CreateNewPartition(ByVal sCube As String, ByVal PartitionDate As String) As Boolean

Dim oQueryBinding As QueryBinding
Dim oCube As Cube = oDB.Cubes.FindByName(sCube)
If oCube Is Nothing Then
Return False
End If

Dim aPartitionNames As New ArrayList()

Try
For Each oMeasureGroup As MeasureGroup In oCube.MeasureGroups
If oMeasureGroup Is Nothing Then
Return False
End If
Dim DefaultPartitionName As String
DefaultPartitionName = oMeasureGroup.Name
Dim sNewPartitionName As String
sNewPartitionName = String.Empty

sNewPartitionName = DefaultPartitionName + “_” + PartitionDate
Dim oPartition As Partition = oMeasureGroup.Partitions(oMeasureGroup.Partitions.Count – 1)
oPartition = oMeasureGroup.Partitions.FindByName(sNewPartitionName)
Dim oPartitionNew As Partition

If oPartition Is Nothing Then
oPartition = oMeasureGroup.Partitions(oMeasureGroup.Partitions.Count – 1)
oPartitionNew = oPartition.Clone
oPartitionNew.ID = sNewPartitionName
oPartitionNew.Name = sNewPartitionName
oQueryBinding = oPartitionNew.Source
End If

If oQueryBinding Is Nothing Then
Return True ‘No query, no partition
End If

oQueryBinding.QueryDefinition = GetNewQuery(oQueryBinding.QueryDefinition, PartitionDate)
oMeasureGroup.Partitions.Add(oPartitionNew)
oPartitionNew.Update()
?
Next
Catch ex As Exception
Return 0
Throw ex
End Try
End Function

Public Function GetNewQuery(ByVal SourceQuery As String, ByVal DatePartition As String) As String
Dim mainQ As String = String.Empty
Dim newQ As String = String.Empty
Dim newCond As String = String.Empty
Dim lastIndex As Integer = SourceQuery.ToUpper().LastIndexOf(“WHERE”)

mainQ = SourceQuery.Substring(0, lastIndex)
newCond = ” WHERE ColName = ” + DatePartition
newQ = mainQ + newCond
Return newQ

End Function
Public Sub ProcessPartitions(ByVal sCube As String, ByVal MesGrpName As String, ByVal spartName As String)
Try
ProcessPartition(sCube, MesGrpName, spartName)
Catch ex As Exception
Throw ex
End Try

End Sub

Public Sub ProcessPartition(ByVal sCube As String, ByVal sMeasureGroup As String, ByVal sPartition As String)

Dim oCube As Cube = oDB.Cubes.FindByName(sCube)

If oCube Is Nothing Then
Return
End If

Dim oMeasureGroup As MeasureGroup = oCube.MeasureGroups.FindByName(sMeasureGroup)
If oMeasureGroup Is Nothing Then
Return
End If
Dim oPartition As Partition = oMeasureGroup.Partitions.FindByName(sPartition)
?
For Each oDim In oDB.Dimensions
oDim.Process(ProcessType.ProcessUpdate) ‘ Process all dimensions
Next
oPartition.Process(ProcessType.ProcessFull)

End Sub

End Class

—————————————————————————————————–

Hello world!

Welcome to WordPress.com! This is your very first post. Click the Edit link to modify or delete it, or start a new post. If you like, use this post to tell readers why you started this blog and what you plan to do with it.

Happy blogging!