So i requested a PowerPivot workbook from SharePoint, what happens?

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 FilesMicrosoft SQL ServerMSAS10_50.GEMINIBIOLAPBackupSandboxesDefaultGeminiServiceAppReadOnlyExclusive-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 FilesMicrosoft SQL ServerMSAS10_50.GEMINIBIOLAPBackupSandboxesDefaultGeminiServiceApp</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 FilesMicrosoft SQL ServerMSAS10_50.GEMINIBIOLAPBackupSandboxesDefaultGeminiServiceAppA45D056461D04A8195CEMS_8377d9f7-8722-452e-8128-71168e30562b.2.dbSandbox.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