Here's a strange and potentially dangeroug situation in an Excel pivot table
that's connected to an OLAP data source. I hada NAME dimension that has
names linked to the keys in the dimension. For example, the data was:

# Name
1 Smith
2 Jones
3 Bush

In the dimension table, I changed Name #3 from Bush to Gore, and reprocessed
the dimension and cubes.

The pivot table currently has the NAME dimension as a row heading. When I
refresh the pivot table, Name #3 remains Bush. However, if I remove the
NAME dimension from the pivot table and replace it with the same dimension,
it now shows Gore.

Why isn't the name refreshing correctly? I assume that it's cached
somewhere, but shouldn't the REFRESH pivot table button reload all of that?
Is there some way that I can be sure that the pivot table is showing the
correct dimensions in the cube?

Thanks.

Jon Pearce
jpearce (AT) remove_this_dgapartners (DOT) com

Jon,
we have experienced the same astonishing behaviour. The
only way to refresh dimension data seems to be dragging
the dimension out of pivot table and back in.

I guess excel pivot tables are not really supposed to be
saved anyway (the goal seems to be ad hoc / instant
analysis rather than saving report structure).

We have not found a solution to this problem (other that
using BIP or sth similar). What the users really want to
save is the report definition anyway, not necessarily the
data itself.

Greetings Lutz

Lutz - thanks for your confirmation of this strange phenomenon. I was
concerned that I hadn't set the pivot cache settings correctly.

I haven't tried the BIP, but it sounds like it's something that we should
lookinto, particularly if it avoids issues such as this.

Jon

"Lutz Morrien" <lutz.nospam.morrien (AT) ocb (DOT) com> wrote

In a word...no. This behavior is totally dependant upon the PTS bits
provided by Analysis Services, and in this case you are right the values are
being cached. . One thing that you can try is setting Cache Mode to False
in the connection string.

Brian

"Jon Pearce" <jpearce (AT) remove_this (DOT) dgapartners.com> wrote

That doesn't make sense. When cube is re-processed AS notifies PTS to dump
its cache.

tom @ the domain below
www.tomchester.net


"Brian Boon" <bwboon (AT) msn (DOT) com> wrote