As I did not find restriction list in documentation, list above is from my experience. It could be that I just did not do my tests properly, but I am sure sooner or latter there will be official list of what is supported.<\/p>\n
Bellow are examples of\u00a0 queries that are very simple, but enough for anyone to get an idea of what it is possible. All these examples where run in Adventure Works DW database.<\/p>\n
<\/p>\n
Examples how to get metadata<\/strong><\/p>\n — List of cubes in current database — Show dimension in one cube or perspective — Show measuregroups in one cube or perspective — Show dimensions related to one measure group — Show available KPIs — List of hierarchies in one dimension Examples how to query data<\/strong><\/p>\n You can also write queries to get actual dimension members from dimension or\u00a0fact records from measure group. I found that performance on measure group queries was quite slow.<\/p>\n So more examples:<\/p>\n — SELECT members from Account dimension –SELECT records from [Exchange Rates] measure group in [Direct Sales] perspective These examples should give you good idea what kind of metadata and data is now accessible.<\/p>\n","protected":false},"excerpt":{"rendered":" In SSAS 2005\u00a0to access SSAS metadata you had to use object model. \u00a0In SSAS 2008 November CTP Microsoft introduced schema rowsets as an alternative way to access metadata. I did some tests last few days and here I’ll post examples of what could be done. Generally it is quite easy to write these queries. BOL […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/39"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=39"}],"version-history":[{"count":0,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/39\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=39"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=39"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=39"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nSELECT CUBE_NAME
\n\u00a0, BASE_CUBE_NAME — For perspective this will show source cube name
\n\u00a0 FROM $system.MDSCHEMA_CUBES
\n\u00a0WHERE CUBE_SOURCE = 1 — Just cubes, =2 – dimensions.<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/01-cubelist.jpg\")
<\/p>\n
\nSELECT DIMENSION_ORDINAL
\n\u00a0, DIMENSION_NAME
\n\u00a0, DIMENSION_CARDINALITY
\n\u00a0, DEFAULT_HIERARCHY
\n\u00a0 FROM $SYSTEM.MDSCHEMA_DIMENSIONS
\n\u00a0WHERE CUBE_NAME = ‘Direct Sales’ — Perspective name
\nORDER BY DIMENSION_ORDINAL<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/02-dimlist.jpg\")
<\/p>\n
\nSELECT CUBE_NAME, MEASUREGROUP_NAME
\n\u00a0 FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS
\n\u00a0WHERE CUBE_NAME = ‘Direct Sales’<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/03-mglist.jpg\")
\n\u00a0<\/p>\n
\nSELECT CUBE_NAME, MEASUREGROUP_NAME, [DIMENSION_UNIQUE_NAME]
\n\u00a0, DIMENSION_IS_VISIBLE
\n\u00a0, DIMENSION_GRANULARITY
\n\u00a0 FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
\n\u00a0WHERE CUBE_NAME = ‘Direct Sales’
\n\u00a0\u00a0 AND MEASUREGROUP_NAME = ‘Internet Customers’<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/04-dimmglist.jpg\")
<\/p>\n
\nSELECT CUBE_NAME, MEASUREGROUP_NAME, KPI_NAME
\n\u00a0, KPI_DESCRIPTION
\n\u00a0, KPI_VALUE
\n—\u00a0, KPI_GOAL\u00a0, KPI_STATUS\u00a0, KPI_TREND — etc
\n\u00a0 FROM $SYSTEM.MDSCHEMA_KPIS
\n\u00a0WHERE CUBE_NAME = ‘Direct Sales’<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/05-kpilist.jpg\")
<\/p>\n
\nSELECT [DIMENSION_UNIQUE_NAME]
\n\u00a0, HIERARCHY_NAME
\n\u00a0, [DEFAULT_MEMBER]
\n\u00a0 FROM $SYSTEM.MDSCHEMA_HIERARCHIES
\n\u00a0WHERE [DIMENSION_UNIQUE_NAME] = ‘[Account]’ — Dimension unique name (should be in []’
\n\u00a0\u00a0 AND CUBE_NAME = ‘$Account’ — Dimension could be used in multiple cubes. This enforces just dimension cube
\nORDER BY HIERARCHY_ORDINAL<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/06-hierlist.jpg\")
<\/p>\n
\nSELECT * FROM [$Account].[$Account]<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/07-account.jpg\")
<\/p>\n
\nSELECT * FROM [Direct Sales].[Exchange Rates]<\/p><\/blockquote>\n![](\"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/BlogFiles\/Post39\/08-exch.jpg\")
<\/p>\n