Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Querying a dimension not a cube

with 2 comments

A small but significant bit of new functionality in AS2005, pointed out by Ohjoo Kwon on the beta newsgroup: you can now run MDX queries against dimensions as well as cubes. So, for example, you can do the following:

SELECT [MYDIMENSION].MEMBERS ON 0 FROM [$MYDIMENSION]

Why is this useful? Anyone who has written any kind of client interface for AS2K and used the ADOMD catalog object will know how slow it is, and that it’s easier to write queries instead; and similarly in Reporting Services very often you have to write queries to populate lists of valid values for parameters. The problem is that when you run a query on a cube you always have to bring back at least one measure’s worth of data, which is a bit of a waste and potentially time- and resource-consuming. Now you don’t have to bring back any measure data because you can query the dimension instead!

Written by Chris Webb

March 8, 2005 at 9:37 am

Posted in MDX

2 Responses

Subscribe to comments with RSS.

  1. Ability to query dimensions is not available to regular users, but only to database administrators. This is a change from current CTP, so in the next CTP you won\’t be able to use this functionality if you are regular user.

    Mosha

    March 8, 2005 at 6:08 pm

  2. what would be the advantage of this over inserting ", {} on 1" which is what i\’ve seen on other AS blogs?

    Peter

    March 8, 2005 at 6:44 pm


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

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers

%d bloggers like this: