SSAS DMV: Join using DataSet

In SSAS 2008 we have Dynamic Management View (DMV), which is basically XMLA discover command, wrapped as data mining select statement (DMX). On SSMS, if we connect to an SSAS instance, right click on the server name, New Query, DMX,  type “select * from $system.discover_connections”, and click Execute, we will see a list of users currently connecting to that server.

The output is like this: (I only pick 1 row and transpose it so we can see the column names clearly)

CONNECTION_ID	42
CONNECTION_USER_NAME	domain\user1
CONNECTION_IMPERSONATED_USER_NAME
CONNECTION_HOST_NAME	10.1.27.146:3315
CONNECTION_HOST_APPLICATION	SQL Server Management Studio
CONNECTION_START_TIME	29/12/2009 11:34
CONNECTION_ELAPSED_TIME_MS	3089784
CONNECTION_LAST_COMMAND_START_TIME	29/12/2009 11:45:03
CONNECTION_LAST_COMMAND_END_TIME	29/12/2009 11:45:04
CONNECTION_LAST_COMMAND_ELAPSED_TIME_MS	62
CONNECTION_IDLE_TIME_MS	1972821
CONNECTION_BYTES_SENT	3610612
CONNECTION_DATA_BYTES_SENT	15880694
CONNECTION_BYTES_RECEIVED	1502120
CONNECTION_DATA_BYTES_RECEIVED	1502120

And if we type “select * from $system.discover_sessions” we will get:

SESSION_ID	D1A8209F-27C4-4013-AB23-154704213CB3
SESSION_SPID	357640
SESSION_CONNECTION_ID	661
SESSION_USER_NAME	domain\user1
SESSION_CURRENT_DATABASE	Cube1
SESSION_USED_MEMORY	71
SESSION_PROPERTIES
SESSION_START_TIME	29/12/2009 11:57
SESSION_ELAPSED_TIME_MS	3193141
SESSION_LAST_COMMAND_START_TIME	29/12/2009 12:09
SESSION_LAST_COMMAND_END_TIME	29/12/2009 12:09
SESSION_LAST_COMMAND_ELAPSED_TIME_MS	0
SESSION_IDLE_TIME_MS	2476374
SESSION_CPU_TIME_MS	87687
SESSION_LAST_COMMAND	{MDX Statement}
SESSION_LAST_COMMAND_CPU_TIME_MS	62
SESSION_STATUS	0
SESSION_READS	103571
SESSION_WRITES	0
SESSION_READ_KB	6593264
SESSION_WRITE_KB	0
SESSION_COMMAND_COUNT	1854

And when we type “select * from $system.discover_commands” we get:

SESSION_SPID	356127
SESSION_COMMAND_COUNT	31
COMMAND_START_TIME	29/12/2009 11:36
COMMAND_ELAPSED_TIME_MS	0
COMMAND_CPU_TIME_MS	281
COMMAND_READS	102
COMMAND_READ_KB	6154
COMMAND_WRITES	0
COMMAND_WRITE_KB	0
COMMAND_TEXT	{MDX Statement}
COMMAND_END_TIME	29/12/2009 11:36

For other DMVs please see my June presentation at Microsoft TVP here. Also see my recent article on SSAS DMV here.

We can link the discover_sessions and discover_connections on CONNECTION_ID. And we can link the discover sessions and discover_commands on SESSION_SPID.  Like this:

If we want to join the three DMVs (command, session, connection), we have 3 options:

1. Join using Linked Server
2. Join using SSIS
3. Join using DataSet

Midas Matelis wrote about the Linked Server. Basically it’s using OpenQuery.
Chris Webb wrote about the SSIS one.
This article is about using DataSet to join the DMVs. It’s using ADOMD.

What is a DataSet? We can think of DataSet as in-memory ERD diagram. It’s a collection of tables which have relational integrity between them.

So, we are going to issue Discover_Connections, Sessions and Commands then join them in the DataSet. The steps are:

1. Add reference to ADOMDClient
2. Open a connection and execute Discover_Commands
3. Create a DataTable for Discover_Commands and populate it
4. Execute Discover_Sessions
5. Create a DataTable for Discover_Sessions and populate it
6. Execute Discover_Connections
7. Create a DataTable for Discover_Connections and populate it
8. Add all three tables to the DataSet and join them
9. Display the joined table to the screen

