AMO - Administrar por código SSAS

Tuve la necesidad de crear variables calculadas en SSAS 2000 mediante y hasta el momento no he encontrado cómo. Si alguien lo sabe ahi les encargo.

Pero en AS2005 la vida cambia. Es posible mediante código crear las famosas calculadas. Medianamente es sencillo, hasta encuentras una guía.

Buscando una luz encontre un post:
Calculated Measures using AMO
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=478534&SiteID=1

Y yo intente mejorar un poco esto pensando en algo más reutilizable. No esta terminado, pero tendrán una idea más clara.

Tendrán que agregar también como referencias al proyecto:
Microsoft.AnalysisServices
Microsoft.ExceptionMessageBox


Imports System
Imports System.Collections
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Globalization
Imports System.Reflection
Imports System.Text
Imports System.Windows.Forms

Imports Microsoft.SqlServer.MessageBox
Imports Microsoft.AnalysisServices


Public Class frmCalculatedsMembers


#Region "Fields"

'- AMO Server Variable
Private AMOServer As Server


#End Region



#Region "Methods"


Private Sub Logger(ByVal Msg As System.String, ByVal Source As System.String)
Try
Me.txtMdxScript.Text = System.DateTime.Now.ToString & " [" & Source & "] " & Msg & System.Environment.NewLine & Me.txtMdxScript.Text
Catch ex As Exception

End Try
End Sub


Private Sub FinishASSession()

If (Not Me.AMOServer Is Nothing) AndAlso (Me.AMOServer.Connected) Then
Me.AMOServer.Dispose()

Try
Me.AMOServer.Disconnect()
Catch ex As Exception

End Try

End If

End Sub


Private Sub btnEnd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnd.Click

Me.FinishASSession()

End

End Sub


Private Sub frmCalculatedsMembers_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

'- loading AS Servers
Me.cmbServers.Items.Add("TERMXDESASGL01\SGLDESARROLLO2K5")
'- Indicating the first one
Me.cmbServers.SelectedIndex = 0

End Sub


Private Function ConnectToASServer() As System.Boolean

Try

If Me.AMOServer Is Nothing Then
'- create
Me.AMOServer = New Server()
Else
'- disconnect
Me.AMOServer.Disconnect()
End If

Me.AMOServer.Connect(CType(Me.cmbServers.Items.Item(Me.cmbServers.SelectedIndex), System.String))

Me.Logger("Conectado al servidor AS [" & CType(Me.cmbServers.Items.Item(Me.cmbServers.SelectedIndex), System.String) & "]", "ConnectToASServer")

Return True

Catch ex As Exception

Me.Logger(ex.Message, ex.TargetSite.Name)

Me.AMOServer = Nothing

Return False

End Try

End Function


Private Function LoadCubesDatabases() As System.Boolean

Try
'- check por si las moscas
If Me.AMOServer Is Nothing Then Return False

Me.cmbCubesDatabases.Items.Clear()

For Each AMODB As Database In Me.AMOServer.Databases
Me.cmbCubesDatabases.Items.Add(AMODB.ID)
Next

Me.cmbCubesDatabases.SelectedIndex = 0

Me.Logger("CubesDatabases Loaded", "LoadCubesDatabases")

Return True

Catch ex As Exception
Me.Logger(ex.Message, ex.TargetSite.Name)
Return False
End Try

End Function


Private Function LoadCubes() As System.Boolean

Try
Dim AMODB = New Database

Me.cmbCubes.Items.Clear()

'Dim AMOCube = New Cube
'AMODB = Me.AMOServer.Databases(0)
'AMOCube = AMODB.Cubes(0)
'AMODB.cubes(0) = Nothing

AMODB = Me.AMOServer.Databases(Me.cmbCubesDatabases.Items.Item(Me.cmbCubesDatabases.SelectedIndex))

For Each AMOCube As Cube In AMODB.Cubes
Me.cmbCubes.Items.Add(AMOCube.ID)
Next

Me.cmbCubes.SelectedIndex = 0

Me.Logger("Cubes Loaded", "LoadCubes")

Return True

Catch ex As Exception

Me.Logger(ex.Message, ex.TargetSite.Name)
Return False

End Try

End Function


Private Sub cmbCubesDatabases_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCubesDatabases.SelectedIndexChanged

