Data Warehousing and Business Intelligence

1 January 2010

SSAS DMV (Dynamic Management View)

Filed under: Analysis Services — Vincent Rainardi @ 12:16 am
Tags:

What are SSAS DMVs?

SSAS Dynamic Management Views are Analysis Services schema rowsets exposed as tables that we can query using T-SQL SELECT statement. For example, to query discover_connections DMV, we can write:
select * from $system.discover_connections

DMVs are more convenient to use than schema rowsets because:
a)    we use SELECT statement rather than XMLA
b)    the output is a table rather than XML

Not all DMVs are schema rowsets. The DMVs which expose cube and dimension data are not schema rowsets. For example, to get the members of product dimension we can write:
select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Red’

Even though they are T-SQL statements, in the Management Studio we write these SSAS DMV SELECT statements in the DMX query window, not in the SQL query window. This is because we need to connect to SSAS, rather than SQL Server relational engine. The SSAS DMVs are available in SSAS 2008 and 2008 R2; they are not available in SSAS 2005 and 2000.

What are they for?

SSAS DMV can be used to monitor the server resources such as:

  • who are connecting to the SSAS server
  • which database/cube they are using
  • what MDX they are running
  • how much memory and CPU is used by each command and each user
  • how long they took to execute, and when did they run
  • which aggregates are used/hit and which are missed
  • which objects are locked and by whom
  • how much memory and CPU is used by each dimension and each measure
  • how much memory is used by each data cache and objects of SSAS engine
  • which cubes, dimensions, partitions and measures are used most and which are rarely used (how many times they are read, how many rows returned, how many KB read)

SSAS DMVs can also be used to find out the structure of SSAS databases in the server:

  • List of dimensions, hierarchies, measures, measure groups, data sources, cubes, actions and KPIs in each SSAS database
  • Members of every hierarchy and level in every cube
  • List of MDX functions, with their descriptions and parameters
  • List of mining structures, models, and algorithms.

How do I use them?

dbschema_tables lists all the DMVs:
select * from $system.dbschema_tables

discover_connections lists who’s connecting to the server, since when, for how long, from which PC (IP) and using what client:
select * from $system.discover_connections

discover_sessions and discover_commands lists the MDX/command used by each user, which SSAS database/cube they are using, how much CPU and memory is used by each command:
select * from $system.discover_sessions
select * from $system.discover_commands

We can join discover_connections and discover_sessions on Connection_ID column. And we can join discover_sessions and discover_commands on Session_SPID column. To join them we can:

  • Create a linked server from a SQL Server to the SSAS server, then use OpenQuery. See Midas Matelis post here.
  • Use SSIS to query the 3 DMVs separately and the join them in SSIS using Merge Join transformation. See Chris Webb post here.
  • Use ADOMD to query the DMV on the SSAS server, and then join them using DataSet. See my post here.

discover_object_memory_usage and discover_object_activity lists the memory usage and CPU time for each object i.e. cube, dimension, cache, measure, partition, etc. They also show which aggregations were hit or missed, how many times these objects were read, and how many rows were returned by them:
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity

The following DMVs provide information on the cube structure:
select * from $system.mdschema_cubes
select * from $system.mdschema_dimensions
select * from $system.mdschema_hierarchies
select * from $system.mdschema_levels
select * from $system.mdschema_measuregroups
select * from $system.mdschema_measuregroup_dimensions
select * from $system.mdschema_measures
select * from $system.mdschema_properties
select * from $system.mdschema_members
select * from $system.mdschema_sets
select * from $system.mdschema_kpis
select * from $system.mdschema_input_datasources

We can use where clause and order by:
select * from $system.discover_object_memory_usage where object_parent_path = ‘Global.ObjectPool’ order by object_id desc

We can use ‘and’, ‘or’ and ‘not’. But there is no ‘like’, ‘<>’ (not equal to), ‘in’ and ‘join’:
select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Blue’ or Color = ‘Silver’
select * from $system.discover_object_activity where not object_rows_returned > 0

