Pro PerformancePoint Server 2007: Building Business Intelligence Solutions

470 Pages·2008·20.59 MB·English

by  Philo Janus

cyan yelloW
MagenTa Black
panTone 123 c
Books for professionals By professionals® The eXperT’s Voice® in Business inTelligence
Companion
Author of eBook Available
Pro PerformancePoint Server 2007:
Pro InfoPath 2007
Building Business Intelligence Solutions P
r
Dear Reader, o

As I started working with Microsoft’s Business Intelligence (BI) solutions three P Pro
years ago, one thing that struck me was how straightforward much of the tech-
e
nology could be if it were simply demystified. Microsoft’s investment in BI has
grown significantly, and its goal has been to help make the technology easier r
to work with so that BI experts can focus on the business side of the business f PerformancePoint
intelligence equation. o
However, as with any new field, entering it can be difficult. There is simply r
the issue of where to start. My goal with this book is to lay out the Microsoft BI m
“stack” in a way that makes it possible to learn and understand how every part
applies to the overall goal of deriving value from large amounts of data—or as a Server 2007
many BI professionals put it, “turning data into information.”
n
Inside, I cover each of the following technologies in such a way as to take a
technical reader from introduction to implementation and basic understanding, c
giving you the foundation to delve into more technical documentation and trial e
and error:
P
• SQL Server Integration Services (data translation and migration)
• SQL Server Analysis Services (building data marts and OLAP cubes) o Building Business Intelligence Solutions
• SQL Server Reporting Services (web-based reports and charts) i
• SharePoint Business Intelligence (Excel Services and KPI lists) n
• ProClarity Analytics Server (ad hoc analysis charts) t
• PerformancePoint scorecards and dashboards
S
• PerformancePoint planning and modeling
I hope you find my guided tour helpful. e
Philo r Empowering productivity for the
v
business intelligence professional
e
Companion eBook
RelAted tItleS
r

2
0

See last page for details 0
on $10 eBook version
7

Philo Janus
SOURCE CODE ONLINE ISBN-13: 978-1-59059-961-7
J
www.apress.com ISBN-10: 1-59059-961-6 a
54999 n Foreword by Bill Baker,
u
s
US $49.99 Distinguished Engineer, Microsoft Corp.
Shelve in
Microsoft Servers
User level:
9 781590 599617
Beginner–Advanced
this print for content only—size & color not accurate spine = 0.894" 472 page count

9616Ch00CMP2 7/24/08 2:20 PM Page i
Pro PerformancePoint
Server 2007
Building Business Intelligence
Solutions
Philo Janus
9616Ch00CMP2 7/24/08 2:20 PM Page ii
Pro PerformancePoint Server 2007: Building Business Intelligence Solutions
Copyright © 2008 by Philo Janus
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-59059-961-7
ISBN-10 (pbk): 1-59059-961-6
ISBN-13 (electronic): 978-1-4302-0588-3
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editor: Jeffrey Pepper
Technical Reviewers: Dana Hoffman, Phillip Taylor
Editorial Board: Clay Andres, Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan
Gennick, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Frank Pohlmann, Ben Renow-Clarke,
Dominic Shakeshaft, Matt Wade, Tom Welsh
Project Manager: Richard Dal Porto
Copy Editors: Damon Larson, Heather Lang
Associate Production Director: Kari Brooks-Copony
Production Editor: Katie Stence
Compositor and Artist: Van Winkle Design Group
Proofreader: April Eddy
Indexer: Broccoli Information Management
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone [phone], fax [phone], e-mail [email protected], or visit
http://www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our Special
Bulk Sales—eBook Licensing web page at http://www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every precaution
has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any
person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by
the information contained in this work.
The source code for this book is available to readers at http://www.apress.com. You may need to answer
questions pertaining to this book in order to successfully download the code.
9616Ch00CMP2 7/24/08 2:20 PM Page iii
For Pamela Janus,my mother,who sparked my love of logic,mathematics,and reading.
9616Ch00CMP2 7/24/08 2:20 PM Page iv
9616Ch00CMP2 7/24/08 2:20 PM Page v
Contents at a Glance
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■ CHAPTER 1 Business Intelligence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
■ CHAPTER 2 Overview of Microsoft’s Business Intelligence Platform . . . . . . . . 17
■ CHAPTER 3 SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
■ CHAPTER 4 SQL Server Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
■ CHAPTER 5 SQL Server Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
■ CHAPTER 6 SQL Server Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
■ CHAPTER 7 Data Mining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
■ CHAPTER 8 Business Intelligence in Excel and SharePoint . . . . . . . . . . . . . . . . 189
■ CHAPTER 9 ProClarity Analytics Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
■ CHAPTER 10 PerformancePoint Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
■ CHAPTER 11 Advanced Scorecarding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
■ CHAPTER 12 Dashboards and Reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
■ CHAPTER 13 Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
■ CHAPTER 14 Management Reporter. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
v
9616Ch00CMP2 7/24/08 2:20 PM Page vi
9616Ch00CMP2 7/24/08 2:20 PM Page vii
Contents
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
■ CHAPTER 1 Business Intelligence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
What Is Business Intelligence? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Scorecards vs.Dashboards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Key Performance Indicators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
KPIs and Business Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
The Law of Unintended Consequences . . . . . . . . . . . . . . . . . . . . . . . . . 7
Strategy Maps . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Data Silos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Data Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Why Do I Care? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
The Microsoft Business Intelligence Stack . . . . . . . . . . . . . . . . . . . . . . . . . 13
SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Microsoft Office 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Microsoft Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
A Successful Business Intelligence Engagement . . . . . . . . . . . . . . . . . . . . 15
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
■ CHAPTER 2 Overview of Microsoft’s
Business Intelligence Platform . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
SQL Server Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
SQL Server Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
SQL Server Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
SharePoint Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Excel Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
SharePoint KPI Lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
vii
9616Ch00CMP2 7/24/08 2:20 PM Page viii
viii ■ CONTENTS
ProClarity 6.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
PerformancePoint Server 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Monitoring and Analytics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Modeling and Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
■ CHAPTER 3 SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
SQL Server Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Compact Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Express Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Workgroup Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Standard Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Enterprise Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Developer Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Business Intelligence Development Studio . . . . . . . . . . . . . . . . . . . . 40
Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Programmability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Web Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Query Notifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Database Mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
XML Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
XQuery and Data Manipulation Language . . . . . . . . . . . . . . . . . . . . . 46
XML Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
High Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Failover Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Online Index Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Database Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47