Exemplos de como processar Databases, cubos, partições, etc no Analysis Services 2005

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

””””””””””””””””””””””””””””””””””””””’
‘ Add References
‘ Microsoft.AnalysisServices
‘ System.XML
””””””””””””””””””””””””””””””””””””””’

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

Public Class ScriptMain

Enum eStatus
SUCESSO
SUCESSO_PARCIAL
ERRO
End Enum
Enum eProcessingSTATUS
NECESSARIO_ATUALIZAR
NAO_ATUALIZAR
End Enum

Private STATUS As eStatus = eStatus.SUCESSO
Private ProcessingSTATUS As eProcessingSTATUS = eProcessingSTATUS.NAO_ATUALIZAR

Public Property flgSTATUS() As eStatus
Get
Return STATUS
End Get
Set(ByVal value As eStatus)
If STATUS = eStatus.SUCESSO Or STATUS = eStatus.SUCESSO_PARCIAL Then
If value = eStatus.ERRO Then
STATUS = value
End If
End If
End Set
End Property

Public Sub Reseta_ProcessingSTATUS()
ProcessingSTATUS = eProcessingSTATUS.NAO_ATUALIZAR
End Sub

Public Property flgProcessingSTATUS() As eProcessingSTATUS
Get
Return ProcessingSTATUS
Reseta_ProcessingSTATUS()
End Get
Set(ByVal value As eProcessingSTATUS)
If ProcessingSTATUS = eProcessingSTATUS.NAO_ATUALIZAR Then
If value = eProcessingSTATUS.NECESSARIO_ATUALIZAR Then
ProcessingSTATUS = value
End If
End If
End Set
End Property

Public Sub Main()

”””””””””””””””””””””””””””””””””””
‘Parametros Externos
”””””””””””””””””””””””””””””””””””
Dim strStringConnection As String
Dim strServerName As String
Dim strDatabaseName As String
Dim strCube As String
Dim strMeasureGroup As String
Dim strPartition As String

Dim SQL_datasource As String
Dim SQL_catalog As String
Dim SQL_userid As String
Dim SQL_password As String

Dim CUBO_PARTICIONADO As String

Dim datPeriodode As String
Dim datPeriodoate As String

Dim strSQL As String

Dim FATO As String
Dim COLUNAS_FATO As String

”””””””””””””””””””””””””””””””””””
‘Carrega Parametros
”””””””””””””””””””””””””””””””””””
Try
strDatabaseName = Dts.Variables(“User::olap_database”).Value.ToString
strServerName = Dts.Variables(“User::olap_datasource”).Value.ToString
strStringConnection = _
“Data Source=” & strServerName & “;” + _
“Provider=MSOLAP.3;” + _
“Integrated Security=SSPI;” + _
“Impersonation Level=Impersonate;”
strCube = Dts.Variables(“User::CUBO”).Value.ToString
strMeasureGroup = Dts.Variables(“User::MeasuresGroup”).Value.ToString
datPeriodode = Dts.Variables(“User::periodo_carga_de”).Value.ToString
datPeriodoate = Dts.Variables(“User::periodo_carga_ate”).Value.ToString
SQL_datasource = Dts.Variables(“User::SQL_datasource”).Value.ToString
SQL_catalog = Dts.Variables(“User::SQL_catalog”).Value.ToString
SQL_userid = Dts.Variables(“User::SQL_userid”).Value.ToString
SQL_password = Dts.Variables(“User::SQL_password”).Value.ToString
CUBO_PARTICIONADO = Dts.Variables(“User::CUBO_PARTICIONADO”).Value.ToString
FATO = Dts.Variables(“User::FATO”).Value.ToString

COLUNAS_FATO = Dts.Variables(“User::CUBO_Colunas”).Value.ToString

Catch ex As DtsException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “Main”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “Main”, ex.Message.ToString, “”, 0)
End Try

”””””””””””””””””””””””””””””””””””
‘Parametros Internos
”””””””””””””””””””””””””””””””””””
Dim Server As Server
Dim Database As Database
Dim DataSourceID As String
Dim MeasureGroup As MeasureGroup

”””””””””””””””””””””””””””””””””””
”””””””””””””””””””””””””””””””””””

Server = ServerConnect(strStringConnection)

Dim SQLConnectionString As String
Dim strPeriodo As String
Dim stridtCiclo As String
Dim strFilter As String
Dim amoPartition As Partition

If IsNothing(Server) = False Then
If Server.Connected = True Then
Try
Database = GetDatabase(Server, strDatabaseName)
DataSourceID = Database.DataSources(0).ID()
MeasureGroup = Database.Cubes(strCube).MeasureGroups(strMeasureGroup)

SQLConnectionString = _
“Server=” & SQL_datasource & _
“;Database=” & SQL_catalog & _
“;UID=” & SQL_userid & _
“;PWD=” & SQL_password

