Pro SQL Server 2008 Analysis Services

468 Pages·2010·13.26 MB·English

by  Philo Janus

CYAN  YELLOW
 MAGENTA  BLACK
 PANTONE 123 C
BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® IN SQL SERVER
Companion
eBook Available
Covers
Pro SQL Server 2008 Analysis
Release 2!
Pro
Services
S
Dear Reader,
Q
SQL Server Analysis Services (SSAS) is a powerful online analytical processing (OLAP)
L
and data-mining environment included with Microsoft’s database platform. Pro SQL Pro

Server 2008 Analysis Services is your guide to using Analysis Services along with SQL S
Server to deliver effective business intelligence (BI) solutions. We show how to create
e
data source views, build and deploy a cube for analysis, and mine your data for busi-
ness insight, and ultimately for profit. r
Our desire is to pave your path to success by sharing our experience. We show v SQL Server 2008
how SQL Server professionals use SSAS to address and solve real-world challenges e
every day. We discuss SSAS features in detail, including the enhanced Cube and r
Dimension Designers. For administrators, we discuss several areas in server man-
2
agement. These include cube processing and processing options, the Performance
Monitor, scheduling, and security. 0
Business intelligence solutions require metrics. To deliver metrics to your users, Analysis Services
0
you will learn how to define, create, and use key performance indicators (KPIs), cal-
8
culated members, perspectives, and actions. Data mining with SSAS enables you
to create predictions based on trends in historical data stores of virtually any size.
A
SQL Server Analysis Services and Microsoft Office 2010 are now tightly integrat-
ed to enable self-service business intelligence capabilities across the enterprise. n
PowerPivot is a new set of technologies that give end users the tools needed to per- a
form complex analysis and data mining at their workspace. We introduce you to
l
PowerPivot, helping you get started with this new and important tool. y
We believe you will gain valuable experience with SQL Server Analysis Services, s
as well as insights into developing BI solutions, by applying the methods demon-
i
strated throughout this book. s

Philo Janus & Guy Fouché S Create value and competitive advantage through
Companion eBook
e careful mining and analysis of your company’s
THE APRESS ROADMAP r business data
v
Beginning
SQL Server 2008 Pro SQL Server i
2008 Analytics c
See last page for details for Developers Pro
on $10 eBook version SQL Server 2008 e
Analysis Services
Accelerated Pro s
SQL Server 2008
SQL Server 2008
Reporting Services
Philo Janus Guy Fouché
SOURCE CODE ONLINE F and
oJ
www.apress.com ISBN 978-1-4302-1995-8 ua
cn
54499 hu
US $44.99 és

Shelve in:
Database / SQL Server
User level:
Intermediate–Advanced 9 781430 219958
this print for content only—size & color not accurate 7.5 x 9.25 spine = 0.90625" 480 page count
Pro SQL Server 2008
Analysis Services

