Cube Documentation with Analysis Management Objects (AMO)


Analysis Management Objects (AMO) is the complete library of programmatically accessed objects that enables an application to manage a running instance of Microsoft SQL Server Analysis Services.

With this exercise I want to show you how we can document a cube by using a C# script to grab the metadata objects from Analysis Services using Analysis Management Objects (AMO).

Great Documentation on this at http://msdn.microsoft.com/en-us/library/ms124924.aspx

The Key to starting off the AMO Script in C# is that we need the Assemblies that call the AMO Classes. So we first want to add the following to our c# code:using Microsoft.AnalysisServices
using Microsoft.AnalysisServices.Hosting;

The next we do is add the references to the project so we need to look for the Microsoft.AnalysisServices and Microsoft.AnalysisServices.Adomdclient assemblies.

In This Project we loop through the the Dimensions and the Measures to get source information. In other words we want to know what the Source columns are associated with Dimension Attributes and Measures in a cube. This is one thing that is so powerful about the AMO classes that could not be retrieved by the DMV queries.

We first want to connect to the Server:
string ConnStr;
string OLAPServerName;
OLAPServerName =“LOCALHOST”;
ConnStr =“Provider=MSOLAP;Data Source=” + OLAPServerName + “;”;
Server OLAPServer = newServer();
OLAPServer.Connect(ConnStr);

To loop through all the databases in an Analysis Services instance:
foreach (Database OLAPDatabase in OLAPServer.Databases)
{

}

To loop through all the Cubes in an Analysis Services instance:
foreach(Cube OLAPCubex in OLAPDatabase.Cubes)
{

}

To loop through all the Dimension in a Cube:
foreach(CubeDimension OLAPDimension in OLAPCubex.Dimensions)
{

}

To loop through all the Attributes in a Dimension:
foreach(CubeAttribute OLAPDimAttribute in OLAPDimension.Attributes)
{

}

To Loop through all the KeyColumns of an Attribute:
for(int i = 0; i < OLAPDimAttribute.Attribute.KeyColumns.Count; i++)
{

}

To loop through all the MeasureGroups in a Cube:
foreach(MeasureGroup OLAPMeasureGroup in OLAPCubex.MeasureGroups)
{

}

To loop through all the Measures in a MeasureGroup:
foreach(Measure OLAPMeasure in OLAPMeasureGroup.Measures)
{

}

The one thing that can be very beneficial for documentation is the Cube’s Data Source Views (DSV) and all of their content which can be accessed by the DMV queries. We can loop through the views and get all the fact and dimensions as well as all the fields in each. This is how we can achieve that using AMO.

foreach (DataSourceView ds in OLAPDatabase.DataSourceViews)
{
  for (int i=0; i < ds.Schema.Tables.Count; i++)
  {
    for (int j = 0; j < ds.Schema.Tables[i].Columns.Count; j++)
    {
      MessageBox.Show(ds.Schema.Tables[i].Columns[j].ColumnName.ToString());
    }
  }
}

Each one of these loops is dependent on each other and once we have all the information we can create tables and stored procedures to insert the data into tables for reporting. The following is a script that was written to give you an idea of some cube documentation. Not all the code is available because it was done for work.


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.Hosting;
using System.Data.SqlClient;

namespace Demo.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            string ConnStr;
            string OLAPServerName;

            OLAPServerName = "LOCALHOST";
            ConnStr = "Provider=MSOLAP;Data Source=" + OLAPServerName + ";";

            Server OLAPServer = new Server();
            OLAPServer.Connect(ConnStr);

            foreach (Database OLAPDatabase in OLAPServer.Databases)
            {
               foreach (DataSourceView ds in OLAPDatabase.DataSourceViews)
                {
                 for (int i=0; i < ds.Schema.Tables.Count; i++)
                  {
                   for (int j = 0; j < ds.Schema.Tables[i].Columns.Count; j++)
                    {
                      MessageBox.Show(ds.Schema.Tables[i].Columns[j].ColumnName.ToString());
                    }
                  }
                }

                foreach (Cube OLAPCubex in OLAPDatabase.Cubes)
                {
                    foreach (CubeDimension OLAPDimension in OLAPCubex.Dimensions)
                    {
                        foreach (CubeAttribute OLAPDimAttribute in OLAPDimension.Attributes)
                        {
                            for (int i = 0; i < OLAPDimAttribute.Attribute.KeyColumns.Count; i++)
                            {
                                string S1 = OLAPDimAttribute.Attribute.KeyColumns[i].Source.ToString();
                                string S2 = OLAPDimAttribute.Attribute.KeyColumns[i].Source.ToString();
                                string STable = Source1.Substring(0, Source1.IndexOf("."));
                                string SColumn = Source2.Substring(Source2.IndexOf(".") + 1);

                                SqlConnection conn = new SqlConnection("Data Source=LOCALHOST;Initial Catalog=Metadata;Integrated Security=SSPI;");
                                conn.Open();

                                SqlCommand cmd = new SqlCommand("SourceDimensionAttribute", conn);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Add(new SqlParameter("@Dim", OLAPDimension.Name));
                                cmd.Parameters.Add(new SqlParameter("@Att", OLAPDimAttribute.Attribute.Name));
                                cmd.Parameters.Add(new SqlParameter("@STable", STable));
                                cmd.Parameters.Add(new SqlParameter("@SColumn", SColumn));
                                cmd.Parameters.Add(new SqlParameter("@Cub", OLAPCubex.Name));
                                cmd.Parameters.Add(new SqlParameter("@Db", OLAPDatabase.Name));

                                cmd.ExecuteNonQuery();
                                conn.Close();
                            }
                        }

                        foreach (MeasureGroup OLAPMeasureGroup in OLAPCubex.MeasureGroups)
                        {
                            foreach (Measure OLAPMeasure in OLAPMeasureGroup.Measures)
                            {
                                string S1 = OLAPMeasure.Source.ToString();
                                string S2 = OLAPMeasure.Source.ToString();
                                string STable = "";
                                string SColumn = "";

                                if (OLAPMeasure.Source.ToString().Contains("."))
                                {
                                    STable = Source1.Substring(0, Source1.IndexOf("."));
                                    SColumn = Source2.Substring(Source2.IndexOf(".") + 1);
                                }

                                SqlConnection conn = new SqlConnection("Data Source=LOCALHOST;Initial Catalog=Metadata;Integrated Security=SSPI;");
                                conn.Open();

                                SqlCommand cmd = new SqlCommand("SourceMeasure", conn);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Add(new SqlParameter("@MG", OLAPMeasureGroup.Name));
                                cmd.Parameters.Add(new SqlParameter("@M", OLAPMeasure.Name));

                                if (OLAPMeasure.MeasureExpression != null)
                                {
                                    cmd.Parameters.Add(new SqlParameter("@ME", OLAPMeasure.MeasureExpression));
                                }
                                else
                                {
                                    cmd.Parameters.Add(new SqlParameter("@ME", " "));
                                }

                                cmd.Parameters.Add(new SqlParameter("@STable", SourceTable));
                                cmd.Parameters.Add(new SqlParameter("@SColumn", SourceColumn));
                                cmd.Parameters.Add(new SqlParameter("@Cub", OLAPCubex.Name));
                                cmd.Parameters.Add(new SqlParameter("@Db", OLAPDatabase.Name));

                                cmd.ExecuteNonQuery();
                                conn.Close();
                            }
                        }
                    }
                }
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}
 

One thought on “Cube Documentation with Analysis Management Objects (AMO)

  1. Pingback: Cube Documentation with Analysis Management Objects (AMO) | jessekraut

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s