More on querying dimensions

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:


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


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.

3 thoughts on “More on querying dimensions

  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.

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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