--------------------------------------------------------------------------------
Foreword ix
Acknowledgments xi
Introduction xiii
1
Making Sense of Data 1
The Types of Data That You Can Analyze
2
Using Data to Make Decisions
4
Developing a Data Analysis Strategy
5
Recording Data
8
Troubleshooting Data Compatibility Issues
9
Understanding Microsoft Data Analysis Software and Features
10
Data Storage
10
Microsoft Excel
12
Microsoft Access
13
Microsoft Office Web Components
13
Microsoft SQL Server 2000
14
Microsoft SQL Server 2000 Desktop Engine
15
Microsoft SQL Server 2000 Analysis Services
16
Microsoft Data Analyzer
16
Summary
17
2
Basic Data Analysis Techniques
19
Sorting Data
20
Summarizing Data
22
Trending Data
23
Filtering Data
24
Formatting Data
25
Importing, Exporting, and Querying Data
27
Importing Data
27
Exporting Data
27
Querying Data
28
Charting Data
28
Pivoting Data
30
Working with Data Lists
31
Working with Relational and Multidimensional Data
32
Working with Relational Data
33
Working with Multidimensional Data
35
Working with XML Data
37
Elements and Attributes
37
Namespaces
38
Summary 39
3
Analyzing Data with Microsoft Excel
41
Sorting and Filtering Data
42
Sorting Data
42
Filtering Data
44
Using Worksheet Functions
49
Applying Conditional Formatting
54
Working with Charts
57
Importing External Data
61
Filtering Imported Data
62
Querying External Data
63
Querying Data on the Web
67
Analyzing Data in Lists
70
Using the Analysis ToolPak
73
The Descriptive Statistics Tool
76
The Histogram Tool
78
The Moving Average Tool
81
The Rank and Percentile Tool
83
The Sampling Tool
84
Using the Solver Add-In
89
Summary
93
4
Analyzing Data with PivotTable and PivotChart Reports
95
Understanding PivotTable and PivotChart Reports
96
Analyzing Data with PivotTable Reports
105
Using the PivotTable Field List
110
Using the PivotTable Toolbar and Shortcut Menu
112
Field Settings
116
Filtering PivotTable Report Fields
118
Creating and Using Calculated Fields and Calculated Items
120
Analyzing Data with PivotChart Reports
124
Summary
127
5
Analyzing Data with Microsoft Access
129
Understanding Relational and Nonrelational Data
130
Connecting to External Data
132
Importing Data
133
Linking to External Data
136
Sorting and Filtering Data
138
Simple Sorting
138
Filtering Data
139
Advanced Filter/Sort
144
Querying Data
146
Creating Reports
150
Analyzing Data with PivotTable and PivotChart Views
153
Using Access to Integrate with SQL Server Databases
155
Analyzing Access Data over the Web
156
Summary
157
6
Analyzing Data with the Office Web Components
159
Introducing the Office Web Components
160
Creating Office Web Components
161
Using the Spreadsheet Component
162
Analyzing Data in the Spreadsheet Component
167
Office XP Spreadsheet Component Features
170
Using the PivotTable Component
172
Analyzing PivotTable Component Data
175
Additional Office XP PivotTable Component Features
181
Using the Chart Component
182
Analyzing Chart Component Data
185
Additional Office XP Chart Component Features
191
Summary
192
7
Introducing Online Analytical Processing
193
The Case for OLAP
194
What Is OLAP?
195
Using OLAP Data to Make Better Business Decisions
199
Understanding Online Transaction Processing
201
Data Storage Options
203
Using Microsoft Office to Analyze OLAP Data
204
Summary
206
8
Analyzing OLAP Data with Microsoft Excel
207
Connecting to OLAP Data
208
Connecting to OLAP Data with Microsoft Excel
208
Connecting to OLAP Data with the Office Web Components
211
Working with OLAP Data
214
Creating Offline Cubes
218
Summary
224
9
Analyzing OLAP Data with Microsoft Data Analyzer
225
Introducing Microsoft Data Analyzer
226
Exploring the Data Analyzer User Interface
227
Connecting to OLAP Data
232
Viewing and Analyzing Data with Data Analyzer
235
Creating Custom Measures
245
Making Better Business Decisions
251
Using the BusinessCenter
252
Exporting Data Analyzer Views to Excel, PowerPoint, or the Web
254
Summary
258
10
Working with XML Data in Excel and Access
259
The Case for XML
260
Making Sense of XML Data
261
Basic XML Terminology
262
Basic XML Rules
264
Analyzing XML Data with Microsoft Excel
266
Understanding and Working with the XML Spreadsheet Flattener
266
Understanding and Working with the XML Spreadsheet Schema
270
Understanding and Working with XML Data in Excel Web Queries
275
Working with XML Data with Microsoft Access
277
Summary
280
11
Extending Office Data Analysis Features with Code 283
Understanding Office Code: Macros and Procedures 284
Understanding Office Code: Programmatic Object Models
289
The Visual Basic Editor
290
The Object Browser and Online Help
291
Working with the Application Object
294
Extending the Data Analysis Features in Microsoft Excel
297
Sort, Filter, and Subtotal Lists of Records
297
Insert Standard Excel Worksheet Functions and Analysis ToolPak Functions
302
Conditionally Format Worksheet Cells
306
Create PivotTable Reports and PivotChart Reports
308
Opening and Saving XML Data in Excel
311
Programming the Data Analysis Features in Microsoft Access
313
Extending the Data Analysis Features in the Microsoft Office Web Components
315
Extending the Data Analysis Features in Microsoft Data Analyzer
321
Summary
326
12
Maintaining Data Reporting and Analysis Systems
329
Creating and Implementing Database Maintenance Plans
330
Maintaining SQL Server 2000 Databases
331
Documenting and Re-Creating SQL Server Databases
333
Backing Up and Restoring SQL Server Databases
334
Using the Database Maintenance Plan Wizard
335
Securing SQL Server Databases
336
Maintaining SQL Server 2000 Desktop Engine Databases
337
Maintaining Access Databases
337
Documenting Access Databases
337
Backing Up and Restoring Access Databases
338
Using the Table Analyzer Wizard
339
Using the Performance Analyzer
339
Compacting and Repairing Databases
339
Securing Access Databases
340
Maintaining SQL Server 2000 Analysis Services Systems
341
Archiving and Restoring OLAP Cubes
341
Processing OLAP Cubes
341
Additional Reading
343
Summary
343
A
Data Analysis Quick Reference
345
Microsoft Excel
345
Working with OLAP Data in Excel
349
PivotTable Reports and PivotChart Reports
351
Microsoft Access
353
Microsoft Office Web Components
356
Microsoft Data Analyzer
358
B
Additional Tools and Resources 363
CD Extras
363
Data Analyzer 3.5 Tutorial
363
Sample PivotTable Reports
364
Microsoft Office XP Web Component Toolpack
364
Online Data Analysis Resources
365
OLAP CubeCellValue for Microsoft Excel 2002
365
Access Links for Microsoft Excel 2002
365
Report Manager for Microsoft Excel 2002
366
Template Wizard with Data Tracking for Microsoft Excel 2002
367
XML Spreadsheet Converter for Microsoft Access 2002
367
INDEX
Paul Cornell
Paul Cornell has been with Microsoft for five years, serving as an inside sales representative, a sales trainer, and a documentation specialist. For the last two-and-a-half years he has been working as a writer and an editor in Office User Assistance. He also currently works for the MSDN Online Office Developer Center and the Microsoft Office Business Intelligence initiative. His projects have included work on the Microsoft Office XP Visual Basicr for Applications Language Reference, the Microsoft Office XP Web Services Toolkit, the Microsoft Office XP Fabrikam 2.0 Solution Sample, and the Microsoft Data Analyzer Tutorial. He writes the 'Office Talk' column for MSDN at http://msdn.microsoft.com/columns/office.asp and contributes to the 'Office Power User Corner' column for the Microsoft Office Assistance Center at http://office.microsoft.com/assistance.
