How To Write Powerpivot For Excel

PowerPivot

for

Business

Intelligence Using

Excel

and

SharePoint

Barry

Ralston

About the

Author

xi

About the

Technical

Reviewer

xii

Acknowledgments

xii!

Chapter

1:

Getting

Started

with

PowerPivot for

Excel

1

Filling

the

Gap

with PowerPivot for Excel

1

What You

Will Need

2

The

64-bit

Decision 3

Installing

the Add-In 3

A Brief Tour of

PowerPivot for

Excel 4

A Trivial Test

Case

5

The Test

_Report

7

Summary

9

Chapter

2: Hello

World,

PowerPivot

_Style

11

The Business

Scenario

11

Assembling the Solution

11

SQL

Server

As a

PowerPivot

Data Source 12

Starting

the

Table

_Import

Wizard 12

Selecting

the Table

14

Monitoring

the

Import

15

Reviewing

the Results.. 16

Data Refresh

17

Creating

the

_Report

17

Narrowing

to the

Top

Ten

19

Behind the

Scenes

in PowerPivot

for Excel

21

ftCONTENTS

Chapter

3:

Combining

Data Sources

25

The

Business Scenario

25

Configuring

Excel AsaData

Source

26

Venturing

into the Date

Dimension

28

Understanding

the

_Design

28

Good

Relationships

30

PivotTables and PivotCharts

34

Slicers

37

Refreshing

the Data

39

Summary

40

Chapter

4: Data

_{Analysis Expressions}

43

Fundamentals of DAX

44

Calculated Columns 44

Creating

a

Calculated Column

46

Filtering

PowerPivot

Data 48

Spotting

Calculated Columns in the PowerPivot Field List 49

Table

_{Relationships}

49

Understanding

the Problem

49

Illustrating

Relationships

with Football

51

Relating

Tables

with DAX 52

Calculated Measures from

_Multiple

Data Sources , 54

The Business

Problem

54

Separation

of Facts

and

Dimensions

55

New Measures

58

Filter Context

60

Begin

withaPivotTable

60

Compute

Percent of Whole 62

Summary

64

Chapter

5: A

Method

to the Madness

65

Justification for

a Method ,

65

Principles

,

66

Embrace Failure 66

Think

_{Dimensionally,}

Act

_Locally

66

Transactionalvs.

Analytical

Data Stores

66

IICONTENTS

Practices

68

Take

_Advantage

of Revision

Control

68

Rename

_Early

to

_Keep

DAX Formulas Sane

68

Creating

a

_Relationship

69

Renaming

an

Object

in

a

_Relationship

70

Examining

the Result

70

Choose Online Datasources When

Possible

72

Relate Fact Sets

Through

a

Dimension

Table 73

Create

a

Separate

Date Dimension

73

Shortcut to

a

Rich Date Dimension

73

Handling

Moving

Holidays

75

Leverage

Built-in Measures

76

No Calculation

77

%of

Grand Total 78

% of Column Total 78

%

of Row Total 78

% Of

78

% of Parent Row

Total 79

%of Parent Column

Total 80

%of Parent

Total 80

Difference

From

81

% Difference

From 82

Running

Total

In 82

%

Running

Total In

83

"Rank Smallest to

Largest"

and

"Rank

_Largest

to

Smallest"

84

Index

84

Customize Your PivotCharts via

PivotTables 85

Summary

•

91

Chapter

6:

_Installing

PowerPlvot for SharePoInt

93

Getting

Started

93

Existing

SharePoInt 2010 Installation

94

Step

One:

Install SQL Server PowerPlvot for SharePoint

94

Step

Two:

_Deploy

the PowerPlvot Solution

Package

95

Step

Three: Start Other

Required

Services

96

Step

Four: PowerPivot

Service Application

96

Step

Five: Enable Excel

Services

, 96

Step

Six: Enable the Secure Store

Service

96

ISCONTENTS

Step

Seven:

Enable

_Usage

Data

Collection

96

Step Eight:

Maximum File

_Upload

Size

96

Step

Nine: Activate PowerPivot at the Site Collection

Level

97

Step

Ten:

_Verify

the

_{Configuration}

Changes

Work

97

Virtual Success

97

Step

One: Create

aNew Virtual Machine

98

Step

Two:

Add the Domain Controller

Role

98

Step

Three: Add the Server to

Your Domain

99

Step

Four:

Install SharePoint 2010

Server 100

Step

Five:

Install SQL Server 2008

R2 and PowerPivot 102

Running

PowerPivot

for SharePointonWindows 7 103

The Road

_Map

104

Step

One: Install SQL Server 2008 R2 104

Step

Two: Install the SharePoint

Prerequisites

108

Edit

the

_Config.xml

File

108

Install

Microsoft Filter Pack 2.0

109

Install WCF Hotfix KB976462

110

Install ADO.Net Data

Services

_Update

110

Install the

Microsoft

_Sync

Framework 110

Install

SQL

Server Native Client 111

Install Windows

_Identity

Foundation 111

Install

Chart Controls

112

Install Microsoft SQL Server 2008

Analysis

Services

AD0MD.NET

112

Enable

_Required

Windows Features

112

Step

Three: Install SharePoint 2010 Server

115

Step

Four: Add PowerPivot for SharePoint

_Existing

Farm 118

Step

Five: Final

_{Configuration}

122

Fun with PowerPivot and SharePoint

123

Verifying

That the Installation Works 123

Viewing

the PowerPivot

_Gallery

123

Potential Errors

125

Summary

125

H

Chapter

7:

_{Collaboration,}

Version

Control,

and

_Management

127

Sharing

Solutions

₁₂₇

Publishing

128

*CONTENTS

Gallery

135

Theater

136

Carousel

136

All

Documents

137

PowerPivot

_Gallery

Permissions

137

Web

_Usage

Scenarios

137

Excel via the

Browser

138

PowerPivot

from an

iPad

orOther Tablet

140

PowerPivot

_Management

Dashboard

142

Accessing

the PowerPivot

_Management

Dashboard

142

Using

the

_Management

Dashboard

143

Query Response

Times

143

Average

Instance CPU

145

Average

Instance

_Memory

145

Activity

146

Performance 147

Workbook

_Activity

Chart

147

Workbook

_Activity

List

148

Configuring Usage

Data Collection

149

Summary

151

Chapter

8:

PowerPivot

Asa

Data Source

153

PowerPivot As

a

Data Source for PowerPivot

153

Verifying

the Solution

_{Prerequisites}

154

Assembling

the Pieces

155

Refreshing

the

_Summary

170

PowerPivot

_Management

Dashboard AsaData Source 174

Creating

the Office Data Connection

_(.ode)

File

174

Writing

a

Report Using

PowerPivot

Management

Data 177

PowerPivot

and PerformancePoInt

Dashboards 178

Creating

the Data Connection

181

Creating

the

_Analytic

Chart

182

Deploying

to SharePoint

186

Summary

188

Chapter

9:

PowerPivot and

SQL

Server

_Reporting

Services

189

Consuming

SSRS As

a

PowerPivot Data

Source

190

MCONTENTS

Configuring

the Connection

192

Using

PowerPivot

Asan SSRS Data Source 197

Creating

the PowerPivot

Source Worksheet

198

Opening Report Manager

203

Developing

the

_Example

SSRS

Report

203

Summary

218

Chapter

10:

PowerPivot

and Predictive

_Analytics

219

Introducing

the Data

Mining

Add-In for Excel 219

Installing

the Data

_Mining

Add-In 220

Locating

the Table

_Analysis

Tools 227

Creating

a

SSAS Connection

229

Configuring

the SSAS Server

230

Avoiding

Connection Frustration 236

Adding

the SSAS

Connection Within

the

Data

Mining

Add-In 237

Preparing

Data

with PowerPivot

241

Mining

Some Data 244

Taking

Data

Mining

to the Cloud with Predixion Software 247

Setting Up

the Predixion Add-In 247

Predicting

Airline

Delays

258

Summary

263

Chapter

11:

_{Tips, Tricks,}

and

_Traps

265

PowerPivot

_Annoyances

265

Disabled PowerPivot Add-In

265

Calculated

Column

_Missing

270 User

_Experience

for PowerPivot Solutions

271

Connect Slicers

_Visually

272

Lose the Grid

272

Tuning

PowerPivot Performance 273

Slicers: Less Is More

273

PowerPivot and

SSAS Interaction

274

Summary

276