1. Add reference to ADOMDClient

Create a new C# project. You can use Windows form, WPF or web project. Or Silverlight project if you use VS2010. Add reference to Microsoft.AnalysisServices.AdomdClient

2. Open a connection and execute Discover_Commands

AdomdConnection ASConn = new AdomdConnection(“Data Source=ServerName;Catalog=;Integrated Security=SSPI;”);
ASConn.Open();
AdomdCommand Cmd = ASConn.CreateCommand();
Cmd.CommandText = “select * from $system.discover_commands”;
AdomdDataReader R = Cmd.ExecuteReader();

Leave the Catalog empty. We don’t need to specify it if we only want to execute discover command, session and connection. We do need to specify the database name for some DMV.

3. Create a DataTable for Discover_Commands and populate it

First we create a DataTable called tblCommand:
DataTable tblCommand = new DataTable(“Command”);

Then we get the column names from the schema:
DataTable tblSchema = new DataTable(“Schema”);
tblSchema = R.GetSchemaTable();
foreach (DataRow schemarow in tblSchema.Rows)
{ tblCommand.Columns.Add(schemarow.ItemArray[0].ToString(), System.Type.GetType(schemarow.ItemArray[5].ToString()));}

Then we populate the DataTable from the ADOMD Data Reader:
while (R.Read())
{object[] ColArray = new object[R.FieldCount];
for (int i = 0; i < R.FieldCount; i++)
{if (R[i] != null) ColArray[i] = R[i];}
tblCommand.LoadDataRow(ColArray, true);
}
R.Close();

4. Execute Discover_Sessions

We reuse the data reader:
Cmd.CommandText = “select * from $system.discover_sessions”;
R = Cmd.ExecuteReader();

5. Create a dataset table for Discover_Sessions and populate it

First we create a DataTable called tblSession:
DataTable tblSession = new DataTable(“Session”);

Then we get the column names from the schema:
tblSchema = R.GetSchemaTable();
foreach (DataRow schemarow in tblSchema.Rows)
{tblSession.Columns.Add(schemarow.ItemArray[0].ToString(), System.Type.GetType(schemarow.ItemArray[5].ToString()));}

Then we populate the DataTable from the ADOMD Data Reader:
while (R.Read())
{object[] ColArray = new object[R.FieldCount];
for (int i = 0; i < R.FieldCount; i++)
{if (R[i] != null) ColArray[i] = R[i]; }
tblSession.LoadDataRow(ColArray, true);
}
R.Close();

6. Execute Discover_Connections (reuse reader)
Cmd.CommandText = “select * from $system.discover_connections”;
R = Cmd.ExecuteReader();

7. Create a DataTable for Discover_Connections and populate it

First we create a DataTable called tblConnection:

DataTable tblConnection = new DataTable(“Connection”);

Get the column names from schema (tblSchema is defined above)

Column name is on the 1st column, data type is on the 6th column.

tblSchema = R.GetSchemaTable();

foreach (DataRow schemarow in tblSchema.Rows)

{ tblConnection.Columns.Add(schemarow.ItemArray[0].ToString(), System.Type.GetType(schemarow.ItemArray[5].ToString()));}

Then we populate the Connection DataTable

while (R.Read())

{ object[] ColArray = new object[R.FieldCount];

for (int i = 0; i < R.FieldCount; i++)

{ if (R[i] != null) ColArray[i] = R[i];}

tblConnection.LoadDataRow(ColArray, true);

}

Then we close the data reader and the connection because we have finished reading data from SSAS. After this we only play in memory.
R.Close();
ASConn.Close(true);
ASConn.Dispose();

8. Add all three DataTables to the DataSet and join them

Create a DataSet and add the DataTables:
DataSet DS = new DataSet();
DS.Tables.Add(tblCommand);
DS.Tables.Add(tblSession);
DS.Tables.Add(tblConnection);

Create a relationship between Session and Command on Session SPID:
DataColumn ParentColumn = DS.Tables[“Session”].Columns[“SESSION_SPID”];
DataColumn ChildColumn = DS.Tables[“Command”].Columns[“SESSION_SPID”];
DataRelation Rel = new DataRelation(“Session_Command”, ParentColumn, ChildColumn);
DS.Tables[“Command”].ParentRelations.Add(Rel);

