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

  1. Hello Benny,

    When querying a Multi-Dimensional SSAS , are you able to query the relationship between database source columns and dimension/measure source columns? For example, if I have measure “Gross Sales”, and in cube the TableID=”Sales” and the ColumnId=”GrossSales”, is there a DMV where I can see that relationship? (same thing for dimension columns)


  2. Hi Benny,

    I am newer to SSAS. Just out of curiosity, do you know the query that I can use to find when the measure groups were last processed?

    I see this code to get the list of measure groups:

    I can run this query to find out when the dimensions were last processed:

    where cube_source = 2

    But, I cannot find the code to see when the Measure Group was last processed. This is especially important because when the cube runs long, we would like to run a report and see what has processed and what still needs to be processed.

    Any assistance would be great! Thank you in advance.

    1. Sherri, for creating and processing dimensions I would recommend using Tabular Object Model(TOM) for tabular models or Analysis Management Objects(AMO) for multidimensional models. These libraries have methods/ attributes that report the last processed time.

  3. Hello Benny,
    Thanks a lot for DMV queries …
    By the way … Is there a possibility to get which (roles\users) have privileges access each cube into DB through Queries ???

  4. Thanks, can I run these queries in SSDT and create a perspective out of it ? I am not able to run the query, it errors out with syntax error near ‘$’.
    Please let me know.


  5. Hi Benny,
    I have a requirement to retrieve details of all cubes from SQL Openquery. The below query is returning data only from first database. Please help to get details of cubes from all databases.

    SELECT *

    Thanks in advance

  6. Hi Benny,
    Great Post.Can you please let me know how to get the Last process Date and Last Schema Update Date of all the Catalogs in a Server using DMV.

    I do not get all the catalogs info(Schema and Process Date) in $system.dbschema_catalogs , while for getting them I need to run individual query for $System.MDSCHEMA_CUBES in individual databases.


  7. Hi Benny, im a newbie at this but all im trying to do is extract a table with many columns and 3 measures in SQL studio. im struggling to get the columns i require and can only get 1 column using a MDX query. how do i get many columns?

  8. Hi , Is there any ways by which i can find out the source query of all the partition using DMVs, OR MDS in SSAS Cubes

  9. Hi Benny,

    Just want to ask if there is a way to extract the table source (physical tables/views/name query) used by the cube measures/dimensions from data source view? If yes may i ask your help to formulate as I’ve been searching on this but found no answer 🙂


    1. Hello, just wondering if you ever found how to extract the table source used by the cube measures from the DSV?
      I have a similar requirement and I can’t find where to get this information from.

  10. Hi Benny,

    Cool post. Any idea how to use DMV against empty (not yet processed) SSAS databases?
    We want to deploy a standard solution to any SSAS instance and then dynamically (via SQL config table) drop cubes and dims.


    1. Sumanth, Measures are usually numeric and you can get the precision if numeric MDSCHEMA_MEASURES Rowset. Don’t think you can get the same info for dimensions as the key value can be different from name value.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s