Delivering
Business
intelligence
Microsoft
®
SQL Server
™
2008
About the Author
Brian Larson is a Phi Beta Kappa graduate of Luther College in Decorah, Iowa,
with degrees in physics and computer science. Brian has 23 years of experience in the
computer industry and 19 years experience as a consultant creating custom database
applications. He is currently the Chief of Technology for Superior Consulting
Services in Minneapolis, Minnesota, a Microsoft Consulting Partner for Reporting
Services. Brian is a Microsoft Certified Solution Developer (MCSD) and a
Microsoft Certified Database Administrator (MCDBA).
Brian served as a member of the original Reporting Services development team
as a consultant to Microsoft. In that role, he contributed to the original code base of
Reporting Services.
Brian has presented at national conferences and events, including the SQL
Server Magazine Connections Conference, the PASS Community Summit, and
the Microsoft Business Intelligence Conference, and has provided training and
mentoring on Reporting Services across the country. He has been a contributor and
columnist for SQL Server Magazine. In addition to this book, Brian is the author of
Microsoft SQL Server 2008 Reporting Services, also from McGraw-Hill.
Brian and his wife Pam have been married for 23 years. Pam will tell you that
their first date took place at the campus computer center. If that doesn’t qualify
someone to write a computer book, then I don’t know what does. Brian and Pam
have two children, Jessica and Corey.
About the Technical Editor

MHID: 0-07-154945-5
The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-154944-8, MHID: 0-07-154944-7.
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. To contact a representative please visit the Contact Us page at www.mhprofessional.com.
Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of
human or mechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy,
or completeness of any information and is not responsible for any errors or omissions or the results obtained from the use of
such information.
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 WAR-
RANTIES 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, INCLUD-
ING 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 there-
from. 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

viii
Delivering Business Intelligence with Microsoft SQL Server 2008
Part V Delivering
Chapter 16 On Report—Delivering Business Intelligence with Reporting Services . . . . 561
Chapter 17 Falling into Place—Managing Reporting Services Reports . . . . . . . . . . . 643
Chapter 18 Let’s Get Together—Integrating OLAPwith Your Applications . . . . . . . . . 683
Chapter 19 Another Point of View—Excel Pivot Tablesand Pivot Charts . . . . . . . . . . 723
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 741
ix

Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
The Maximum Miniatures Databases and Other Supporting Materials . . . . . xviii
Part I Business Intelligence
Chapter 1 Equipping the Organization for Effective Decision Making . . . . . . . . . . . . . 3
Effective Decision Making . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Who Is a Decision Maker? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
What Is an Effective Decision? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Keys to Effective Decision Making . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Are We Going Hither or Yon? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Is Your Map Upside-Down? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Panicked Gossip, the Crow’s Nest, or the Wireless . . . . . . . . . . . . . . . . . . . . . . . . 9
Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Business Intelligence and Microsoft SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . 12
Chapter 2 Making the Most of What You’ve Got—Using Business Intelligence . . . . . . .

13
What Business Intelligence Can Do for You . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
When We Know What We Are Looking For . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Discovering New Questions and Their Answers . . . . . . . . . . . . . . . . . . . . . . . . . 15

The Business Intelligence Development Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Visual Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Navigating the Business Intelligence Development Studio . . . . . . . . . . . . . . . . . . 64
Business Intelligence Development Studio Options . . . . . . . . . . . . . . . . . . . . . . . 78
The SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
The SQL Server Management Studio User Interface . . . . . . . . . . . . . . . . . . . . . . . 82
Don Your Hardhat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Part II Defining Business Intelligence Structures
Chapter 6 Building Foundations—Creating Data Marts . . . . . . . . . . . . . . . . . . . . . 91
Data Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Who Needs a Data Mart Anyway? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Designing a Data Mart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Decision Makers’ Needs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Available Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
Data Mart Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Creating a Data Mart Using the SQL Server Management Studio . . . . . . . . . . . . . . . 109
Creating a Data Mart Using the Business Intelligence Development Studio . . . . . . . . . 117
Contents
xi
Table Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Types of Table Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
The Benefits of Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Chapter 7 Transformers—Integration Services Structure and Components . . . . . . . . . 135
Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Package Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Package Items . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Control Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Data Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Getting Under the Sink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Chapter 8 Fill ’er Up—Using Integration Services for Populating Data Marts . . . . . . . . 233

. . . . . . . . . . . . . . . . .
331
Where No Cube Has Gone Before . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Deploying and Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333
Deploying from the Business Intelligence Development Studio . . . . . . . . . . . . . . . . 334
Deploying from the Analysis Services Deployment Wizard . . . . . . . . . . . . . . . . . . . 340
Additional Cube Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Linked Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
The Business Intelligence Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Key Performance Indicators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Actions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
Partitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Aggregation Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Perspectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Translations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
More Sophisticated Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Chapter 11 Writing a New Script—MDX Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . 389
Terms and Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
Where Are We? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
Getting There from Here . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
Putting MDX Scripting to Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
Cube Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
This Year to Last Year Comparisons and Year-to-Date Rollups . . . . . . . . . . . . . . . . . 426
Extracting Data from Cubes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Chapter 12 Pulling It Out and Building It Up—MDX Queries . . . . . . . . . . . . . . . . . . . . 433
The MDX SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434
The Basic MDX SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435
Additional Tools for Querying . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Additional Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
Additional MDX Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458

Microsoft Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521
Microsoft Neural Network . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523
Microsoft Association Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
Microsoft Sequence Clustering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 526
Microsoft Time Series . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
Reading the Tea Leaves . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
Chapter 15 Spelunking—Exploration Using Data Mining . . . . . . . . . . . . . . . . . . . . . 529
Mining Accuracy Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530
Column Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
Lift Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532

xiv
Delivering Business Intelligence with Microsoft SQL Server 2008
Profit Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
Classification Matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538
Cross Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539
Mining Model Prediction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541
A Singleton Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541
A Prediction Join Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
Data Mining Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
Prediction Query Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
Types of Prediction Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552
Special Delivery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
Part V Delivering
Chapter 16 On Report—Delivering Business Intelligence with Reporting Services . . . . . 561
Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
Report Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563
Report Delivery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565
Report Serving Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566
Report Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566

Chapter 18 Let’s Get Together—Integrating OLAPwith Your Applications . . . . . . . . . . . 683
ADOMD.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684
ADOMD.NET Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684
ADOMD.NET Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687
Using Reporting Services Without the Report Manager . . . . . . . . . . . . . . . . . . . . . . . . . 693
URL Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
Web Service Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 710
The Report Viewer Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 715
Ready-Made Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721
Chapter 19 Another Point of View—Excel Pivot Tablesand Pivot Charts . . . . . . . . . . . . 723
Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 724
Creating Pivot Tables and Pivot Charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 724
Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 725
Pivot Chart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 735
Great Capabilities, Great Opportunities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 738
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 741