Kasper de Jonge PowerPivot Blog

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

The file that you selected could not be found. Check the spelling of the file name and verify that the location is correct.

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:

• PowerPivot for SharePoint: User Identity Fundamentals for Server Administrators
• PowerPivot for SharePoint: Security Context of PowerPivot Connections in a Farm
• PowerPivot for SharePoint: Data Refresh
• PowerPivot for SharePoint: Using Workbooks as a Data Source

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.

Just a quick post to let you know that you don’t have to look for the SharePoint Add-in in the RTM version of SharePoint and SQL Server 2008 R2. This is now integrated into the SharePoint prereq installation:

Very good from Microsoft to make this step Although i have installed the RTM on my PC I didn’t notice it until someone asked it on twitter, i found the answer on this blogpost:  http://whitepages.sfintelligence.com/?p=98

At the excellent linkedin discussion on PowerPivot, someone suggested that we might make review of a PowerPivot file possible. I answered we can because the PowerPivot gallery is a document library and we can manage approval workflows on this gallery. But i also wanted to check it out how I can do this.

In this blog post i’ll show you how to configure a PowerPivot gallery so that all new PowerPivot workbooks published to a PowerPivot gallery are reviewed by IT. I used a blog post i found which explains howto configure approval workflows step by step.

We start at our PowerPivot Gallery:

To enable workflows we go to Workflow settings in the library tools:

Select the current library and click add new workflow, we now get to our workflow settings:

We want to use the Approcal – SharePoint 2010 workflow, set a appropriate name  ”PowerPivot approval”, we want a new task to be created at the reviewers task pane for every document that is added to the library. Next, we define the history list for the workflow. History lists are special logs which monitor the execution of the workflow. At each step of the workflow, messages at written to the log upon execution of the step. I want the workflow only to be executed when i upload a new file to the gallery:

We now can set up who the approvers are responsible of reviewing the PowerPivot workbooks, the settings are pretty straightforward.

Assign approvers, enter a request they will see at the workflow. The other settings aren’t necessary to complete the workflow.

We now can save our workflow and it will be in effect immediately.

When i now upload a new document to my PowerPivot gallery, we can see that the PowerPivot approval is in progress:

When i would look at my tasks lists within the same site collection i would see my new PowerPivot sheet is awaiting my review:

And we can open this workflow and comment on it, we can also open the document to look inside it:

We just approve my workbook now. When we check back at the gallery to see our workbook is approved:

So using workflow we can make an additional step in our Managed self service BI, and it is very easy to set up.

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 was playing around with my new SharePoint machine and when i openend the PowerPivot gallery i asked myself how can we manage the sort order of the powerpivot sheet ourselves. Luckily the PowerPivot gallery is a just a regular SharePoint list.

This is the gallery now: 

when you look at the list properties you see:

It will be sorted on the modified date. What we are going to do is add a new column to the list where we are going to sort on, click create column:

And add a new numeric column:

We have some default views available:

The All documents view shows us the list as a traditional list, click the modify view and select the sortorder to show up in the view. You now can edit the properties of an item and set the sortorder:

When you go back to the Gallery view, click modify view again and select the sortorder as column to sort on, this gives you your own way of sorting the gallery:

Found this post at  Dan English’s BI Blog about SharePoint 2010, he found these intresting artikels at at  the MSDN Downloads:

Discusses the business intelligence tools available in SharePoint Server 2010

• Getting started with business intelligence in SharePoint Server 2010 (download site link)

This one is a poster, so feel free to blow it up and put it up on your wall;)

Interested in what the upgrade path to SharePoint 2010 will look like?  Take a look at this Visio diagram – Microsoft SharePoint Server 2010 Upgrade Planning.