Konstantin Gorakine's BI Lingual Blog

Just another WordPress.com site

List all cubes’ Dimensions, Hierarchies and Current Members

leave a comment »

I needed to list all dimensions and their current members to see the overall picture of my cube context and autoexists behavior. I used Chris Webb’s query, but strangely enough, it was running longer than expected. So, here is my version of it that shows me all info that I need.

with

 

member [Dim Rank] as rank([Customer].[Customer].CurrentMember,

                          [Customer].[Customer].Members) – 1

member [Dimension Name] as dimensions([Dim Rank]).Dimension_Unique_Name

member [Hierarchy Name] as dimensions([Dim Rank]).UniqueName

member [Current Member Name] as dimensions([Dim Rank]).CurrentMember.UniqueName

 

select {[Dim Rank], [Dimension Name], [Hierarchy Name], [Current Member Name]} on 0,

head([Customer].[Customer].Members, dimensions.count) on 1

from [Adventure Works]

 

where [Date].[Fiscal Year].&[2002]

 

Here is the result of the query

 
Advertisements

Written by Konstantin Gorakine

June 17, 2009 at 12:10 pm

Posted in MDX

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: