excel msolap powerpivot oledb transports tcptcp ssas server (1) server= ; data source= ; how used:...

Deep Dive on PowerPivot TechnologiesDave WickertPrincipal Program ManagerMicrosoft Corporation

SESSION CODE: BIE401

All three PowerPivot architectural areasClient overall architectureCool ‘in the belly of the beast’ demoSharePoint components

Querying (both from outside the farm and ECS)Data refreshGalleryMgmt Dashboard

Engine overall architecture

Outline

Let us start with the Client

EXCEL

MSOLAP

PowerPivotOLEDB

Transports

TCP

SSASServer

(1) SERVER=<server-name>;DATA SOURCE=<server-name>;How used: regular client-server connectionJust like SQL 2000, SQL 2005, SQL 2008

PivotTable

Traditional OLAP client-server connections

EXCEL

MSOLAP

HTTP

PowerPivotOLEDB

Transports

TCP

(2) =http://<web-server>/mdmdpump.dll;How used: Existing SSAS ‘data pump’ connectionSQL 2000, SQL 2005, SQL 2008

Web

DataPump

SSASServer

PivotTable

Native http connectivity

EXCEL

MSOLAP

HTTP

PowerPivotOLEDB

Transports

TCP

(3) =http://<SP-server>/sales.xlsx;How used: PowerPivot SharePoint connectionNew in PowerPivot

SharePoint Farm

PivotTable

Native http connectivity

EXCEL

MSOLAPPowerPivot Addin

Client add-in

EXCEL


ADOMD.NETAMO

Client add-in accessing data

EXCEL


ADOMD.NETAMO

PowerPivot in-proc engine

In-proc Engine

EXCEL


ADOMD.NETAMO

PowerPivot in-proc engineSERVER=$EMBEDDED$;How used: PowerPivot embedded connectionNew in PowerPivot

HTTP

PowerPivotOLEDB

Transports

TCP

INPROC

PivotTable

In-proc Engine

EXCEL


ADOMD.NETAMO


File Open:• Excel opens file and reads the data in the

workbook to its own memory• If there is a MSOLAP connection that contains

an embedded workbook, Excel streams the data into MSOLAP using a streaming interface.

• Data is loaded into the in-proc engine (there are temporary files written on disk)

File Open

EXCEL


ADOMD.NETAMO


File Save:• User hits ‘Save’• Excel sees that there is an embedded

PowerPivot connection and calls the stream read on MSOLAP

• MSOLAP reads the data from the in-proc engine and streams it back to Excel

• Excel writes the PowerPivot in-proc data to the workbook

File Save

File Format

File Format:• OpenXML• Easy to see what is happening

o Just rename the .xlsx to .zip and browse it• PowerPivot data stored as a single file associated with the embedded

connection• Backup of the SSAS database• Can unzip the file, extract the embedded data to a ‘.abf’ file and do a

restore by-hand if you want on a PowerPivot SSAS instance

A cool “in the belly of the beast” demoDave Wickert

DEMO

SharePoint Farm

EXCEL

MSOLAP

ADOMD.NETAMO

PowerPivot Addin

PowerPivot In-Proc Engine

SSASServer Web

DataPump

SSASServer

PivotTable

So . . . in summary

SharePoint Farm

EXCEL

MSOLAP

ADOMD.NETAMO

PowerPivot Addin

PowerPivot In-Proc Engine

• User does a Save-As against a workbook with SP as the destination

• User does a “Publish to SharePoint”• Webdav file copy (SharePoint site looks like a

file share)• User goes to SharePoint doc lib and does a file

upload

Fastest!

How to files get to SharePoint?

Now let’s switch to the Server

WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server

PSS

AS

PWS

HTTP

PowerPivotOLEDB

Transports

SharePoint Farm

Other PowerPivot-enabled SP Farm(s)

WFE

ExcelDesktop

Application

InternetExplorer

SSASServer

SQL ServerInstance

TCP

CHANNEL

PowerPivotOLEDBProvider

Config DbContent Db(s)PowerPivot Db(s)

On the server-side we are building towards . . .

SharePoint Farm

InternetExplorer

SharePoint as a portal

WFE

SharePoint Farm

InternetExplorer

SQL ServerInstance

