Pro SQL Server 2008 Analysis Services (eBook)

Philo Janus is a senior technology specialist with Microsoft. Over the years, he has presented InfoPath to thousands of users and developers, and assisted with enterprise implementations of InfoPath solutions. With that background, he is particularly sensitive to the difficulties users and developers have had with InfoPath. He graduated from the U.S. Naval Academy with a bachelor of science degree in electrical engineering in 1989 to face a challenging career in the U.S. Navy. After driving an aircraft carrier around the Pacific Ocean and a guided missile frigate through both the Suez and Panama canals, and serving in the U.S. Embassy in Cairo, a small altercation between his bicycle and an auto indicated a change of career (some would say that landing on his head in that accident would explain many things). Philo's software development career started with building a training and budgeting application in Access 2.0 in 1995. Since then, he's worked with Oracle, Visual Basic, SQL Server, and .NET building applications for federal agencies, commercial firms, and conglomerates. In 2003, he joined Microsoft as a technology specialist evangelizing Office as a development platform.

Title Page 1
Copyright Page 2
Contents at a Glance 4
Table of Contents 5
About the Authors 14
About the Technical Reviewers 15
Acknowledgments 16
Introduction 17
CHAPTER 1 Introduction to OLAP 18
From Pivot Tables to Dimensional Processing 19
Data Warehousing 21
Applications of OLAP 22
History of OLAP 24
SQL Server Analysis Services 25
Data Mining 30
Summary 31
CHAPTER 2 Cubes, Dimensions, and Measures 32
Cubes and Their Components 32
Defining Measures and Dimensions 35
Schemas 35
Dimensions in Depth 37
Attributes 38
Slowly Changing Dimensions (SCD) 38
Hierarchies 40
Time Dimensions 43
Measures 44
Types of Aggregation 48
Additive 49
Semiadditive 49
Nonadditive 49
Writeback 49
Calculated Measures 50
Actions 51
XMLA 52
Multidimensional Expressions (MDX) 53
Data Warehouses 54
Storage 55
Staging Databases 55
Storage Modes 55
MOLAP 56
ROLAP 56
HOLAP 56
Summary 56
CHAPTER 3 SQL Server Analysis Services 57
Requirements 57
Hardware 57
Virtualization 59
Software 60
Upgrading 60
Standard or Enterprise Edition? 61
Architecture 62
The Unified Dimensional Model 62
Logical Architecture 65
Physical Architecture 68
Storage 70
Cube Structures in SSAS 75
Data Sources 77
Data Source View 77
The Cube Structure Itself 79
Dimensions 80
Mining Structures 81
What’s New in SQL Server 2008 81
Performance 81
Management Data Warehouse 81
Reliable Query Cancel 83
Dynamic Management Views 84
Block Computing—Optimization 84
Writeback Performance Improvements 84
Change Data Capture 84
Tools 85
Dimension Design 85
Aggregation / UBO Designer 87
AMO Design Warnings 88
Summary 89
CHAPTER 4 SSAS Developer and Admin Interfaces 90
Business Intelligence Development Studio 90
BIDS Is Visual Studio? 90
Panes 91
Solution Explorer 94
Properties Pane 95
Creating or Editing a Database Solution 97
Create a New Analysis Services Project 97
Open an Existing SSAS Database 99
Open an Existing SSAS Database as Part of a Project 100
SQL Server Management Studio 101
Managing Analysis Services 102
Data Sources 102
Data Source Views 103
Cubes 104
Dimensions 105
Mining Structures 105
Roles 105
Executing MDX Queries 106
PowerShell 107
A Convincing Example 108
PowerShell for SQL Server 108
PowerShell with SSAS 109
Summary 110
CHAPTER 5 Creating a Data Source View 111
Cubes Need Data 111
Data Sources 112
Data Source Views 119
Designer Tour 119
Finding Tables 120
Replacing a Table 120
Named Calculations and Queries 124
Named Calculations 124
Named Queries 126
Summary 130
CHAPTER 6 Creating Dimensions 131
Dimensional Analysis 131
Review of the Dimension Concept 132
Star or Snowflake? 133
The Advantage of Simplicity 134
Dimensions in SSAS 141
Creating a Dimension 141
Analysis Management Objects (AMO) Warnings 149
Dimension Properties 150
Dimension Type 151
ErrorConfiguration 154
MdxMissingMemberMode 154
Processing 154
Storage 155
UnknownMember 162
WriteEnabled 162
Attributes 162
Attribute Relationships 164
Attribute Properties 170
Parent-Child Dimensions 171
The Time Dimension 173
Summary 180
CHAPTER 7 Building a Cube 181
Dimensions and Cubes 183
Creating Cubes 184
Using Measure Group Tables 184
Selecting Dimensions 187
Defining Dimension Usage 193
Measures and Measure Groups 196
Measures 196
Measure Groups 198
Calculated Measures 199
Summary 207
CHAPTER 8 Deploying and Processing 208
Deploying a Project 208
Project Properties 208
Deployment Methods 211
Using the Deployment Wizard 211
Running the Wizard 212
Input Files 214
Deployment Scripts 215
Synchronizing SSAS Databases 215
Processing 217
What Processing Does for Us 218
How to Initiate Processing from BIDS 221
Processing from SQL Server Management Studio 227
Processing via XMLA 228
Processing with Analysis Management Objects (AMO) 228
Scheduling OLAP Maintenance 228
Summary 231
CHAPTER 9 MDX 232
Why the Need? 232
Tuples and Sets 234
Notation 235
Tuples 236
Sets 241
MDX Queries 241
SELECT 242
WHERE 245
MDX Functions 246
Categories of Functions 252
Tree Functions 252
Aggregate Functions 254
Time Functions 258
Summary 262
CHAPTER 10 Cube Features 263
Business Intelligence 263
Time Intelligence 264
Account Intelligence 266
Dimension Intelligence 267
Operators, Functions, and More 267
Unary Operators 267
Custom Member Formulas 268
Attribute Ordering 269
Currency Conversion 269
Calculations Tab 270
Calculated Measures 271
Named Sets 274
Other Cube Features 274
Key Performance Indicators 275
Actions 277
Perspectives 282
Translations 284
Summary 285
CHAPTER 11 Data Mining 286
Why Mine Data? 286
Using Data-Mining Algorithms 287
Microsoft Naïve Bayes 287
Microsoft Clustering 287
Microsoft Decision Trees 288
Creating the Accessory Buyers Marketing Campaign 288
Preparing the Data Warehouse 289
Creating the Accessory Buyers Views in AdventureWorks 289
Creating the Accessory Campaign Data Source View 292
Finding Accessory Buyers by Using the AdventureWorks EDW 293
Using the Data Mining Model Designer 300
The Mining Structure View 301
The Mining Models View 302
The Mining Model Viewer View 303
Exploring the Decision Tree 303
Using Drill-Through to View a Model’s Training Cases 306
Using the Dependency Network 307
The Mining Accuracy Chart View 308
The Mining Model Prediction View 310
Finding Accessory Buyers by Using Data Mining Extensions (DMX) 314
Use the DMX Development Environment 314
Create the Accessory Buyers Mining Structure 315
Add a Naïve Bayes Mining Model to the Accessory Buyers Campaign 316
Process the Accessory Buyers Campaign 316
View the Accessory Buyers Mining Model 317
Predict Our Accessory Buyers 319
Summary 321
CHAPTER 12 PowerPivot 322
PowerPivot Support in SQL Server 2008 R2 322
Master Data Services 322
Excel Writeback 324
PowerPivot from Excel 331
PowerPivot with SharePoint Server 2010 337
Summary 342
CHAPTER 13 Administration 343
DBA Tasks 343
Processing a Cube 343
Processing Options 345
Processing Architecture 346
Profiler 347
Performance Monitor 347
Automation 349
XML for Analysis 350
Analysis Management Objects 350
PowerShell 353
Scheduling 353
SQL Server Agent 354
SQL Server Integration Services 355
Security 358
Authentication 358
Authorization 359
Performance 362
Design 362
Dimensions 363
Attribute Design 363
Partitions 366
Aggregations 369
Understanding Aggregations 369
Creating an Aggregation 370
Preparing an Aggregation for Use 375
Scaling 377
Large Data Size 377
Large Number of Users 377
Virtualization 379
SharePoint Server 2010 379
Summary 381
CHAPTER 14 User Interfaces 382
Excel 2007 382
Data Source Connections 383
Pivot Tables 388
Pivot Table Task Pane 389
Pivot Table Ribbons 390
Formatting a Pivot Table 392
Formulas Using Pivot Table Values 394
Pivot Charts 395
Visio 2007 408
SQL Server Reporting Services 410
Reports 410
Tablix 411
Charts 419
Report Builder 2.0 427
MOSS 2007 429
KPI Lists 430
Excel Services 432
PerformancePoint 432
Summary 438
APPENDIX A Setting Up Adventure Works 439
APPENDIX B Data-Mining Resources 443
Books 443
Web Sites 443
Index 444