Microsoft Office 2007 business intelligence : reporting, analysis, and measurement from the desktop

454 Pages·2008·49.964 MB·English

by  Doug Harts

Microsoft® Office 2007
Business Intelligence:
Reporting, Analysis, and
Measurement from the Desktop
Doug Harts
New York Chicago San Francisco
Lisbon London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2008 by The McGraw-Hill Companies. All rights reserved. Manufactured in the United States of America.
Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or
distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of
the publisher.
0-07-159529-5
The material in this eBook also appears in the print version of this title: 0-07-149424-3.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a
trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention
of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in
corporate training programs. For more information, please contact George Hoare, Special Sales, at george_hoare@
mcgraw-hill.com or [phone].
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in
and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the
right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce,
modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any
part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any
other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these
terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR
WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED
FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE
WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS
FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained
in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor
its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or
for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed
through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental,
special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of
them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause
whatsoever whether such claim or cause arises in contract, tort or otherwise.
About the Author
Doug Harts is the Chief Technology Officer at Cizer Software (www.cizer.com),
a Microsoft Gold Partner focused on delivering packaged software and professional
services to enhance the Microsoft Business Intelligence platform. Doug led Cizer’s
Joint Development Partnership with the Redmond SQL Server 2000 Reporting
Services group, resulting in the Cizer.Net Ad-Hoc reporting toolset for Report
Definition Language (RDL) that extends SQL Server 2005 Reporting Services
and functions as a Trusted Report Source within PerformancePoint dashboards.
He has been a participant on Microsoft’s Business Intelligence Partner Advisory
Council and is currently a member of the Excel Advisory Council. His experience
encompasses 20 years of delivering BI solutions to large, geographically dispersed
organizations. This work was responsible for the first sale of Microsoft’s Business
Scorecard Manager in the history of the product, which has progressed to the
delivery of PerformancePoint solutions to government and corporate accounts in all
aspects of monitoring, analysis, and planning. Doug maintains the Office Business
Intelligence & Data Mining community web site at www.OfficeBIcentral.com.
About the Technical Editor
Sean Boon is a Program Manager at Microsoft and has ten years of experience in
the business intelligence industry building data warehouses and designing software.
Prior to working on the first version of Excel Services that ships as part of Microsoft
Office SharePoint Server, Sean worked on the BI Practices team that developed the
SQL Server Accelerator for Business Intelligence. He is currently working on the
next release of SQL Server Reporting Services.
For more information about this title, click here
Contents
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii
Chapter 1 Introduction to Microsoft Office BI Concepts . . . . . . . . . . . . . . . . . . . . . 1
The Scope of the Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
The Book’s Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Setting the Stage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Chapter Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Chapter 2: Excel PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Chapter 3: Excel Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Chapter 4: Excel and External Data . . . . . . . . . . . . . . . . . . . . . . . . . 6
Chapter 5: SharePoint 2007 Dashboards and Reports . . . . . . . . . . . . . . . . 7
Chapter 6: Data Mining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Chapter 7: PerformancePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Chapter 8: All the Other Microsoft BI Pieces . . . . . . . . . . . . . . . . . . . . . 10
Chapter 2 PivotTables, PivotCharts, and Conditional Formatting . . . . . . . . . . . . . . . 13
What Is a PivotTable? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Where Do PivotTables Fit in the Microsoft BI Picture? . . . . . . . . . . . . . . . . 15
Opening an Excel 2003 Spreadsheet in Excel 2007 . . . . . . . . . . . . . . . . . . . . . 17
The Business Reason for a PivotTable . . . . . . . . . . . . . . . . . . . . . . . . 18
Save As Excel 2007: New 2007 Options and Excel File Formats . . . . . . . . . . . 18
Office 2007 Ribbon Menu: A Brand-New Look in 2007 . . . . . . . . . . . . . . . . 21
Getting Started: Selecting Data for the PivotTable . . . . . . . . . . . . . . . . . . . . . . 24
How to Choose PivotTable Fields That Make Sense . . . . . . . . . . . . . . . . . . 26
The PivotTable 2007 Interface: Understanding Screen Components . . . . . . . . . . 27
PivotTable Columns, Values, Rows, Filters: The New Look in 2007 . . . . . . . . . . 28
The PivotTable Field List Pane: Data for the PivotTable . . . . . . . . . . . . . . . . 30
PivotTable Fields: Choosing Data Elements in Excel 2007 . . . . . . . . . . . . . . . 31
Basic PivotTable Formatting and Data Element Placement . . . . . . . . . . . . . . . . . . 38
Report Filters on the PivotTable: How to Filter the Entire PivotTable . . . . . . . . . 41
PivotTable Value Header Labels and a Percentage Column . . . . . . . . . . . . . . 42
PivotTable Field Settings: Suppressing Subtotals and Formatting . . . . . . . . . . . 47
iv
Contents v
Using the Ribbon Menu for More Advanced Formatting . . . . . . . . . . . . . . . . . . . 49
Business Intelligence Presentations . . . . . . . . . . . . . . . . . . . . . . . . . 50
PivotTable Menu Options for the Entire PivotTable . . . . . . . . . . . . . . . . . . 51
PivotTable Active Field Menu Options: Collapse and Expand . . . . . . . . . . . . . 52
PivotTable Sorting with On-screen Data . . . . . . . . . . . . . . . . . . . . . . . 57
PivotTable Data Menu Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
PivotTable Actions Menu Options . . . . . . . . . . . . . . . . . . . . . . . . . . 58
PivotTable Tools: Creating a PivotChart . . . . . . . . . . . . . . . . . . . . . . . 59
PivotTable Show/Hide Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Ribbon Menu Design Tab: Power Formatting . . . . . . . . . . . . . . . . . . . . . . . . . 65
PivotTable Layout Screen Design . . . . . . . . . . . . . . . . . . . . . . . . . . 66
PivotTable Style Options: Banded Rows . . . . . . . . . . . . . . . . . . . . . . . 68
PivotTable Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
The Right Data: Filters for PivotTable and PivotChart Data . . . . . . . . . . . . . . . . . . 72
PivotTable Field Filter Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
PivotTable Field Collapse/Expand Controls . . . . . . . . . . . . . . . . . . . . . . 77
Swapping Row and Column Fields in the PivotTable:Slice It and Dice It . . . . . . . . 79
Conditional Formatting Inside a PivotTable . . . . . . . . . . . . . . . . . . . . . . . . . 81
Conditional Formatting Data Bars . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Conditional Formatting Color Scales . . . . . . . . . . . . . . . . . . . . . . . . . 82
Conditional Formatting Icon Sets . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Chapter 3 Excel Tables: Conditional Formatting,
What-If and Statistics, and Charts . . . . . . . . . . . . . . . . . . . . . . . . . . 87
What Is an Excel Table? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Where Do Excel Tables Fit in the Microsoft BI Picture? . . . . . . . . . . . . . . . . 90
Opening an Excel 2003 Spreadsheet in Excel 2007 . . . . . . . . . . . . . . . . . . . . . 91
The Business Reason for an Excel Table . . . . . . . . . . . . . . . . . . . . . . . 92
Save As Excel 2007: New 2007 Options and Excel File Formats . . . . . . . . . . . 93
Office 2007 Ribbon Menu: A Brand-New Look in 2007 . . . . . . . . . . . . . . . . 95
Selecting Data for the Excel Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Using the Excel Table User Interface and Ribbon Menu . . . . . . . . . . . . . . . . 100
Sorting Data in the Excel Table . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Entering Data into Excel Tables with Data Validation . . . . . . . . . . . . . . . . . 103
Excel Tables and Data Filtering on Columns . . . . . . . . . . . . . . . . . . . . . 109
Excel Tables with Groups and Subtotals . . . . . . . . . . . . . . . . . . . . . . . 111
Excel Tables with Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . 114
Adding a Calculated Column to an Excel Table . . . . . . . . . . . . . . . . . . . . 117
Excel Tables and Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . 120
Excel What-If Applied to an Excel Table . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Excel Statistical ToolPak Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
vi Microsoft Office 2007 Business Intelligence
Excel 2007 Charts with Excel Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Excel Chart Axis Labels and Data Series . . . . . . . . . . . . . . . . . . . . . . . 134
Formatting Excel Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Chapter 4 Excel and External Data: Files, Databases, Cubes, and Data from the Internet . . . 143
What Is an External Data Connection? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Where Do Excel External Data Connections Fit in the Microsoft BI Picture? . . . . . . 146
Importing Data from a Text File into Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . 148
The Truth about Importing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Get External Data from Text . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Understanding Screen Components in the Excel User Interface . . . . . . . . . . . . 150
Excel 2007 Text Import Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Office 2007 Trusted Locations . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Importing Additional Text Data: Concatenation . . . . . . . . . . . . . . . . . . . . 160
Using Excel Tools to Clean Up Imported Data . . . . . . . . . . . . . . . . . . . . 163
Formatting the Imported Data as an Excel Table . . . . . . . . . . . . . . . . . . . 164
Subtotaling the Excel Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Charting the Excel Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Importing Access Data into Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Using MS Query SQL to Join Tables and Import Data . . . . . . . . . . . . . . . . . 173
Importing Oracle Data into Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Oracle Client Requirements for Excel Connection . . . . . . . . . . . . . . . . . . . 181
Importing SQL Server Data into Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . . . 182
Excel Direct-Connect to SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . 183
Importing SQL Server OLAP Data into Excel 2007 . . . . . . . . . . . . . . . . . . . . . . 186
What Is an Analysis Services OLAP Cube? . . . . . . . . . . . . . . . . . . . . . . 187
Excel Direct-Connect to SQL Server OLAP Cubes . . . . . . . . . . . . . . . . . . . 187
Excel PivotTable Display of SQL Server Cubes . . . . . . . . . . . . . . . . . . . . 190
Excel Slice and Dice with PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . 195
Excel Conditional Formatting with PivotTables . . . . . . . . . . . . . . . . . . . . 196
Excel PivotChart with PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Connecting Excel to the Internet with Web Queries . . . . . . . . . . . . . . . . . . . . . . 200
Excel X Y Scatter Chart with Trendline . . . . . . . . . . . . . . . . . . . . . . . . 204
Chapter 5 Excel and SharePoint: Reports,
Key Performance Indicators, and Dashboards . . . . . . . . . . . . . . . . . . . . 207
What Is a SharePoint Portal? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Where Does a SharePoint Portal Fit in the Microsoft BI Picture? . . . . . . . . . . . 211
SharePoint 2007 Portal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
SharePoint 2007 Reports Site . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Report Center Reports Library Excel Workbooks . . . . . . . . . . . . . . . . . . . 215
Report Center Reports Library Excel Properties and Permissions . . . . . . . . . . . 221
Contents vii
SharePoint 2007 Data Connections Library . . . . . . . . . . . . . . . . . . . . . . . . . 228
Office Data Connections Available to Excel on End-user Computers . . . . . . . . . . 231
Creating an Excel Report with a SharePoint Office Data Connection . . . . . . . . . . 232
Publishing an Excel 2007 Report to SharePoint 2007 Excel Services . . . . . . . . . . . . . 239
A SharePoint 2007 Dashboard with an Excel Services Report . . . . . . . . . . . . . 244
Key Performance Indicators in SharePoint 2007 . . . . . . . . . . . . . . . . . . . . . . . 249
SharePoint KPIs from a SharePoint List . . . . . . . . . . . . . . . . . . . . . . . 250
SharePoint KPIs from an Excel Workbook . . . . . . . . . . . . . . . . . . . . . . 254
SharePoint KPIs from an Analysis Services Cube . . . . . . . . . . . . . . . . . . . 258
SharePoint KPIs from Manual Entry . . . . . . . . . . . . . . . . . . . . . . . . . 262
SharePoint Images and Links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
SharePoint Check In, Version History, and SharePoint Designer . . . . . . . . . . . 267
Chapter 6 Excel and Data Mining: Key Influencers,
Categories, and Forecasting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
What Is Data Mining? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Downloading the Excel 2007 Data Mining Add-In . . . . . . . . . . . . . . . . . . 276
Where Does Data Mining Fit in the Microsoft BI Picture? . . . . . . . . . . . . . . . 277
Get External Data from SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . 279
Excel User Data Mining Interface: Understanding the Screen Components . . . . . . 281
Excel 2007: Data Mining Analyze Ribbon Menu Tools . . . . . . . . . . . . . . . . . . . . 283
Excel 2007: Data Mining Analyze Tab, Analysis Tools . . . . . . . . . . . . . . . . . . . . 284
Excel Table: Analyze Key Influencers . . . . . . . . . . . . . . . . . . . . . . . . 284
Excel Table: Analyze Key Influencers, Discrimination Report . . . . . . . . . . . . . 288
Excel Table: Detect Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Excel Table: Detect Categories Continuing Analysis . . . . . . . . . . . . . . . . . . 293
Excel 2007: Data Mining Analyze Tab, Predictive Tools . . . . . . . . . . . . . . . . . . . 298
Excel Table: Fill From Example . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Excel Table: Forecast . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Excel Table: Highlight Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Excel Table: Goal Seek . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Excel Table: What-If . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Excel 2007: Data Mining Tab Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Excel Model: Explore Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Excel Model: Cluster . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Visio 2007: Data Mining Cluster Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Chapter 7 PerformancePoint: Dashboards, Scorecards, and Key Performance Indicators . . . 321
What Is a PerformancePoint Scorecard? . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
Where Does PerformancePoint Fit in the Microsoft BI Picture? . . . . . . . . . . . . 324
viii Microsoft Office 2007 Business Intelligence
PerformancePoint 2007 Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
PerformancePoint 2007 Dashboard Designer . . . . . . . . . . . . . . . . . . . . . . . . 328
Dashboard Filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Dashboard Display Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . 336
KPI Design and Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
KPI Scoring Patterns and Banding Methods . . . . . . . . . . . . . . . . . . . . . 340
KPI Actual and Target Data Mapping . . . . . . . . . . . . . . . . . . . . . . . . 344
Scorecard Design and Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Report Design and Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Analytic Grid Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Analytic Chart Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Trend Analysis Chart Design: Data Mining . . . . . . . . . . . . . . . . . . . . . . 360
Dashboard Filters with Scorecards and Analytic Charts . . . . . . . . . . . . . . . . 364
Data Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Indicator Design and Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Strategy Map Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368
Chapter 8 Office BI: Office, Visio, Windows Mobile,
Office Online, SQL Server, and Virtual Earth . . . . . . . . . . . . . . . . . . . . . 371
What Are the Other Parts of Office Business Intelligence? . . . . . . . . . . . . . . . . . . 373
Where Do These Other Pieces Fit in the Microsoft BI Picture? . . . . . . . . . . . . . 374
Outlook 2007 SharePoint View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Connecting SharePoint to Outlook 2007 . . . . . . . . . . . . . . . . . . . . . . . 377
SharePoint Objects in Outlook 2007 . . . . . . . . . . . . . . . . . . . . . . . . . 380
PowerPoint 2007 Charts with Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Publishing PowerPoint 2007 to SharePoint . . . . . . . . . . . . . . . . . . . . . . 389
Visio 2007 PivotDiagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
Saving Visio 2007 to SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Word 2007 and Excel 2007 PivotTables . . . . . . . . . . . . . . . . . . . . . . . . . . . 402
Windows Mobile 6.0 and Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Copying the Excel Spreadsheet to a Smartphone . . . . . . . . . . . . . . . . . . . 404
SharePoint Alerts of Spreadsheet Changes to a Smartphone . . . . . . . . . . . . . 409
Office Online . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414
SQL Server Business Intelligence Platform Technologies . . . . . . . . . . . . . . . . . . . 417
SQL Server Integration Services (SSIS) . . . . . . . . . . . . . . . . . . . . . . . . 418
SQL Server Analysis Services (SSAS) . . . . . . . . . . . . . . . . . . . . . . . . . 423
SQL Server Reporting Services (SSRS) . . . . . . . . . . . . . . . . . . . . . . . . 425
Virtual Earth in a Business Intelligence Context . . . . . . . . . . . . . . . . . . . . . . . 427
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Foreword
D
oug Harts and I have been colleagues in the Microsoft Business
Intelligence Partner Advisory Council for years. We’ve been friends
as long. Over those years we’ve compared notes as both fathers and as
practitioners in business intelligence. When Doug told me he was writing this
book, I was excited for him and for you, his readers. Doug’s passion for business
intelligence carries faithfully into this book. I believe you will find Doug’s
writing clear and concise. He uses practical examples with plenty of step-by-
step instructions. Every chapter will help you grow your knowledge of business
intelligence and Microsoft Office.
I have another reason to be excited about this book. We don’t have many books
on business intelligence in Microsoft Office. Given the very important role Office
plays in the Microsoft BI offering, this surprises me. Over the years many customers
have asked about successful patterns for implementing business intelligence. At
Microsoft, our advice has been to start with the data layer and invest in “sound data.”
That is, make sure you know where your data comes from, how clean and consistent
it is, and how your users access it. Sound data is so fundamental to sound business
intelligence that we started our BI efforts in the SQL Server team at Microsoft.
Sound data alone is insufficient to improve companies. The next layer is
just as critical. That layer supports personal and team insights. This is the role
of Office in the Microsoft BI stack. I don’t know any way to make people
more insightful. But I do know that insights usually come from hunches. We
have hunches all the time. A successful BI tool, built on sound data, lets employees
explore their hunches and test them against facts. When the data is easily found
and explored, using familiar tools, the process of going from hunch to insight is
enhanced. Excel is simply the most natural tool for data work for huge numbers
of people. The work the Excel team has done over many releases has provided our
customers with a first-class business intelligence tool. SharePoint enables sharing of
not just documents, but the insight our users develop using Excel—turning personal
insight into team insight.
ix
x Microsoft Office 2007 Business Intelligence
Business intelligence becomes truly impactful for corporations and organizations
when it leads to better decisions. Insight alone is not enough; action leading to
improved business performance is the true end goal. The top layer of the Microsoft
stack is performance management. PerformancePoint Server 2007 is the Microsoft
tool that builds on SQL Server and Microsoft Office (both Excel and SharePoint)
and helps our customers measure and manage performance.
At Microsoft, we believe true business intelligence comes when you trust your
data, trust your insights, and trust your decisions.
Regarding this book, check out Chapter 5, which introduces Microsoft Office
SharePoint Server’s (MOSS) role in business intelligence. This chapter may be
acronym-laden (ODC, DCL, etc.), but it also provides an excellent overview of the
Report Center, KPI lists, and other BI features of SharePoint.
Chapter 6 covers data mining. Data mining has been a practical, valuable, but
relatively little-used analysis technique for years. SQL Server introduced data
mining functionality in SQL Server 2000. With the Microsoft SQL Server 2005 Data
Mining Add-In for Office 2007 (yes, two different years in that name), Excel users
can easily add data mining to their analytic arsenal. All the details are in Chapter 6.
I hope you enjoy this book and profit from it.
Bill Baker
Distinguished Engineer and GM,
Business Intelligence Applications
Microsoft Corporation
Redmond, WA
October 2007