The first book to offer the comprehensive insights professionals need to build enterprise-class OLAP solutions with Analysis Services 2008 rapidly and efficiently. Using this text, developers, architects, and DBA can create solutions that uncover new opportunities and solve real business problems.
Introduction 1
Part 1: Introduction to Analysis Services
1 Introduction to OLAP and Its Role in Business Intelligence 7
The Multidimensional Data Model ................................................................8
The Conceptual Data Model .................................................................9
The Application Data Model .................................................................9
The Physical Data Model.......................................................................9
Unified Dimensional Model................................................................11
Basic Concepts .....................................................................................13
2 Multidimensional Space 17
Describing Multidimensional Space .............................................................17
Dimension Attributes....................................................................................20
Cells...............................................................................................................22
Measures........................................................................................................22
Aggregation Functions..................................................................................23
Subcubes........................................................................................................24
3 Client/Server Architecture and Multidimensional Databases: An Overview 27
Two-Tier Architecture ...................................................................................28
One-Tier Architecture ...................................................................................29
Three-Tier Architecture .................................................................................30
Four-Tier Architecture ...................................................................................31
Distributed Systems ......................................................................................32
Distributed Storage ..............................................................................32
Thin Client/Thick Client.....................................................................32
Part 2: Creating Multidimensional Models
4 Conceptual Data Model 37
Data Definition Language.............................................................................37
Objects in DDL ....................................................................................38
Multilanguage Support ........................................................................39
Rules of Ordering.................................................................................41
Specifying Default Properties ..............................................................41
Rules of Inheritance.............................................................................42
5 Dimensions in the Conceptual Model 43
Dimension Attributes....................................................................................44
Attribute Properties and Values...........................................................45
Relationships Between Attributes........................................................47
Attribute Member Keys........................................................................50
Attribute Member Names ....................................................................53
Relationships Between Attributes........................................................54
Dimension Hierarchies .................................................................................57
Types of Hierarchies.............................................................................57
Attribute Hierarchies.....................................................................................60
6 Cubes and Multidimensional Analysis 63
Cube Dimensions..........................................................................................65
Cube Dimension Attributes.................................................................68
Cube Dimension Hierarchies...............................................................69
Role-Playing Dimensions.....................................................................70
The Dimension Cube....................................................................................71
Perspectives ...................................................................................................72
7 Measures and Multidimensional Analysis 75
Measures in a Multidimensional Cube.........................................................76
SUM......................................................................................................78
MAX and MIN .....................................................................................79
COUNT ................................................................................................79
DISTINCT COUNT...............................................................................79
Measure Groups ............................................................................................81
Measure Group Dimensions .........................................................................84
Granularity of a Fact............................................................................84
Measure Group Dimension Attributes and
Cube Dimension Hierarchies ............................................................87
8 Advanced Modeling 91
Parent-Child Relationships ...........................................................................91
Parent-Child Hierarchies .....................................................................94
Attribute Discretization ................................................................................95
Indirect Dimensions .....................................................................................97
Referenced Dimensions .......................................................................98
Many-to-Many Dimensions ..............................................................102
Measure Expressions ...................................................................................105
Linked Measure Groups ..............................................................................107
9 Multidimensional Models and Business Intelligence Development Studio 109
Creating a Data Source ...............................................................................110
Creating a New Data Source..............................................................110
Modifying an Existing Data Source...................................................111
Modifying a DDL File ........................................................................112
Designing a Data Source View....................................................................114
Creating a New Data Source View.....................................................114
Modifying a DSV ...............................................................................115
Designing a Dimension ..............................................................................117
Creating a Dimension .......................................................................118
Modifying an Existing Dimension ....................................................119
Designing a Cube........................................................................................124
Creating a Cube .................................................................................124
Modifying a Cube..............................................................................125
Building a Cube Perspective ..............................................................130
Defining Cube Translations...............................................................131
Configuring and Deploying a Project So That You
Can Browse the Cube ...............................................................................133
Configuring a Project ........................................................................133
Deploying a Project ...........................................................................135
Browsing a Cube................................................................................136
Part 3 Using MDX To Analyze Data
10 MDX Concepts 139
The SELECT Statement ...............................................................................140
The SELECT Clause............................................................................140
Defining Coordinates in Multidimensional Space ...........................141
Default Members and the WHERE Clause ........................................144
Query Execution Context...........................................................................147
Set Algebra and Basic Set Operations .........................................................149
Union.................................................................................................149
Intersect .............................................................................................150
Except.................................................................................................150
CrossJoin............................................................................................151
Extract ................................................................................................152
MDX Functions...........................................................................................152
Functions for Navigating Hierarchies ...............................................153
The Function for Filtering Sets..........................................................155
Functions for Ordering Data .............................................................157
Referencing Objects in MDX and Using Unique Names ...........................158
By Name.............................................................................................158
By Qualified Name ............................................................................159
By Unique Name ...............................................................................159
11 Advanced MDX 161
Using Member and Cell Properties in MDX Queries .................................161
Member Properties.............................................................................161
Cell Properties....................................................................................162
Dealing with Nulls ......................................................................................165
Null Members, Null Tuples, and Empty Sets ....................................165
Nulls and Empty Cells.......................................................................170
Type Conversions Between MDX Objects..................................................173
Strong Relationships ...................................................................................174
Sets in a WHERE Clause..............................................................................177
SubSelect and Subcubes ..............................................................................180
Applying Visual Totals.......................................................................185
12 Cube-Based MDX Calculations 189
MDX Scripts ................................................................................................191
Calculated Members ...................................................................................192
Defining Calculated Members...........................................................193
Assignments ................................................................................................198
Assignment Operator.........................................................................199
Specifying a Calculation Property.....................................................202
Scope Statements ...............................................................................203
Root and Leaves Functions................................................................206
Calculated Cells .................................................................................208
Named Sets..................................................................................................209
Static Name Sets.................................................................................210
Dynamic Named Sets.........................................................................213
Order of Execution for Cube Calculations.................................................215
The Highest Pass Wins ......................................................................216
Recursion Resolution .........................................................................218
13 Dimension-Based MDX Calculations 221
Unary Operators..........................................................................................221
Custom Member Formulas .........................................................................225
Semi-Additive Measures ..............................................................................227
ByAccount Aggregation Function .....................................................229
Order of Execution for Dimension Calculations .......................................232
The Closest Wins ...............................................................................233
14 Extending MDX with Stored Procedures 237
Creating Stored Procedures.........................................................................239
Creating Common Language Runtime Assemblies ..........................239
Using Application Domains to Sandbox Common
Language Runtime Assemblies........................................................244
Creating COM Assemblies.................................................................245
Calling Stored Procedures from MDX ........................................................246
Security Model ............................................................................................248
Role-Based Security ............................................................................248
Code Access Security .........................................................................248
User-Based Security............................................................................249
Server Object Model....................................................................................251
Operations on Metadata Objects.......................................................252
Operations on MDX Objects.............................................................255
Calling Back into Stored Procedures ..........................................................257
Using Default Libraries ...............................................................................260
15 Key Performance Indicators, Actions, and the DRILLTHROUGH Statement 261
Key Performance Indicators........................................................................261
Defining KPIs .....................................................................................262
Discovering and Querying KPIs ........................................................270
Actions ........................................................................................................272
Defining Actions................................................................................273
Discovering Actions...........................................................................279
Drillthrough ................................................................................................283
DRILLTHROUGH Statement..............................................................285
Defining DRILLTHROUGH Columns in a Cube...............................287
16 Writing Data into Analysis Services 291
Using the UPDATE CUBE Statement to Write Data into Cube Cells ........292
Updatable and Non-Updatable Cells..........................................................298
Lifetime of the Update................................................................................299
Enabling Writeback.....................................................................................301
Converting a Writeback Partition to a Regular Partition...........................303
Other Ways to Perform Writeback..............................................................304
Part 4 Creating a Data Warehouse
17 Loading Data from a Relational Database 307
Loading Data...............................................................................................307
Data Source Objects ....................................................................................310
Data Source Object Properties ...........................................................310
Data Source Security ..........................................................................312
Connection Timeouts........................................................................314
Connection Pooling ..........................................................................314
18 DSVs and Object Bindings 317
DSV Objects ................................................................................................317
Named Queries and Named Calculations.........................................319
Object Bindings...........................................................................................321
Column Bindings...............................................................................321
Row Bindings.....................................................................................323
Tabular Bindings ................................................................................324
Query Bindings ..................................................................................326
19 Multidimensional Models and Relational Database Schemas 329
Relational Schemas for Data Warehouses ..................................................329
Optimizing Relational Schemas ........................................................331
Building Relational Schemas from the Multidimensional Model .............334
Using Wizards to Create Relational Schemas ...................................334
Using Templates to Create Relational Schemas ................................339
Part 5 Bringing Data into Analysis Services
20 The Physical Data Model 345
Internal Components for Storing Data ......................................................346
Data Store Structure...........................................................................346
File Store Structure.............................................................................346
Bit Store Structure..............................................................................348
String Store Structure.........................................................................348
Compressed Store Structure...............................................................349
Hash Index of a Store ........................................................................350
Data Structure of a Dimension ...................................................................351
Data Structures of the Attributes.......................................................351
Attribute Relationships......................................................................355
Data Structures of Hierarchies ...........................................................360
Physical Model of the Cube........................................................................364
Defining a Partition Using DDL........................................................364
Physical Model of the Partition.........................................................367
Overview of Cube Data Structures ....................................................375
21 Dimension and Partition Processing 377
Dimension Processing.................................................................................377
Attribute Processing...........................................................................377
Hierarchy Processing .........................................................................383
Building Decoding Tables..................................................................384
Building Indexes ................................................................................384
Schema of Dimension Processing .....................................................385
Dimension Processing Options .........................................................386
Processing ROLAP Dimensions .........................................................388
Processing Parent-Child Dimensions ................................................389
Cube Processing ..........................................................................................390
Data Processing..................................................................................391
Building Aggregations and Indexes...................................................393
Cube Processing Options...................................................................395
Progress Reporting and Error Configuration..............................................400
ErrorConfiguration Properties ...........................................................402
Processing Error Handling.................................................................405
22 Using SQL Server Integration Services to Load Data 407
Using SSIS....................................................................................................408
Using Direct-Load ETL ................................................................................409
Creating an SSIS Dimension-Loading Package .................................410
Creating an SSIS Partition-Loading Package .....................................414
23 Aggregation Design and Usage-Based Optimization 417
Aggregations and Collection of Aggregations............................................417
Designing Aggregations ..............................................................................419
Relational Reporting-Style Dimensions ............................................420
Flexible Versus Rigid Aggregations....................................................422
Aggregation Objects and Aggregation Design Objects .....................423
The Aggregation Design Algorithm...................................................426
Query Usage Statistics.................................................................................427
Setting Up a Query Log .....................................................................428
Manual Design and Management of Aggregations....................................431
Monitoring Aggregation Usage...................................................................433
24 Proactive Caching and Real-Time Updates 435
Data Latency and Proactive Caching .........................................................436
Timings and Proactive Caching..................................................................438
Update Frequency..............................................................................438
Long-Running MOLAP Cache Processing.........................................439
Proactive Caching Scenarios.......................................................................440
MOLAP Scenario................................................................................440
Scheduled MOLAP Scenario ..............................................................440
Automatic MOLAP Scenario..............................................................441
Medium-Latency MOLAP Scenario ...................................................442
Low-Latency MOLAP Scenario ..........................................................442
Real-Time HOLAP Scenario ...............................................................442
Real-Time ROLAP Scenario................................................................443
Change Notifications and Object Processing During Proactive Caching......443
Scheduling Processing and Updates..................................................443
Change Notification Types................................................................445
Incremental Updates Versus Full Updates.........................................447
General Considerations for Proactive Caching..........................................448
Monitoring Proactive Caching Activity .....................................................448
25 Building Scalable Analysis Services Applications 451
Approaches to Scalability............................................................................451
The Scale-Up Approach .....................................................................451
The Scale-Out Approach....................................................................452
OLAP Farm ..................................................................................................453
Data Storage.......................................................................................453
Network Load Balancing ...................................................................455
Linked Dimensions and Measure Groups ..................................................455
Updates to the Source of a Linked Object ........................................457
Linked Dimensions............................................................................457
Linked Measure Groups.....................................................................461
Remote Partitions........................................................................................464
Processing Remote Partitions ............................................................466
Using Business Intelligence Development Studio to
Create Linked Dimensions .......................................................................467
Using BI Dev Studio to a Create Virtual Cube..................................468
Shared Scalable Databases...........................................................................470
Attach\Detach, Read-Only, and DbStorageLocation .................................470
Detach................................................................................................470
Attach.................................................................................................472
Read-Only ..........................................................................................473
DbStorageLocation.............................................................................473
Part 6 Analysis Server Architecture
26 Server Architecture and Command Execution 477
Command Execution..................................................................................477
Session Management ..................................................................................481
Server State Management ...........................................................................482
Executing Commands That Change Analysis Services Objects .................483
Creating Objects ................................................................................484
Editing Objects ..................................................................................484
Deleting Objects ................................................................................486
Processing Objects .............................................................................486
Commands That Control Transactions.............................................489
Managing Concurrency.....................................................................491
Using a Commit Lock for Transaction Synchronization..................492
Canceling a Command Execution..............................................................494
Batch Command.........................................................................................496
27 Memory Management 503
Economic Memory Management Model....................................................504
Server Performance and Memory Manager................................................504
Memory Holders ................................................................................504
Memory Cleanup...............................................................................507
Managing Memory of Different Subsystems..............................................509
Cache System Memory Model...........................................................509
Managing Memory of File Stores ......................................................510
Managing Memory Used by User Sessions .......................................510
Other Memory Holders .....................................................................510
Memory Allocators......................................................................................511
Effective Memory Distribution with Memory Governor...........................512
Memory Models of Attribute and Partition Processing ....................515
Memory Model of Building Aggregations.........................................517
Memory Model of Building Indexes .................................................518
28 Thread Management 521
Thread Pools................................................................................................522
Architecture of a Thread Pool.....................................................................523
Managing Threads by Different Subsystems ..............................................525
29 Architecture of Query Execution–Calculating MDX Expressions 527
Query Execution Stages ..............................................................................528
Parsing an MDX Request ............................................................................530
Creation of Calculation Scopes ..................................................................531
Global Scope and Global Scope Cache .............................................535
Session Scope and Session Scope Cache ...........................................536
Global and Session Scope Lifetime ...................................................536
Building a Virtual Set Operation Tree ........................................................538
Optimizing Multidimensional Space by Removing Empty Tuples............541
Calculating Cell Values ...............................................................................542
Logical Plan Construction .................................................................542
Physical Plan Construction ...............................................................546
Execution of the Physical Plan..........................................................547
Cache Subsystem ........................................................................................548
Dimension and Measure Group Caches ...........................................548
Formula Caches .................................................................................550
30 Architecture of Query Execution–Retrieving Data from Storage 553
Query Execution Stages ..............................................................................554
Querying Different Types of Measure Groups............................................556
Querying Regular Measure Groups ...................................................556
Querying ROLAP Partitions...............................................................559
Querying Measure Groups with DISTINCT_COUNT Measures........560
Querying Remote Partitions and Linked Measure Groups ...............563
Querying Measure Groups with Indirect Dimensions......................564
Part 7 Accessing Data in Analysis Services
31 Client/Server Architecture and Data Access 569
Using TCP/IP for Data Access .....................................................................569
Using Binary XML and Compression for Data Access......................570
Using HTTP for Data Access .......................................................................571
Offline Access to Data.................................................................................573
Client Components Shipped with Analysis Services .................................574
Using XML for Analysis to Build Your Application ..........................574
Using Analysis Services Libraries to Build Your Application ............575
Query Management for Applications Written in Native Code ........576
Query Management for Applications Written in Managed Code ......576
Using DSO and AMO for Administrative Applications ....................577
32 XML for Analysis 579
State Management ......................................................................................580
XML/A Methods..........................................................................................583
The Discover Method ........................................................................583
The Execute Method..........................................................................587
Handling Errors and Warnings...................................................................593
Errors That Result in the Failure of the Whole Method...................594
Errors That Occur After Serialization of the Response Has Started ......596
Errors That Occur During Cell Calculation ......................................597
Warnings............................................................................................598
33 ADOMD.NET 599
Creating an ADOMD.NET Project ..............................................................599
Writing Analytical Applications .................................................................602
ADOMD.NET Connections.........................................................................603
Working with Metadata Objects.................................................................610
Operations on Collections.................................................................612
Caching Metadata on the Client.......................................................615
Working with a Collection of Members (MemberCollection)..........618
Working with Metadata That Is Not Presented in the
Form of Objects ...............................................................................625
AdomdCommand .......................................................................................630
Properties ...........................................................................................630
Methods .............................................................................................632
Using the CellSet Object to Work with Multidimensional Data ...............636
Handling Object Symmetry...............................................................644
Working with Data in Tabular Format .......................................................647
AdomdDataReader.............................................................................649
Using Visual Studio User Interface Elements to Work with
OLAP Data .......................................................................................652
Which Should You Use: AdomdDataReader or CellSet? ............................654
Using Parameters in MDX Requests ...........................................................655
Asynchronous Execution and Cancellation of Commands.......................657
Error Handling ............................................................................................662
AdomdErrorResponseException ........................................................663
AdomdUnknownResponseException................................................666
AdomdConnectionException............................................................666
AdomdCacheExpiredException.........................................................666
34 Analysis Management Objects 669
AMO Object Model.....................................................................................669
Types of AMO Objects.......................................................................670
Dependent and Referenced Objects ..................................................678
Creating a Visual Studio Project That Uses AMO ......................................685
Connecting to the Server............................................................................685
Canceling Long-Running Operations.........................................................688
AMO Object Loading ..................................................................................692
Working with AMO in Disconnected Mode ..............................................693
Using the Scripter Object............................................................................694
Using Traces ................................................................................................697
Error Handling ............................................................................................706
OperationException...........................................................................706
ResponseFormatException.................................................................707
ConnectionException........................................................................708
OutOfSyncException .........................................................................708
Part 8 Security
35 Security Model for Analysis Services 713
Connection Security ...................................................................................714
TCP/IP Connection Security..............................................................714
HTTP Security ....................................................................................715
External Data Access Security .....................................................................718
Choosing a Service Logon Account ..................................................718
Configuring Access to External Data Sources ...................................719
Changing a Service Logon Account ..................................................720
Security for Running Named Instances (SQL Server Browser) .........721
Security for Running on a Failover Cluster.......................................721
Object Security Model for Analysis Services...............................................721
Server Administrator Security............................................................722
Database Roles and Permission Objects ............................................723
Defining Object Permissions .............................................................726
Managing Database Roles..................................................................730
36 Securing Dimension Data 731
Defining Dimension Security .....................................................................734
The AllowedSet and DeniedSet Properties ........................................735
The VisualTotals Property ...........................................................................740
Defining Dimension Security Using the User Interface ...................742
Testing Dimension Security ........................................................................744
Dynamic Dimension Security.....................................................................746
Dimension Security Architecture................................................................748
Dimension Security, Cell Security, and MDX Scripts.................................748
37 Securing Cell Values 751
Defining Cell Security.................................................................................751
Testing Cell Security....................................................................................754
Contingent Cell Security ............................................................................756
Dynamic Cell Security ................................................................................758
Part 9 Management
38 Using Trace to Monitor and Audit Analysis Services 763
Trace Architecture .......................................................................................764
Types of Trace Objects ................................................................................765
Administrative Trace..........................................................................765
Session Trace ......................................................................................765
Flight Recorder Trace .........................................................................765
Creating Trace Command Options ............................................................766
SQL Server Profiler ......................................................................................768
Defining a Trace.................................................................................768
Running a Trace.................................................................................770
Flight Recorder ............................................................................................773
How the Flight Recorder Works ........................................................774
Configuring Flight Recorder Behavior ..............................................775
Discovering Server State ....................................................................776
Tracing Processing Activity .........................................................................776
Reporting the Progress of Dimension Processing .............................776
Reporting the Progress of Partition Processing .................................779
Query Execution Time Events ....................................................................780
Running a Simple Query ...................................................................780
Changing the Simple Query..............................................................781
Running a More Complex Query......................................................782
Changing the Complex Query..........................................................783
Changing Your Query Just a Little More ..........................................784
39 Backup and Restore Operations 787
Backing Up Data .........................................................................................787
Planning Your Backup Operation......................................................788
Using the Backup Database Dialog Box to Back Up Your Database ...790
Using a DDL Command to Back Up Your Database.........................792
Backing Up Related Files.............................................................................793
Backing Up the Configuration File ...................................................793
Backing Up the Query Log Database ................................................793
Backing Up Writeback Tables ............................................................794
Backup Strategies.........................................................................................795
Typical Backup Scenario ....................................................................795
High-Availability System Backup Scenario........................................795
Automating Backup Operations .................................................................796
SQL Server Agent ...............................................................................796
SQL Server Integration Services.........................................................797
AMO Application...............................................................................798
Restoring Lost or Damaged Data ................................................................798
Using the Restore Database Dialog Box ............................................799
Using the DDL Command to Restore Your Database .......................800
Using DISCOVER_LOCATIONS to Specify Alternative
Locations for Partitions ...................................................................801
MDX Extensions for Browsing Your File System........................................803
The MDX Extensions.........................................................................804
40 Deployment Strategies 805
Using the Deployment Wizard ...................................................................805
Synchronizing Your Databases ...................................................................807
Using the Synchronize Database Wizard ..........................................809
Using a DDL Command to Synchronize Databases .........................809
Similarities Between the Synchronization and Restore Commands 811
Synchronization and Remote Partitions ....................................................812
Synchronization and Failover Clusters.......................................................814
41 Resource Monitoring 815
DMVs and SchemaRowsets.........................................................................816
Querying DMVs and SQL Semantics..........................................................817
Monitoring Connections, Sessions, and Commands.................................818
Monitoring Server State ..............................................................................820
Using Perfmon Counters ............................................................................821
Index 823