Insane corruption that I can't resolve

Question

I have been working in a massive project for 2 months and saving the files in dropbox so I have every version.  Despite the relatively small size of my file (20Mb) I have resorted to using a 64-bit Office version on a VM as getting out of memory errors. So all great and going along adding stuff and saving regularly.  Suddenly I find when I reopen the file that was working fine it is trying to automatically insert a new field into the pivot table  and gives and error saying the field already exists. It then goes into melt down running the OLAP query over and over. If I press Esc it says "a pivot table has failed to refesh Continue to refesh all?" I click cancel and it just keeps going. only way out is to kill the excel process.

What is really worrying is I've had this before and fixed it by winding back 1 or 2 versions from Dropbox and carried on. This time I've gone back 15 versions to things that look quite different and it is throwing the same error. I have shutdown the VM and restarted with no effect.  i just can't understand if i load a file that was working perfectly why it is trying to add a field to a pivot table without being asked and why I can't stop the calculation and remove the field.

I also notice that for some reason I have connections to both Pivottable Data and PivottableData 1?

Is it caching this error somehwere so it isn't actually part of the file? As it stands my 2 months of work seems to be completely destroyed as no version now loads. Please help!!!!

Mike

Answer 1

Can you check if your connection to the data source is still good?

In  PowerPivot window, go to Design tab and click on "Existing Connections". Open each connection and test if it still works.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

The problem is I can't interact with the workbook at all once I open it. It opens, does reading data, sticks some stupid item in the pivot table which makes it go mad and then just runs OLAP querty over and over and I can't break it with out ending the excel porcess. I have gone back to a previous day and got one to work but lost everything I worked on.  this happens every few days and driving me mad and if I wasn't using Dropbox I would be finsihed.  What I would like to understand is:

1. Why doesn't restoring aprivious version fix it. Surely the powerpivot object and conenctions are saved alongside the workbook so loading one that worked before shoudl still work?
2. The item it tried to put into the pivot table is a name set of dates. The name set is not part of the pivot when it was saved. Why on earth is it randomly tryingt to add this to a saved pivot when I'm not touching the keyboard.
3. Why do I have 2 conenctions ot my pivotdata in my workbook and shoudl I try and make them al lthe same connection? Is this causing my variety of problems such as out of memory.

Overall while I find pivotpower extremelly powerful my experience has been it is very very unstable with frequent crashes.

just reopened the one I got working and starts with "initialisation of data source failed". I get this quite often and usually I open the powerpivot window, update all tables, refresh all pivots and seems to them work. Is this normal?

I'm taking 2 steps forward, 1 step back at the momnet which is really frustrating and hope you can help to resolve the issues as too far down this project ot start again.

Thanks

Mike

Answer 3

I might have located the issue. The project was orignally built in 32-bit office. In the 32-bit version I have date formats 01 March 2011 or March 2011. I have created some name sets to reverse the dates using these formats.  When I load the project in 64-bit office these date formats don't exsit and appear as 01 March, 2011 and March, 2011.  It is these name sets that are always the offending field being randomly added. I just tried to change all my "powerpivot data connection 1" to "powerpivot data conenction" and when i did so the pivot using this name set as labels disappeared.

So the question in

1. Why are date formats not consistent between 32-bit and 64-bit?
2. Is this why I have 2 connections, one for the stuff I did in 32-bit and a 2nd for 64-bit. If so is this OK or not.
3. Should I change every pivot (about 30 of them) to link to one connection and fix all the name sets? This seems to kill all my slicers so a lot of work if not going to help.

Answer 4

Your 64-bit machine and 32-bit machine probably have different collations - Go to control Panel->Clock, Language and Region->Region and Language->Change the date, time and number format, make sure your date format appear the same on two machines.

PowerPivot workbook always sticks to the collation with the one of the machine that it is first created with.

You should only use 1 connection.

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 5