Config DbContent Db(s)

SharePoint as a portal

Rendering an Excel Workbook

SharePoint Farm

InternetExplorer

WFE

EWA

EWS

ECS

App Server SharePoint Farm

Application

InternetExplorer

SQL ServerInstance



WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server SharePoint Farm

Application

InternetExplorer

SQL ServerInstance




WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server

HTTP

PowerPivotOLEDB

Transports

SharePoint Farm

Application

InternetExplorer

SSASServer

SQL ServerInstance

TCP




SSASServer

WebServer

WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server

HTTP

PowerPivotOLEDB

Transports

SharePoint Farm


WFEApplication

InternetExplorer

SSASServer

SQL ServerInstance

TCP




Let’s take a step back and look at in-coming PowerPivot requests

WFE

App Server

PSS

AS

PWS

SharePoint FarmExcelDesktop

SQL ServerInstance


PowerPivot on-demand loading and routing

PSS = PowerPivot System Service(aka the “Midtier Service”)

AS in SharePoint integration mode(aka the “Engine Service”) the SSAS service we all know and love

PWS = PowerPivot Web Service

WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server

PowerPivotOLEDB

Transports

SharePoint Farm

Application

InternetExplorer

SQL ServerInstance



Rendering a workbook with embedded PowerPivot data

SERVER=$EMBEDDED$;PowerPivot embedded connection

. . . Or . . .

SERVER=HTTP://SPSERVER/SITE/DOCLIB/SALES.XLSXPowerPivot ‘linked’ server

CHANNEL

WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server

PSS

PowerPivotOLEDB

Transports

SharePoint Farm

Application

InternetExplorer

SQL ServerInstance

CHANNEL




WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server

PSS

AS

PowerPivotOLEDB

Transports

SharePoint Farm

Application

InternetExplorer

SQL ServerInstance

CHANNEL




WFE

EWA

EWS

ECS

OtherOLEDB

Providers. . .

App Server

PSS

AS

PWS

HTTP

PowerPivotOLEDB

Transports

SharePoint Farm


WFE

ExcelDesktop

Application

InternetExplorer

SSASServer

SQL ServerInstance

TCP

CHANNEL



PowerPivot and SharePoint Overall Architecture

Per service applicationContains:

Instance Map – a collection of tables that indicate what databases are on located on what servers and their state, loaded or cachedUsage information – events for: connection, query response histograms, load, unload and Server+SSAS healthData refresh schedules (per workbook)Data refresh history results (success + failures)

PowerPivot database

WFE

EWA

ECS

OtherOLEDB

Providers

. . . PowerPivotOLEDBProvider

App Server

STSC2WTS

PowerPivot Security Infrastructure

WFE

EWA

ECS

OtherOLEDB

Providers


App Server

STSC2WTS

Uses S4U• Domain controller• 2-way trusts


WFE

EWA

ECS

OtherOLEDB

Providers


App Server

STSC2WTS

AppServer

PSS


Data refresh

Data Refresh

PowerPivot Application Database

SharePoint Content

Database

SharePoint Content

Database

SharePoint Configuration

Database

Microsoft SQL Server 2008 R2

Microsoft SharePoint Server 2010

Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services

Service Applications

User-Defined Functions

Excel Calculation Services

Excel Workbook

External Data Sources

PowerPivotSystem Service

Analysis ServicesOLE DB Provider

PowerPivot Management

Dashboard

PowerPivot Web Service

WCF TCP HTTP

Analysis Services Service

VertiPaq Engine

Internet Explorer

PowerPivot for Excel 2010

BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET

Analysis Services OLE DB

Provider

XLSX

SharePoint Timer Service

Usage Data Import

Usage Data Processing

PowerPivot Data Refresh

PowerPivot Health Statistics Collector

PowerPivot Mgmt Dashboard Processing

PowerPivot Setup Extension

Timer Jobs:

SharePoint Content

Database

PSS

(1 min)

(data refresh job)

(SharePoint content db)

AS

.xlsx

PowerPivot Timer Job Infrastructure


SharePoint Content

Database

SharePoint Content

Database


Database



Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services




Excel Workbook





Dashboard


WCF TCP HTTP


VertiPaq Engine

Internet Explorer


BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET


Provider

