SSAS: Using DMV Queries to get Cube Metadata

SSAS Dynamic Management Views (DMV’s) are very useful to query metadata of a cube. For developers, this is quite handy to provide simple documentation for the cubes they build.

There are some excellent posts on SSAS DMVs by Yaniv Mor and Vidas Matelis

Given below are some of the queries which I found particularly useful. A complete reference to the DMV schema rowset is available in msdn – http://msdn.microsoft.com/en-us/library/ms126079.aspx. Execute these queries from SQL Server Management Studio (SSMS) using MDX or DMX query editor.

--All Cubes in database
SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME
FROM $system.MDSchema_Cubes
WHERE CUBE_SOURCE=1

 

--All dimensions in Cube
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION]
 FROM $system.MDSchema_Dimensions
WHERE CUBE_NAME  ='Adventure Works'
AND DIMENSION_CAPTION <> 'Measures'
ORDER BY DIMENSION_CAPTION

 

--All Attributes
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],
HIERARCHY_IS_VISIBLE AS [VISIBLE]
 FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Adventure Works'
AND HIERARCHY_ORIGIN=2
ORDER BY [DIMENSION_UNIQUE_NAME]

 

--All Attributes with key and name columns
SELECT [CATALOG_NAME] as [DATABASE],
      CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
      LEVEL_CAPTION AS [ATTRIBUTE],
      [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],
      [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]
FROM $system.MDSchema_levels
WHERE CUBE_NAME  ='Adventure Works'
AND level_origin=2
AND LEVEL_NAME <> '(All)'
order by [DIMENSION_UNIQUE_NAME]

 

--All Hierarchies (user-defined)
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY],
HIERARCHY_IS_VISIBLE AS [VISIBLE]
 FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Adventure Works'
and HIERARCHY_ORIGIN=1
ORDER BY [DIMENSION_UNIQUE_NAME]

 

--All Hierarchies (Parent-Child)
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY],
HIERARCHY_IS_VISIBLE AS [VISIBLE]
FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Adventure Works'
AND HIERARCHY_ORIGIN=3
ORDER BY [DIMENSION_UNIQUE_NAME]

 

--All Levels of Hierarchies (user-defined)
SELECT [CATALOG_NAME] as [DATABASE],
	CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
	[HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],
	LEVEL_CAPTION AS [LEVEL],
    [LEVEL_NAME],
	[LEVEL_NUMBER] AS [LEVEL NUMBER],
	[LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],
	[LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],
	[LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN]
 FROM $system.MDSchema_levels
 WHERE CUBE_NAME  ='Adventure Works'
 AND level_origin=1
 order by [DIMENSION_UNIQUE_NAME]

 

--All Levels of Hierarchies (Parent-Child)
SELECT [CATALOG_NAME] as [DATABASE],
	CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
	[HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],
	LEVEL_CAPTION AS [LEVEL],
    [LEVEL_NAME],
	[LEVEL_NUMBER] AS [LEVEL NUMBER],
	[LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],
	[LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],
	[LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN]
 FROM $system.MDSchema_levels
 WHERE CUBE_NAME  ='Adventure Works'
 AND LEVEL_ORIGIN=3
 order by [DIMENSION_UNIQUE_NAME]

 

