15 июня 2010 г. 21:10
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?
16 июня 2010 г. 15:07МодераторWe will get back to you on this
15 июля 2010 г. 19:24Feel free to let me know - anytime now.. when you're going to get back to me on this.
26 июля 2010 г. 23:07
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?
27 июля 2010 г. 1:11umm.. 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.
27 июля 2010 г. 4:03
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?
20 сентября 2010 г. 17:02
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.