This is one of the two main errors that users could see from Excel Services when using PowerPivot. This is encountered when refreshing PowerPivot data connections or performing an action which requires re-querying the PowerPivot database, such as clicking on a slicer or expanding a node in a pivot table. To debug, some level of understanding of what Excel Services is doing is required. For this, I recommend reading an earlier post on this blog by Dave … http://powerpivotgeek.com/2009/12/11/excel-services-delegation/. In general, this is an add-on to Dave’s post which is a quick summary of how to debug this error.
|
||||||
|
Who is this mystery man?
Click on the icon to find out.  |
If there is any error trying to load the bubble chart data for the dashboard, instead of getting a page load failure, the bubble chart simply displays a red X. Whenever you get a red X in the bubble chart there will be an associated log entry in the ULS log on the machine running the Central Admin site. It will look something like this: SSAS Mid-Tier Service Administration 106 High Error loading history for workbook history bubble chart SSAS Mid-Tier Service Administration 99 High EXCEPTION: Microsoft.AnalysisServices.AdomdClient.AdomdUnknownResponseException: Unsupported data format : application/vnd.ms-excel.12
The key string that you are looking for is “Error loading history for workbook history bubble chart”. The exception causing the error should immediately follow that entry for logging on that thread and should hopefully give you some indication as to why the chart didn’t load (it is what I look at). In the majority of cases I have seen, the issue is simply not having the latest version of the ADOMD.Net client library and all you need to do is upgrade your version of the library to that of SQL Server 2008 R2 and the error will go away. Our code is actually meant to provide a user friendly error when SQL Server 2008 R2 Analaysis Services ADOMD.Net library is not installed on the server hosting SharePoint’s Central Administration site. However, as of the most recent RC build (and so most probably the RTM build), SharePoint is installing ADOMD.Net from an earlier version of SQL Server and so this user friendly error is not always coming through and users might simply get a red X. To know if that is what you are hitting, you can either look in the Control Panel’s Programs List and see if the SQL Server 2008 R2 Analysis Services ADOMD.Net library is installed (look for a version number of 10.50.XXXX.X … I don’t know what the final RTM version will be) or you can look in the ULS log. The above (cryptic) example is what you will see if you have an older version of Analysis Services ADOMD.Net client library installed on your machine. If you have installed PowerPivot for SharePoint on the same machine as Central Admin, then we will have automatically installed the latest bits of ADOMD.Net and so this would not be the root issue. I am not sure what other errors would be “expected” misconfigurations, but as I(we) find them I will try to add them. HTH Lee This error is another in a series of common errors that users can hit during seemingly normal scenarios that might be a little bit difficult to interpret at first. We actually did work to try and make the error descriptive, but to the new user, it may still be cryptic. This error occurs when you chose the “Manage PowerPivot Data Refresh” option of an xlsx file which does not contain any embedded PowerPivot data. Sometimes when you look at the timer jobs associated with the PowerPivot service application you see these weird entries:
So where do they come from and what do they do? Ans: It just so happens that one of the little known aspects of PowerPivot is that we have written a series of health rules that scan the SharePoint system looking for unusual circumstances. For example, we try to identify machines with the wrong version of various dlls on them, such as ECS machines that haven’t upgraded their msolap oledb provider to PowerPivot’s R2 version, or WFEs that don’t have ADOMD.NET installed on them (which we need to generate the PowerPivot Mgmt Dashboard, things like that . . . These timer jobs come from that subsystem. Don’t panic — it is just your friendly local SharePoint system trying to keep everyone well and working happily along. Enjoy. In scanning through some recent discussions on the PowerPivot forums, I noticed this very nice posting by Marco Russo (http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx) . I strongly recommend it. The only additional point I would like to make is that the RTM version of PowerPivot has an additional check on the in-memory database when saving to disk. The CTP3 (and earlier) versions of the add-in allowed unlimited memory use. With CTP3, this means that users could create very large files; we’ve seen some as large as 15 or 20GB. Unfortunately SharePoint has a 2GB maximum file upload size – thus it was easy for users to create datasets that could not be shared with other users. In RTM, we now check to see if in-memory database use exceeds 4GB. If so, we won’t allow the in-memory database to be saved to disk. With the 2:1 compression ratio that we normally see when building the ‘blob’ that is stored in the workbook, this gets the file size under the SharePoint 2GB limit. << BTW: and before, the geeks out there ask the obvious question, “Why does SharePoint have this limit if RBS and other technology allows >2GB blobs to be stored in SQL Server?” The answer is that unfortunately the internal file offsets in SharePoint are still 32-bit signed integers – thus the 2GB limit regardless of the storage mechanism used. This architectural limit will likely be with SharePoint for a long, long time >> Enjoy. Good job, Marco! The Excel 2010 blog just posted an interesting article on the management aspects of PowerPivot – more of a “Why should I care?” kind of article, see (http://blogs.msdn.com/excel/archive/2010/02/01/management-features-of-powerpivot-for-sharepoint.aspx). It shows how PowerPivot helps IT answer:
All in all, it is an interesting read. Enjoy! I get this question a lot and I thought that it was a good one to address here in the blog. If you look at the PowerPivot for SharePoint architecture, you know that we use several SQL Server 2008 R2 components within the architecture, but how about SharePoint itself . . . What RDBMS does PowerPivot with SharePoint 2010 use? and does it need to be R2? The answer is that we use the farm’s regular SharePoint RDBMS database instance. We do NOT need SQL 2008 R2 relational engine support for the SharePoint farm. SharePoint 2010 requires 64-bit SQL Server 2005 SP3 or later (yes, 64-bit is required), see http://technet.microsoft.com/en-us/library/cc262485(office.14).aspx for more details. And PowerPivot lives quite happily in that environment. But you know the answer cannot be quite that straightforward as just that, so here is a more in-depth answer. When you install PowerPivot using the EXISTING FARM option, we simply take whatever RDBMS is configured with the existing SharePoint Farm. This can be SQL Server 2005 SP2 or later. We ensure that any resources that we use on that farm, such as the RDBMS database that each PowerPivot service application creates, is done so in such a way that we are compatible with SQL Server 2005. Our stored procedures use 2005 syntax; we don’t use any 2008 TSQL commands or syntax. But for NEW FARM, we do lay down a SQL Server 2008 R2 RDBMS. Remember that for NEW FARM, then scenario is a single machine farm and that farm is going to be created and configured automatically by the SQL Server 2008 R2 setup. You don’t have to do anything on the SharePoint side – we do everything for you. Given that you have nothing on the server, and we are inside an R2 setup environment, the RDBMS that we lay down and configure is R2 (surprise, surprise, surprise). Thus in this environment (NEW FARM), you do get an R2 RDBMS. The second aspect to this question (oriented to data refresh) is: What RDBMS does PowerPivot with SharePoint 2010 pull data from? and does it need to be R2? The answer to this is that data refresh will pull data from whatever provider is specified on the connect string and what versions of its data source that it supports. As the data provider is being called in-process from the AS engine, you need 64-bit versions of the providers and ODBC drivers. If using SQL Server, this typically means the SQL Native Client, which means PowerPivot data refresh pulls data from SQL Server 2000, 2005, 2008 or 2008 R2 – 32-bit or 64-bit (like most providers, the ‘bit-ness’ of the SQL Native Client does not dictate the ‘bit-ness’ of the data source itself). If using other providers, take a look at the versions of their software that they connect to and support. For example the Oracle provider might only support V9i or earlier. BTW: So far all of this topic was about how PowerPivot connects to data outside itself. Internally all of PowerPivot components are 64-bit SQL Server 2008 R2 versions: the web services are obviously R2, but so are the msolap OLEDB provider, ADOMD.NET, AMO, the SSAS engine (in vertipaq mode), and everything else. For those who I haven’t had a chance to meet at SQL PASS, the SharePoint 2010 Conference, the PNWSQL Users group, or countless other SSAS conferences over the years, I just posted a photo of myself on the About page. (photo taken by my beautiful daughter Jeannie with her new Nikon D5000 12.3 MP DX DLSR and a Sigma 50mm F/1.4 EX DG HSM lens that her father got her this Christmas) Enjoy. _-_-_ Dave A number of people have had issues with PowerPivot installation failures in CTP3. Hopefully, we have resolved all of these for RTM, but I can tell you that setup is still a bit tricky. To give you something of an understanding, the “New Farm” installation option is responsible for getting your machine up and running with as few steps from you as possible. It installs SQL Relational Engine & AS, then configures SharePoint using the newly installed Relational Engine, then configures AS integration with SharePoint. In can be especially tricky in the case of patching and so forth since technically speaking SQL Server does not have to be running to be patched but we cannot interact with SharePoint if SQL Server is not running. We have done a lot of work to try and get this right, but I am not foolish enough to think that there will be no issues. Any issues you hit should be reported to the appropriate forums and perhaps followed up with CSS if necessary, but to get you started on installation issues, this is what you want to look at: In the %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log you will see a list of folders with Date-like names. Search inside them for a file called detail.txt (easiest way is probably “dir /s /b detail.txt”). You want to go to the most recent directory with this file and open up this file and search for the string ”Running Action: ASSPI_Install_ASSPIInstallStartupFinalize”. This is the beginning of the section in which we do our configuration of SharePoint with Analysis Services. I will admit that the logging has not been “scrubbed” so grammatical errors might abound, however you should be able to glean some potentially useful information from it. I tend to look for exceptions with stacks. Even if you can’t figure out what the error means, it will be useful to include in any “help” postings and might get you faster turnaround. Please make sure, even if you figure out what is happening and are able to “workaround” it, that you report any issues so that we can continue to improve the experience. Thanks A number of people have hit this issue and so it deserves a quick walkthrough of why it (unfortunately) can happen and how you fix it.
The error occurs when you try to select a Document Library of type PowerPivot Gallery (sometimes referred to as Report Gallery). After installing Power Pivot for SharePoint, all new site collections you create have the option to create a new document library of this type and if you use the PowerPivot Site template when creating the site collection, we automatically add a library of this type (that is the main thing we do in this template). The ability to add this type of library in all new site collections is there because we defined the library in a feature which is subsequently stapled to all sites. This means it is automatically activated on all site collections. The problem is that the page which is loaded when you click on the library as its default view uses an ASP.Net control whose code behind is found in a dll which is not being found. So when does this happen. Every case I have seen so far, this occurs when the user creates a new Web Application and does not deploy our WebApp solution to the new Web Application. Because of how we reference the classes for our pages, we need to have the associated assembly in the <vdir>\bin directory. This assembly is Microsoft.AnalysisServices.SharePoint.Integration.dll. The dll is “pushed” to the web applications on every WFE in the farm via the SharePoint solution infrastructure. |
|||||
|
Copyright © 2010 PowerPivotGeek, Dave Wickert - All Rights Reserved |
||||||
Recent Comments