If CUBO_PARTICIONADO = “Yes” Then
If datPeriodode <> “” And datPeriodoate <> “” Then
strSQL = “EXEC dbo.SPR_CFV_seleciona_periodo @dat_ini='” & datPeriodode & “‘, @dat_fim='” & datPeriodoate & “‘”
Else
strSQL = “EXEC dbo.SPR_CFV_seleciona_periodo ”
End If

Dim RowSet As Data.DataRowCollection
RowSet = GetRowSetFromSQL(SQLConnectionString, strSQL)

For Each Row As Data.DataRow In RowSet
strPeriodo = Trim(Row.Item(“cvi_des_ciclo_visitacao”).ToString)
stridtCiclo = Row.Item(“cvi_idt_ciclo_visitacao”).ToString
‘ Definindo filtro
strFilter = FATO & “.cvi_idt_ciclo_visitacao = ” & stridtCiclo
strPartition = “Ciclo ” & Replace(strPeriodo, “/”, “”)

If MeasureGroup.Partitions.ContainsName(strPartition) = False Then
Me.AddPartition(MeasureGroup, strPartition, DataSourceID, PartitionQueryString + strFilter)
End If

amoPartition = MeasureGroup.Partitions.FindByName(strPartition)

amoPartition.AggregationPrefix = “CFV_” & stridtCiclo

””””””””””””””””””””””””””””””””””””””””
‘Atualiza e Processa Partição
””””””””””””””””””””””””””””””””””””””””
Dts.Events.FireProgress(“Processando – ” + strPartition, 0, 0, 100, “Main”, True)
Me.UpdateObjects(amoPartition, UpdateOptions.AlterDependents)
Me.ProcessPartitions(amoPartition, ProcessType.ProcessFull)
Dts.Events.FireProgress(“Processando – ” + strPartition, 100, 0, 100, “Main”, True)
””””””””””””””””””””””””””””””””””””””””

Next

Else
Dts.Events.FireProgress(“Processando – MeasureGroup FULL”, 0, 0, 100, “Main”, True)
””””””””””””””””””””””””””””””””””””””””
‘Apenas Processa Partição
””””””””””””””””””””””””””””””””””””””””
Me.ProcessPartitions(MeasureGroup, ProcessType.ProcessFull)
””””””””””””””””””””””””””””””””””””””””
Dts.Events.FireProgress(“Processando – MeasureGroup FULL”, 100, 0, 100, “Main”, True)
End If

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “Main”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “Main”, ex.Message.ToString, “”, 0)

Finally
‘Desconecta OLAP
Try
Server.Disconnect()
Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “Main:Disconnect”, ex.Message.ToString, “”, 0)
End Try

End Try
Else
flgSTATUS = eStatus.ERRO
End If
Else
flgSTATUS = eStatus.ERRO
End If

”””””””””””””””””””””””””””””””””””
‘Verifica Status
”””””””””””””””””””””””””””””””””””
If flgSTATUS = eStatus.SUCESSO Or flgSTATUS = eStatus.SUCESSO_PARCIAL Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

End Sub

Private Function ServerConnect(ByVal strStringConnection As String) As Server
Const methodCaption As String = “ServerConnect method”
Dim svr As Server = New Server()
Try
svr.Connect(strStringConnection)

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
svr = Nothing
Dts.Events.FireError(1, “ServerConnect”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
svr = Nothing
Dts.Events.FireError(1, “ServerConnect”, ex.Message.ToString, “”, 0)
End Try

Return svr

End Function

Private Function GetDatabase(ByVal Server As Server, ByVal strDatabaseName As String) As Database
Dim Database As Database
Try
Database = Server.Databases(strDatabaseName)

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “GetDatabase”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “GetDatabase”, ex.Message.ToString, “”, 0)
End Try
Return Database
End Function

Private Sub UpdateObjects(ByRef MeasureGroup As MeasureGroup, Optional ByVal UpdateOptions As UpdateOptions = UpdateOptions.AlterDependents)
Try
If flgProcessingSTATUS = eProcessingSTATUS.NECESSARIO_ATUALIZAR Then
MeasureGroup.Update(UpdateOptions)
End If

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “UpdateObjects”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “UpdateObjects”, ex.Message.ToString, “”, 0)
End Try
End Sub
Private Sub UpdateObjects(ByRef Partition As Partition, Optional ByVal UpdateOptions As UpdateOptions = UpdateOptions.AlterDependents)
Try
If flgProcessingSTATUS = eProcessingSTATUS.NECESSARIO_ATUALIZAR Then
Partition.Update(UpdateOptions)
End If

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “UpdateObjects”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “UpdateObjects”, ex.Message.ToString, “”, 0)
End Try
End Sub

Private Function ProcessDatabase(ByVal Database As Database, ByVal ProcessType As ProcessType) As Database
Try
Database.Process(ProcessType)
Return Database

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessDatabase”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessDatabase”, ex.Message.ToString, “”, 0)
End Try
End Function