Ah walked away when it was installing the OS so got US by default. OK fixed that so my dates in the powerpivot window look the same in both 32 and 64-bit. However the pivot tables are stuck at "March, 2012" despite the field being formatted "March 2012" in powerpivot window. Even removing the field and replacing it doesn't change it. How do I get the field format in the pivot table to update?

Also on wider worry i've beenahving this issue with name sets being randomly thrown in and screwing the workbook when I was just using 32-bit so it isn't new problem caused by mismatched date formats. I woud love to understand why loading up an old version doesn't solve my problem?

I have removed all references to Connection1

Thanks Mike

Answer 6

Hi Chu its doing it again. Saved it overnight and it was fine reopen this morning and again its throwing an extra field into the pivot without me touching the keyboard and then I can't break it. Looks like this where @Months Reverse@ wasn't in the pivot when it was saved.  Why can't I break this by hitting escape?  I hit cancel and it just keeps going. I'm so fed up with this as happening daily.

Answer 7

This is getting worse. Rolled back a few versions and now getting this. This workbook was absolutely fine yesterday evening.

============================
Error Message:
============================

Exception from HRESULT: 0x800A03EC
----------------------------

----------------------------
The command was canceled. Please press F1 to get the help topic "Excel Window: PowerPivot Field List" for more details.

============================
Call Stack:
============================


Server stack trace:


Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   at Microsoft.Office.Interop.Excel.CubeField.set_Orientation(XlPivotFieldOrientation RHS)
   at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToFields(String cubeFieldName, XlPivotFieldOrientation pivotOrientation, Int32 positionIndex)
----------------------------
   at Microsoft.AnalysisServices.XLHost.Addin.ExcelInterOpUtil.AddToFields(String cubeFieldName, XlPivotFieldOrientation pivotOrientation, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
----------------------------
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.SetFieldOrientation(PivotArea location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.AddToFieldListInternal(PivotArea location, IGeminiColumn column, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.GeminiPivot.MoveField(IGeminiColumn column, PivotArea source, PivotArea target, Int32 positionIndex)
   at Microsoft.AnalysisServices.XLHost.Addin.FieldListControl.MoveFieldInternal(IGeminiColumn field, PivotArea sourceArea, PivotArea targetArea, Int32 targetIndex)

============================

Answer 8

Hi masplin, 

I am not sure if this problems still bothers you, but I have the same problem with an Excel Powerpivot file, so  any info about it would be appreciated. 

My suspicion is that it is connected to the use of named sets. I use them a lot, and the error of Excel trying to add a field on its own only happens in Pivot tables that use named sets. I am not sure if it happens with one specific set only or with different sets, but it seems to be connected to this. I tried to change the name of set or create it from scratch, but that has not really helped.

At least I found a way to access the file, even when the Olap-Refresh-Loop occurs. I right-click on the Excel symbol in the task bar and select the last option, close all windows. Usually refresh error message appears, but then comes "by ref value cannt be 0" or something like that, and then I can access the file. Opening named sets and then deleting the named set that is in the current pivot table solves the issue and makes the file usable, of course only without this set. I will check if removing the set solves the issue or if it returns with other sets.

Answer 9

My soution was to completely rebuild my VM and reinstall OS and Office & everything from scratch. Then it worked!!!  No idea what the issue was as I'm not using name sets at all.   I think it was some confusion over the data format, but I don't know how that suddenly started.

Answer 10

Ok, glad you found a solution and thanks for your feedback.

Your experience fits to my impression that I could open the file on a different PC without the issue. Maybe there are temp files that can cause this, I have seen MS recommended a  deinstall- delete folder - reinstall of the Powerpivot addin in other cases.  

But didn't you use a named set for some kind of date conversion? My problems happen with simple text fields, but then maybe it is a case of same symptons, but a different cause.... 

Answer 11

I did have some name sets but deleted them as got some wierd stuff going on earlier. so could be an artefact.