Kasper de Jonge PowerPivot Blog

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 !

This screencast shows how you can import data from a reporting services report published to SharePoint into PowerPivot:

This blog post will describe how you I enabled PerformancePoint 2010 on my SharePoint 2010 with PowerPivot integraton where I used the VidasM installaton guide. This is by no way the best way to do this but it enables you to play with PerformancePoint.  I used (and took some quotes) the PerformancePoint team excellent blog post Deploying PerformancePoint 2010 Soup to Nuts as a base (this is the official way to do it).

 We start by creating a site collection:

Set a name, location and choose  Business intelligence center as template:

Site should be created ok:

Now we are going to turn on the PerformancePoint services:
Go to central admin , system settings, manage services on serve.  You need to start the PerformancePoint Services and the Secure Store Service:

Once the Business Intelligence Center has been created, the administrator must create a PerformancePoint service application. This creates the service application proxy that facilitates web service calls between the WFE and the app server, along the Windows Communication Framework.

Go to Application Management, Service Applications , Manage service applications, click new, select performancepoint service application:

Last step is configuring the service app, give it a name and add check the “Add this service application’s proxy to the farm’s default proxy list” and create a new app pool.

Read the entire PerformancePoint team blog post for the details on the why.

When you finished this last step you can start playing with PerformancePoint and publish the dashboards:

In my previous blog posts we made use of a published PowerPivot workbook to use a datasource. In this post we are going to discover what goes on under the hood  when you call a PowerPivot workbook on a SharePoint 2010 server. Thanks to Denny Lee from the PowerPivotTwins for reviewing this post, clearing some things up and providing excellent information.

When I make a connection with the browser to the workbook of a published xlsx by clicking the PowerPivot gallery the following things happen:

• The Excel web access service starts rendering the workbook
• It will connect to Excel Calculation Services which will extract the workbook from the SharePoint content dB
• The workbook is rendered as it was published, whatever slicers that were chosen at the point of publishing will be shown as is, including the data at the point of publishing.
• When you interact with the workbook the following happens:
  • Excel Web Access connects to Excel Calculation services which then connects to the OLE DB Provider for Analysis Services. Because it (MSOLAP) recognizes this as a published workbook, it will then redirect the request to the PowerPivot System Service.
  • PowerPivot System Service opens a request to the Analysis Services Engine Service to get the data to be rendered if the data has already been uploaded.
  • Otherwise, the PowerPivot System Service will obtain the workbook (remember Excel Services has already grabbed it), extract the database from it, find the right Analysis Services Engine Service (based on round robin or health status), and attach it to the Analysis Services Engine Service. You can see this latter action from the SSAS Profiler trace:
    

    <ImageLoad xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine” xmlns:ddl100=”http://schemas.microsoft.com/analysisservices/2008/engine/100” xmlns:ddl200_200=”http://schemas.microsoft.com/analysisservices/2010/engine/200/200” xmlns:ddl100_100=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100“>
    <ddl200_200:ImagePath>C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp\ReadOnlyExclusive-006bc445-9ea7-419d-ab2d-886f33b89a03-BP.xlsx</ddl200_200:ImagePath>
    <ddl200_200:ImageUniqueID>213a3a72-eb2d-4646-aa6d-46d9baf5c070</ddl200_200:ImageUniqueID>
    <ddl200_200:ImageVersion>01/09/2010 20:01:13</ddl200_200:ImageVersion>
    <ddl200_200:ImageUrl>http://sp2010ppdemo/PowerPivot Gallery/BP.xlsx</ddl200_200:ImageUrl>
    <ddl100_100:DbStorageLocation>C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp</ddl100_100:DbStorageLocation>
    <ddl100:ReadWriteMode>ReadOnlyExclusive</ddl100:ReadWriteMode>
    <DatabaseName>BP Sandbox 006bc445-9ea7-419d-ab2d-886f33b89a03</DatabaseName>
    <DatabaseID>006bc445-9ea7-419d-ab2d-886f33b89a03</DatabaseID>
    </ImageLoad>
    <PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
    <LocaleIdentifier>1033</LocaleIdentifier>
    </PropertyList>

    next the cube database is restored from xml files inside the powerpivot data:

    \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp\A45D056461D04A8195CE\MS_8377d9f7-8722-452e-8128-71168e30562b.2.db\Sandbox.492.cub.xml; Size=24KB

    These files are XML so you can open them, this file contains the definition of the cube, what drew my attention was:

    <StorageMode valuens=”ddl200_200″>InMemory </StorageMode>

    Which means the analysis cube itself is being loaded straight into memory We probably can use this to load our own cubes into memory in a future release.

    When the cube is restored the data is then loaded from files within the workbook.
    
    As last step some additional metadata like security is loaded, then the model (The PowerPivot terminology of a cube is model) is available in the Analysis Services Engine Service

• A MDX query is fired to the SSAS cube to return the data:
  

  SELECT {[Measures].[Total last month of year],[Measures].[AVG YTD last year],[Measures].[Trend prev year],[Measures].[Total last month -1 of year],[Measures].[Trend this year],[Measures].[Total same month previous year],[Measures].[Trend 2 years]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[DimPromotion].[EnglishPromotionCategory].[All]},,,INCLUDE_CALC_MEMBERS)}), Hierarchize({DrilldownLevel({[DimPromotion].[EnglishPromotionName].[All]},,,INCLUDE_CALC_MEMBERS)})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM [Sandbox] WHERE ([DimSalesTerritory].[SalesTerritoryRegion].&[Central],[DimDate].[CalendarYear].&[2008]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

  <PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis” xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”><Catalog>BP Sandbox 006bc445-9ea7-419d-ab2d-886f33b89a03</Catalog><Timeout>276</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><MdxMissingMemberMode>Error</MdxMissingMemberMode><DbpropMsmdOptimizeResponse>1</DbpropMsmdOptimizeResponse><LocaleIdentifier>1033</LocaleIdentifier><DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility><DbpropMsmdSubqueries>2</DbpropMsmdSubqueries></PropertyList>

• The data is then put from the Analysis Services Engine Service into the rendered workbook by the excel services. It goes through PowerPivot System Service but it never actually does anything – from this point onwards, its just about directing the traffic.
• Excel Calculation services passes the workbook to the Excel web access layer who passes it on to the browser.
• The PowerPivot enabled Excel worksheet is rendered in the browser with the new interactive data

Sources for this post are:

Article on BOL which explains how the loading happens: Plan for PowerPivot Integration with Excel Services with a great diagram describing the steps:

(Although Denny pointed out: The diagram about Powerpivot System Service getting data form the content database isn’t right. It will get some metadata from the SharePoint SQL database store, but doesn’t actually get the data from there.)
and  the PowerPivotTwins (Denny Lee and Dave Wickert) downloadable Academy Live presentation with in depth server architecture

• Academy Live: A Preview to PowerPivot Server Best Practices.

A few things to keep in mind:

• Data is not refreshed when you call a workbook, only by scheduling a data refresh
• Caching occurs by loading the SSAS database into memory, after a while of not using the app the database is deleted from the SSAS server (so never connect to the database itself, always to the workbook)
• First time a workbook is interacted will be slow, after that fast.
• Caching of  the PowerPivot data of a workbook is for all users not per user (caching at database level)
• For a Excel workbook at Excel Services, caching is at the user level

I hope this gives you more insight in what goes on on the server side of PowerPivot