When Change is bad - Updating Names

Question

So - just had an interesting experience updating my main PowerPivot sheet.  One of our sales people had their name changed over the past month.  I updated the change in our sales database and today when I updated PowerPivot - it basically after many many error boxes stated that it couldn't find Agent Johnson - and froze.  My dashboard has several pivot charts visible, with the tied in data sheets.  I had sales by rep in a couple of reports.  When I tried to refresh, Agent Johnson was not replaced by Agent Smith throughout the reports/charts and PowerPivot did NOT like that it could not find Agent Johnson - hence the error boxes and crash.  "The PivotTable report is Invalid. Try refreshing the data.. "

So, I've gone in manually to every point in the data tables, linked tables - anywhere and in the workbook and physically changed the name from Johnson to Smith. After a refresh, I am still getting - Data could not be retrieved from the external source.  Error message...: Query(13,144) the level '&agent Johnson) object was not found in the cube when the string [SalesPerson].[RepName] & [Agent Johnson] was parsed."

Ok. I can understand that I did not update a table before and hence the error messages and the crash - but even after I've updated the sales database (Source) and I've updated all linked tables and all pivot chart data tables - why am I still getting this error?

Answer 1

We will get back to you on this

Answer 2

Feel free to let me know - anytime now.. when you're going to get back to me on this.

Answer 3

It looks like Excel is hanging on to the fully qualified member name. These can be on rows/columns or in report filters.. or perhaps someone created named sets with these in them?

regards

ash

Answer 4

umm.. Yes.  That's the problem.  If one creates a pivot table from data with historical information and then changes the data from the source, the pivot table basically pukes because it's looking for the old name.  I did manually have to delete several sheets and recreate them to get around this 'hanging'.  The issue is the note to the team that funny little things like changing names - whether updating sales people or stores or territories or what ever you have with your source sales data can have bad consequences to an existing pivot tables/charts.   You may want to set up some sort of best practices/warning, being all helpful like.

Answer 5

Deleting and recreating the sheets is one way to deal with it... were you able to narrow it down to what (PivotTable/slicers/report filters/named sets/etc.) had to be updated? Did you have slicers on the sales people/territory/store name?

 

thx

 

Answer 6

I just posted 'PowerPivot Refresh Issue' describing this same problem.  Has there been a fix released yet?  If not, where do I find the xml documents for the cached slicers, etc.?  I think I'd rather update those files instead of rebuilding my charts.