OLAP - EXCEL problem - urgent

Hello,

I have a dimension hierarchy with 2 levels, like this:

A - X

- Y

- Z

B - U

- V

- T

And I have a cube with the dimension above and one measure (sum).

The fact table of this cube has 2 columns (level2,sumvalue) and i have the values:

Level2 SumValue

X 1

Y 2

T 6

When I access this cube from Excel and use multiple selection weird stuff happends.

So, I create a Pivot table, in which i select only X,Y,U:

Level1

Level2

Total

A

X

1

Y

2

A Total

3

Grand Total

3

If i give a refersh to this pivot table i get :

SumValue

Level1

Level2

Total

A

X

1

Y

2

A Total

3

B

6

Grand Total

9

Why i see the B in the pivot table, if i selected only X,Y,U and U has no value in the fact table? I need an urgent answer because end-users use a lot the refresh option and in this case they see wrong values.

I'm using Microsoft Analysis Server build 9.00.2153 (SP1) and Microsoft Office Excel 2003 (11.6560.6568) SP2. I made the test with Microsoft Analysis Server SP2 and same version of Excel and i got the same result.

Any help will be appreciated.

Thanks,

Vulcano

A couple of things to check, that I can think of:

Is "Visual Totals" still enabled after refresh (ie. "Totals include Hidden Items" is disabled) - otherwise, these results would be expected?|||Thanks Deepak for your answer.

The property of the pivot table "Include hidden members in totals" is disable all the time. So no hidden members are included. In my dimension i don't have hidden members.

I run a Profiler and I discovered something strange:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[T] , [Dimension].[Hierarchy].[Level1].[ V ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ T ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

Why the two queries are different? Why in the second query [Dimension].[Hierarchy].[Level1].[ B ] disappears?

Thanks,

Vulcano|||

The member which disappeared in the 2nd query is: [Dimension].[Hierarchy].[Level1].[ V ] , not [Dimension].[Hierarchy].[Level1].[ B ] (though maybe these are place-holder, not real, names?).

When you look at the drop-down list of members selected in the pivot table, do you find that this missing member is now selected? If so, this could be a side-effect of refreshing the pivot table (though I'm not sure why the refresh would change the selected members).

|||

My mistake Deepak, the queries are wrong, they are missing the A, B Members of the dimension. I post again the queries:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] , [Dimension].[Hierarchy].[Level1].[ B ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[ V ], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

And you are right again: when the first pivot is created the X,Y,U members are selected, after the pivot is refresh the U member is not selected anymore ( so the member selected are X,Y,B). Is this the right behaviour, or maybe is a bug?

Thanks,Vulcano