I should have posted something on this a long time ago as it is a common error that people experience. This error shows up in two different scenarios.
In one case it is when using Excel to connect to a workbook:
The reason you get this error is that the version independent progid for MSOLAP is set to MSOLAP.4 (SQL 2008) (MSOLAP is the oledb library for Analysis Services also sometimes referred to as ASOLEDB). This happens usually for 1 of 2 reasons:
1) You never installed the newer libraries or
2) You installed them but after installing them, you installed Office 2010 (or some other product which brings with it the 2008 libraries). There was a bug in 2008 libraries that would incorrectly overwrite the version independent progid even if it was at a later version (ie MSOLAP.5).
The other time you might see this error is in a ULS log after getting a ubiquitous error about failing to connect to external connection ‘Data’ in XLViewer (Excel Service UI page). This is caused by the same issue:
1) You never installed the libraries (http://msdn.microsoft.com/en-us/library/ee210608.aspx) on the ECS box (you have a mutli-machine farm and PowerPivot was not installed on all of the ECS machines)
2) After installing SharePoint (and configuring PowerPivot) you installed Office 2010 on the server which overwrote the version independent progid. This is more common here than in case #1 as people build single box demo machines.
In both cases, you can verify by looking in the registry
If you look at HKEY_CLASSES_ROOT\MSOLAP\CurVer you should see MSOLAP.5 (this is SQL 2012). If you see MSOLAP.4, look to see if MSOLAP.5 exists as a registered class (you can see it in the screen shot). If it does, and you want to “fix it quick”, you can simply update the CurVer “(Default)” value and set it to MSOLAP.5 and also update the associated HKEY_CLASSES_ROOT\MSOLAP\CLSID and set it to the same value as HKEY_CLASSES_ROOT\MSOLAP.5\CLSID. Version Independent Progids are just a way to discover the last version of a component when trying to create it (you can read more online if you want). If you don’t have .5 installed or don’t feel comfortable with what I described above, you can simply re-install the SQL 2012 version of MSOLAP (this page (http://msdn.microsoft.com/en-us/library/ee210608.aspx) should have a link on where to install from).
SIDENOTE: SQL Server 2008 R2 was a “dot” release (10.5) and so the version of MSOLAP which shipped with it, 10.5, was still called MSOLAP.4 (and that version supported PowerPivot datasouces). Hence, it is possible your system is working fine with MSOLAP.4 (although I doubt you are reading this blog post if you system is running fine). To feel really confident about what version of MSOLAP.4 is installed on the machine, I always simply look at the version associated with the dll in the Program Files (or Program Files(x86) directory). 10.0.xxxx.x is SQL 2008. 10.5.xxxx.x is SQL 2008 R2. Like I said, I don’t think this really will affect people reading this post, but I add it for information
Got enough BI Appliance info yet? Here are some way cool videos. I found them in a nice Technet blog posting that Dana Kaufman did (http://blogs.technet.com/b/dataplatforminsider/archive/2011/01/18/hp-business-decision-appliance-a-closer-look-at-backup-and-availability-features.aspx).
A demo of what’s in the box . . .
Nice demo of PowerPivot running on the new BI Appliance
Well, it is past midnight, East Coast time, so I can finally, and I mean FINALLY, talk about a new product that Microsoft and HP have been jointly developing for many months now. Welcome to a new PowerPivot world – a world with the first PowerPivot Appliance.
So what is it? . . . I’ll give my take as one of the lead architects of the product. It is PowerPivot-in-a-box. It is PowerPivot on a quality, server-class machine with the following hardware and software:
- Hardware: HP ProLiant DL360 G7 (X5650 processor, 12 cores) with 96GB memory, 8 internal 300GB SFF SAS disks (total 2.4TB raw storage), with support services.
- Software: Windows 2008 R2, SharePoint 2010, SQL Server 2008 R2, and PowerPivot for SharePoint 2010
- During installation, we’ve automated the complete Windows, SharePoint, SQL Server and PowerPivot installations – you answer a few questions and off we go. Come back in a few minutes and you have a complete, tuned, PowerPivot server all up and running. We’ve tailored SharePoint and the SQL RDBMS for the hardware. We’ve tuned the backup and restore facility for the hardware. Everything is ready to go. This makes the provisioning of the machine much more straightforward than if you had purchased the component parts individually and installed them yourself.
Just download and install the PowerPivot for Excel add-in into your Excel 2010 desktop machines, point your browser to the appliance – and off you go.
I don’t normally go for marketing speak, but the IDC white paper explains it best:
“The HP Business Decision Appliance solves many of the challenges commonly associated with implementing BI solutions. For the small-to-medium-sized business that has little to no IT infrastructure, an HP Business Decision Appliance can provide powerful BI functionality without the need to hire an army of consultants to make it ready for use. For larger organizations, an HP Business Decision Appliance can provide a complete production environment for users sooner than IT has the manpower to construct a comparable configuration in piecemeal fashion. Although users are responsible for defining data connections and producing workbooks, IT still has the ability to monitor and manage the overall environment.
For organizations having no prior experience with Microsoft technologies, the HP Business Decision Appliance allows IT to implement a working solution with confidence that the hardware and software will work together correctly from day one. Even when IT has extensive experience with Microsoft, the appliance saves time and costs associated with the design, acquisition, and implementation of a high-performing BI environment.
The HP Business Decision Appliance is beneficial for consultants as well. For many consultants, a proof-of-concept is often the beginning of a long-term relationship with an organization. Even with a proof-of-concept project, consultants often spend the first two days performing installation and configuration tasks that are necessary, but provide no value. By using the appliance, consultants can start building out SharePoint sites and connecting to data sources on the first day of engagement, thereby engaging with the client organization sooner in the process of solution development.
No matter the size of an organization or the level of experience with Microsoft technologies, the HP Business Decision Appliance has a positive impact on the business in several ways:
- IT reduces costs and risks associated with implementation, saving 1-6 months of research and testing.
- Users start integrating and analyzing business data on the first day with minimal IT involvement.
- Users work with familiar Microsoft technologies to create, share, and secure powerful insights.
- IT uses an integrated management dashboard to easily monitor usage and server health.”
Now is that cool or what? All of this for a single low price from HP. You get the best of all worlds.
Have any questions? What to know the internal details? Feel free to ask them below as comments and I’d be glad to answer them for you.
Enjoy. This is such a cool product.
So we finally have a product, the codename “Dallas” product is now announced and shipping (or whatever that means as a cloud-based application). I’ll let you read up on the product itself http://www.microsoft.com/windowsazure/marketplace/ – what I want to comment on is what it means to PowerPivot.
First, PowerPivot is right there on-stage with DataMarket. It is the primary end-user delivery vehicle for DataMarket data feeds. So much so, take a look at this screen shot of the PowerPivot for Excel download page:
Continue reading: New Windows Azure Marketplace announced: DataMarket
Updated 28-Oct-2010: Some folks are reporting that an iisreset is needed to make the updates visible to SharePoint.
So . . . here I am playing around with PowerPivot to get ready for a demo. As this is a group from the SharePoint dev team (actually SharePoint Online), I wanted to include some cool SharePoint functionality into the demo. Trying to be cool and ‘wow’ them, I decide to use a SharePoint list as a data source. I want to show the data mashup capabilities of PowerPivot so I have the bulk of my demo come from the Contoso sample database (the three product catalog tables) and the 4 million row Sales Fact table.
I create my SP list by extracting the 11 distinct manufacturers – and I assign them to a ‘shipper’ that I made up.
Continue reading: Using a SharePoint list as a data source
I always am on the look out for cool applications that use data feeds. Personally I think that it is by far the most interesting new concept in data interchangeability in many years.
So I am very sensitive and want to ensure that users become knowledgeable of its ‘quirks’ as well. Here is a good one. Recently I had an email sent to me from a user who was reporting that Reporting Services as returning a very difficult error to understand when he used a data feed. The error is:
Unable to obtain schema for data feed ‘<name>’. Please make sure this feed exists.
Here is a screen shot:
Continue reading: Data feeds: Cannot get schema? But it is there . . .
So you’ve got a bunch of XML data. How do you get it into PowerPivot? Various ideas:
- Import it into Excel using its normal data input capabilities. Once you’ve got the data into a worksheet, you have two options depending on if you are going to modify it later on. If the data is dynamic, i.e. you might change it by-hand later on, then mark the range and convert it into a table. Once you have a table, you can create a linked table with PowerPivot. The advantage is that you can go back and edit the data and PowerPivot will re-import the data on the change. However, a far simplier approach is an option if the data is static and read-only. If this is the case then there is no need to link a table. Once you have the data imported, then just cut-and-paste it directly into PowerPivot – and you’ve got a new table. From here you can create relationships and start integrating the data. The only problem, in both of cases, is that the data cannot be refreshed.
- Import the data into SQL Server and from there load it into PowerPivot. In this case the data may be refreshable depending on the format and source. This is a common IT function, typically solved via XML datatypes, and SSIS – so a DBA should be able to help you.
- Use SSRS (Reporting Services). Once you have a report on top of the data, then you can export it as a data feed to PowerPivot. So long as the report can render any updated data, this means that the information is fully refreshable by PowerPivot using its automated data refresh facility.
Hope this helps.
So I am playing around with the PowerPivot add-in constructing a new sample application. I’ve got the data and I am inserting pivot tables in my worksheets. All of a sudden I noticed that the field list isn’t the PowerPivot field list – it is the Excel field list. What happened? (I never did figure it out) – but more importantly how do I get it back?
Trick: In Excel, click on your pivot table data. Most of the time the PowerPivot field list comes back. If that doesn’t then go to the Excel ribbon, select the ‘PowerPivot’ tab, and click on the ‘Field List’ button. Now you should see it.
Recently I was making a pass through my old emails and I noticed that several folks have been asking me about PowerPivot and Windows 7. As you know – and I hope that everyone knows – PowerPivot is *NOT* supported on Windows 7. We have very specific prerequisite requirements – and running on a Windows Server OS is one of our requirements. However, from time to time, customers ask about using Windows 7 (even if it is not supported). I went back in my old files and I found this very interesting posting from Stacia Misner who explained her experiences.
I don’t know how long Stacia ran with Windows 7; nor how much of PowerPivot and SharePoint worked (all or just some). In either case, I thought the general community might be interested in her experiences.
I recently ran into this in scanning through some forum problem reports. Typically this health rule violation is reported on a large farm installation. The health rule reports the app servers that do not have PowerPivot installed on them as being out of date. Can you read “bug?” Yes, this is one. You can either safely ignore the error report or you can disable the health rule. The problem is solved in CU3.
As always, sorry for the inconvenience. We should have done a better job in our error reporting.