(cid:132) (cid:132) (cid:132)
Philo Janus
Guy Fouché
Pro SQL Server 2008 Analysis Services
Copyright © 2010 by Philo Janus and Guy Fouché
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-4302-1995-8
ISBN-13 (electronic): 978-1-4302-1996-5
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with
every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an
editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the
trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not
identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to
proprietary rights.
Publisher and President: Paul Manning
Lead Editor: Jonathan Gennick
Technical Reviewers: Dana Hoffman and Fabio Claudio Ferrachiatti
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan
Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes, Jeffrey Pepper,
Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom
Welsh
Coordinating Editors: Candace English and Fran Parnell
Copy Editors: Sharon Wilkey and Mary Ann Fugate
Compositor: Bytheway Publishing Services
Indexer: John Collin
Artist: April Milne
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street, 6th
Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail orders-ny@springer-
sbm.com, or visit www.springeronline.com.
For information on translations, please e-mail [email protected], or visit 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 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 www.apress.com. You will need to answer questions
pertaining to this book in order to successfully download the code.
(cid:132) CONTENTS
Contents at a Glance
(cid:132) Contents..................................................................................................................v
(cid:132) About the Authors................................................................................................xiv
(cid:132) About the Technical Reviewers............................................................................xv
(cid:132) Acknowledgments...............................................................................................xvi
(cid:132) Introduction........................................................................................................xvii
(cid:132) Chapter 1: Introduction to OLAP.............................................................................1
(cid:132) Chapter 2: Cubes, Dimensions, and Measures.....................................................15
(cid:132) Chapter 3: SQL Server Analysis Services..............................................................41
(cid:132) Chapter 4: SSAS Developer and Admin Interfaces...............................................75
(cid:132) Chapter 5: Creating a Data Source View...............................................................97
(cid:132) Chapter 6: Creating Dimensions.........................................................................117
(cid:132) Chapter 7: Building a Cube.................................................................................167
(cid:132) Chapter 8: Deploying and Processing.................................................................195
(cid:132) Chapter 9: MDX...................................................................................................219
(cid:132) Chapter 10: Cube Features.................................................................................251
(cid:132) Chapter 11: Data Mining.....................................................................................275
(cid:132) Chapter 12: PowerPivot......................................................................................311
(cid:132) Chapter 13: Administration.................................................................................333
(cid:132) Chapter 14: User Interfaces................................................................................373
(cid:132) Appendix A: Setting Up Adventure Works...........................................................431
(cid:132) Appendix B: Data-Mining Resources..................................................................435
(cid:132) Index...................................................................................................................437
iv
(cid:132) CONTENTS
Contents
(cid:132) Contents at a Glance..............................................................................................iv
(cid:132) Contents..................................................................................................................v
(cid:132) About the Authors................................................................................................xiv
(cid:132) About the Technical Reviewers............................................................................xv
(cid:132) Acknowledgments...............................................................................................xvi
(cid:132) Introduction........................................................................................................xvii
(cid:132) Chapter 1: Introduction to OLAP.............................................................................1
From Pivot Tables to Dimensional Processing...................................................................2
Data Warehousing.............................................................................................................4
Applications of OLAP..........................................................................................................5
History of OLAP..................................................................................................................7
SQL Server Analysis Services............................................................................................8
Data Mining.....................................................................................................................13
Summary.........................................................................................................................14
(cid:132) Chapter 2: Cubes, Dimensions, and Measures.....................................................15
Cubes and Their Components..........................................................................................15
Defining Measures and Dimensions................................................................................18
Schemas.................................................................................................................................................18
Dimensions in Depth...............................................................................................................................20
Measures................................................................................................................................................27
Types of Aggregation..............................................................................................................................31
v
(cid:132) CONTENTS
Writeback...............................................................................................................................................32
Calculated Measures..............................................................................................................................33
Actions.............................................................................................................................34
XMLA................................................................................................................................35
Multidimensional Expressions (MDX)..............................................................................36
Data Warehouses.............................................................................................................37
Storage............................................................................................................................38
Staging Databases..................................................................................................................................38
Storage Modes.......................................................................................................................................38
Summary.........................................................................................................................39
(cid:132) Chapter 3: SQL Server Analysis Services..............................................................41
Requirements..................................................................................................................41
Hardware................................................................................................................................................41
Virtualization...........................................................................................................................................43
Software.................................................................................................................................................44
Upgrading...............................................................................................................................................44
Standard or Enterprise Edition?..............................................................................................................44
Architecture.....................................................................................................................46
The Unified Dimensional Model..............................................................................................................46
Logical Architecture................................................................................................................................49
Physical Architecture..............................................................................................................................52
Storage...................................................................................................................................................54
Cube Structures in SSAS.................................................................................................59
Data Sources..........................................................................................................................................61
Data Source View...................................................................................................................................61
The Cube Structure Itself........................................................................................................................63
Dimensions.............................................................................................................................................64
Mining Structures...................................................................................................................................65
vi
(cid:132) CONTENTS
What’s New in SQL Server 2008......................................................................................65
Performance...........................................................................................................................................65
Tools.......................................................................................................................................................69
Summary.........................................................................................................................73
(cid:132) Chapter 4: SSAS Developer and Admin Interfaces...............................................75
Business Intelligence Development Studio......................................................................75
BIDS Is Visual Studio?............................................................................................................................75
Panes......................................................................................................................................................76
Solution Explorer....................................................................................................................................79
Properties Pane......................................................................................................................................80
Creating or Editing a Database Solution.................................................................................................82
SQL Server Management Studio......................................................................................86
Managing Analysis Services...................................................................................................................87
Executing MDX Queries..........................................................................................................................91
PowerShell.......................................................................................................................92
A Convincing Example............................................................................................................................93
PowerShell for SQL Server.....................................................................................................................93
PowerShell with SSAS............................................................................................................................94
Summary.........................................................................................................................95
(cid:132) Chapter 5: Creating a Data Source View...............................................................97
Cubes Need Data.............................................................................................................97
Data Sources...................................................................................................................98
Data Source Views.........................................................................................................105
Designer Tour.......................................................................................................................................105
Named Calculations and Queries..........................................................................................................110
Summary.......................................................................................................................116
vii
(cid:132) CONTENTS
(cid:132) Chapter 6: Creating Dimensions.........................................................................117
Dimensional Analysis.....................................................................................................117
Review of the Dimension Concept........................................................................................................118
Star or Snowflake?...............................................................................................................................119
Dimensions in SSAS......................................................................................................127
Creating a Dimension...........................................................................................................................127
Analysis Management Objects (AMO) Warnings...................................................................................135
Dimension Properties...........................................................................................................................136
Attributes.......................................................................................................................148
Attribute Relationships.........................................................................................................................150
Attribute Properties..............................................................................................................................156
Parent-Child Dimensions...............................................................................................157
The Time Dimension......................................................................................................159
Summary.......................................................................................................................166
(cid:132) Chapter 7: Building a Cube.................................................................................167
Dimensions and Cubes..................................................................................................169
Creating Cubes.....................................................................................................................................170
Using Measure Group Tables................................................................................................................170
Selecting Dimensions...........................................................................................................................173
Defining Dimension Usage...................................................................................................................179
Measures and Measure Groups.....................................................................................182
Measures..............................................................................................................................................182
Measure Groups...................................................................................................................................184
Calculated Measures............................................................................................................................185
Summary.......................................................................................................................193
(cid:132) Chapter 8: Deploying and Processing.................................................................195
Deploying a Project........................................................................................................195
Project Properties.................................................................................................................................195
viii
(cid:132) CONTENTS
Deployment Methods............................................................................................................................198
Using the Deployment Wizard........................................................................................198
Running the Wizard..............................................................................................................................199
Input Files.............................................................................................................................................201
Deployment Scripts..............................................................................................................................202
Synchronizing SSAS Databases.....................................................................................202
Processing.....................................................................................................................204
What Processing Does for Us...............................................................................................................205
How to Initiate Processing from BIDS...................................................................................................208
Processing from SQL Server Management Studio................................................................................214
Processing via XMLA............................................................................................................................215
Processing with Analysis Management Objects (AMO)........................................................................215
Scheduling OLAP Maintenance......................................................................................215
Summary.......................................................................................................................218
(cid:132) Chapter 9: MDX...................................................................................................219
Why the Need?...............................................................................................................219
Tuples and Sets.............................................................................................................221
Notation................................................................................................................................................222
Tuples...................................................................................................................................................223
Sets......................................................................................................................................................228
MDX Queries..................................................................................................................228
SELECT.................................................................................................................................................229
WHERE..................................................................................................................................................232
MDX Functions......................................................................................................................................233
Categories of Functions........................................................................................................................239
Summary.......................................................................................................................249
(cid:132) Chapter 10: Cube Features.................................................................................251
Business Intelligence.....................................................................................................251
ix
(cid:132) CONTENTS
Time Intelligence..................................................................................................................................252
Account Intelligence.............................................................................................................................254
Dimension Intelligence.........................................................................................................................255
Operators, Functions, and More....................................................................................255
Unary Operators....................................................................................................................................255
Custom Member Formulas...................................................................................................................256
Attribute Ordering.................................................................................................................................257
Currency Conversion............................................................................................................................257
Calculations Tab............................................................................................................258
Calculated Measures............................................................................................................................259
Named Sets..........................................................................................................................................262
Other Cube Features......................................................................................................262
Key Performance Indicators.................................................................................................................263
Actions..................................................................................................................................................265
Perspectives.........................................................................................................................................270
Translations..........................................................................................................................................272
Summary.......................................................................................................................273
(cid:132) Chapter 11: Data Mining.....................................................................................275
Why Mine Data?.............................................................................................................275
Using Data-Mining Algorithms.......................................................................................276
Microsoft Naïve Bayes..........................................................................................................................276
Microsoft Clustering.............................................................................................................................276
Microsoft Decision Trees......................................................................................................................277
Creating the Accessory Buyers Marketing Campaign....................................................277
Preparing the Data Warehouse......................................................................................278
Creating the Accessory Buyers Views in AdventureWorks...................................................................278
Creating the Accessory Campaign Data Source View..........................................................................281
Finding Accessory Buyers by Using the AdventureWorks EDW.....................................282
x