Excel 2003 does not refresh data properly ?

Hi all,

I'm currently having an issue with Excel 2003 pivot table accessing an AS2005 cube.

If we do an update on a dimension attribute (product description) for instance the modification does not show in our pivot table when the user hit the refresh button. To show the modified value of the attribute, the user need to drag the dimension out of the pivot table and drag it back in...

Here are the step to reproduce this issue on adventure works:

- Create a pivot table showing internet sales amount for every product (product description) on rows and every calendar year on columns.

- Change the description of one of the product

- Fully process dimensions and cubes

- hit the refresh button in your Excel pivot table

To show the modified description you need to drag the product description out of the pivot table and drag it back in.

Any help would be appreciated

TIA,

Eric

Any property I need to change in my connection string in order to refresh my dimension data when the user hit the refresh button in Excel 2003 Pivot table?|||We are dealing with the same problem. I would love to know if anyone has found a solution that doesn't involve dragging the field out of the table and then back in.

Kyle
|||

Hi Kyle,

I currently have an open case with Microsoft about this issue, I'll let you know as soon as they find a solution. The problem is they can't reproduce this issue on their side but I face this problem in every installation we do.

Currently, we are using a macro when the worksheet opens to move the fields out of the pivot table and the bring them back in.

Regards,

|||Thanks for the information Eric. After doing more research, it looks like the issue for us may be that we are using integer surrogate keys that can change every time the star schema tables are reloaded. So when the Excel PivotTable is initially built, the key for one dimension member may be 100, and when the data is reloaded the same member may end up with a key value of 105. Then, when the PivotTable is refreshed, it confuses the data between the dimension members with keys 100 and 105.

I'll be interested to hear what the solution is for you, and maybe you could say whether or not surrogate keys or changing dimension member keys would be possible in your environment.