Report Portal

About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

Search

blank

Katmai Analysis Services 2008 November CTP5 – tests on metadata rowsets

November 21st, 2007 by Vidas Matelis

In 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 »

6 Responses

  1. Ella Says:

    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

  2. Vidas Matelis Says:

    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

  3. Asaf Says:

    What about caculations?
    in SQL 2005 it is not easy even by using AMO!

  4. Pankaj Says:

    This is really helpful, thank you!

  5. Hennie Says:

    Great, just what i needed…

  6. KW Says:

    Vitas,

    This is great info. Do these DMVs also provide Size Information for Cubes?

    Thanks lots.