Data Warehousing and Business Intelligence

29 December 2010

SSAS DMV: Join using DataSet

Filed under: Analysis Services — Vincent Rainardi @ 4:32 pm
Tags:

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.
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: