FM.book Page 1 Wednesday, March 1, 2006 5:11 PM
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2006 by Hitachi Consulting
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by
any means without the written permission of the publisher.
Library of Congress Control Number 2006921726
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 1 0 9 8 7 6
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further information
about international editions, contact your local Microsoft Corporation office or contact Microsoft Press Inter-
national directly at fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments
to mspinput@microsoft.com.
Microsoft, Excel, Microsoft Press, MSDN, PivotTable, Visual Basic, Windows, Windows NT, and Windows
Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or
other countries. Other product and company names mentioned herein may be the trademarks of their respec-
tive owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain
name, e-mail address, logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided with-
out any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers,
or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly
by this book.
Acquisitions Editor: Ben Ryan
Project Editor: Denise Bankaitis
Technical Editor: Robert Hogan
Copy Editor: Elaine Alibrandi
Indexer: Abbey Briggs
Body Part No. X11-82264
FM.book Page iii Wednesday, March 1, 2006 5:11 PM
iii
Table of Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
Finding Your Best Starting Point . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
About the Companion CD-ROM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x
System Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
Installing and Using the Sample Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
Conventions and Features in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii
Part
I Getting Started with Analysis Services
1 Understanding Business Intelligence and Data Warehousing . . . . . . . . .3
Introducing Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Reviewing Data Warehousing Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The Purpose of a Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The Structure of a Dimensional Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
A Fact Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Dimension Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Chapter 1 Quick Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2 Understanding OLAP and Analysis Services . . . . . . . . . . . . . . . . . . . . . . 17
Understanding OLAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Consistently Fast Response . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Metadata-Based Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Spreadsheet-Style Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Understanding Analysis Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Analysis Services and Speed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Analysis Services and Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Analysis Services Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Analysis Services Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Chapter 2 Quick Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
3 Building Your First Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Exploring Business Intelligence Development Studio. . . . . . . . . . . . . . . . . . . . . . . . . . . 31
What do you think of this book?
We want to hear from you!
Microsoft is interested in hearing your feedback about this publication so we can
continually improve our books and learning resources for you. To participate in a brief
online survey, please visit: www.microsoft.com/learning/booksurvey/
6-2199-3eBook.book Page iii Wednesday, March 1, 2006 5:05 PM
iv Table of Contents
Examining the Contents of an Analysis Services Project . . . . . . . . . . . . . . . . . . 32
Exploring Menu Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Preparing to Create a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Reviewing the Analysis Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Creating a New Analysis Services Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Creating a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Using the Cube Wizard Without a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . 38
Reviewing the Cube Structure in the Cube Designer. . . . . . . . . . . . . . . . . . . . . 45
Generating a Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Using the Schema Generation Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Loading Data into the Relational Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Processing and Browsing a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Deploying and Processing a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Browsing a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Chapter 3 Quick Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Part II Design Fundamentals
4 Designing Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Reviewing the Data Warehouse Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Building a Standard Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Adding a Data Source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Creating a Data Source View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Using the Dimension Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Deploying a Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Changing Attribute Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Working with a Time Dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Modifying a Data Source View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Creating a Time Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Working with Role-Playing Dimensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Creating a Parent-Child Dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Adding an Employee Dimension. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Totaling Data for Non–Leaf-Level Data Members . . . . . . . . . . . . . . . . . . . . . . . 88
Managing Levels within a Parent-Child Dimension . . . . . . . . . . . . . . . . . . . . . . 92
Chapter 4 Quick Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
5 Designing Measure Groups and Measures. . . . . . . . . . . . . . . . . . . . . . . . 99
Adding Measure Groups to a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
6-2199-3eBook.book Page iv Wednesday, March 1, 2006 5:05 PM
Table of Contents v
Building a Cube . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Changing Properties for Measure Groups and Measures . . . . . . . . . . . . . . . . 103
Specifying Dimension Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Browsing Multiple Measure Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Aggregating Semiadditive Measures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Adding a Measure Group to an Existing Cube . . . . . . . . . . . . . . . . . . . . . . . . . 113
Using a Semiadditive Aggregate Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Calculating Distinct Counts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Creating Simple Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Adding a Calculation to a Cube. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Applying Conditional Formatting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126
Chapter 5 Quick Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
6 Working with a Finance Measure Group . . . . . . . . . . . . . . . . . . . . . . . . 129
Designing an Account Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Working with Account Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Using Unary Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Aggregating by Account. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Designing Nonadditive Financial Measures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Creating a Nonadditive Measure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Chapter 6 Quick Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
7 Designing Aggregations and Hierarchies. . . . . . . . . . . . . . . . . . . . . . . . 149
Understanding Aggregation Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Using the Aggregation Design Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Inspecting Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Changing Partition Counts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Adding Attributes to the Aggregation Design . . . . . . . . . . . . . . . . . . . . . . . . . 160
Designing User Hierarchies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Adding a User Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Aggregating User Hierarchies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Optimizing Aggregations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Using the Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Viewing Usage Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Using the Usage-Based Optimization Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Maintaining the Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Chapter 7 Quick Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
6-2199-3eBook.book Page v Wednesday, March 1, 2006 5:05 PM
