One of the really cool things about SQL Server Analysis Services 2005 is the ability to examine and dynamically manage its objects (cubes, measure groups, dimensions etc). Using the Analysis Management Objects (AMO) model you can interact with Analysis Services objects - manage and manipulate them using simple code. I currently use this method to manage very large OLAP cubes in the organisation I work for. Combined with the functionalities provided by SQL Server Integration Services (SSIS) I have found you can be extremely creative in the way you administer your OLAP solutions.

In this article I will show you how to extract definitions and states of your OLAP objects using SSIS with AMO, and place this information in a relational database. We will be extracting information about Dimensions and Partitions, and in future articles I hope to show you how you can use this information in your administrative tasks.

I started by creating the relational tables that will hold the OLAP objects - so for partitions:

CREATE TABLE [dbo].[my_partitions](
 [partition_Id] [varchar](100) NULL,
 [partition_name] [varchar](100) NULL,
 [cube_Id] [varchar](100) NULL,
 [cube_name] [varchar](100) NULL,
 [measure_group_id] [varchar](100) NULL,
 [measure_group_name] [varchar](100) NULL,
 [process_state] [int] )

As you can see I am collecting the partitions Id, name, the cube it belongs to, the measure group it belongs to and the processed state of the partition. I use this method to regularly check for any changes that have been made to the OLAP environment I manage and to identify objects for processing.

In SSIS create two Connection Managers in your package one for the relational database and one for the Analysis Services database. Create 2 Variables one for the Analysis Services Connection (type String) and one for the Analysis Services database name (type String). You can get the Connection variable's value by simply right clicking the connection to Analysis Services in the SSIS connection manager, select rename and copy the text to the variable value. The Database variable should hold the name of your Analysis Services Database.

Pull in a Script Task and pass the two variables to it and then go into the Design Script area and you are ready to write some code. The first thing is to make sure you have a reference to AMO. You can do this by right clicking References in the Project Explorer and selecting Add Reference. Look for Microsoft.AnalysisServices component.

You then need to import this AMO into your script:

Imports Microsoft.AnalysisServices - Plus the stuff for the relational database - in this case SQL Server:

Imports System.Data.SqlClient - In the ScriptMain class create a shared Analysis Services Server instance:

Public Class ScriptMain

Shared oServer As New Server()

Then in Sub Main() build the connection to Analysis Services using your SSIS Connection variable:

Dim oConnection As ConnectionManager
 oConnection = Dts.Connections(CStr(Dts.Variables("Connection").Value))
 Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
 Dim sDatabase As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Define the Analysis Services database you will be querying using the other SSIS variable:

Dim olap_db_name As String = CStr(Dts.Variables("Database").Value)

Now connect to the server and database

oServer.Connect(sServer)
Dim db As Database = oServer.Databases.GetByName(olap_db_name)

With AMO you can now create instances of Analysis Services objects and use them. Remember to create string or integer variables to hold the values you are gathering. In my case I placed the whole activity in a loop statement to tranvesrse the database/cube hierachy using the code below:

Dim cube As Cube

For Each cube In db.Cubes
 olap_cube_name = CStr(cube.Name)
 olap_cube_id = CStr(cube.ID)
 'Now get measure groups and partitions
 Dim mg As MeasureGroup
 Dim part As Partition
 For Each mg In cube.MeasureGroups
 measure_group_name = CStr(mg.Name)
 measure_group_Id = CStr(mg.ID)
 For Each part In mg.Partitions
 partition_Id = part.ID
 partition_name = part.Name
 If part.State = AnalysisState.Processed Then
 process_Id = 1
 Else
 process_Id = 0
 End If

Dim cnn As SqlConnection = New SqlConnection("data source=MUKSQL094\DW;initial catalog=OLAP_Processing;Integrated Security=SSPI")
Dim cmd As SqlCommand = New SqlCommand()
Dim sql As String

sql = "INSERT INTO [OLAP_Processing].[dbo].[ my_partitions] " & _
"([partition_Id],[partition_name],[cube_Id] " & _
",[cube_name],[measure_group_id],[measure_group_name]" & _
",[process_state]) " & _
" VALUES (" & "'" & partition_Id & "', '" & partition_name & "', '" & _
olap_cube_id & "', '" & olap_cube_name & "', '" & measure_group_Id & "', '" measure_group_name & "', '" & process_Id & "')"
 cnn.Open()
 cmd.Connection = cnn
 cmd.CommandType = CommandType.Text
 cmd.CommandText = sql
 cmd.ExecuteNonQuery()

'Release resources for next loop
 cnn.Close()
 cnn.Dispose()
 cmd.Dispose()
 Next Next
 Next

Dimensions are not part of the cube hierarchy we need a separate loop for them. Here it is:

Dim dimension As Dimension

For Each dimension In db.Dimensions
 dimension_name = CStr(dimension.Name)
 dimension_id = CStr(dimension.ID)
 'Get the dimensions current state
 If dimension.State = AnalysisState.Processed Then
 dim_processed = 1 
 Else
 dim_processed = 0
 End If

....SQL Insert into db Stuff here
 Next

You will now have a set of tables that hold the structures and processed state of your Analysis Services objects. As mentioned above I use a similar method to identify partitions for processing each day. The way I do it is using a reference table in my data warehouse that matches 'new' sales data dates with partitions in my OLAP reference table created by the method above and the tells Analysis Services to process those partition. That way I only process partitions that have new data each day.