REALIZING BUSINESS INSIGHTS WITH POWERPIVOT

About the PresenterChris Campbell• Principal Consultant with BlueGranite• Certified Professional in Microsoft BI

Development• Microsoft Virtual Technology Specialist• Business Intelligence Technical Architect at the

Microsoft Technology Center, Detroit

ccampbell@blue-granite.comb-chcamp@microsoft.comhttp://www.blue-granite.comhttp://twitter.com/ckcampbell

Agenda Introduction

What is PowerPivot?

Data Modeling for Analysis

Demo

Resources

Questions

WHAT IS POWERPIVOT?

BI 1.0

Trends in Business Intelligence

• IT Owned & Managed•Slow to Respond•Monolithic•Not Interactive

SiloSilo

BI 2.0Discoverabil

ity

Collaboration

Self ServiceBusiness Insights

Data Access Structured &

UnstructuredData

•Business Owned, IT Managed•Agile•Flexible•Collaborative

UnstructuredData

StructuredData

What is PowerPivot?A solution that re-defines the line between I.T. and end-user roles

... using familiar tools without requesting support

… managing usage and resources involved

I.T.

• Provision• Administer• Secure• Track dataUs

ers • Model

• Analyze• Personalize• Share BI

solutions

Why Use PowerPivot?• Process massive amounts of

data in seconds• Take advantage of familiar

Excel tools and features• Load even the largest data

sets from virtually any source• It is FREE!

Download and Install PowerPivot

Go to http://www.powerpivot.com and choose a version

Download and Install PowerPivotMake sure to select the appropriate version for your version of office (32-bit or 64-bit)

Finding out what version of Excel You Have

In Excel, Click “File” then Help. The version of Excel is noted under “About Microsoft Excel”

DATA MODELING FOR ANALYSIS

OLAP Data Modeling• OLAP Data Models

generally take the form of a “Star” Schema

• Two kinds of tables• Dimension• Fact

• Fact tables have a 1:M relationship to all Dimension tables.

Data Modeling for Analysis• PowerPivot blurs the

line between dimensions and facts.

• Only requires that a user understand where the numbers are and how they want to slice them.

• Can join together many disparate data sources.

• DAX allows you to manipulate the data and build custom calculations.

DEMO

BUILDING A DATA MODEL AND DASHBOARD WITH POWERPIVOT

DAX Evaluation Context• Row Context• “The current row”• Determined by the content of the TABLE• Includes ALL columns• May include “RELATED()” columns from other

tables• Query Context• What row(s) and column(s) are selected?• Determined by what are on rows/columns in

your pivot table• Filter Context• What rows are left after the filter is applied?

Row Contex

t

Query Contex

t

Filter Contex

tResult

DAX Evaluation Context – Example

Product Category Quantity

Unit Price Amount

15” Tablet Computers 5 $500 $250017” Laptop Computers 1 $1200 $1200Halo 4 Games 1 $60 $60

Category AmountComputers $3700Games $60

Category AmountComputers $3700

Row Context

Query Context

Filter Context

DEMO

POWERPIVOT MANAGEMENT DASHBOARD

PowerPivot Management Dashboard

Management Dashboard – Server Activity

Management Dashboard – Workbook Activity

Management Dashboard – Workbook Details

RESOURCES

PowerPivot Resources• Web sites

− http://msbiacademy.com− http://powerpivotpro.com− http://www.powerpivot.com− http://powerpivotgeek.com− http://powerpivot-info.com

• BOL− http://msdn.microsoft.com/en-us/library/ee210682(SQL.10

5).aspx

QUESTIONS?

ccampbell@blue-granite.comb-chcamp@microsoft.comhttp://www.blue-granite.comhttp://twitter.com/ckcampbell