Katmai Analysis Services 2008 November CTP5 – tests on metadata rowsets
November 21st, 2007 by Vidas MatelisIn SSAS 2005 to access SSAS metadata you had to use object model. In 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 already have description for most of the tables and fields you can query. What was not so easy is to get metadata on structure that is hierarchical. That is there is an easy way to get a list of hierarchies in one dimension. But to get list of levels of hierarchies of dimensions becomes not an easy task.
SELECT statements you can write on these rowsets appear to be quite limited. For example:
- SELECT DISTINCT does not return DISTINCT values
- ORDER BY clause accepts just one field to order by. Adding second field raises error: “Error (Data mining): Only one order expression is allowed for TOP expression at line 1, column 1”
- COUNT, SUM does not work
- WHERE clause works
- ORDER BY <number> does not ORDER, but no error
- JOINS appear not to work
- LIKE does not work
- string functions like LEFT do not work
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.
Bellow are examples of 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.
Examples how to get metadata
— List of cubes in current database
SELECT CUBE_NAME
, BASE_CUBE_NAME — For perspective this will show source cube name
FROM $system.MDSCHEMA_CUBES
WHERE CUBE_SOURCE = 1 — Just cubes, =2 – dimensions.
— Show dimension in one cube or perspective
SELECT DIMENSION_ORDINAL
, DIMENSION_NAME
, DIMENSION_CARDINALITY
, DEFAULT_HIERARCHY
FROM $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE CUBE_NAME = ‘Direct Sales’ — Perspective name
ORDER BY DIMENSION_ORDINAL
— Show measuregroups in one cube or perspective
SELECT CUBE_NAME, MEASUREGROUP_NAME
FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS
WHERE CUBE_NAME = ‘Direct Sales’
— Show dimensions related to one measure group
SELECT CUBE_NAME, MEASUREGROUP_NAME, [DIMENSION_UNIQUE_NAME]
, DIMENSION_IS_VISIBLE
, DIMENSION_GRANULARITY
FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE CUBE_NAME = ‘Direct Sales’
AND MEASUREGROUP_NAME = ‘Internet Customers’
— Show available KPIs
SELECT CUBE_NAME, MEASUREGROUP_NAME, KPI_NAME
, KPI_DESCRIPTION
, KPI_VALUE
— , KPI_GOAL , KPI_STATUS , KPI_TREND — etc
FROM $SYSTEM.MDSCHEMA_KPIS
WHERE CUBE_NAME = ‘Direct Sales’
— List of hierarchies in one dimension
SELECT [DIMENSION_UNIQUE_NAME]
, HIERARCHY_NAME
, [DEFAULT_MEMBER]
FROM $SYSTEM.MDSCHEMA_HIERARCHIES
WHERE [DIMENSION_UNIQUE_NAME] = ‘[Account]’ — Dimension unique name (should be in []’
AND CUBE_NAME = ‘$Account’ — Dimension could be used in multiple cubes. This enforces just dimension cube
ORDER BY HIERARCHY_ORDINAL
Examples how to query data
You can also write queries to get actual dimension members from dimension or fact records from measure group. I found that performance on measure group queries was quite slow.
So more examples:
— SELECT members from Account dimension
SELECT * FROM [$Account].[$Account]
–SELECT records from [Exchange Rates] measure group in [Direct Sales] perspective
SELECT * FROM [Direct Sales].[Exchange Rates]
These examples should give you good idea what kind of metadata and data is now accessible.
Posted in SSAS, SSAS 2008 - Katmai | 6 Comments »
December 4th, 2007 at 8:20 am
dear mr. matelis,
i really enjoy your posts and i was wondering whether it would be possible to get an rss feed for them? do you publish somewhere else? i see this site only enables me to rss other subjects and not necessarily your blog.
with thanks in advance,
ella
December 4th, 2007 at 8:33 am
Ella,
I am suprised that you have problems subscribing to my blog. I have RSS and Atom subscriptions available. On my PC IE7 reconginzes available RSS and shows option to subscribe.
Can you try using direct link?
RSS2:
http://www.ssas-info.com/VidasMatelisBlog/?feed=rss2
Atom:
http://www.ssas-info.com/VidasMatelisBlog/feed/atom
If that does not work, please let me know, I’ll check what else I can do.
Thanks
December 27th, 2007 at 3:10 am
What about caculations?
in SQL 2005 it is not easy even by using AMO!
June 6th, 2008 at 3:47 pm
This is really helpful, thank you!
July 21st, 2011 at 6:36 am
Great, just what i needed…
November 8th, 2011 at 4:00 am
Vitas,
This is great info. Do these DMVs also provide Size Information for Cubes?
Thanks lots.