Microsoft ® Office 2007 Business Intelligence

Home >
Browse >
Microsoft ® Office 2007 Business Intelligence

Microsoft ® Office 2007 Business Intelligence

Authors: Doug Harts

Published: November 2007

eISBN: 9780071595292 0071595295 | ISBN: 9780071494243

Contents

Foreword

Preface

Acknowledgments

Chapter 1 Introduction to Microsoft Office BI Concepts

The Scope of the Book

The Book’s Purpose

Setting the Stage

Chapter Content

Chapter 2: Excel PivotTables

Chapter 3: Excel Tables

Chapter 4: Excel and External Data

Chapter 5: SharePoint 2007 Dashboards and Reports

Chapter 6: Data Mining

Chapter 7: PerformancePoint

Chapter 8: All the Other Microsoft BI Pieces

Chapter 2 PivotTables, PivotCharts, and Conditional Formatting

What Is a PivotTable?

Where Do PivotTables Fit in the Microsoft BI Picture?

Opening an Excel 2003 Spreadsheet in Excel 2007

The Business Reason for a PivotTable

Save As Excel 2007: New 2007 Options and Excel File Formats

Office 2007 Ribbon Menu: A Brand-New Look in 2007

Getting Started: Selecting Data for the PivotTable

How to Choose PivotTable Fields That Make Sense

The PivotTable 2007 Interface: Understanding Screen Components

PivotTable Columns, Values, Rows, Filters: The New Look in 2007

The PivotTable Field List Pane: Data for the PivotTable

PivotTable Fields: Choosing Data Elements in Excel 2007

Basic PivotTable Formatting and Data Element Placement

Report Filters on the PivotTable: How to Filter the Entire PivotTable

PivotTable Value Header Labels and a Percentage Column

PivotTable Field Settings: Suppressing Subtotals and Formatting

Using the Ribbon Menu for More Advanced Formatting

Business Intelligence Presentations

PivotTable Menu Options for the Entire PivotTable

PivotTable Active Field Menu Options: Collapse and Expand

PivotTable Sorting with On-screen Data

PivotTable Data Menu Options

PivotTable Actions Menu Options

PivotTable Tools: Creating a PivotChart

PivotTable Show/Hide Group

Ribbon Menu Design Tab: Power Formatting

PivotTable Layout Screen Design

PivotTable Style Options: Banded Rows

PivotTable Styles

The Right Data: Filters for PivotTable and PivotChart Data

PivotTable Field Filter Controls

PivotTable Field Collapse/Expand Controls

Swapping Row and Column Fields in the PivotTable: Slice It and Dice It

Conditional Formatting Inside a PivotTable

Conditional Formatting Data Bars

Conditional Formatting Color Scales

Conditional Formatting Icon Sets

Chapter 3 Excel Tables: Conditional Formatting, What-If and Statistics, and Charts

What Is an Excel Table?

Where Do Excel Tables Fit in the Microsoft BI Picture?

Opening an Excel 2003 Spreadsheet in Excel 2007

The Business Reason for an Excel Table

Save As Excel 2007: New 2007 Options and Excel File Formats

Office 2007 Ribbon Menu: A Brand-New Look in 2007

Selecting Data for the Excel Table

Using the Excel Table User Interface and Ribbon Menu

Sorting Data in the Excel Table

Entering Data into Excel Tables with Data Validation

Excel Tables and Data Filtering on Columns

Excel Tables with Groups and Subtotals

Excel Tables with Conditional Formatting

Adding a Calculated Column to an Excel Table

Excel Tables and Conditional Formatting

Excel What-If Applied to an Excel Table

Excel Statistical ToolPak Add-In

Excel 2007 Charts with Excel Tables

Excel Chart Axis Labels and Data Series

Formatting Excel Charts

Chapter 4 Excel and External Data: Files, Databases, Cubes, and Data from the Internet

What Is an External Data Connection?

Where Do Excel External Data Connections Fit in the Microsoft BI Picture?

Importing Data from a Text File into Excel 2007

The Truth about Importing Data

Get External Data from Text

Understanding Screen Components in the Excel User Interface

Excel 2007 Text Import Wizard

Office 2007 Trusted Locations

Importing Additional Text Data: Concatenation

Using Excel Tools to Clean Up Imported Data

Formatting the Imported Data as an Excel Table

Subtotaling the Excel Data

Charting the Excel Data

Importing Access Data into Excel 2007

Using MS Query SQL to Join Tables and Import Data