Create a relationship betweeen Connection and Session on Coonection ID
ParentColumn = DS.Tables[“Connection”].Columns[“CONNECTION_ID”];
ChildColumn = DS.Tables[“Session”].Columns[“SESSION_CONNECTION_ID”];
DataRelation Rel2 = new DataRelation(“Connection_Session”, ParentColumn, ChildColumn);
DS.Tables[“Session”].ParentRelations.Add(Rel2);

Join the three tables: (David M provides a good reference, using array)

First we create a DataTable called tblJoin to store the final result:
DataTable tblJoin = new DataTable(“Join”);

Then we create the columns of tblJoin, by getting the column names and data types from tblConnection, tblSession and tblCommand. To avoid duplicate column name, add “2” on the second join column.

for (int i = 0; i < tblConnection.Columns.Count; i++)
{ tblJoin.Columns.Add(tblConnection.Columns[i].ColumnName,
     tblConnection.Columns[i].DataType);
}

for (int i = 0; i < tblSession.Columns.Count; i++)
{ if (!tblJoin.Columns.Contains(tblSession.Columns[i].ColumnName))
    tblJoin.Columns.Add(tblSession.Columns[i].ColumnName, tblSession.Columns[i].DataType);
  else
    tblJoin.Columns.Add(tblSession.Columns[i].ColumnName + "2", tblSession.Columns[i].DataType);
}

for (int i = 0; i < tblCommand.Columns.Count; i++)
{ if (!tblJoin.Columns.Contains(tblCommand.Columns[i].ColumnName))
    tblJoin.Columns.Add(tblCommand.Columns[i].ColumnName, tblCommand.Columns[i].DataType);
  else
    tblJoin.Columns.Add(tblCommand.Columns[i].ColumnName + "2", tblCommand.Columns[i].DataType);
} 

tblJoin.BeginLoadData();
foreach (DataRow ConnectionRow in tblConnection.Rows)
{ DataRow[] SessionRows = ConnectionRow.GetChildRows(Rel2);
  if (SessionRows != null && SessionRows.Length > 0)
  { object[] ConnectionArray = ConnectionRow.ItemArray;
    foreach (DataRow SessionRow in SessionRows)
    { DataRow[] CommandRows = SessionRow.GetChildRows(Rel);
      if (CommandRows != null && CommandRows.Length > 0)
      { object[] SessionArray = SessionRow.ItemArray;
        foreach (DataRow CommandRow in CommandRows)
        { object[] CommandArray = CommandRow.ItemArray;
          object[] JoinArray = new object[ConnectionArray.Length
            + SessionArray.Length + CommandArray.Length];
          Array.Copy(ConnectionArray, 0, JoinArray, 0, ConnectionArray.Length);
          Array.Copy(SessionArray, 0, JoinArray, ConnectionArray.Length, SessionArray.Length);
          Array.Copy(CommandArray, 0, JoinArray, ConnectionArray.Length +
          SessionArray.Length, CommandArray.Length);
          tblJoin.LoadDataRow(JoinArray, true);
        }
      }
    }
  }
}

7. Display the joined table to the screen

Here I used DataGridView object. If you want the AutoSizeColumnsMode can be set to cover the widest content.
dataGridView1.DataSource = tblJoin;

We can also display only the column that we like (not all columns).
We can put the server name in a text box so we can change SSAS server easily.
The result contains 48 columns: (the tblConnection columns are not shown)

The code is attached (C#): App1v2 (it’s pptx, rename to .zip first).
The zip file contains the Visual Studio solution (VS 2008).
Version 1 (only Session and Command, no Connection):  App1 (it’s pptx, rename to .zip first)

Potential for further development:

• Rename columns on the dataGridView to be more user friendly. Hide unnecessary columns.
• Refresh every 2 seconds
• Cascade drill down: you click + on a connection and it displays the session, you click + on a connection and it displays the commands.
• Execute other DMVs, such as displaying object resources (memory etc) used by each command or connection.
• Give warning if the amount of resources used by a connection exceeds certain pre-set limits.