XLSX


Usage Data Import






Timer Jobs:

SharePoint Content

Database

PSS

(1 min)

(data refresh job)


(sources)

AS

.xlsx



SharePoint Content

Database

SharePoint Content

Database


Database



Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services




Excel Workbook





Dashboard


WCF TCP HTTP


VertiPaq Engine

Internet Explorer


BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET


Provider

XLSX


Usage Data Import






Timer Jobs:

SharePoint Content

Database

PSS

(1 min)

(data refresh job)


AS

.xlsx


Data refreshtimer job

(every 1 min)

PowerPivot System Service

MainDR thread

Slot#1

Slot#2

Slot#3

• Multiple jobs can be running in parallel• Maximum concurrency is a property of the AS Engine instance

• Default amount of memory 4GB• Max is the # of CPUs

• Shared across all of the PowerPivotservice applications (we use a mutex tocontrol this)

PowerPivot Gallery

• Built ondoclibtemplate

PowerPivot Gallery

• Silverlightcontrol

• Thumbnailscalc’ed inbackground

• Hooks innew contenteventsbeing firedin SP OM

PowerPivot Gallery

• But other UIas well . . .

• Excel launched linked to workbook

• Setup data refresh schedule

PowerPivot Gallery

• Regulardoclibview

• Reach backand do whatever SharePoint provides

Such as: check-in/out, approvals, workflow, versioning, revert content, view and change permissions, . . .

PowerPivot Gallery

PowerPivot Gallery RestrictionsTo prevent information exposure

If .xlsx: Any ‘refresh on open’ connections

must be an embedded PowerPivot data Or if linked case must be in same doc lib as the sourceIf .rdl: All connections must be to PowerPivot workbook contained

in same doclib as the Gallery

Else ‘lock’ icon is displayed

verses

PowerPivot Mgmt Dashboard

Customer Insider Session – Microsoft NDA Only


Based on two sources:Usage data (see architecture slides earlier)Data refresh schedules and history

Usage data kept in PowerPivot workbook stored in CA document library (see demo)Can use canned reports or your own

Feel free to extend the reports and add your own



SharePoint Content

Database

SharePoint Content

Database


Database



Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services




Excel Workbook





Dashboard


WCF TCP HTTP


VertiPaq Engine

Internet Explorer


BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET


Provider

XLSX


Usage Data Import






Timer Jobs:

SharePoint Content

Database

PSS

xyz.usage files(written asynchronously

using ETW; samefolder as ULS)

.usage

Events collected:• Connections• Query histograms• Load and Unload

PowerPivot Usage Infrastructure

(5 min)


SharePoint Content

Database

SharePoint Content

Database


Database



Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services




Excel Workbook





Dashboard


WCF TCP HTTP


VertiPaq Engine

Internet Explorer


BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET


Provider

XLSX


Usage Data Import






Timer Jobs:

SharePoint Content

Database

PSS

WSS_Loggingdatabase

(centralized)

(30 min)

.usage





(5 min)


SharePoint Content

Database

SharePoint Content

Database


Database



Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services




Excel Workbook





Dashboard


WCF TCP HTTP


VertiPaq Engine

Internet Explorer


BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET


Provider

XLSX


Usage Data Import






Timer Jobs:

SharePoint Content

Database

PSS

WSS_Loggingdatabase

(centralized)

PowerPivotApplicationDatabase(s)

(one per serviceapplication)

(30 min)

(daily)

.usage





(5 min)


SharePoint Content

Database

SharePoint Content

Database


Database



Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services




Excel Workbook





Dashboard


WCF TCP HTTP


VertiPaq Engine

Internet Explorer


BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET


Provider

XLSX


Usage Data Import






Timer Jobs:

SharePoint Content

Database

PSS

WSS_Loggingdatabase

(centralized)



(30 min)

(daily)

.usage

Events collected:• Connections• Query histograms• Load and Unload• Server health




(15 min)

(CPU & memoryusage for msmdsrv)

(5 min)


SharePoint Content

Database

SharePoint Content

Database


Database



Web Applications

PowerPivot Gallery

Excel WebAccess

Excel Web Services




Excel Workbook





Dashboard


WCF TCP HTTP


VertiPaq Engine

Internet Explorer