Importing Oracle Data into Excel 2007

Oracle Client Requirements for Excel Connection

Importing SQL Server Data into Excel 2007

Excel Direct-Connect to SQL Server

Importing SQL Server OLAP Data into Excel 2007

What Is an Analysis Services OLAP Cube?

Excel Direct-Connect to SQL Server OLAP Cubes

Excel PivotTable Display of SQL Server Cubes

Excel Slice and Dice with PivotTables

Excel Conditional Formatting with PivotTables

Excel PivotChart with PivotTables

Connecting Excel to the Internet with Web Queries

Excel X Y Scatter Chart with Trendline

Chapter 5 Excel and SharePoint: Reports, Key Performance Indicators, and Dashboards

What Is a SharePoint Portal?

Where Does a SharePoint Portal Fit in the Microsoft BI Picture?

SharePoint 2007 Portal

SharePoint 2007 Reports Site

Report Center Reports Library Excel Workbooks

Report Center Reports Library Excel Properties and Permissions

SharePoint 2007 Data Connections Library

Office Data Connections Available to Excel on End-user Computers

Creating an Excel Report with a SharePoint Office Data Connection

Publishing an Excel 2007 Report to SharePoint 2007 Excel Services

A SharePoint 2007 Dashboard with an Excel Services Report

Key Performance Indicators in SharePoint 2007

SharePoint KPIs from a SharePoint List

SharePoint KPIs from an Excel Workbook

SharePoint KPIs from an Analysis Services Cube

SharePoint KPIs from Manual Entry

SharePoint Images and Links

SharePoint Check In, Version History, and SharePoint Designer

Chapter 6 Excel and Data Mining: Key Influencers, Categories, and Forecasting

What Is Data Mining?

Downloading the Excel 2007 Data Mining Add-In

Where Does Data Mining Fit in the Microsoft BI Picture?

Get External Data from SQL Server

Excel User Data Mining Interface: Understanding the Screen Components

Excel 2007: Data Mining Analyze Ribbon Menu Tools

Excel 2007: Data Mining Analyze Tab, Analysis Tools

Excel Table: Analyze Key Influencers

Excel Table: Analyze Key Influencers, Discrimination Report

Excel Table: Detect Categories

Excel Table: Detect Categories Continuing Analysis

Excel 2007: Data Mining Analyze Tab, Predictive Tools

Excel Table: Fill From Example

Excel Table: Forecast

Excel Table: Highlight Exceptions

Excel Table: Goal Seek

Excel Table: What-If

Excel 2007: Data Mining Tab Tools

Excel Model: Explore Data

Excel Model: Cluster

Visio 2007: Data Mining Cluster Diagram

Chapter 7 PerformancePoint: Dashboards, Scorecards, and Key Performance Indicators

What Is a PerformancePoint Scorecard?

Where Does PerformancePoint Fit in the Microsoft BI Picture?

PerformancePoint 2007 Planning

PerformancePoint 2007 Dashboard Designer

Dashboard Filters

Dashboard Display Configuration

KPI Design and Configuration

KPI Scoring Patterns and Banding Methods

KPI Actual and Target Data Mapping

Scorecard Design and Configuration

Report Design and Configuration

Analytic Grid Design

Analytic Chart Design

Trend Analysis Chart Design: Data Mining

Dashboard Filters with Scorecards and Analytic Charts

Data Sources

Indicator Design and Configuration

Strategy Map Design

Chapter 8 Office BI: Office, Visio, Windows Mobile, Office Online, SQL Server, and Virtual Earth

What Are the Other Parts of Office Business Intelligence?

Where Do These Other Pieces Fit in the Microsoft BI Picture?

Outlook 2007 SharePoint View

Connecting SharePoint to Outlook 2007

SharePoint Objects in Outlook 2007

PowerPoint 2007 Charts with Excel 2007

Publishing PowerPoint 2007 to SharePoint

Visio 2007 PivotDiagram

Saving Visio 2007 to SharePoint

Word 2007 and Excel 2007 PivotTables

Windows Mobile 6.0 and Excel 2007

Copying the Excel Spreadsheet to a Smartphone

SharePoint Alerts of Spreadsheet Changes to a Smartphone

Office Online

SQL Server Business Intelligence Platform Technologies

SQL Server Integration Services (SSIS)

SQL Server Analysis Services (SSAS)

SQL Server Reporting Services (SSRS)

Virtual Earth in a Business Intelligence Context

Index