spine=2.16"
Wrox Programmer to Programmer TM
Professional
Microsoft
SQL Server
Wrox Programmer to Programmer TM
Harinath, Carroll,
Meenakshisundaram,
Zare, Lee
®
®
Analysis Services 2008 with MDX
The new features of Analysis Services 2008 make it even easier to use and
build your databases for efficient and improved performance. This authoritative
book, written by key members of the Analysis Services product team at
Microsoft, explains how to best use these enhancements for your business
needs. The authors provides you with valuable insight on how to use Analysis
Services 2008 effectively to build, process, and deploy top-of-the-line
business intelligence applications.
Professional
This book is for database and data warehouse developers and administrators interested in exploiting the power of business
intelligence and leveraging the SQL Server 2008 tool set.
Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers,
developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They
provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.
www.wrox.com
Recommended
Computer Book
Categories
$49.99 USA
$59.99 CAN
Database Management
®
Who this book is for
Microsoft
Enhance Your Knowledge
Advance Your Career
®
● The basic concepts of using Analysis Services and the common operations
you need to design your databases
● How to create multi-dimensional databases (such as multiple measure
groups, business intelligence wizards, key performance indicators, and
more)
● Methods for extending MDX via external functions
● Ways to administer your Analysis Services programmatically and design and
optimize your cube for best performance
● How data mining along with Microsoft Office 2007 makes it easy to use
and effective to perform analysis on data
SQL Server
What you will learn from this book
Analysis Services 2008 with MDX
You’ll explore everything that Analysis Services 2008 has to offer and examine
the important features of this product with the help of step-by-step instructions
on building multi-dimensional databases. Within each chapter, you will not only
learn how to use the features, but you’ll also discover more about the features
at a user level and what happens behind the scenes to make things work.
You’ll get a look at how features really operate, enabling you to understand
how to use them to their full potential. Plus, you’ll sharpen your ability to debug
problems that you might not have been able to otherwise.
Professional
®
Microsoft
SQL
Server
Analysis Services 2008
®
with MDX
ISBN: 978-0-470-24798-3
General
Updates, source code, and Wrox technical support at www.wrox.com
spine=2.16"
Professional
Microsoft
SQL Server
®
Programmer to Programmer™
®
Analysis Services 2008 with MDX
Professional Microsoft SQL Server 2008
Integration Services
Get more out of
WROX.com
978-0-470-24795-2
This book shows developers how to master the 2008 release of SSIS, covering topics
including data warehousing with SSIS, new methods of managing the SSIS platform,
and improved techniques for ETL operations.
Professional SQL Server 2008 Reporting Services
978-0-470-24201-8
This book teaches solutions architects, designers, and developers how to use
Microsoft’s reporting platform to create reporting and business intelligence solutions.
Professional Microsoft SQL Server 2008 Analysis Services
978-0-470-24798-3
Professional Microsoft SQL Server 2008 Analysis Services shows readers how to
build data warehouses and multidimensional databases, query databases, and
use Analysis Services and other components of SQL Server to provide end-to-end
solutions.
Interact
Chapters on Demand
Take an active role online by participating in
our P2P forums
Purchase individual book chapters in pdf
format
Wrox Online Library
Join the Community
Hundreds of our books are available online
through Books24x7.com
Sign up for our free monthly newsletter at
newsletter.wrox.com
Wrox Blox
Browse
Beginning Microsoft SQL Server 2008 Programming
Download short informational pieces and
code to keep you up to date and out of
trouble!
Ready for more Wrox? We have books and
e-books available on .NET, SQL Server, Java,
XML, Visual Basic, C#/ C++, and much more!
This comprehensive introduction to SQL Server covers the fundamentals and moves on to discuss how to create and change tables, manage
keys, write scripts, work with stored procedures, and much more.
Professional Microsoft SQL Server 2008 Programming
978-0-470-25702-9
This updated new edition of Wrox’s best-selling SQL Server book has been expanded
to include coverage of SQL Server 2008’s new datatypes, new indexing structures,
manageability features, and advanced time-zone handling.
Enhance Your Knowledge
Advance Your Career
Professional Microsoft SQL Server 2008 Administration
978-0-470-24796-9
A how-to guide for experienced database administrators, this book is loaded with
unique tips, tricks, and workarounds for handling the most difficult SQL Server
administration issues. The authors discuss data capture, performance studio, Query
Governor, and new techniques for monitoring and policy management.
978-0-470-25701-2
Beginning T-SQL with Microsoft SQL Server 2005 and 2008
978-0-470-25703-6
Beginning T-SQL with Microsoft SQL Server 2005 and 2008 provides a comprehensive introduction to the T-SQL programming language, with
concrete examples showing how T-SQL works with both SQL Server 2005 and SQL Server 2008.
Beginning Database Design Solutions
978-0-470-38549-4
Contact Us.
We always like to get feedback from our readers. Have a book idea?
Need community support? Let us know by e-mailing
[email protected]
Beginning Database Design Solutions introduces IT professionals—both DBAs and database developers—to database design. It explains what
databases are, their goals, and why proper design is necessary to achieve those goals. It tells how to decide what should be in a database to
meet the application’s requirements. It tells how to structure the database so it gives good performance while minimizing the chance for error.
Professional Microsoft® SQL Server®
Analysis Services 2008 with MDX
Introduction .............................................................................................. xxix
Part I: Introduction
Download from Wow! eBook
Chapter 1: Introduction to Data Warehousing and SQL Server 2008 Analysis
Services ........................................................................................3
Chapter 2: First Look at Analysis Services 2008 ...........................................23
Chapter 3: Introduction to MDX ....................................................................67
Chapter 4: Working with Data Sources and Data Source Views ......................93
Chapter 5: Dimension Design ......................................................................117
Chapter 6: Cube Design ..............................................................................161
Chapter 7: Administering Analysis Services .................................................197
Part II: Advanced Topics
Chapter
Chapter
Chapter
Chapter
Chapter
8: Advanced Dimension Design ......................................................245
9: Advanced Cube Design ..............................................................285
10: Advanced Topics in MDX ..........................................................367
11: Extending MDX Using External Functions .................................395
12: Data Writeback .......................................................................413
Part III: Advanced Administration and
Performance Optimization
Chapter 13: Programmatic and Advanced Administration .............................441
Chapter 14: Designing for Performance .......................................................457
Chapter 15: Analyzing and Optimizing Query Performance ............................517
Part IV: Integration with Microsoft Products
Chapter 16: Data Mining.............................................................................553
Chapter 17: Analyzing Cubes Using Microsoft Office Components ................601
Chapter 18: Using Data Mining with Office 2007 .........................................677
Continues
ffirs.indd i
2/10/09 7:20:52 PM
Chapter 19: Integration Services ................................................................747
Chapter 20: Reporting Services ..................................................................779
Part V: Scenarios
Chapter 21: Designing Real-Time Cubes ......................................................833
Chapter 22: Securing Your Data in Analysis Services ...................................855
Chapter 23: Inventory Scenarios .................................................................897
Chapter 24: Financial Scenarios..................................................................923
Chapter 25: Web Analytics .........................................................................951
Appendix A: MDX Functions ........................................................................991
Index .........................................................................................................993
ffirs.indd ii
2/10/09 7:20:53 PM
Professional
Microsoft® SQL Server®
Analysis Services 2008 with MDX
ffirs.indd iii
2/10/09 7:20:53 PM
ffirs.indd iv
2/10/09 7:20:53 PM
Professional
Microsof t® SQL Server®
Analysis Services 2008 with MDX
Sivakumar Harinath
Matt Carroll
Sethu Meenakshisundaram
Robert Zare
Denny Guang-Yeu Lee
Wiley Publishing, Inc.
ffirs.indd v
2/10/09 7:20:53 PM
Professional Microsoft® SQL Server® Analysis
Services 2008 with MDX
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN: 978-0-470-24798-3
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Library of Congress Cataloging-in-Publication Data is available from the publisher.
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by
any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted
under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written
permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the
Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600.
Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley &
Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at
http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or
warranties with respect to the accuracy or completeness of the contents of this work and specifically
disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No
warranty may be created or extended by sales or promotional materials. The advice and strategies contained
herein may not be suitable for every situation. This work is sold with the understanding that the publisher is
not engaged in rendering legal, accounting, or other professional services. If professional assistance is
required, the services of a competent professional person should be sought. Neither the publisher nor the
author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in
this work as a citation and/or a potential source of further information does not mean that the author or the
publisher endorses the information the organization or Web site may provide or recommendations it may
make. Further, readers should be aware that Internet Web sites listed in this work may have changed or
disappeared between when this work was written and when it is read.
For general information on our other products and services please contact our Customer Care Department
within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.
Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade
dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United
States and other countries, and may not be used without written permission. Microsoft and SQL Server are
registered trademarks of Microsoft Corporation in the United States and/or other countries. All other
trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with any
product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not
be available in electronic books.
ffirs.indd vi
2/10/09 7:20:54 PM
I dedicate this book in the grandest possible manner to my dear wife, Shreepriya,
who has been fully supportive and patient with me for all the late nights I worked on this book.
It is also dedicated to my twins, Praveen and Divya, who have seen me work long hours on this book.
I dedicate this book in memory of my father, Harinath Govindarajalu, who passed away in 1999 and
who I am sure would have been proud of this great achievement, and to my mother, Sundar Bai,
and my sister, Geetha Harinath. Finally, I dedicate this book in memory of my uncle, Jayakrishnan Govindarajalu,
who passed away in 2007 and who was very proud of me co-authoring the first edition of this book,
and was eagerly looking forward to seeing this book.
—Siva Harinath
Thanks to my wife, Wendy, for her love and patience. Love and hope to Lawrence,
Loralei, and Joshua.
—Matt Carroll
To my Parents, Uncle & Aunt, Guru(s), and the Lord Almighty for molding
me into who I am today.
—Sethu Meenakshisundaram
To the patience and love from Isabella and Hua-Ping.
—Denny Lee
ffirs.indd vii
2/10/09 7:20:54 PM
ffirs.indd viii
2/10/09 7:20:54 PM
About the Authors
Sivakumar Harinath was born in Chennai, India. Siva has a Ph.D. in Computer Science from the
University of Illinois at Chicago. His thesis title was “Data Management Support for Distributed Data
Mining of Large Datasets over High Speed Wide Area Networks.” Siva has worked for Newgen Software
Technologies (P) Ltd., IBM Toronto Labs, Canada; National Center for Data Mining, University of Illinois
at Chicago; and has been at Microsoft since February of 2002. Siva started as a Software Design Engineer
in Test (SDET) in the Analysis Services Performance Team and currently is a Senior Test Lead in the
Analysis Services team. Siva’s other interests include high-performance computing, distributed systems,
and high-speed networking. Siva is married to Shreepriya and has twins, Praveen and Divya. His
personal interests include travel, games, and sports (in particular carrom, chess, racquet ball, and board
games). You can reach Siva at [email protected].
Matt Carroll is currently a Senior Development Lead on the SQL Server Integration Services team at
Microsoft. Prior to this, he spent 10 years working on the SQL Server Analysis Services team as a
developer and then development lead. He’s presented on Analysis Services at VSLive and compiled and
edited the whitepaper “OLAP Design Best Practices for Analysis Services 2005.”
Sethu Meenakshisundaram has more than 20 years of Enterprise System Software Development
experience. Sethu spent a good portion of his career at Sybase Inc. in architecture, development, and
management building world class OLTP and OLAP Database Systems. Sethu was instrumental in
developing and leading highly complex clustered systems of Adaptive Server Enterprise. Early in the
‘90s, Sethu developed a version of Sybase Adaptive Server running on the Windows platform. Most
recently he was an Architect in the SQL Server BI team driving technology and partner strategy. Prior to
Microsoft, Sethu managed all of Server development as Senior Director at Sybase including building
teams in the U.S., India, and China. He is currently a Vice President in charge of Technology Strategy at
SAP Labs, USA.
Rob Zare is a program manager on the SQL Server development team. He’s worked on the product since
shortly before the first service pack of SQL Server 2000. During that time, he’s focused primarily on
Analysis Services, though for the next major release of SQL Server he’ll be focused on Integration
Services. He is the co-author of Fast Track to MDX and regularly speaks at major technical conferences
around the world.
Denny Lee is a Senior Program Manager based out of Redmond, WA in the SQLCAT Best Practices Team.
He has more than 12 years experience as a developer and consultant implementing software solutions to
complex OLTP and data warehousing problems. His industry experience includes accounting, human
resources, automotive, retail, web analytics, telecommunications, and healthcare. He had helped create
the first OLAP Services reporting application in production at Microsoft and is a co-author of “SQL Server
2000 Data Warehousing with Analysis Services” and “Transforming Healthcare through Information
[Ed. Joan Ash] (2008)”. In addition to contributing to the SQLCAT Blog, SQL Server Best Practices, and
SQLCAT.com, you can also review Denny’s Space (http://denster.spaces.live.com). Denny
specializes in developing solutions for Enterprise Data Warehousing, Analysis Services, and Data Mining;
he also has focuses in the areas of Privacy and Healthcare.
ffirs.indd ix
2/10/09 7:20:54 PM
ffirs.indd x
2/10/09 7:20:55 PM
Credits
Contributors
Production Manager
Akshai Mirchandani
Wayne Robertson
Leah Etienne
Grant Paisley
Tim Tate
Executive Editor
Vice President and Executive Publisher
Robert Elliott
Barry Pruett
Development Editor
Associate Publisher
Kelly Talbot
Jim Minatel
Technical Editor
Project Coordinator, Cover
Ron Pihlgren
Prashant Dhingra
Lynsey Stanford
Vice President and Executive Group Publisher
Richard Swadley
Proofreader
Production Editor
Nancy Carrasco
Daniel Scribner
Indexer
Copy Editor
Ron Strauss
Kim Cofer
Editorial Manager
Mary Beth Wakefield
ffirs.indd xi
2/10/09 7:20:55 PM
ffirs.indd xii
2/10/09 7:20:55 PM
Acknowledgments
Wow!!! It has been an amazing 15 months from when we decided to partner in writing this book. The
first edition of this book started when Siva jokingly mentioned to his wife the idea of writing a book on
SQL Server Analysis Services 2005. She took it seriously and motivated him to start working on the idea
in October 2003. Because the first edition was well received, Siva identified co-authors for the new
edition. All the co-authors of this book were part of the SQL Server team when they started writing this
book. As always, there are so many people who deserve mentioning that we are afraid we will miss
someone. If you are among those missed, please accept our humblest apologies. We first need to thank
the managers of each co-author and Kamal Hathi, Product Unit Manager of the Analysis Services team
for permission to moonlight. Siva specifically thanks his manager Lon Fisher for his constant
encouragement and support to help Analysis Services customers. We thank our editors, Bob Elliott and
Kelly Talbot, who supported us right from the beginning but also prodded us along, which was
necessary to make sure the book was published on time.
We would like to thank our technical reviewers, Ron Pihlgren and Prashant Dhingra, who graciously
offered us their assistance and significantly helped in improving the content and samples in the book.
We thank Akshai Mirchandani, Wayne Robertson, Leah Etienne, and Grant Paisley for their
contributions in the book for Chapters 5, 6, 14, 17, and 18. We thank all our colleagues in the Analysis
Services product team (including Developers, Program Managers, and Testers) who helped us in
accomplishing the immense feat of writing the book on a development product. To the Analysis Services
team, special thanks go to Akshai Mirchandani, T. K. Anand, Cristian Petculescu, Bogdan Crivat, Dana
Cristofor, Marius Dumitru, Andrew Garbuzov, Bo Simmons, and Richard Tkachuk from the SQL Server
Customer Advisory team for patiently answering our questions or providing feedback to enhance the
content of the book.
Most importantly, we owe our deepest thanks to our wonderful families. Without their support and
sacrifice, this book would have become one of those many projects that begins and never finishes. Our
families were the ones who truly took the brunt of it and sacrificed shared leisure time, all in support of
our literary pursuit. We especially want to thank them for their patience with us, and the grace it took to
not kill us during some of the longer work binges.
ffirs.indd xiii
2/10/09 7:20:55 PM
ffirs.indd xiv
2/10/09 7:20:56 PM
Contents
Introduction
xxix
Part I: Introduction
Chapter 1: Introduction to Data Warehousing and SQL Server 2008 Analysis
Services
A Closer Look at Data Warehousing
Key Elements of a Data Warehouse
Fact Tables
Dimension Tables
Dimensions
Cubes
The Star Schema
The Snowflake Schema
Inmon Versus Kimball — Different Approaches
Business Intelligence Is Data Analysis
Microsoft Business Intelligence Capabilities
Integrating Data
Storing Data
The Model
Exploring Data
Visualizing
Deliver
SQL Server Analysis Services 2008
The Unified Dimensional Model
Summary
Chapter 2: First Look at Analysis Services 2008
Differences between Analysis Services 2000, Analysis Services 2005,
and Analysis Services 2008
Development, Administrative, and Client Tools
Analysis Services Version Differences
ftoc.indd xv
3
4
7
7
8
9
10
11
12
13
13
14
14
15
15
15
15
15
17
20
22
23
24
24
25
2/10/09 12:22:50 PM
Contents
Upgrading to Analysis Services 2008
Using Business Intelligence Development Studio
26
35
Creating a Project in the Business Intelligence Development Studio
35
Creating an Analysis Services Database Using Business Intelligence Development Studio 38
Using SQL Server Management Studio
The Object Explorer Pane
Querying Using the MDX Query Editor
Summary
Chapter 3: Introduction to MDX
What Is MDX?
Fundamental Concepts
59
61
63
65
67
67
68
Members
Cells
Tuples
Sets
70
71
73
74
MDX Queries
75
The
The
The
The
SELECT Statement and Axis Specification
FROM Clause and Cube Specification
WHERE Clause and Slicer Specification
WITH Clause and Calculated Members
MDX Expressions
Operators
Arithmetic Operators
Set Operators
Comparison Operators
Logical Operators
Special MDX Operators — Curly Braces, Commas, and Colons
MDX Functions
MDX Function Categories
Set Functions
Member Functions
Numeric Functions
Dimension Functions, Level Functions, and Hierarchy Functions
String Manipulation Functions
Other Functions
Summary
76
77
77
79
82
84
84
84
84
85
85
85
86
87
89
90
91
91
91
92
xvi
ftoc.indd xvi
2/10/09 12:22:51 PM
Contents
Chapter 4: Working with Data Sources and Data Source Views
Data Sources
Data Sources Supported by Analysis Services
.NET versus OLE DB Data Providers
Data Source Views
DSV Wizard
DSV Designer
Data Source Views in Depth
Data Source View Properties
Different Layouts in DSVs
Validating Your DSV and Initial Data Analysis
Multiple Data Sources within a DSV
Summary
Chapter 5: Dimension Design
Working with the Dimension Wizard
Working with the Dimension Designer
Attributes
Attribute Relationships
Hierarchies and Levels
Browsing the Dimension
Sorting Members of a Level
Optimizing Attributes
Defining Translations in Dimensions
Creating a Snowflake Dimension
Creating a Time Dimension
Creating a Parent-Child Hierarchy
Summary
Chapter 6: Cube Design
The Unified Dimensional Model
Creating a Cube Using the Cube Wizard
Browsing Cubes
Cube Dimensions
93
93
95
98
99
100
100
107
109
111
112
114
115
117
117
124
125
127
132
136
145
147
148
150
153
156
160
161
161
163
169
173
Relationship Types
Browsing Reference Dimensions
174
178
Measures and Measure Groups
Calculated Members
180
187
Calculated Measures
Querying Calculated Measures
188
191
xvii
ftoc.indd xvii
2/10/09 12:22:51 PM
Contents
Creating Perspectives
Creating Translations
Browsing Perspectives and Translations
Summary
192
193
194
196
Chapter 7: Administering Analysis Services
197
Administration Using SQL Server 2008 Tools
197
Managing Analysis Servers
Managing Analysis Services Objects
Database Creation
Processing Analysis Services Database Objects
Managing Partitions
Managing Assemblies
Backup and Restore
Detach and Attach
Synchronization
Managing Security
198
200
201
204
215
221
224
229
233
237
Online Mode
Summary
239
242
Part II: Advanced Topics
Chapter 8: Advanced Dimension Design
Custom Rollups
Enhancements to Parent-Child Hierarchies
Unary Operators
Specifying Names of Levels in a Parent-Child Hierarchy
Using Properties to Customize Dimensions
Ordering Dimension Members
The All Member, Default Member, and Unknown Member
Error Configurations for Processing
Storage Mode
Grouping Members
Dimension Intelligence Using the Business Intelligence Wizard
245
246
255
255
259
261
261
262
264
264
265
266
Account Intelligence
Time Intelligence
Dimension Intelligence
267
272
275
Server Time Dimension
Dimension Writeback
Summary
277
281
284
xviii
ftoc.indd xviii
2/10/09 12:22:52 PM
.PDF 
1056 
131 
71 
