For your PowerPivot Best Practices, Lessons Learned, and random thoughts!
By powerpivotgeek (dwickert@microsoft.com), on January 29th, 2010
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?
By Lee Graber (leegr@microsoft.com), on January 22nd, 2010
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:
By powerpivotgeek (dwickert@microsoft.com), on December 11th, 2009
I am inspired by a recent post from a colleague about the various issues that can come up with Excel Services delegation (see a Denny Lee’s blog here: http://dennyglee.com/2009/11/18/troubleshooting-powerpivot-excel-services-connectivity/) and I wanted to take it a bit further (and maybe a bit ‘geekie’-er)
First, why is this a problem? After all, as you can see in Denny’s post, you can see the workbook and you even have a thumbnail for it in the Gallery. What’s up here? The core of the problem is that unless you’ve set the connection to refresh when you first open the workbook, Excel Services uses its pivot cache to construct the pivot table and slicers. It is only if you manually refresh the connection, or click on a slicer, that you make an actual connection to the embedded data. Until then you are just looking at cached information. Until you click on a slicer, you don’t really know if Excel Services is working – so a strong recommendation that I make to any person doing a validating their installation is to “ALWAYS CLICK ON A SLICER” if you want to make sure that your installation is working properly.
Please note, we have uploaded in DRAFT form the PowerPivot for SharePoint Installation guides. We have propped this information on the page:
PowerPivotGeek.com Server Install
We encourage you to go to this site, review the documents, and provide feedback on what works (or does not) directly on the PowerPivotGeek.com Server Install page.
There are four guides in the works:
As Dave noted on the PowerPivotGeek.com Server Install page:
Let’s give credit where credit is due. Lots of folks have spent countless hours working on these documents and we should acknowledge them right now:
Principal author: Leon Cyril (Microsoft Tester, PowerPivot development team)
Secondary authors:
- Denny Lee (Microsoft SQLCAT team)
- Dave Wickert (Microsoft Program Manager, PowerPivot development team)
- Kathy MacDonald (Microsoft Program Manager, PowerPivot development team)
Technical reviewers:
- Jim Howey (Microsoft Program Manager, PowerPoint development team)
- Jennifer Chu (Microsoft Service Engineer, SQL IT Support)
- Heidi Steen (Microsoft Technical Writer, SQL UE team)
- Ed Campbell (Consultant, Murphy & Associates)
Do you have other configurations that you would like to see instructions for?
If so, please go to the PowerPivotGeek.com Server Install page and provide your comments there!
Buried in the launching of CTP3/beta2 bits, is this tidbit: “Microsoft Virtual Lab: PowerPivot for Excel 2010”. Basically this facility is a remotely hosted VM with Excel 2010, PowerPivot for Excel and SQL Server 2008 R2 RDBMS. WOW!
You signup for the course and you have about 2 hrs of connect time with the lab. You don’t need anything on your desktop except a browser and an ActiveX control for the terminal server part. Everything is hosted remotely. You don’t have to build a VM; nor managed it; nor worry about cleanup afterwards. After signing up, it takes a minute or so to fire up and – there you are – you are connected to a remote image with all of the software pre-loaded and ready to go. Didn’t finish in an hour, signup for a second one.
You’re on your way to PowerPivot for SharePoint functionality – you’ve uploaded your PowerPivot for Excel workbook to your SharePoint PowerPivot Gallery. You view the thumbnails of your report and they look nice.
From the thumbnail, you click on the report you want to see, and the report renders nicely.
But then you click on a slicer, and then all of a sudden you get an error like the one below.
What can you do?
When you install SharePoint Beta 2 to go with your PowerPivot for SharePoint CTP3 install, some will install SharePoint on a set of servers (or VMs) that do not have internet connectivity.
The first step to installing SharePoint Beta 2 is to run the SharePoint Prerequisite Installer. If you are in a lab environment that does not have internet connectivity, this step will fail because it is trying to download the required files.
Within the installer dialog, it will provide you a link to the installation log. Search for “download” within the file and you can then identify the component and URL it was trying to download from. Copy the URL to a box that has internet connectivity to download that file, place it in a location your lab machine can get access to, run the component, and then you can then proceed with the next step of your installation.
The operational issue here is that there are potentially seven different components that you need to download so going through the step of downloading, running the Prerequisite Installer, running into an error, identifying the component and URL, downloading it, running the component, continuing… is a tad daunting. To help things out, below are the links to the files required for the SharePoint Prerequisite Installer for Windows Server 2008 (they are slightly different for Windows Server 2008 R2 – mainly the first step).