In 2008 R2 we can use ‘<>’ (not equal to):
select * from $system.dbschema_tables where table_catalog <> ‘Adventure Works DW’ (only works in 2008 R2)

We don’t have ‘like’ so use left or right instead. Remember we can use ‘not’:
select table_schema from $system.dbschema_tables where table_catalog = ‘Adventure Works DW’ and left(table_schema,1) = ‘$’
select * from … where … and not left(table_schema,1) = ‘$’

We can use distinct:
select distinct object_parent_path from $system.discover_object_activity

On some DMVs we need to write ‘SystemRestrictSchema’. For example if we write:
select * from $system.discover_partition_stat
SSAS will respond with:
The ‘CUBE_NAME’ restriction is required but is missing from the request.  Consider using SYSTEMRISTRICTSCHEMA to provide restrictions.

This is because discover_partition_stat requires 4 restrictions: database_name, cube_name, measure_group_name and partition_name. In other words, we need to specify these 4 parameters. So we need to write:
select * from SystemRestrictSchema($system.discover_partition_stat, CUBE_NAME = ‘Adventure Works’, DATABASE_NAME = ‘Adventure Works DW’, MEASURE_GROUP_NAME = ‘Internet Orders’, PARTITION_NAME = ‘Internet_Orders_2004′)

Another example on restriction:
select * from systemrestrictschema($system.discover_performance_counters, PERF_COUNTER_NAME = ‘\MSAS 2008:Connection\Current connections’, PERF_COUNTER_NAME = ‘\MSAS 2008:MDX\Total Cells Calculated’)

If we have a SSAS database with 1 cube, 1 dimension and 1 measure group, when we execute: “select * from $system.dbschema_tables”, at the top of the list we will see 4 rows with table_catalog = ‘DatabaseName’:

No table_catalog table_schema table_name table_type
1 DB Name $Dim1 Dim1MG system_table
2 DB Name $Dim1 $Dim1 table
3 DB Name Cube1 MG1 system_table
4 DB Name Cube1 $Dim1 table

Each of these 4 tables is a DMV that we can query using SELECT statement.

  • No 2 is a database dimension
  • No 4 is cube dimension (a database dimension that is used in a cube).
  • No 3 is the measure group.
  • No 1 is the ‘dimension cube’. For every dimension, SSAS create a cube. This cube is called ‘dimension cube’. This cube contains only 1 dimension with no measure. This cube can’t be accessed from the client. It is only available internally within SSAS engine.

So to query these 4 DMVs, we need to fully qualify the table name, i.e. table_catalog.table_schema.table_name:

#1 select * from [DB Name].[$Dim1].[$Dim1]  –database dimension
#2 select * from [DB Name].[Cube1].[$Dim1]  –cube dimension
#3 select * from [DB Name].[Cube1].[MG1]    –-measure group
#4 select * from [DB Name].[$Dim1].[Dim1MG] –dimension cube

#1 returns the members of Dim1.
#2 also returns the members of Dim1.
#3 returns the fact rows for measure group1.
#4 returns nothing

DMV on Books Online

The only mention about DMV in the SQL Server Books Online is on the Data Mining Schema Rowsets page of the November 2009 edition:

“In SQL Server 2008, the data mining schema rowsets are also exposed as tables in the Transact-SQL language, in the $SYSTEM schema. For example, the following query on an Analysis Services instance returns a list of the schemas that are available on the current instance: SELECT * FROM [$system].[DBSCHEMA_TABLES]”

The Books Online explains every column of the 46 schema rowsets in SSAS: 16 XMLA, 4 OLE DB, 16 OLE DB for OLAP and 10 Data Mining. However, there are 54 DMVs in 2008. The following 12 discover_* DMVs are not mentioned in Nov 2009 SQL Server Books Online’s schema rowsets documentation: traces, trace_definition_providerinfo, trace_columns, trace_event_categories, memoryusage, memorygrant, transactions, master_key, performance_counters, partition_dimension_stat, partition_stat, dimension_stat. But, they are mentioned in the SSAS Protocol Specification section 3.1.4.2.3, about Discover Request Types.

