Answered by:
Project Server 2013 Olap Data Connection Problem

-
I have created a number or Excel 2013 reports which use OlapPortfolioAnalyser data connection. Typically creating a pivot table. I can select any dimensions and measures and excel works as expected. However if I include the Project list or Resource list it does not. I have tried this in two separate instances but do not have a separate environmnent availabale but would appreciate confirmation or othrewise of the misbehaviour.
Steps are: (Assuming cube is built and permissions set correctly and using Excel 2013 client)
In PWA\Bi\Data connections navigate to cube folder
Double click on OlapPortfolioAnalyser – opens in Excel 2013 (warning about data connections being blocked)
Click Enable
In pivot select project list from pivottable fields
Save file (locally or to sharepoint)
Close file
Now re-open file
Excel displays warning - "We found a problem with some content in "xxx.xslx". Do you want us to try and recover as much as we can? If you trust the source of the workbook, click Yes.
Click Yes
Another warning is displayed (Repairs to xxxx.xslx)
Removed Part: /xl/pivotCache/pivotCacheDefinition1.xml part with XML error. (PivotTable cache) Load error. Line 2, column 0.
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)Click Close - you now see data but pivottable functionality is gone.
Note: this also happens with Resource list data.
How do I resolve this?
Thx
Mike
Mike Mahoney
- Moved by Brian Smith - MSFTMicrosoft employee, Moderator Tuesday, July 23, 2013 4:06 PM Correct forum
Question
Answers
-
Hi Mike, as the OLAP cube reporting feature will only be available for on-premise installations of Project Server 2013 you may wish to re-ask this in the http://social.technet.microsoft.com/Forums/gu-IN/projectserver2010general/threads forum. These forums are not version specific, despite the names - but this one is just for the currently in preview Project Online.
I do however repro the condition you are seeing and am researching if this is a known issue and if there are workarounds.
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page- Marked as answer by Sleekstone Friday, January 04, 2013 11:03 AM
All replies
-
Hi Mike,
this could be related to this documented issue http://support.microsoft.com/kb/2782523/en (although it's not a IRM protected file)
Can you try to disable the protected view and add the document/document location in your trust center settings?
Greetings,
Markus
- Edited by MWaldingerModerator Wednesday, December 19, 2012 7:37 AM format
-
Hi Markus
Thanks for the suggestion unfortunately it has no effect on this problem. It is important to note that Excel 2013 works as expected with cube data so long as it does not include project list or resource list dimensions, which is pretty strange. It would be nice if someone can confirm this finding (or otherwise - in which case it will be a problem in my environment).
rgds and Happy Xmas
Mike
Mike Mahoney
-
-
Hi Mike, as the OLAP cube reporting feature will only be available for on-premise installations of Project Server 2013 you may wish to re-ask this in the http://social.technet.microsoft.com/Forums/gu-IN/projectserver2010general/threads forum. These forums are not version specific, despite the names - but this one is just for the currently in preview Project Online.
I do however repro the condition you are seeing and am researching if this is a known issue and if there are workarounds.
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page- Marked as answer by Sleekstone Friday, January 04, 2013 11:03 AM
-
-
Hi Brian,
have you been able to find the source of this issue or escalate to the proper technical teams?
Please see http://social.technet.microsoft.com/Forums/en-US/projectserver2010general/thread/29dd170e-336d-4912-a534-90058dab6443/ for further details. This issue only occurs with Excel 2013 and only when using the Project List or Resource List dimensions. I.e. when using the Task List dimension, the file saves correctly and can be openend again.
Kind regards,
Adrian
Hi Mike, as the OLAP cube reporting feature will only be available for on-premise installations of Project Server 2013 you may wish to re-ask this in the http://social.technet.microsoft.com/Forums/gu-IN/projectserver2010general/threads forum. These forums are not version specific, despite the names - but this one is just for the currently in preview Project Online.
I do however repro the condition you are seeing and am researching if this is a known issue and if there are workarounds.
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
-
Hi Adrian,
The issue is now under investigation by the Excel team and hopefully I will have more news soon. This appears to be a bug that can affect Analysis Services based PivotTables and not specifically Project Server ones - the Project and Resource list seem to trigger the issue - but the Task list does not. I haven't been able to find any workaround that avoids the bug for the project ones though - If I do find something I'll post back.
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page -
-
Hi Suzanne, still not aware of any workaround but reviewing the bug it does appear that it has been fixed and should be appearing in an future update. As Excel isn't my specialty (I came across this as many users of Project Server use OLAP reporting) I want to ask the right people internally before committing to a specific update. Thanks for your patience.
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page -
-
'Confirmed' is the tricky word Suzanne. It currently looks like it is planned to be in the May Public Update due out May 14th. Things can however change depending on test results etc. - if I hear more I will update this thread.
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page -
Hi all,
I'm experiencing the same issue with our Project Server 2013 OLAP Reporting through Excel 2013. I managed to find a "workaround" to keep existing excel pivot reports alive and working. I did that by saving the excel report in an older version, in my case excel 2003. You do lose some of the more advanced formatting which is available since Excel 2007 as well as any slicers you might have foreseen.Nevertheless the pivot functionality, conditional formatting and data refresh functionalities which my customer really requires, are more important than some advanced formatting.
Looking forward to a real fix though!
Hope this workaround also works for other people.
Best Regards,
Kjell -
-
Hi Brian,
apparently, there are do not seem to be any Excel updates in the May update for Office 2013: http://support.microsoft.com/kb/2847265
Having installed all updates for Excel 2013 on my testing rig, the OLAP issue still occurs.The fix has been moved to a later update, I presume?
-
Hi Adrian, I just reviewed the bug and it is now scheduled for release on July 9th 2013, in the July Public Update. I'm not on the Excel team, but on the Project team so not able to comment on the delay - but sorry this fix didn't get out on time.
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page -
-
While still being broken (there was no explicit mention change for Excel 2013 in the July PU), a workaround has been published by the Excel team that works for me:
Create your PivotTable as usual. Then Right Click on the PivotTable / Click PivotTableOptions / On the Display tab, clear the checkbox labeled “Show Properties in ToolTips.” Of course, this needs to be done before the PivotTable is corrupted.
Source: http://social.technet.microsoft.com/Forums/projectserver/en-US/9a6a76fd-7d93-4283-9838-b874e69a4a80/project-server-2013-olap-data-connection-problem
Kind regards,
Adrian
- Edited by Adrian Sauerbrey Wednesday, July 17, 2013 7:21 PM Typo
-
Thanks for the update and tip Adrian. I'll check the bug status too and see what happened to the fix when I return to the office next week.
Best regards,
Brian
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page -
This appears to be fixed with the 15.0.4517.1004 build Adrian - I am unable to repro now, even with the box checked to show properties in tooltips. This patch level fits with the July Public Update. Click to run users would get this automatically.
Anyone else still seeing that issue with that build?
Best regards,
Brian.
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page -
More research and KB 2817339 appears to have the fix - see http://blogs.technet.com/b/projectsupport/archive/2013/07/30/excel-2013-error-opening-saved-pivottable-reports-from-pwa-bi-center.aspx
As I mention in the blog - the fix will not recover a broken xlsx as the break appeared to be on save rather than open.
Best regards,
Brian
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page -
More research and KB 2817339 appears to have the fix - see http://blogs.technet.com/b/projectsupport/archive/2013/07/30/excel-2013-error-opening-saved-pivottable-reports-from-pwa-bi-center.aspx
As I mention in the blog - the fix will not recover a broken xlsx as the break appeared to be on save rather than open.
Best regards,
Brian
Blog | Facebook | Twitter | Posting is provided "AS IS" with no warranties, and confers no rights.
Project Server TechCenter | Project Developer Center | Project Server Help | Project Product PageUnfortunately the blog post you linked is in error - it reports that the issue is fixed in build 15.0.4517.1001. However I am running 15.0.4517.1504 and still hit the problem. The only workaround is the one post by adrian_rho, above, which solved my problem. Thanks Adrian for the workaround, and to Brian for the great job tracking and assisting people on this issue, even though it is Excel and not Project - much appreciated!
Mike
-
The issue is not fixed and is not related to project server, it is related to excel olap pivot. The insidious aspect is that you might successfully save the document with no mention of any problems, but when you go to open it, it is corrupt. The "recovery" usually turns all your pivot tables and pivot charts into regular charts and simple worksheets of data with no pivot connections, in other words it irretrievably destroys all your work, requiring you to completely recreate the spreadsheet. I believe it occurs if there are too many pivot objects in a single document. Sometimes this will cause excel to crash or create unstable behaviors, but other times it doesn't give any warning, but saving the doc at that point saves a corrupt doc. It's a terrible inconvenience and unfortunately appears that Microsoft is not planning to correct it.
-
The issue is not fixed and is not related to project server, it is related to excel olap pivot. The insidious aspect is that you might successfully save the document with no mention of any problems, but when you go to open it, it is corrupt. The "recovery" usually turns all your pivot tables and pivot charts into regular charts and simple worksheets of data with no pivot connections, in other words it irretrievably destroys all your work, requiring you to completely recreate the spreadsheet. I believe it occurs if there are too many pivot objects in a single document. Sometimes this will cause excel to crash or create unstable behaviors, but other times it doesn't give any warning, but saving the doc at that point saves a corrupt doc. It's a terrible inconvenience and unfortunately appears that Microsoft is not planning to correct it.
Hi Lee,
last year's http://support.microsoft.com/kb/2817339/en-us for Excel fixed the issue. I've been working without OLAP in Excel ever since. Please retry or verify before claiming that it is not fixed yet.
Best regards,
Adrian