BusinessApplication

Data Cache

Client Libraries

Analysis Management

Objects (AMO)

ADOMD.NET


Provider

XLSX


Usage Data Import






Timer Jobs:

SharePoint Content

Database

PSS

WSS_Loggingdatabase

(centralized)



(30 min)

(daily)

.usage

Events collected:• Connections• Query histograms• Load and Unload• Server health




(15 min)

(CPU & memoryusage for msmdsrv)

PowerPivotUsage

Workbook(one per service

application)

(daily)

(runs aninternal datarefresh job)

(5 min)

Engine

Engine Architecture

Formula Engine

XMLA & MDX

VertiPaq SE

Parsing and query preparation

Block computation

On-disk files loadedInto memory ondatabase open

VertiPaq Storage Engine

Optimized for in-memory operation; no paging(But we do have on-disk files for transactional integrity)Column-oriented; high compressionExpressions evaluated natively by storage engine (where possible)No aggregates

Summary

We’ve reviewed PowerPivot’s architectural components for the client, midtier and serverAreas:

Client overall architectureSharePoint components

Querying (both from outside the farm and ECS)Data refreshGalleryMgmt Dashboard

Engine overall architecture

Web siteshttp://powerpivotgeek.comhttp://powerpivotpro.comhttp://powerpivot-info.com(lots of links off of those)

BOLhttp://msdn.microsoft.com/en-us/library/ee210682(SQL.105).aspx

Resources

http://powerpivotgeek.com/

http://powerpivotpro.com/

http://powerpivot-info.com/

Related Content

Breakout Sessions (session codes and titles)BIE303 - Deploying and Managing PowerPivot for SharePoint 2010BIE401 - Deep Dive on PowerPivot TechnologiesBIE403 - Real-Time Business Intelligence with Microsoft SQL Server 2008 R2BIP301 - So Many BI Tools, So Little TimeBIP302 - Enabling Real-time Business Insight, Analytics and ReportingBIU201 - Building Sophisticated BI Applications Using Microsoft PowerPivot for Microsoft ExcelBIU203 - Delivering Self-Service BI in Your Organization Using Microsoft Excel, Excel Services, and Microsoft PowerPivotBIU302 - Enriching Microsoft PowerPivot for Microsoft Excel Applications Using DAX (Data Analysis Expressions)BIU303 - Using Microsoft Office 2010 with PowerPivot to Analyze SAP BW Data

Related Content

Interactive Sessions (session codes and titles)BIC06-INT - SQLCAT: PowerPivot Best Practices and Enterprise Case StudiesBIE04-INT - Building Custom Extensions to the PowerPivot Management DashboardBIE22-INT - Tips and Tricks for Troubleshooting a Microsoft SQL Server PowerPivot for SharePoint InstallationBIP09-INT - Unleash the Power of PowerPivot in Your EnterpriseBIU01-INT - Accelerating Adoption of PowerPivot: Demos, Learning, and Guidance from Internal MSIT TAP TeamsBIU02-INT - Budgeting Solution Built with PowerPivotBIU04-INT - Data Analysis Using Microsoft Excel 2010BIU05-INT - DAX Patterns in PowerPivot

Related Content

Hands-on Labs (session codes and titles)BIE03-HOL - Working with ATOM Data Feeds in Microsoft SQL Server 2008 R2 Reporting ServicesBIO03-HOL - Managing Microsoft PowerPivot for Microsoft Excel 2010 Solutions in SharePoint Server 2010BIU01-HOL - Creating Reports with Microsoft PowerPivot for Microsoft Excel 2010 BIU02-HOL - Defining DAX Calculations with Microsoft PowerPivot for Microsoft Excel 2010 BIU03-HOL - Loading and Preparing Data in the Microsoft PowerPivot for Microsoft Excel 2010 Client

Product Demo Stations (demo station title and location)TLC-68 - Microsoft SQL Server PowerPivot for Excel 2010

Complete an evaluation on CommNet and enter to win!

© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to

be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

excel msolap powerpivot oledb transports tcptcp ssas server (1) server= ; data source= ; how used:...

Documents

embedded powerpivot

embedded data

powerpivot ssas instance

data source

itpowerpivot data

embedded workbook

file shareuser

single file