Kasper de Jonge PowerPivot Blog

yes, the word that any BI or IT pro dreads: Kerberos .. Imagine you want to run Power View in a SharePoint farm on top of a SSAS database running in Tabular mode. You probably want to use security so you can secure the data by the user that is actually running the report using the AS security features.

But most likely you won’t run your AS instance on a machine that is inside of the SharePoint farm. When all of your machines are inside the same SharePoint farm, SharePoint will take care of passing the credentials between the machines. But as soon as one of the machines lives outside of the farm you have to setup Kerberos to make sure the security is correctly passed on between machines. Configuring Kerberos is a lot of (hard) work if Kerberos is not already set up correctly in your organisation.

But there is some good news on this with SQL server 2012. In SQL Server 2012 we introduce the BISM file that allows us to start a Power View report based on the connection information in this BISM file. Whenever a connection is made from Power View to the Tabular database it tries to connect using the credentials of the user that is executing the Power View report. If Kerberos is not configured this connection will fail regardless of him having access.

But there is a fall back scenario, when using the BISM connection to connect to a tabular database ADOMD will give it another try using the execution account the Reporting Services app server is running under and then switch to the actual user who initiated the connection. All you have to do is add the execution account the Reporting Services app server is running under to the administrators of the AS instance and this connection will succeed even without Kerberos is set up. Admin connections are always allowed cross machines, under the covers ADODM uses the effectiveusername connection string parameter to switch to the actual user initiating the connection after connection is being made.

This post is dedicated to Marc Valk my esteemed former colleague with whom we battle the Kerberos beast several times at my previous job .

Hot from the press: SharePoint 2010 SP1 has been released. With it come some great performance gains for PowerPivot in SharePoint.

Check out the details here.

Get SP1 here.

Now that PowerPivot is getting more and more known the IT departments are getting used to the idea they will need to publish data feeds to end users. But how can we do this ? How can we make one central location for this ?

In this blog post we are going to create a data feed on top of my SSAS cube and publish this feed to a central datafeed library.

Read more…

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.