posted on Wednesday, January 03, 2007 5:16 PM by marcorusso

Improve measures display in SSAS 2005

I just submitted a suggestion feedback to SQL Server development team.

It is very bad for the user to be forced to see measures under their physical measure groups. There are a lot of reasons for which a customer would like to see measures organized in different folders.

The most important is related to distinct count measures: each time you define one distinct count measure, a new measure group is created. This physical definition has nothing to do with logical grouping of measures.
Display folder is useless, because it defines only a subfolder under the measure groups.

A possible workaround (define only calculated measures as visible items for the end user) has an unacceptable limitation: you loose the ability to drillthrough cells.

A fast workaround could be to enable the drillthrough, offering a way to define for each calculated measures the corresponding cube space to drillthrough.

A better solution would be to decouple the logical presentation of measures from the physical implementation of them.

If you agree with my suggestion, please vote the "Change measures display: decouple them from measure groups to the client" and the "Enable drillthrough on calculated measures" suggestions (follow the links).

If you don't agree.. well, you never had real users really working with distinct count measures! :-)

Comments

# re: Improve measures display in SSAS 2005 @ Friday, January 05, 2007 3:55 PM

There are couple of inaccuracies here:

1. Two distinct count measures cannot share same measure group - but this doesn't mean that new measure group needs to be created per DC measure - they can perfectly share measure group with other measures.

2. You _CAN_ set display folders for real measures easily - this way they will appear the way you want - with hierarchical views etc. Your suggested workaround is not nessesary.

HTH,
Mosha.

mosha

# re: Improve measures display in SSAS 2005 @ Wednesday, January 10, 2007 3:43 AM

I talked with Mosha by mail about the inaccuracies described in his comment and I clarified some aspect of my post. The following is the results of the discussion.

1. I know that a Distinct Count measure can be combined with other "regular" measures in the same measure group. I would refer to cubes with 4-5 Distinct Count measures. Cube editor creates a new measure into a new measure group, by default. This is a best practice anyway, due to performance reason.

2. Yes, I can set display folder for real measures, but I'm not able to display all measures (DCs, regular measures and calculated measures) into the same folder for the end user. Display Folder is a local
hierarchy on the Measure Group - in other words, you cannot detach a real measure from its measure group. The result is that when I have a cube with 4 Distinct Count measures and 1 real measure, with Excel 2007 they are
split into 5 different groups. As I said in my post, the problem of Display Folder is that they are local to the measure group, if they were absolute path into the cube it would be much better. If my customer ask
me to see all 5 measures together with Excel 2007 and not in 4/5 different groups, just as he saw with the same cube with AS2000, how can I answer him? Mosha agreed with me that the only possible workaround, today, is to create calculated measures, but this has some disadvantages, like the fact that you lose the ability to drillthrough.

At the end, I am still convinced of my reasons and of the importance of the features I requested.

marcorusso