--All Measures
SELECT [CATALOG_NAME] as [DATABASE],
	CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
	[MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
 WHERE CUBE_NAME  ='Adventure Works'
 ORDER BY [MEASUREGROUP_NAME]

 

--Calculated Measures/Members
SELECT [MEMBER_UNIQUE_NAME] AS [CALCULATED_MEASURE],
	[MEMBER_CAPTION] AS [CAPTION],
	[EXPRESSION]
FROM $system.MDSCHEMA_MEMBERS
WHERE CUBE_NAME ='Adventure Works'
AND [MEMBER_TYPE]=4 --MDMEMBER_TYPE_FORMULA

 

--Dimension Usage/Fact-Dimension Bus Matrix
SELECT	[MEASUREGROUP_NAME] AS [MEASUREGROUP],
		[MEASUREGROUP_CARDINALITY],
		[DIMENSION_UNIQUE_NAME] AS [DIM],
		[DIMENSION_GRANULARITY] AS [DIM_KEY],
		[DIMENSION_CARDINALITY],
		[DIMENSION_IS_VISIBLE] AS [IS_VISIBLE],
		[DIMENSION_IS_FACT_DIMENSION] AS [IS_FACT_DIM]
FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE [CUBE_NAME] ='Adventure Works'
	AND [MEASUREGROUP_NAME] ='Internet Sales'

Benny Austin


95 comments

  1. Thanks for a great post Benny.

    If possible, I want to extract the real table names and columns as they are defined in the datasource relational database.

    I’ve looked through the DMV query examples in the post and I was hoping that the following would provide the requested information (at least for the column names).

    –All Attributes with key and name columns
    SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
    LEVEL_CAPTION AS [ATTRIBUTE],
    [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],
    [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]
    FROM $system.MDSchema_levels
    WHERE CUBE_NAME =’Sales’
    AND level_origin=2
    AND LEVEL_NAME ‘(All)’
    order by [DIMENSION_UNIQUE_NAME]

    But, unless I am doing something wrong or misunderstanding the whole thing, the query above seems to NOT be returning any table or column names as they are named in the relational database. Instead it seems to be returning Dimension names and Attribute names from the SSAS model, used by the cube specified in the query filter.

    I can easily see that this is the case, because in the tables in my datasource the column names have the character “_” between words and no spaces. But the query above return names with spaces for “[ATTRIBUTE_KEY_SQL_COLUMN_NAME]”, which clearly is NOT the source column names in the datasource.

  2. I can not find DimensionID anywhere where is it? when we process cube using xmla DimensionID is used and I cant find it. Dimension_unique_name is not the one as I see its different then Dimension ID. Please help.

  3. […] WHERECUBE_SOURCE=1  Mais informação sobre estas views pode ser obtida aqui. e algumas consultas já feitas. A boa noticia é que para SSAS tabulares, estas mesmas queries também são […]

  4. […] You can document your cubes whether they are OLAP or TABULAR using the schema queries built into the cube.  Go into SQL Management Studio and connect to your cube (OLAP or TABULAR), then start a new MDX query.  It is especially useful in large models because the measure expression is also in the table so it is pretty easy to give the client a list of measures and calculations for them to review. select * from $system.mdschema_measures select * from $system.mdschema_dimensions There are lots more here: https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/ […]

  5. Hi Beeny,

    Excellent post, I am very new to Cubes and SSAS,I am having 5 Data Source,

    1.How to get list of Fact and Dimension table coming from corresponding Data Source.
    2.How to get Measure and Calculated measure from Corresponding data source.
    3.How to find What the dimension table related to specific Fact table.

    Pls help me.it will be very useful.

  6. […] WHERECUBE_SOURCE=1  Mais informação sobre estas views pode ser obtida aqui. e algumas consultas já feitas. A boa noticia é que para SSAS tabulares, estas mesmas queries também são válidas. Posted […]

  7. Thanks for the post.
    But I want to get the source table and source column name for a particular dimension attribute.
    you said its not possible through DMV but is possible with MDX.
    Could you please help me with that.

    • Sudipta, if you haven’t already tried one of these queries in this post- “All Attributes with key and name columns” or “All Levels of Hierarchies (user-defined)” will give you the information you are looking for.

  8. Hi, Is there any way to get Cube Process state (Yes or No) in AMO code.
    If yes can you share the code.

  9. Hi Guys,

    We can get measure group name by using MEASUREGrOUP_NAME property. But in case of Translated cube I want to get the caption for this measure group name, which property I have use. Please help me, as of my analysis with my little acquaintance of knowledge, Microsoft doesn’t support this scenario in ADOMD.net and in AMO we can get it by using the property in Measure Group class called “Translations”

    Thanks,
    JasParvathi

    • JasParvathi, does this DMV query help ?

      SELECT MEASUREGROUP_NAME,MEASUREGROUP_CAPTION
      FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS
      WHERE [CUBE_NAME]=’CubeName’

  10. Hi, does anyone know if there is a way to count all of the partitions for a measure type? Ideally just using DMV’s
    I know you can use the following but you have to specify a partition name with SystemRestrictSchema.

    select * from SystemRestrictSchema($system.discover_partition_stat, CUBE_NAME = ‘xxx’, DATABASE_NAME = ‘xxx’, MEASURE_GROUP_NAME = ‘xxx’, PARTITION_NAME = ‘xxx’)

    • Jamie, DMV may not be able to report on partition count. Try AMO instead. The following AMO code will return partition count

      using System;
      using System.Text;
      using Microsoft.AnalysisServices;

      namespace AmoPartitionCount
      {
      class Program
      {
      static void Main(string[] args)
      {
      Server svr = new Server();
      svr.Connect(“localhost”);

      Database db =svr.Databases.FindByName(“AdventureWorks”);
      Cube cbe = db.Cubes.FindByName(“Adventure Works”);

      MeasureGroup mg = cbe.MeasureGroups.FindByName(“Internet Sales”);

      int count =mg.Partitions.Count;
      Console.WriteLine(count);

      }
      }
      }

  11. . hi
    please look into the below reqirement
    How can we identify what dimensions are date based dimensions – either complete or partial dates – like Date, Year and Month, Year and Quarter, Year, etc.
    2. For these date based fields do we have a standard way of building the Unique Identifier for a given date – like 20110215 for 02/15/2011, 20110401 for 2011Q2, 20110101 for 2011 year and 2011Q1, etc.

  12. Hi Benny,

    This is really great!!! I am wondering if I can have a single query to have Cube metadata where I can loop through the hierarchies in dimensions and cube metadata. In short, I wish to have a query which will give me the whole cube metadata with all the hierarchies…
    Please help.

  13. Great post!! is there a way to get the whole query used as a partition definition? I am particularly interested in the table names in the from clause
    thanks!

  14. Hi Benny:
    This post is really a life saver. Almost evry thing related to Cube metadata is here…..Just a quick question…
    Can we use something like ‘Contains’ or ‘Like’ operator as in SQL? I tried but its not working. Is there any workarounds for the same?

    E.g.
    select Measure_Caption from $system.MDSchema_measures
    where MeasureGroup_Name Like ‘%abc%’
    and Cube_Name=’XYZ’

  15. can we find the list of measures which related to all the dimentions,just like additive fact,i need dmv query to find list of measures which related to all the dimensions for each fact

    .

  16. I can do that, but unfortunately any processing of the cube initiated manually would go unnoticed. All i wanted to do is to process a partition based on some condition (user input) in a table and update the cube.. but while doing so i need to check if the cube is already getting processed. Anyways thanks for the info.

  17. Thanks for the Info Benny, I used the discover methods to figure that out.
    Now have another question, is it possible to check if a cube is getting processed (at an instance) either through MDX or AMO or XMLA . The state property will always show as Processed (understand its either processed or Unprocessed) even when the cube is getting processed. Is there anyway to do that?

    thanks
    Kiran

  18. Hi Benny,

    Is it possible to get the Measure group partition process state and last processed date through DMV or by someother means programatically? Thanks for your help in advance.

  19. Hi Benny,
    I am new to MDX queries. Can you please suggest me with some useful links to start with MDX queries on SSAS Cubes. I have a requirement to change the definition of Measure of SSAS Cube( measure is a formula which has changed now. need to change the formula for the measure).
    Please guide me .
    Thanks in advance.

  20. Benny my requirement is ,i need every dimensions attribute and measures data types dmv query with out using open query

    one more question is i got the query but it shows date attribute also shows as var char how can i solve this issue please let me know if you have any idea

    thanks
    prashanth

    • Prashanth, you might have already realized that datatype is returned as enum values from MDSCHEMA_LEVELS and MDSCHEMA_MEASURES rowsets. may not be able to avoid open query. The data type that is returned is the attribute’s key column. It could be possible the key column of your date attribute is a string valued column.

  21. hi benny can we use DMV query with in operator

    like this
    below query
    select *
    from $system.mdschema_measures
    Where CUBE_NAME = ‘cube1’
    and measure_unique_name in( ‘[Measures].[vi]’ ,'[Measures].[ad]’)

    thanks,
    prashanth k

  22. hi Benny , I need information about assp.dll. It’s used for metadata but its not working is there any solution for that? please its urgent..
    assp.dll is 3rd party dll
    If I use assp.dll its giving exception that assp dll is missing ….
    what is the problem please its urgent

    thanks,
    prashanth k

    • No idea mate. Please consult the source from where you got this dll. Probably missing in GAC or missing a path – but that’s just a guess.

  23. Hi
    First of all great post. I do have another problem, I would like to know if there is a way to get the metadata for Dimension measuregroup relation, i.e. what columns from the dimension are connected to what column in the fact?

    • Daniel, the query under this section – “Dimension Usage/Fact-Dimension Bus Matrix” gives the relationship between dimensions and related measure group. For regular and fact relationships, the dimension key or DIMENSION_GRANULARITY is the column that links with the fact table. These relationships have MEASUREGROUP_CARDINALITY of ONE and DIMENSION_CARDINALITY of MANY. Most of the dimension relationships usually fall under this category and DIMENSION_GRANULARITY would be sufficient, provided the dimensions are linked to fact by key columns.

  24. HI ,
    i need DMV for 1 measure related to how many dimentions
    and 1 dimension is related to how many measures

    thanks
    prashanth

  25. Excellent post ! Thank you !
    I of course have a question: when using MDSCHEMA_MEASURES, I observe that the CUBE_NAME column is really the name of a perspective, if the cube has any. For base measures there is one row per perspective, including one for the cube as a whole (in this case CUBE_NAME does equal the cube name).

    I have a question though: there are also rows returned where CUBE_NAME = “$” plus the name of a cube database dimension, and the corresponding MEASURE_NAME is [Measures].[dimension name]. Selecting that measure name in an MDX query does not work however. What is the meaning of these rows/measures ?

    • Thanks Mark for that information. I am able to query by [Measures].[dimension name] for MEASURE_NAME in 2012. Not sure what they mean, couldn’t find any documentation that could explain this.

  26. Hi Benny, very nice post! I am working on a DataDictionary report set for our Cubes and your post helped me a lot.

    The only thing I am wondering is why I can’t get the [DESCRIPTION] back for all my Dimension Attributes. I filled most of the via Visual Studio but can’t see them back in the $SYSTEM.MDSCHEMA_HIERARCHIES or $SYSTEM.MDSCHEMA_MEMBERS.

    I can query the [DESCRIPTION] of the Calculated Members via $SYSTEM.MDSCHEMA_MEASURES.

    Do you know why and how can I get these Dimension Attribute Descriptions out of the SSAS database?

    Thanks,
    Michiel

    • Michiel, I am getting descriptions from $system.MDSchema_hierarchies and $system.MDSchema_levels. I am using SQL Server 2012, haven’t tried this in earlier SQL Server versions though.

      • Hello Benny,
        Sorry to interpret in the middle.. here i have a requirement when we use DMV with mdx Queries and i got total cube structure by using $SYSTEM.MDSCHEMA_LEVELS and the user expects now the hierarchy like below
        Country Ship To
        └ Customer Sold To ID
        └ Customer Ship To ID.

        It could be great helpif suggest us on this.

        Reagrds,
        Kiran

  27. Hi benny
    How can i get the list of the SSAS Properties? , I want to save the Propertie settings of “Analysis Server Properties
    1- i need the GENERAL tab
    2- SECURITY tab

    second i need the CUBE database propertie.
    basically i want to save the properties in a table.
    even if you have a POWERSHELL script it will do, anything

    Thanks

    • –All Measures
      For calculated measures, MEASURE_AGGREGATOR of MDSCHEMA_MEASURES rowset returns a value of 127(MDMEASURE_AGGR_CALCULATED) which indicates the aggregation is formula based (as most calculated measures are)

      SELECT [CATALOG_NAME] as [DATABASE],
      CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
      [MEASURE_IS_VISIBLE],[MEASURE_NAME_SQL_COLUMN_NAME],[MEASURE_AGGREGATOR]
      FROM $SYSTEM.MDSCHEMA_MEASURES
      WHERE CUBE_NAME =’Casework’
      ORDER BY [MEASUREGROUP_NAME]

        • Yeah, [Expression] would work just as well in ‘most’ cases. Not sure how it will distinguish regular measures that have MeasureExpression for e.g you could have regular measure like target sales defined as sales this period * 0.2. Might be worth trying this out.

  28. Useful queries. For one automation task, I was looking at a way to pull the name column and key column of measures and dimension attributes from properties. I looked at the query ‘All Attributes with key and name columns’, for columns LEVEL_NAME_SQL_COLUMN_NAME and LEVEL_KEY_SQL_COLUMN_NAME, the values are NAME([$Dimension Name].[Attribute Name]) and KEY([$Dimension Name].[Attribute Name]), but it doesn’t show the actual value of this property. Is there a way I can get the actual value like source table name.column name

  29. Hi,

    is it possible to get the information about the current state of the cube (processed/unprocessed). I found the “last-update” column. But this is not, what I want.

    Thx for help
    Tobias

    • Tobias, the LAST_DATA_UPDATE column of rowset MDSCHEMA_CUBES is the closest to what you are looking for. Take note that if the cube is in an unprocessed state the query doesn’t return anything. Also I have to mention it is likely that LAST_DATA_UPDATE will be updated even if a common dimension shared with another cube is processed.

  30. Hi I want to set the WHERE CLAUSE to be set as AND BASE_CUBE_NAME IS EMPTY or NULL
    How can i do that

    SELECT CUBE_NAME , BASE_CUBE_NAME , *
    FROM $system.MDSCHEMA_CUBES
    WHERE Cube_Source = 1
    AND BASE_CUBE_NAME = ” — <<——- My question is in this section
    Thanks

    • Boolean comparison is not allowed in DMV query. Instead you could use a VBA function like LEN to get similar result.

      SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME,VBA!ISNULL(BASE_CUBE_NAME) AS ISNULL
      FROM $system.MDSchema_Cubes
      WHERE CUBE_SOURCE=1
      AND VBA!LEN(BASE_CUBE_NAME)=0

  31. Hello!

    I just started to work with SSAS and I have the following problem: I want to use the Description property for some attributes but when I run the query using $system.MDSchema_hierarchies attributes from one dimension have the Dimension property and attributes from another dimension do not have the Description property.

    Do you know any reason for this behavior?

    Txs!

    • Source column info is available in MDSchema_levels rowset in these columns [LEVEL_NAME_SQL_COLUMN_NAME], [LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] and [LEVEL_KEY_SQL_COLUMN_NAME]. Have a look at the Hierarchy Levels in the post query which has since been updated.

      • Hi! This is so great! I’m wondering if we can pull has the underlying Data Source View Table and Column name that populates the dimension attribute. In BIDS, this will be under the Properties window of the Attibute under Source ->NameColumn. Thanks!

        • Yes you can get the underlying key and name columns from the MDSchema_levels rowset. The [LEVEL_KEY_SQL_COLUMN_NAME] & [LEVEL_NAME_SQL_COLUMN_NAME] columns give the underlying key and name columns respectively in Table.Column format. Have a look at the DMv query under “All Levels of Hierarchies (user-defined)”

  32. I am a technical writer and working on a data dictionary for our BI project. I was incredibly excited to read and use your blog information for helping me document our Data Dictionary’s Cube.

    I have found lots of information on the web for using a Linked Server with Reporting Services in order to create reports, but I don’t understand why this is.

    Any help would be greatly appreciated, which will allow me to use Report Builder 3.0 (or BIDS) to create Cube Reports.

  33. Finally, just what I was looking for. So much less work than having a procedure in .Net using DSO to get a cube’s metadata!

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