PowerPivot Basics and
Creative Implementations
By: Jen Underwood, Microsoft
Agenda
PowerPivot: What it is and what you can do!
Basics
Installation
Creating mash-ups
Designing PowerPivot reports
Extending with DAX
Publishing to SharePoint PowerPivot Gallery
Consuming as data source in SharePoint
Resources
Empower Your Users
PowerPivot – With2010
for Excel Familiar Data
• PowerPivoting Massive Data Volumes
• Business users create, interactive
self-service analytics solutions
• Blows away v-lookup with
high performance, lightning
speeds, in-memory
VertiPaq technology
• Embedded Analysis
database
PowerPivot Case Study
Mediterranean Shipping Company
SQL Server 22 terabytes
Transactions per Minute 396,907
Transactions per Year 210 billion
PowerPivot “mash-ups” 33 million rows,
1.7 GB compressed
PowerPivot Examples
PowerPivot.com
Marketing
PowerPivot Examples
Personal Time Management
PowerPivot.com
PowerPivot Examples
Sports PowerPivot.com
PowerPivot Examples
Retail
PowerPivot Examples
Retail
“Killer PowerPivot” Tech Ed 2011
Source: TechEd North America 2011
PowerPivot Examples
Government PowerPivot.com
PowerPivot Examples
Government
Education “Killer PowerPivot” Tech Ed 2011
Source: TechEd North America 2011
PowerPivot Examples
Medical Survey
“Killer PowerPivot” Tech Ed 2011
Source: TechEd North America 2011
PowerPivot Examples
HR
PowerPivot.com
PowerPivot Examples
http://www.tushar-mehta.com
Mapping and Free Form Data Visualizations in Excel!
Common PowerPivot Applications
Personalized Excel and SharePoint Dashboards
Excel Reports, Reporting Services Report Builder and
PerformancePoint Dashboards built on PowerPivot models
Power User mashes up and personalizes corporate data sets for
team reporting model usage
Many data source types available!!!
Forecasting, Budgeting, What-If Analysis
Combining OLAP actuals with free form data scenarios
Unions of Excel files with same column structures to combine
departmental inputs into one consolidated view
Surveys
PowerPivot with SAP
PowerPivot for combining SAP or SAP BW data sources
with other Non-SAP data
*No Third Party: NetWeaver, Open Hub, Reporting Services
*Third Party: Theobald, ERPLink, Simplement
So easy, true win-win for everyone
ACE 14 OLE DB
*SAP BW OLE DB
SAP BW
Excel
workbook
PowerPivot Gallery in SharePoint
SharePoint
Schedule data
refreshes to keep
your analysis
up-to-date
Manage security just
like a document
Build reports upon
PowerPivot models
PowerPivot Report Sources
Create report
Option
PowerPivot Management Dashboard
PowerPivot is
great for IT:
Track the usage of
PowerPivots
Discover mission-
critical applications
Increase IT efficiency:
Familiar Technologies for
Authoring, Sharing,
Security and Compliance
Customizable IT
Dashboard
Visualize system
performance and usage
with animated charts
Basics
Install PowerPivot in Excel 2010
Go to http://www.powerpivot.com
1 Click Download link and follow the
installation instructions.
Click See Details
then click the PowerPivot link
2
3
Choose the
PowerPivot_for_Excelx86.msi to
download the install file
Install PowerPivot in Excel 2010
Follow the install instructions
4
x
Watch some of
the training videos
Getting Started
1
To Start PowerPivot, open Excel
and click the PowerPivot tab
To start loading data from various
sources, click the PowerPivot
Window Launch icon.
Available Data Sources for
PowerPivot Mash Ups
• Database: Relational or multidimensional
Microsoft SQL Server
Microsoft Access
Microsoft SQL Server Analysis Services cubes
• Report: Reporting Services data feed
• Data Feeds: Online data source feeds
• Text: Get data from raw text files
• Other Sources:
Microsoft SQL Azure
Microsoft SQL Server Parallel Data Warehouse
Oracle
Teradata
Sybase
Informix
IBM DB2
Others (OLE DB/ODBC)
Excel Files
To Load Data From Database
Select From Database,
1 click SQL Server
2 Enter Log In information
Server name: <<x>>
User Name: <<user name>>
Password: <<password>>
Database name: <<x>>
3
Click Next
To Load Data From Database
1
Click tables
Select from a list of to import
tables and views
3
WARNING
Use Filters to
limit data you
import with
2 Preview & Filter
4
Click Next
5
Click Finish
To Load Data From Database using
Preview & Filter
1 Select column drop down
arrow to display Filter options
2 Choose Filter type
Enter Filter criteria
To Load Data From Database using
Preview & Filter
1
Click column check boxes
to include or remove them
from the import
To Load Data From Database
Wait for data to
finish loading.
To Load Data From Database using
Advanced Filtering
Select Write a Query Click Design for Visual
to have better Filter Query Designer
control or use
advanced features 3
Click Next
To Load Data From Database using
Advanced Filtering
5 Click Run Query to Test
Select Field to Filter
2
Select table to Click Filter Icon
Filter
3
1
4
Define Filter criteria
Click OK 6
To Load Data From Database
Unlike OLAP Cubes, PowerPivot data
is not automatically refreshed. It is copied.
To Refresh data, click the Refresh icon.
Each Table loaded to a
PowerPivot Worksheet
1
To Use Loaded Data From Database
and Other Data Sources
Click Design tab
1
2
Click Manage
Relationships
To Use Loaded Data From Database
and Other Data Sources
Click Create
or Edit
1
Choose what columns have the
same data to “relate” them. 2
For example, DimProduct table
uses ProductKey and
FactInternetSales table also has a
Click OK
ProductKey column to tie them
3
together for slicing and dicing.
To Use Loaded Data From Database
and Other Data Sources
If PowerPivot detects missing
Relationships it can automatically build
them for you when you click Create
Click Close when it completes
2
To Use Loaded Data From Database
and Other Data Sources
1 Click Home tab
Select Pivot Table
layout type
2
To Use Loaded Data From Database
and Other Data Sources
Pick field for “Power” Pivoting
NOTE
All fields are now
available for drag
and drop reporting!
To Use Loaded Data From Database
and Other Data Sources
3
2
Insert Charts Insert Slicer
for dynamic visuals for dynamic filtering
1
Create interactive
reports and charts
PowerPivot Refreshes when Source Data
Columns are Added, Removed or Changed
If PowerPivot detects changes to the source data
columns, it will prompt you to Refresh. Click 1
Refresh and new source tables and columns will
be available in the PowerPivot Field List.
To Load, Link and Use an External List
3
Click the PowerPivot tab
1
2
Select all the cells in the list
Create a list that contains a column
that has the same content as a
column in the other data sources in a
new worksheet in the same
PowerPivot workbook.
To Load, Link and Use an External List
Click the Create Linked Table
Confirm the cell range
2
To Load, Link and Use an External List
Rename the new linked tab
Table1 to something you like
1
To Load, Link and Use an External List
1 Click Design tab
2 Click Manage
Relationships
Click Create to add
your list
Choose what columns have the
same data to “relate” them. For
this example, PromotionKey
matches PromotionID.
5
Click Create
To Load, Link and Use an External List
1 Optionally “Hide” unfriendly columns
by clicking Hide and Unhide
Choose fields to
Hide in Pivot Table
Click OK
To Load, Link and Use an External List
1
Click Home tab
Select Pivot Table
2 layout type
To Load, Link and Use an External List
Custom Agency list is now
available to be combined with
other data sources for pivoting
1
Linking Slicers to Multiple Pivot Objects
les
1 Select Slicer Tools and Slicer Settings
or click PivotTable Connections
Right-click Slicer and select
PivotTable Connections
3
Choose the Pivot Tables
you want the Slicer to
dynamically filter
Formatting Slicers using Properties
1
Select Slicer Tools and choose
Slicer Styles, Sizes etc.
Right-click Slicer Settings
2 and/or Size and Properties
To Add a DAX Calculated Measure
1
Click New Measure
New Profit Measure is now available
with a Calculator icon
3
Enter Measure
Settings and
add a Formula
DAX Expressions
Click Add Column
1
2
Enter a DAX expression
identified by prompt
“expression evaluated
for each row in a table”
DAX Expressions
Example PREVIOUSYEAR
DAX formula syntax
DAX Expressions
http://powerpivot-info.com/post/53-list-of-powerpivot-dax-functions-short
DATE TIME FUNCTIONS INFORMATION FUNCTIONS
DATE(<year>, <month>, <day>) ISBLANK(<value>)
DATEVALUE(date_text) ISERROR(<value>)
DAY(<date>) ISLOGICAL(<value>)
EDATE(<start_date>, <months>) ISNONTEXT(<value>)
EOMONTH(<start_date>, <months>) ISNUMBER(<value>)
HOUR(<datetime>) ISTEXT(<value>)
MINUTE(<datetime>) FILTER AND VALUE FUNCTIONS
MONTH(<datetime>) ALL(<table_or_column>)
NOW() ALLEXCEPT(<table>,column1>,<column2>,…)
SECOND(<time>) BLANK()
TIME(hour, minute, second) CALCULATE(<expression>,<filter>,<filter>…)
TIMEVALUE(time_text) CALCULATETABLE( <expression>, <filter>…)
TODAY() DISTINCT(<column>)
WEEKDAY(<date>, <return_type>) EARLIER(<column>, <number>)
WEEKNUM(<date>, <return_type>) EARLIEST(<table_or_column>)
YEAR(<date>) FILTER(<table>,<filter>)
YEARFRAC(<start_date>, …) RELATED(<column>)
RELATEDTABLE(<table>)
VALUES(<column>)
FIRSTNONBLANK(<column>,<expression>)
DAX Expressions
http://powerpivot-info.com/post/53-list-of-powerpivot-dax-functions-short
LOGICAL FUNCTIONS TIME INTELLIGENCE
AND(<logical1>,<logical2>,…) DATESINPERIOD(<date_column>…)
FALSE() DATESBETWEEN(<column>,<start_date>…)
IF(logical_test>,<value_if_true>, …) DATEADD(<date_column>,…)
IFERROR(value, value_if_error) FIRSTDATE (<datecolumn>)
NOT(<logical>) LASTDATE (<datecolumn>)
TRUE() LASTNONBLANK (<datecolumn>,…)
OR(<logical1>,<logical2>,…) STARTOFMONTH (<date_column>)
TEXT FUNCTIONS STARTOFYEAR(<date_column>[,<YE_date>])
CODE(<text>) ENDOFMONTH(<date_column>)
CONCATENATE(<text1>, <text2>,…) ENDOFYEAR(<date_column>)
FIND(<find_text, within_text, start_num) PARALLELPERIOD(<date_column>,…)
FIXED(<number>, <decimals>, …) PREVIOUSDAY(<date_column>)
LEFT(<text>, <num_chars>) PREVIOUSMONTH(<date_column>)
LEN(<text>) PREVIOUSYEAR(<date_column>)
MID(<text>, <start_num>, <num_chars>) NEXTDAY(<date_column>)
REPLACE(<old_text>, <start_num>,….) NEXTMONTH(<date_column>)
REPT(<text>, <num_times>) NEXTYEAR(<date_column>[,<YE_date>])
RIGHT(<text>, <num_chars>) DATESMTD(<date_column>)
VALUE(<text>) DATESQTD (<date_column>)
FORMAT(<value>, <format_string>) DATESYTD (<date_column> [,<YE_date>])
Help
1
Click blue ? Icon
when inside the
PowerPivot Window.
2
Select a help topic.
Good PowerPivot Web Sites and Books
• http://www.PowerPivot.com
• http://powerpivot-info.com
• http://powerpivotgeek.com
• http://powerpivotpro.com
