Table Of ContentDelivering
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
Robert M. Bruckner is a senior developer with the SQL Server Reporting Services
(SSRS) product group at Microsoft. Prior to this role at Microsoft, he researched,
designed, and implemented database and business intelligence systems as a scientific
researcher at Vienna University of Technology, and as a system architect at T-Mobile
Austria. Robert joined the Reporting Services development team in early 2003
and has been specializing on the data and report processing engine that is running
inside server and client components of Reporting Services. Ever since the initial
beta release of SSRS 2000, Robert has been sharing insights, tips, tricks, and expert
advice about RDL, data and report processing, and SSRS in general, helping people
learn about, understand, and succeed with SSRS (e.g., by posting on newsgroups
and MSDN forums, publishing whitepapers, and speaking at conferences). Robert
holds Master and PhD degrees with highest distinctions in Computer Science from
Vienna University of Technology, Austria.
Microsoft® SQL Server
™ 2008
Delivering
Business
intelligence
Brian Larson
New York Chicago San Francisco Lisbon
London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore
Sydney Toronto
Copyright © 2009 by The McGraw-Hill Companies. All rights reserved. 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.
ISBN: 978-0-07-154945-5
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
otherwise.
This book is dedicated to my parents. To my father, Robert, who even after 40-plus years
as a junior high mathematics teacher and computer instructor, has a love of teaching. He has
shown me a real commitment to sharing knowledge with others. To my mother, Beverly,
who was my first editor, coaching me through elementary school papers on this state or that
president. She taught me the value of sticking with a job and seeing it through to the end.
I owe them both a debt of love, caring, and support that can never be adequately repaid.
This page intentionally left blank
vii
Contents at a Glance
Part I
Business Intelligence
Chapter 1
Equipping the Organization for Effective Decision Making
3
Chapter 2
Making the Most of What You’ve Got—Using Business Intelligence
13
Chapter 3
Seeking the Source—The Source of Business Intelligence
25
Chapter 4
One-Stop Shopping—The Unified Dimensional Model
43
Chapter 5
First Steps—Beginning the Development of Business Intelligence
61
Part II
Defining Business Intelligence Structures
Chapter 6
Building Foundations—Creating Data Marts
91
Chapter 7
Transformers—Integration Services Structure and Components 135
Chapter 8
Fill ’er Up—Using Integration Services for Populating Data Marts 233
Part III
Analyzing Cube Content
Chapter 9
Cubism—Measures and Dimensions 295
Chapter 10
Bells and Whistles—Special Features of OLAP Cubes 331
Chapter 11
Writing a New Script—MDX Scripting 389
Chapter 12
Pulling It Out and Building It Up—MDX Queries 433
Part IV
Mining
Chapter 13
Panning for Gold—Introduction to Data Mining 469
Chapter 14
Building the Mine—Working with the Data Mining Model 495
Chapter 15
Spelunking—Exploration Using Data Mining 529
v iii
D e l i ver i n g B us i n e s s I nte l l i g e nce wi t h M ic ro s o f t S Q L S e r ve r 2 0 0 8
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
Business Intelligence at Many Levels
16
The Top of the Pyramid
16
Mid-Level
19
The Broad Base
19
Maximum Miniatures, Inc
20
Business Needs
20
Current Systems
21
Building the Foundation
23
Chapter 3
Seeking the Source—The Source of Business Intelligence
25
Seeking the Source
26
Transactional Data
26
x
D e l i ver i n g B us i n e s s I nte l l i g e nce wi t h M ic ro s o f t S Q L S e r ve r 2 0 0 8
The Data Mart
29
Features of a Data Mart
30
Data Mart Structure
32
Snowflakes, Stars, and Analysis Services
40
Chapter 4
One-Stop Shopping—The Unified Dimensional Model
43
Online Analytical Processing
44
Building OLAP—Out of Cubes
45
Features of an OLAP System
48
Architecture
50
Disadvantages
52
Read-Only
52
The Unified Dimensional Model
53
Structure
53
Advantages
58
Tools of the Trade
60
Chapter 5
First Steps—Beginning the Development of Business Intelligence
61
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
Co nte nt s
x i
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
Package Development Features 234
Give It a Try 234
Programming in Integration Services Packages 241
Package Development Tools 250
Migrating from SQL Server 2000 DTS Packages 262
Putting Integration Services Packages into Production 263
Deploying Integration Services Packages 263
Change Data Capture 267
Change Data Capture Architecture 267
Loading a Data Mart Table from a Change Data Capture Change Table 272
Loading a Fact Table 277
Meanwhile, Back at the Unified Dimensional Model (UDM) 292
Part III
Analyzing Cube Content
Chapter 9
Cubism—Measures and Dimensions 295
Building in Analysis Services 296
Creating a Cube 296
Measures 302
Measure Groups 303
Made-up Facts—Calculated Measures 305
It Doesn’t Add Up—Measure Aggregates Other Than Sum 309
Dimensions 314
Managing Dimensions 314
Relating Dimensions to Measure Groups 320
xii
D e l i ver i n g B us i n e s s I nte l l i g e nce wi t h M ic ro s o f t S Q L S e r ve r 2 0 0 8
Types of Dimensions 321
Slowly Changing Dimensions 324
You Are Special 329
Chapter 10
Bells and Whistles—Special Features of OLAP Cubes 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
Operators 459
Functions 461
Can You Dig It? 465
Co nte nt s
x i i i
Part IV
Mining
Chapter 13
Panning for Gold—Introduction to Data Mining 469
What Is Data Mining? 470
Order from Chaos 470
Tasks Accomplished by Data Mining 474
Steps for Data Mining 480
Data Mining Algorithms 483
Microsoft Decision Trees 483
Microsoft Linear Regression 484
Microsoft Naïve Bayes 485
Microsoft Clustering 487
Microsoft Association Rules 488
Microsoft Sequence Clustering 490
Microsoft Time Series 491
Microsoft Neural Network 493
Microsoft Logistic Regression Algorithm 494
Grab a Pick Axe 494
Chapter 14
Building the Mine—Working with the Data Mining Model 495
Data Mining Structure 496
Data Columns 496
Data Mining Model 497
Training Data Set 497
Mining Model Viewer 512
Microsoft Decision Trees 513
Microsoft Naïve Bayes 517
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
D e l i ver i n g B us i n e s s I nte l l i g e nce wi t h M ic ro s o f t S Q L S e r ve r 2 0 0 8
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
The Parts of the Whole 568
Reporting Services Installation Considerations 571
Creating Reports Using the Tablix Data Region 573
A Tablix Data Region Created with the Table Template 574
A Tablix Data Region Created with the Matrix Template 591
A Tablix Data Region Created with the List Template 607
The Chart Data Region 617
The Gauge Data Region 634
Get Me the Manager 642
Chapter 17
Falling into Place—Managing Reporting Services Reports 643
Report Manager 644
Folders 644
The Report Manager 644
Deploying Reports Using the Report Designer 645
Uploading Reports Using Report Manager 647
Printing from Report Manager 653
