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


146 thoughts on “SSAS: Using DMV Queries to get Cube Metadata”

  1. Hi Austin,

    Is there any way to know the details of users who are using the cube in SSAS.
    Regards,
    Venkat

    Reply

      1. Hi Benny,

        Thanks for your information. The below queries gave me some sort idea about the connections and sessions on SSAS.
        2.Paste any one of the following DMV queries in an MDX query window to get a list of all sessions, connections, and commands that are currently executing:

        Select * from $System.Discover_Sessions

        Select * from $System.Discover_Connections

        Select * from $System.Discover_Commands

        —
        One final question here..Is there any way to get the statistics for particular Cube on SSAS.

        Regards,
        Venkat

  2. Hi Benny, I’m referencing Discover_Connections, Discover_Commands and Discover_Sessions DMV’s to monitor Cube usage like connected User, MDX, Time taken etc every 30 minutes. Based on my test results, I do see Usage information lasts for atleast 3 hours. Do you have any idea how long data lasts after an event occurs?

    Reply

  3. hi , Thank u for the post , Is there a Way to Query for the Dimention Columns that are used to join between the Dimention and Fact ? (not just the [DIMENSION_GRANULARITY])

    Reply

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

    Reply

    1. Martin, you are right. The key and name columns are returning the attribute names instead of it’s SQL representation as it should (according to MSDN documentation). Perhaps you should raise it in connect.

      Reply

      1. Thanks for very helpful post about cube metadata. Is there any way to get list of attributes and measures involved in a particular MDX query.

  5. Pingback: Meilleur outils gratuit pour développeur MS BI | Le Post de MCNEXT

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

    Reply

    1. Richa, seems like DMV does not provide info on DimensionID. It usually takes the underlying view or table name in dsv with space between words.

      Reply
  7. Pingback: Metadados SSAS
  8. Pingback: 10 Things I Learned Working with the TABULAR Model | TCSC

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

    Reply
  11. Pingback: Metadados SSAS | SQLPT

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

    Reply

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

      Reply

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

    Reply
  15. Pingback: JMP | SSAS Cube Queries to Extract Cube Measures, Dimensions, Attributes, etc.

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

    Reply

    1. JasParvathi, does this DMV query help ?

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

      Reply

  17. 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’)

    Reply

    1. 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);

      }
      }
      }

      Reply

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

    Reply

      1. How can we identify the granularity of these date fields – like differentiate between Year, Year & Quarter, Year & Month, and Date fields?

      2. thank you very much benny,

        How can we identify the granularity of these date fields – like differentiate between Year, Year & Quarter, Year & Month, and Date fields?

      3. I don’t think SSAS DMV’s extend to that level. You might want to let Microsoft Connect know about it.

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

    Reply

  22. 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!

    Reply

  23. 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’

    Reply

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

    .

    Reply

    1. Does this DMV -“Dimension Usage/Fact-Dimension Bus Matrix” (last one on the post) help ?

      Reply

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

    Reply

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

    Reply

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

    Reply

    1. DMV’s don’t work when cube is in unprocessed state, so your best option is using SSAS AMO to programmatically get the state and last processed date time http://msdn.microsoft.com/en-US/library/ms124924(v=sql.90).aspx. The cube, measure group and partition classes of AMO has 2 properties that will be of interest to you – State and Last Processed. You will have to get object reference for the server and database fundamental classes before you can use cube, measure group or partition classes.

      Reply

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

    Reply

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

    Reply

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

      Reply

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

    Reply

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

    Reply

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

      Reply

  32. 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?

    Reply

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

      Reply

  33. Hi Benny

    Can I update description field with column value from table using this framework ?

    Reply

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

    thanks
    prashanth

    Reply

    1. Prashanth, I have updated the post with the DMV query you requested. It’s the last query on this post.

      Reply

  35. 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 ?

    Reply

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

      Reply

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

    Reply

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

      Reply

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

      2. adding to previous post..
        It should be like tree for each level based on level number..

        Regards,
        Kiran

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

    Reply

    1. –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]

      Reply

      1. Thanks alot!
        Can we rely on [Expression] column for determining calculated measure?

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

  40. Pingback: Davide Mauri : SSAS DMVs: useful links

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

    Reply

    1. Pavan, DMV’s gives you the metadata only. To get the actual values you need to use MDX query

      Reply

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

    Reply

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

      Reply

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

    Reply

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

      Reply

  46. 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!

    Reply

    1. Hi, could you pls provide more details on the problem you are encountering. If possible send me the query

      Reply

  48. i am not able to find Source column information for cube attribute with above given statements.

    Reply

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

      Reply

      1. 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!

      2. 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)”

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

    Reply

  50. 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!

    Reply

Leave a reply to Benny Austin Cancel reply