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.
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.
Mosha, could you tell us please, what for reason to turn off this woundeful feature in the AS2K5?
Some complications with database dimension permission inheritance rules. Nothing unsolvable, but in AS2K5 it will be enabled to database admins only.