Contents
Chapter 1: Getting Started with PowerPivot for Excel
Filling the Gap with PowerPivot for Excel
A Brief Tour of PowerPivot for Excel
Chapter 2: Hello World, PowerPivot Style
SQL Server As a PowerPivot Data Source
Starting the Table Import Wizard
Behind the Scenes in PowerPivot for Excel
Chapter 3: Combining Data Sources
Configuring Excel As a Data Source
Venturing into the Date Dimension
Chapter 4: Data Analysis Expressions
Spotting Calculated Columns in the PowerPivot Field List
Illustrating Relationships with Football
Calculated Measures from Multiple Data Sources
Separation of Facts and Dimensions
Chapter 5: A Method to the Madness
Think Dimensionally, Act Locally
Transactional vs. Analytical Data Stores
Measures, Dimensions, and Hierarchies
Take Advantage of Revision Control
Rename Early to Keep DAX Formulas Sane
Renaming an Object in a Relationship
Choose Online Datasources When Possible
Relate Fact Sets Through a Dimension Table
Create a Separate Date Dimension
Shortcut to a Rich Date Dimension
“Rank Smallest to Largest” and “Rank Largest to Smallest”
Customize Your PivotCharts via PivotTables
Chapter 6: Installing PowerPivot for SharePoint
Existing SharePoint 2010 Installation
Step One: Install SQL Server PowerPivot for SharePoint
Step Two: Deploy the PowerPivot Solution Package
Step Three: Start Other Required Services
Step Four: PowerPivot Service Application
Step Five: Enable Excel Services
Step Six: Enable the Secure Store Service
Step Seven: Enable Usage Data Collection
Step Eight: Maximum File Upload Size
Step Nine: Activate PowerPivot at the Site Collection Level
Step Ten: Verify the Configuration Changes Work
Step One: Create a New Virtual Machine
Step Two: Add the Domain Controller Role
Step Three: Add the Server to Your Domain
Step Four: Install SharePoint 2010 Server
Step Five: Install SQL Server 2008 R2 and PowerPivot
Running PowerPivot for SharePoint on Windows 7
Step One: Install SQL Server 2008 R2
Step Two: Install the SharePoint Prerequisites
Install Microsoft Filter Pack 2.0
Install ADO.Net Data Services Update
Install the Microsoft Sync Framework
Install SQL Server Native Client
Install Windows Identity Foundation
Install Microsoft SQL Server 2008 Analysis Services ADOMD.NET
Enable Required Windows Features
Step Three: Install SharePoint 2010 Server
Step Four: Add PowerPivot for SharePoint Existing Farm
Step Five: Final Configuration
Fun with PowerPivot and SharePoint
Verifying That the Installation Works
Viewing the PowerPivot Gallery
Chapter 7: Collaboration, Version Control, and Management
PowerPivot Gallery View Settings
PowerPivot Gallery Permissions
PowerPivot from an iPad or Other Tablet
PowerPivot Management Dashboard
Accessing the PowerPivot Management Dashboard
Using the Management Dashboard
Configuring Usage Data Collection
Chapter 8: PowerPivot As a Data Source
PowerPivot As a Data Source for PowerPivot
Verifying the Solution Prerequisites
PowerPivot Management Dashboard As a Data Source
Creating the Office Data Connection (.odc) File
Writing a Report Using PowerPivot Management Data
PowerPivot and PerformancePoint Dashboards
Chapter 9: PowerPivot and SQL Server Reporting Services
Consuming SSRS As a PowerPivot Data Source
Using PowerPivot As an SSRS Data Source
Creating the PowerPivot Source Worksheet
Developing the Example SSRS Report
Chapter 10: PowerPivot and Predictive Analytics
Introducing the Data Mining Add-In for Excel
Installing the Data Mining Add-In
Locating the Table Analysis Tools
Avoiding Connection Frustration
Adding the SSAS Connection Within the Data Mining Add-In
Preparing Data with PowerPivot
Taking Data Mining to the Cloud with Predixion Software
Setting Up the Predixion Add-In
Chapter 11: Tips, Tricks, and Traps
User Experience for PowerPivot Solutions