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'
Hi Benny,
How can we get all cubes details available in a server together. I want to fetch the refresh date for all the cubes for all databases in a single select.
Please help.
Datasources how can i get for
Hi Benny,
Is there a way to get a result as follows :
Table Name, Table Type [Normal / Calculated], Column Name, Column Type [Normal / Calculated / Measure], Data Type, Data Format, Expression [Measure or Calculation Dax Formula] .
I was struggling to figure it out, how to handle Boolean Column in Where Clause, For example,
[select * from $SYSTEM.MDSCHEMA_MEASURES WHERE MEASURE_IS_VISIBLE = True ] is returning an error, not sure how to handle it. However, Dax Studio does not give any detail of the error.
I appreciate your kind response.
Regards,
Sukhomoy
Hello,
Can I get the DatabaseID, CubeID, MeasureGroupID and PartitionID via DMV from cube?
If not is there any other way?
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)
Thanks!
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:
SELECT
[MEASUREGROUP_NAME]
FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS
I can run this query to find out when the dimensions were last processed:
SELECT [CUBE_NAME], LAST_DATA_UPDATE
FROM $System.MDSCHEMA_CUBES
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.
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.
Can we combine both queries (Dimensions and Measures) to get both as a single result set.
Hello,
Could help me to get the Cube size and Tables size individually.
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 ???
Hi Heron, did you find an answer to your question? I have the same issue..trying to extract (for all my ssas databases) roles and members for each cube..but don’t know how to link roles to cubes..
Thanks
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.
Thanks
Sujatha
Is there a way to run this DMVs using sql
Yes you can using OPENROWSET() or OPENQUERY()
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 * FROM OPENQUERY (ADSI,’
SELECT *
FROM $SYSTEM.MDSCHEMA_CUBES’)
Thanks in advance
Hi you can only query the cubes separately. I’ve created an ETL process to query them for example and merge them. Dont forget to add a key per cube to identify them.
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.
Thanks
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?
Hi,
Can you please any one tell me who deleted the cube database from the analysis server2008
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
Ayush, you can get the partition query using AMO. Check out this post https://codeperfect.wordpress.com/2009/12/08/using-amo-to-query-cube-partitions-and-other-ssas-objects-how-to-find-query-definition-of-the-partition/
SELECT * FROM $System.TMSCHEMA_PARTITIONS
Hi Benny,
Thanks for your article !
I have search to get the data of traduction in the DMVs :
-traductions of the cube dimension
-traductions of the cube name
-traductions of the dimensions
I am talking about the traductions the developer can add in SSDT
Could you help me ?
thanks
have a nice day
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 🙂
Thanks!
Bernard
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.
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.
Cheers,
Tom
Tom, DMV’s work only if the SSAS objects are in processed state.
Visit: http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/
Hello Benny, thanks for your SSAS DMV resource, I’ve just mentioned it in my latest blog post, thanks again!
http://datanrg.blogspot.ca/2017/01/how-to-compare-two-ssas-databases.html
Hi Benny, is it possible to get information about actions?
Thanks
Ramon, you can use MDSchema_Actions rowset to get information about actions
Hi Benny, IS there any way to get the Type of each Attribute under Dim/Factt?
Is it possible to get the members count from dimension and sum of a measure in measure group by using the DMV
Tayyab, you should use MDX for that.
How does one retrieve all the database tables in all the databases in a specific SSAS instance?
Hi Benny ,
Is there a way to get the size of each cube using DMV’s
Sunny, not possible in DMV. Try this powershell script instead http://www.ssas-info.com/analysis-services-scripts/1197-powershell-script-to-list-info-about-ssas-databases
Hi Benny,
Is there any MDX query to get data types of a cube measures and dimension
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.