A lot of times you want your PowerPivot site to have another URL then the one you original got during the install. During installation the it uses the server name as url. In just installed PowerPivot on a local machine in our netwerk called l01, but because we have this running in a separate lab domain it cannot be accessed from outside the domain.
Calling the website from his entire URL does work from outside the domain as well: http://l01.labdomain.nl. Until you actually open the PowerPivot file from the gallerie. You get a
“Excel Web Access
The file that you selected could not be found. Check the spelling of the file name and verify that the location is correct.
Make sure that the file has not been renamed, moved, or deleted.”
The reason is that SharePoint is not fully aware that the site (and all the services like Excel Services on it) you created also has to listen to the other url: “http://l01.labdomain.nl”. You can make SharePoint aware of other URL’s by configuring Alternate Access mapping (AAM) in SharePoint 2010. On technet you can find a great video how to do this:
http://technet.microsoft.com/en-us/sharepoint/ff679917.aspx. Most of the times I use the default alternate access mapping method in stead of extending my site.
A thing might be worth checking out is whether your newly created site or AAM mapping is available from your server console. When this is not the case PowerPivot will be unable to generate thumbnails and you get a red cross. In a few cases i had to resort to changing the registry according to this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;896861
The SQL Server SSAS team just release 4 great video’s where MSFT’s Lee Graber explains the PowerPivot for SharePoint Architecture. This gives great insight on PowerPivot and SharePoint integration. Mind you these videos are not for the weak of hart and go pretty deep 
But well worth the time if you are into SharePoint and PowerPivot:
Update: They are now all available on the PowerPivot Technet site.
We recently created a new web application on our SharePoint farm, we chose claims-based authentication as authentication option. All seemed ok until i wanted to create a report on top of the PowerPivot application in SSRS. I got a error “‘Too many automatic redirections were attempted” in the report builder 3. Searching on the Internet i can across a blog of Dave Wickert that explains that we need to run a PowerPivot web application in classic mode: Why PowerPivot requires ‘classic-mode’ web applications.
Unfortunately we cannot change back classic mode from a claims based web app. So we had to create a new webapplication and copy everything from one site to the other (or create a backup of your sitecollection and restore it in a new web application.
One of the most difficult things when using a multi server farm is the security delegation between the servers. In most cases this still will be done using Kerberos, even though we have Claims based authentication in SP2010 most systems outside the native SharePoint 2010 farm will be using Kerberos (like SSAS and SSRS).
Microsoft has released a great whitepaper that:
This document provides you with the information that will help you:
- Understand the concepts of identity in SharePoint 2010 Products
- Learn how Kerberos authentication plays a critical role in authentication and delegation scenarios
- Identify the situations where Kerberos authentication should be leveraged or may be required in solution designs
- Configure Kerberos authentication end-to-end within your environment including scenarios which leverage various service applications in SharePoint Server
- Test and validate that Kerberos authentication is configured correctly and working as expected
- Find additional tools and resources to help you configure Kerberos authentication in your environment
This document is divided in two major sections:
- Overview of Kerberos Authentication In SharePoint 2010 Products
This section provides conceptual information about managing identity in SharePoint Products, the Kerberos protocol, and how Kerberos authentication plays a key role in SharePoint 2010 Solutions
- Step-By-Step Configuration
This section will walk you through the steps required to configure Kerberos authentication and delegation in various SharePoint solution scenarios.
Download the whitepaper here.
Last week i got a question if they could use a access datasource for PowerPivot and be able to automaticly refresh it. The answer is yes.
I uploaded a Access file to a SharePoint gallery. Openend PowerPivot and started to add the access datasource. The trick is to not use the default URL to the access file but use the SharePoint WebDAV.
When adding the datasource click the browse button and go to your Shared Documents folder and select the access file there. SharePoint will make this kind of connection:
“\\sp2010\DavWWWRoot\Shared Documents\Nwind.accdb”
When you use this as source you can also schedule a refresh in the PowerPivot Gallery.
Update: Dave Wickert wrote a great blog post on potential trouble concerning refresh of a access database, check it out here: http://powerpivotgeek.com/2010/04/02/working-with-the-2010-office-ace-provider/
Since my server has desktop experience turned on i did not have this problem.
I was trying to upload a PowerPivot file of 1 GB to SharePoint, this didn’t work because SharePoint is by default set to maximum of 50 MB. To increase this limit you have to do the following configurations:
Out of the box, SharePoint allows you to upload files up to 50MB in size and Excel Services allows you to view files as large as 10 MB in a browser. Our new farm setup increases the Excel Services limit to 50MB. You can change these settings even further if users will be publishing and interacting with larger files.
To increase Web Application limits:
a. Navigate to SharePoint 2010 Central Administration > Application Management > Manage Web Applications.
b. Select SharePoint-80 and then, on the Ribbon, select General Settings > General Settings.
c. Change the Maximum Upload Size setting to the MB limit you want to set. (The maximum allowed by SharePoint is 2047MB.)
To increase Excel Services limits:
a. Navigate to SharePoint Central Administration > Application Management > Manage Service Applications.
b. Select your Excel Service Application.
c. On the Ribbon, click Manage.
d. Click Trusted File Locations and on the following page, select your trusted location. (Typically, this is listed as http:// in the Address column.)
e. In the Workbook Properties area, set the Maximum Workbook Size property to 2000 and the Maximum Chart or Image Size to 100 MB
Found at the excellent powerpivotgeek blog: http://powerpivotgeek.com/server-installation/single-server-install/
For those who missed it, the launch date of SharePoint was announced. The launch will be done by President of the Microsoft Business Division Stephen Elop as he introduces Office and SharePoint 2010 on May 12, 2010 at 11 a.m. EST.
You can join him live at: http://sharepoint.microsoft.com/businessproductivity/proof/pages/2010-launch-events.aspx#fbid=JadoeNy2pBe
I just can’t wait to get PowerPivot to production !
I recently found a great PowerPivot feature to make the published PowerPivot sheet “view only” for a end user.
When you have write permissions for a PowerPivot sheet you can open the sheet in Excel and use all the measures and columns and use everything available in the sheet by using the “Open in Excel” button:
Now the great news, you can contain these permission to view only, documents can only be viewed sever side:
When you open the PowerPivot sheet in the browser you see the Open in Excel button is gone:
When we open the PowerPivot gallery as a default document library you will not be able to download the Excel sheet, it will render as html when you download it. I think this will be a very usefull permission !