Private Sub ProcessDimensions(ByVal Dimension As Dimension, ByVal ProcessType As ProcessType)
Try
Dimension.Process(ProcessType)

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessDimensions”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessDimensions”, ex.Message.ToString, “”, 0)

End Try
End Sub
Private Sub ProcessDimensions(ByVal Database As Database, ByVal ProcessType As ProcessType)
Try
For Each Dimension As Dimension In Database.Dimensions
Dimension.Process(ProcessType)
Next

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessDimensions”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessDimensions”, ex.Message.ToString, “”, 0)
End Try
End Sub

Private Sub ProcessCubes(ByVal Cube As Cube, ByVal ProcessType As ProcessType)
Try
Cube.Process(ProcessType)

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessCubes”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessCubes”, ex.Message.ToString, “”, 0)
End Try
End Sub
Private Sub ProcessCubes(ByVal Database As Database, ByVal ProcessType As ProcessType)
Try
For Each Cube As Cube In Database.Cubes
Cube.Process(ProcessType)
Next

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessCubes”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessCubes”, ex.Message.ToString, “”, 0)
End Try
End Sub

Private Sub ProcessPartitions(ByVal Partition As Partition, ByVal ProcessType As ProcessType)
Try
Partition.Process(ProcessType)

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessPartitions”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessPartitions”, ex.Message.ToString, “”, 0)

End Try
End Sub
Private Sub ProcessPartitions(ByVal MeasureGroups As MeasureGroup, ByVal ProcessType As ProcessType)
Try
For Each Partition As Partition In MeasureGroups.Partitions
Partition.Process(ProcessType)
Next

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessPartitions”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “ProcessPartitions”, ex.Message.ToString, “”, 0)

End Try
End Sub

Private Sub AddPartition _
( _
ByRef MeasureGroup As MeasureGroup, _
ByVal NewPartiotionName As String, _
ByVal DataSourceID As String, _
ByVal QueryString As String _
)
Try
‘Se MeasureGroup for particionado
If MeasureGroup.Partitions.Count > 0 Then
‘Se MeasureGroup Não conter partição com este nome
If MeasureGroup.Partitions.ContainsName(NewPartiotionName) = False Then
Dim NewPartition As New Partition
‘Copia propriedades de partição qualquer
NewPartition = MeasureGroup.Partitions(0).Clone()
NewPartition.ID = NewPartiotionName
NewPartition.Name = NewPartiotionName
NewPartition.Source = New QueryBinding(DataSourceID, QueryString)
MeasureGroup.Partitions.Add(NewPartition)

””””””””””””””””””””””””””””””””
”UPDATE
flgProcessingSTATUS = eProcessingSTATUS.NECESSARIO_ATUALIZAR
””””””””””””””””””””””””””””””””
Else
flgSTATUS = eStatus.SUCESSO_PARCIAL
Dts.Events.FireInformation(1, “AddPartition”, “Partition (” + NewPartiotionName + “) já existe”, “”, 0, True)
End If
Else
flgSTATUS = eStatus.SUCESSO_PARCIAL
End If

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “AddPartition”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “AddPartition”, ex.Message.ToString, “”, 0)

End Try
End Sub

Private Sub MergePartitions _
( _
ByRef MeasureGroup As MeasureGroup, _
ByVal PartitionToMerge_Destiny As Partition, _
ByVal PartitionToMerge_Source As Partition _
)
‘Dim PartitionsToMerge As PartitionCollection
Dim PartitionsToMerge As New System.Collections.ArrayList

Try
PartitionsToMerge.Add(PartitionToMerge_Source)
PartitionToMerge_Destiny.Merge(PartitionsToMerge)
””””””””””””””””””””””””””””””””
”UPDATE
flgProcessingSTATUS = eProcessingSTATUS.NECESSARIO_ATUALIZAR
””””””””””””””””””””””””””””””””

Catch ex As AmoException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “MergePartitions”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “MergePartitions”, ex.Message.ToString, “”, 0)

End Try
End Sub

Private Function GetRowSetFromSQL(ByVal SQLConnectionString As String, ByVal QueryString As String) As Data.DataRowCollection
Dim SQLConnection As New SqlClient.SqlConnection
Dim DataSet As New DataSet

SQLConnection = New SqlClient.SqlConnection(SQLConnectionString)

Try
SQLConnection.Open()
Dim SQLDataAdapter As New SqlClient.SqlDataAdapter(QueryString, SQLConnection)
SQLDataAdapter.Fill(DataSet)

Catch ex As SqlClient.SqlException
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “GetRowSetFromSQL”, ex.Message.ToString, “”, 0)

Catch ex As Exception
flgSTATUS = eStatus.ERRO
Dts.Events.FireError(1, “GetRowSetFromSQL”, ex.Message.ToString, “”, 0)

Finally
SQLConnection.Close()
End Try

Return DataSet.Tables(0).Rows

End Function

End Class

Anúncios