There are 3 schema rowsets which are not made into DMV: mdschema_actions, discover_datasources and discover_xml_metadata. To use them we still need to use XMLA. Those of you who wonder about the numbers don’t tally (54 <> 46+12-3), that’s because there is 1 duplicate: discover_keyword is available both as XMLA and OLE DB for OLAP.

In 2008 R2 (I’m using Nov CTP), there 3 new DMVs: discover_storage_tables, discover_storate_table_columns, and discover_storage_tables_column_segments. Again, they are not mentioned in SQL Server Books Online’s schema rowsets documention, but they are mentioned in the SSAS Protocol Specification section 3.1.4.2.3.

About these ads

31 Comments »

  1. [...] details see SSAS DMV. Leave a [...]

    Pingback by Who Are Using The Cubes Now? « Data Warehousing and Business Intelligence — 25 January 2010 @ 1:26 pm | Reply

  2. Hi,

    Nice article, but I think it’s a bit misleading to describe the queries as “T-SQL” queries. They are in fact DMX queries – DMX and SQL share some similarities in syntax, but they are not the same thing.

    cheers, Nathan

    Comment by Nathan Griffiths — 1 April 2010 @ 2:28 am | Reply

    • Thanks Nathan. I am of the same opinion as you at first, because in SSMS we write them in DMX window. And because there are so many things in T-SQL that we can’t do on DMV queries. But on the link to Microsoft.com that I refer to in the article, Microsoft says: “In SQL Server 2008, the data mining schema rowsets are also exposed as tables in the Transact-SQL language, in the $SYSTEM schema”
      For example, the DMV select statement below is querying a table called dbschema_tables, and the where clause is referring to a column in that table:
      select table_schema from $system.dbschema_tables where table_catalog = ‘Adventure Works DW’
      Where as in DMX we are browsing a model (or predicting), and the where clause is referring to the column in that model. Taking an example from Dejan Sarka‘s blog:
      select ModelRegion, PredictTimeSeries(Amount, 3) as Amount from SalesForecast where ModelRegion >= ‘M200′

      Comment by Vincent Rainardi — 1 April 2010 @ 5:52 am | Reply

      • Hi,

        Having looked at the article I see what you mean – however another section (http://msdn.microsoft.com/en-us/library/cc645879.aspx) of Books Online refers to DMX statements only – very confusing! However I did eventually find this page (http://msdn.microsoft.com/en-us/library/ms132058.aspx) which clarifies:

        “The data mining model object is structured like an SQL table, with columns, data types, and meta information that describe the model. This structure lets you use the DMX language, which is an extension of SQL, to create and work with models.”

        Comment by Nathan — 1 April 2010 @ 7:21 pm

  3. Very helpful, thank you! :)

    Comment by Michelle Ufford — 9 July 2010 @ 6:12 pm | Reply

  4. [...] found a great article by Vincent Rainardi describing some DMVs (Dynamic Management Views) available in SQL 2008 which got me thinking, why [...]

    Pingback by Automating SSAS cube docs using SSRS, DMVs and spatial data | The Frog-Blog — 25 September 2010 @ 10:25 pm | Reply

  5. [...] found a great article by Vincent Rainardi describing some DMVs (Dynamic Management Views) available in SQL 2008 which got me thinking, why [...]

    Pingback by Automating SSAS cube docs using SSRS, DMVs and spatial data | Purple Frog Systems — 29 September 2010 @ 7:02 pm | Reply

  6. Thanks a lot for this great post !

    Comment by Jegan — 9 January 2011 @ 9:40 pm | Reply

  7. To join the DMX or SQL debate, my opinion is that while the syntax described here is not T-SQL it is still SQL and it isn’t DMX. SSAS has always supported querying using a very basic subset of SQL (even in SSAS2K and I think before that too), to allow for the creation of local cubes, and I think that’s what SSAS uses to query DMVs. See my blog post here for more details: http://cwebbbi.wordpress.com/2006/06/14/can-i-run-sql-against-an-analysis-services-cube/

    That’s why I’d also say you’re wrong when you say:
    Not all DMVs are schema rowsets. The DMVs which expose cube and dimension data are not schema rowsets. For example, to get the members of product dimension we can write:
    select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Red’

    What you’re seeing here is this ability to query cubes and dimensions in SQL. All DMVs are schema rowsets; it’s just that there are other objects you can query in SSAS SQL too.

    Comment by Chris Webb — 14 February 2011 @ 10:25 pm | Reply

  8. [...] what useful information can you get from a DMV then? Vincent Rainardi has a great post on SSAS DMVs here which covers all the useful ones and has plenty of syntax examples, but here are some things you [...]

    Pingback by Querying PowerPivot DMVs from Excel « Chris Webb's BI Blog — 23 February 2011 @ 5:01 pm | Reply

  9. Here are this and some other articles on SSAS Dynamic Management Views: http://ssas-wiki.com/w/Articles#DMV_.28Dynamic_Management_View.29

    Comment by Sam Kane — 23 February 2011 @ 10:17 pm | Reply

  10. [...] http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ Like this:LikeBe the first to like this post. Categories: Uncategorized Comments (0) Trackbacks (0) Leave a comment Trackback [...]

    Pingback by SSAS DMV (Dynamic Management Views) « Thameem — 18 August 2011 @ 8:04 am | Reply

  11. Hi, usefull article. do you know if it is possible to see the users that used the cube. Like $system.discover_sessions but then not only the active sessions?

    Thanks in advance,
    Arnoud

    Comment by verwegh — 29 November 2011 @ 9:18 pm | Reply

    • No I don’t Arnoud. To get the users who used the cube I loaded SSAS Query Log into a data mart like this.

      Comment by Vincent Rainardi — 2 December 2011 @ 8:43 pm | Reply

      • Thx for the reply. I will look into it. Do you know how badly the OlapQueryLog influences the performance if the every query is logged (instead of 1 for every 10 which is the default)

        Comment by verwegh — 3 December 2011 @ 9:20 am

      • I didn’t get noticeable performance degradation when every query was logged.

        Comment by Vincent Rainardi — 3 December 2011 @ 7:32 pm

  12. Have knowm about DMVs since the Longhorn beta, but not clearly understood them till now. Many thanks. JK

    Comment by Joe Kelly — 19 January 2012 @ 3:55 pm | Reply

  13. [...] muchas cosas, información de la metadata de nuestro cubo. Si quieren más información de los DMV, Vincent Rainardi escribió un excelente post al respecto hace ya algún [...]

    Pingback by Script XMLA para procesar nuestras dimensiones de Analysis Services (Multidimensional) « MSExpertos — 10 May 2012 @ 1:43 pm | Reply

  14. I need the “Analysis Server Properties” list, I couldn’t find a DMV for it, maybe I am wrong? but i am assuming that you can get it by using PowerShell, does anyone know How?
    Nik

    Comment by Nik-Shahriar Nikkhah — 2 August 2012 @ 2:20 pm | Reply

  15. [...] Pingback:http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ Vincent Rainardi @ 12:16 am Tags: Analysis Services [...]

    Pingback by SSAS DMV (Dynamic Management View) « Road to BI — 4 September 2012 @ 5:07 pm | Reply

  16. [...] found a great article by Vincent Rainardi describing some DMVs (Dynamic Management Views) available in SQL 2008 which got me thinking, why [...]

    Pingback by Cristianetips » Documentar cubo OLAP con Reporting Services (SSRS) — 18 October 2012 @ 12:42 pm | Reply

  17. [...] fazla bilgi için Buraya ve buraya [...]

    Pingback by Berkant » Blog Archive » SSAS Dynamic Management View — 11 December 2012 @ 11:54 pm | Reply

  18. Thanks Vincent. Very helpful.

    Comment by Tony Wilkinson — 3 April 2013 @ 12:27 pm | Reply

  19. […] SSAS dynamic management view.  Vincent Rainardi (b | t) has a nice post (here) where you can read more about querying the SSAS dynamic management […]

    Pingback by Creating a Pareto Chart in SSRS « byoBI.com — 24 October 2013 @ 2:28 pm | Reply


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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 187 other followers

%d bloggers like this: