ssas

Generating large numbers of SSAS partitions using Excel

Link to Chris Webb’s BI Blog page:

http://cwebbbi.wordpress.com/2008/08/27/generating-large-numbers-of-partitions-using-excel/

Tags: , ,

Friday, March 2nd, 2012 ssas No Comments

Process SSAS Measure Groups (Fact tables) using SSIS

for my own reference:

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

Public Class ScriptMain
Public Sub Main()
Dim strFactNames As String = Dts.Variables("FactNames").Value.ToString
Dim strCubeName As String = Dts.Variables("CubeName").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, "Fact Tables to process: ", strFactNames, "", -1, True)
Dts.Log("Fact Tables to process:" + strFactNames, 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 oCube As Cube = oDB.Cubes.FindByName(strCubeName)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull

If oDB Is Nothing Or oCube Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else

Dim oFact As MeasureGroup

oServer.CaptureXml() = True ' Start capturing XML.
For Each oFact In oCube.MeasureGroups
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If InStr(1, strFactNames, oFact.Name.Replace(" ", "")) > 1 Then
oFact.Process(ProcessType)
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.Log("Processing Facts Now", 0, bt)
Dts.Events.FireInformation(-1, "Process Facts Script: ", "Processing Facts Now", "", -1, True)
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 Error while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Error while processing facts:", oMessage.Description, "", -1, True)
'The processing failed
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warning while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warning while processing facts:", 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 ssas, SSIS No Comments