Try
If Me.LoadCubes() Then
'- ok
Else
'- nok
Me.Logger("No se pudo cargar los Cubes", "cmbCubesDatabases_SelectedIndexChanged:LoadCubes")
End If
Catch ex As Exception
Me.Logger(ex.Message, ex.TargetSite.Name)
End Try

End Sub


Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnect.Click
Try
If Me.ConnectToASServer() Then

' ok, now discover CubesDatabases
If Me.LoadCubesDatabases() Then
'- ok
Else
'- nok
Me.Logger("No se pudo cargar las DB-Cubes", "btnConnect_Click:LoadCubesDatabases")
End If

Else
' log the event
Me.Logger("No se pudo conectar al servidor AS [" & CType(Me.cmbServers.Items.Item(Me.cmbServers.SelectedIndex), System.String) & "]", "btnConnect_Click:ConnectToASServer")
End If
Catch ex As Exception
Me.Logger(ex.Message, ex.TargetSite.Name)
End Try
End Sub


Private Function ExecuteMDXScript(ByVal MdxScript As System.String) As System.Boolean

'CREATE
'MEMBER CURRENTCUBE.[MEASURES].[Paradas SETUP_Min_New]
'AS SUM({[DimDemoras].[Dim Demo Clase Descripcion].&[0]}, [Measures].[Fact Demo Duracion]) / 60,
'FORMAT_STRING = "#,#",
'VISIBLE = 1;

Try
Dim sb As New System.Text.StringBuilder()
sb.Append(MdxScript)
If Me.WriteMDXScript(sb) Then
Return True
Else
Return False
End If

Catch ex As Exception
Return False
End Try

End Function


Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click
Try
If Me.txtMdxScript.Text.Length > 0 Then
If Me.ExecuteMDXScript(Me.txtMdxScript.Text) Then
'- ok
Else
'- nok
Me.Logger("No se pudo ejecutar la DMX", "btnRun_Click:ExecuteMDXScript")
End If
Else
Me.Logger("No mames!! ponle una pinche consulta w!!! si no es magia esto!!!", "btnRun_Click")
End If

Catch ex As Exception
Me.Logger(ex.Message, ex.TargetSite.Name)
End Try
End Sub


Private Function ShowMDXScript() As System.String
Try
Dim AMODB = New Database()
Dim AMOCube = New Cube()
Dim AMOMdxScript As New MdxScript()

AMODB = CType(Me.AMOServer.Databases(Me.cmbCubesDatabases.Items.Item(Me.cmbCubesDatabases.SelectedIndex)), Database)
AMOCube = CType(AMODB.Cubes(Me.cmbCubes.Items.Item(Me.cmbCubes.SelectedIndex)), Cube)

'AMOMdxScript = AMOCube.MdxScripts(0)
'Microsoft.AnalysisServices.Database()

Dim sb As New System.Text.StringBuilder()

For Each Mdx As MdxScript In AMOCube.MdxScripts
sb.Append(Mdx.Name)
Next

Return "Nada"
Catch ex As Exception
Return "Nada"
End Try
End Function


Private Function WriteMDXScript(ByVal sb As System.Text.StringBuilder) As System.Boolean
Try
Dim AMODB = New Database()
Dim AMOCube = New Cube()
Dim AMOMdxScript As New MdxScript()

AMODB = CType(Me.AMOServer.Databases(Me.cmbCubesDatabases.Items.Item(Me.cmbCubesDatabases.SelectedIndex)), Database)
AMOCube = CType(AMODB.Cubes(Me.cmbCubes.Items.Item(Me.cmbCubes.SelectedIndex)), Cube)

AMOMdxScript = AMOCube.MdxScripts(0)
AMOMdxScript.Commands(0).Text = AMOMdxScript.Commands(0).Text & System.Environment.NewLine & sb.ToString

AMOMdxScript.Update()

AMOCube.Update()
AMOCube.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)

AMOMdxScript.Dispose()
AMOCube.Dispose()
AMODB.Dispose()

Return True
Catch ex As Exception
Return False
End Try
End Function


Private Sub btnShowMdxScript_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowMdxScript.Click
Me.txtMdxScript.Clear()
Me.txtMdxScript.Text = Me.ShowMDXScript()
End Sub


Private Sub cmbCubes_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbCubes.SelectedIndexChanged
Me.txtMdxScript.Clear()
End Sub


#End Region


End Class



Espero les sirva un poco.