-1

Do you have any clues why excel is presenting more dimention members when i add measures to grid?

When there is no measure selected then there is for example 3 members visible, when I add some measure then there is 5 and with next measure there is 6 dimention members visible. All measures are non empty - there are some values. Can it be some excel setting or dimention?

I have not yet checked the mdx query from excel to the cube.

I've found that there is default member when no measure is selected so setting it will give answer for first case. In production env there is plenty of measures and without explicit setting, default is first measure from first group so it can have different nonempty set than other measure when sliced by given dimention.

But I cant figure out why adding second measure to grid also generates larger dimension member list - measures have values on every row.

I've found there can be cache problem in such case.

Excel is not key I think, in SSMS you can reproduce too.

skk
  • 45
  • 5

1 Answers1

2

First case - no measure -> one measure. When no measure is selected there is deafault used by SSAS. Deafault cube measure can be checked by:

SELECT NON EMPTY { [Measures].DefaultMember } ON COLUMNS

Changing default as null = no members in rows:

CREATE MEMBER CURRENTCUBE.MEASURES.UseAsDefaultMeasure AS NULL, VISIBLE = 1;
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER = [Measures].UseAsDefaultMeasure;

Changing as 0= all members in rows:

CREATE MEMBER CURRENTCUBE.MEASURES.UseAsDefaultMeasure AS 0, VISIBLE = 1;
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER = [Measures].UseAsDefaultMeasure;

Or you can set any other measure.

Second case - one measure -> two measures. NON EMPTY is default excel setting in MDX queries so in my case there was overlooked 2 rows without first measure value, so empty members were eliminated. All values are set for second measure so new rows appeared.

So everything works as designed.

skk
  • 45
  • 5