Chris Webb's BI Blog

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

More on querying dimensions

with 3 comments

Apart from Mosha’s putting a dampener on my excitement (should I really be getting excited by MDX anyway? Is it healthy?) about being able to query a dimension directly, by saying that in the future only database administrators will be able to do it after the next CTP, there was another interesting comment on yesterday’s entry – why is being able to do this any more efficient than doing the following:

SELECT MYDIMENSION.MEMBERS ON 0, {} ON 1 FROM MYCUBE

Hmm, well, good question. To be honest I’d not thought of doing this – I’d always done

SELECT MYDIMENSION.MEMBERS ON 0 FROM MYCUBE

which of course does bring back unwanted data. You learn something new every day! In the future I suppose the first of these queries is going to be the best option available to non-database-admins, but I was curious to see if querying a dimension was any more efficient than querying a cube. Now I’m sure a certain someone out there with intimate knowledge of how the AS engine handles each of these queries will be able to give a definitive answer; in the meantime I fired up Profiler to see if I could see any difference between the two approaches. Unfortunately I don’t have much experience about the data Profiler returns and I have to say that what it did return didn’t seem that relevant, but looking at the durations of queries it seemed that querying a dimension directly was slightly more efficient although the differences were too tiny to be noticeable.

Written by Chris Webb

March 9, 2005 at 10:59 am

Posted in MDX

3 Responses

Subscribe to comments with RSS.

  1. Yes, querying database dimension $DIMENSION is more efficient then querying cube dimensions from cube, although not significantly so. Putting "{} on 1" – is the old trick, which, for example, ADOMD.NET does when it populates certain collections of member properties. Note, however, that there is semantic difference between querying dimension in the cube and database dimension. You may have different naming in the cube, you may have multiple role playing dimensions (which correspond to single database dimension), you may have dimension security etc.

    Nataly

    March 9, 2005 at 6:46 pm

  2. Mosha, could you tell us please, what for reason to turn off this woundeful feature in the AS2K5?

    chtepa

    March 22, 2005 at 10:09 am

  3. Some complications with database dimension permission inheritance rules. Nothing unsolvable, but in AS2K5 it will be enabled to database admins only.

    Mosha

    March 23, 2005 at 4:22 am


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 3,082 other followers

%d bloggers like this: