Process SSAS Dimensions using SSIS

for my own reference:

populate DimensionNames var from a exec sql task:

Select CAST(REPLACE(STUFF(
(SELECT ',' + a.tablenamecube AS [text()]
from config.BusinessprocessTables a
WHERE TableType ='D' AND BusinessProcess = 'Metrics' for xml PATH('')),1,1,'' ),' ','') AS VARCHAR(1000)) AS DimensionNames


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

Public Class ScriptMain
Public Sub Main()
Dim strDimensionNames As String = Dts.Variables("DimensionNames").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager
oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dts.Events.FireInformation(-1, "Dimensions to Process:", strDimensionNames, "", -1, True)
Dts.Log("Dimensions to Process:" + strDimensionNames, 0, bt)

Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull 'ProcessUpdate

If oDB Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else
Dim oDim As Dimension

oServer.CaptureXml() = True ' Start capturing XML.
For Each oDim In oDB.Dimensions
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If (oDim.MiningModel Is Nothing) Then
If InStr(1, strDimensionNames, oDim.Name.Replace(" ", "")) > 1 Then
oDim.Process(ProcessType)
End If
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML

' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult

Dts.Events.FireInformation(-1, "Process Dimensions script", "Processing Dimensions ow", "", -1, True)
Dts.Log("Processing Dimensions Now", 0, bt)
oResults = oServer.ExecuteCaptureLog(True, True, True)

Dim oMessage As XmlaMessage

'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Errors while processing Dimensions:" + oMessage.Description, 0, bt)
'The processing failed
Dts.Events.FireInformation(-1, "XMLA Errors while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warnings while processing Dimensions:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warnings while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done

If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS

No comments yet.

Leave a comment

You must be logged in to post a comment.