Transcript Session Title

Self-Service Business Intelligence
with Microsoft PowerPivot
Andrew J. Brust
Who Am I?

Co-chair VSLive! and my 15th year as a speaker

Member, Microsoft BI Partner Advisory Council

Microsoft Regional Director, MVP

Co-moderator, NYC .NET Developers Group
– http://www.nycdotnetdev.com

“Redmond Review” columnist for
Visual Studio Magazine and Redmond Developer News

Chief, New Technology
– Microsoft Gold Certified Partner
– BI, Integration, Portals, Custom .NET development

brustblog.com, tweeting @andrewbrust
Read all about it!
Why Self-Service BI?

“Spreadmarts” exist, no matter what:
– Power users like Excel, but…
– No true db or OLAP engine
– Email as sharing mechanism, Excel as only client
– Often, no refresh
– No discoverability by IT

People do need BI, but:
– They don’t have the vocabulary or the MD concepts
– They don’t want to deal with formal design
– They do know Excel and how to analyze data within it

They need RBID (rabid?) – Rapid BI
Development, and Excel gives it to them
Enter PowerPivot


Excel + Analysis Services + SharePoint
Enables the scenario but mitigates the
technological deficits:
– Stay in Excel
– Use Analysis Services (AS) as a hidden engine
– Share via SharePoint, accessible by all AS clients
– Formal data refresh on server
– Allow IT to monitor
– Provide path to more rigorous implementations
Architecture

Column-Oriented, In-Memory BI (IMBI)
– Support extra-large data sets

Desktop version of AS with Excel add-in
– Add-in and Excel are only UIs

Special SharePoint-integrated version of AS
– Load-balanced
– Instances spun up on demand

Supported by MSOLAP.4 (i.e. 2008) OLE DB
provider
– PowerPivot installs extensions to provider to enable
this
The Simple View
The Detailed View
Column-Oriented Stores





Imagine, instead of:
Employee ID
Age
Income
1
43
90000
2
38
100000
3
35
100000
You have:
Employee ID 1
2
3
Age
43
38
35
Income
90000
100000
100000
Perf: values you wish to aggregate are adjacent
Efficiency: great compression from identical or nearlyidentical values in proximity
Fast aggregation and high compression means huge
volumes of data can be stored and processed, in RAM
Client

Excel 2010
– PivotTables, PivotCharts, Slicers (new)

PowerPivot add-in/client
– Provides in-memory, column-oriented desktop version
of Analysis Services, accessible only by Excel
– Can import data from a number of heterogeneous
data sources
– Actually builds an AS cube in the background
– Supports special calculated columns
– Embeds its data in the .xlsx workbook
The Ribbons
Excel 2010:
PowerPivot
Client:
Data Import

Relational databases
– SQL Server (including SQL Azure!), Access
– Oracle, DB2, Sybase, Informix
– Teradata
– “Others” (OLE DB, including OLE DB prvdr for ODBC)

Atom feeds and R2 Reporting Services
– And any ADO.NET Data Services (Astoria) feed


Filter, preview, friendly names
Excel
– Paste from clipboard, linked tables
PowerPivot Client Data Modeling
DEMO
Reports and Feeds

Service Document
– External, inline



Individual ATOM Feeds
File spec or http(s):// URI
Automating from RS R2 viewer
Data Feed and Report Data Imports
DEMO
Relationships




Between tables from same data source: often
automatic
Between tables from heterogeneous data
sources: always manual
No composite keys
Why relationships are important
Calculated Columns and DAX


Formula-based columns may be created
Formula syntax is called DAX (Data Analysis
eXpressions).
– Not to be confused with MDX or DMX. Or DACs.

DAX expressions are similar to Excel formulas
– Work with tables and columns; similar to, but distinct from,
worksheets and their columns (and rows)





=FUNC('table name'[column name])
=FUNCX('table name', <filter expression>)
FILTER(Resellers,[ProductLine] = "Mountain")
RELATED(Products[EnglishProductName])
DAX expressions can be heavily nested
Relationships and Calculated Columns
DEMO
Client Query



Push data back to Excel: creating PowerPivot
PivotTables and PivotCharts
Using slicers
Calculated measures
Querying PowerPivot Data in Excel
DEMO
Server

Publish to Excel Services
– Office Synchronization Center



Viewing and interacting
Data Refresh
Treating as cube
– URL to .xlsx as server name
Db name is GUID-based; best to discover it
– Use Excel, Reporting Services as clients
– Examine in SSMS, BIDS

IMBI vs. MOLAP/ROLAP/HOLAP
PowerPivot on the Server
DEMO
Monitoring and Conventional BI


IT dashboard
Why “refactor” a popular project?
– Better dimensional design: hierarchies, parent-child
dimensions
– Calculated measures in MDX
– Aggregation design, proactive caching
– No superfluous measure groups and dims
Market Analysis

IMBI, columnar models respond to
competition
– Self-Service, desktop: QlikView
– Columnar: Sybase IQ, Vertica (and QlikView again)
– In-memory: MicroStrategy (and QlikView again)

Catering to Excel power users and “quants” is
the perfect sweet spot.
– Excel is where everyone tries to be
– DAX has a great Excel affinity
– BI for the discriminating non-specialist

Tying to SharePoint leverages emerging
franchise
– Other vendors do it, but only MS can do it so well
Timeline


CTP3 is here, and public
SQL 2008 R2, Office 2010, SharePoint 2010
– Due “first half” o CY 2010
– 64-bit required on server
Resources

Web Sites:
– http://www.powerpivot.com
– http://www.powerpivot-info.com

Blogs
–
–
–
–

http://blogs.msdn.com/powerpivot (team)
http://powerpivotpro.com (Rob Collie)
http://powerpivottwins.com (Denny Lee and Dave Wickert)
http://powerpivotgeek.com (Dave Wickert)
Documentation:
– Server: http://tinyurl.com/GeminiServerDocs
– DAX reference: http://bit.ly/1eBXJQ

Social networks:
– http://www.twitter.com/andrewbrust/powerpivot
– http://www.facebook.com/PowerPivot

Hands-on-Lab (developed by 26NY for MS)
Questions?


@andrewbrust on Twitter
[email protected]