Microsoft SQL Server 2005 Analysis Services

by Melomed, Edward; Gorbach, Irina; Berger, Alexander; Bateman, Py

• ISBN13: 9780672327827
• ISBN10: 0672327821
• Edition: 1st
• Format: Paperback
• Copyright: 2006-12-15
• Publisher: Sams Publishing

Summary

The definitive guide to programming Analysis Services.

Author Biography

Edward Melomed is one of the original members of the Microsoft SQL Server Analysis Services team. He arrived in Redmond as a part of Microsoft's acquisition of Panorama Software Systems, Inc., which led to the technology that gave rise to Analysis Services 2005. He works as a program manager and plays a major role in the infrastructure design for the Analysis Services engine.

 

Irina Gorbach is a senior software designer on the Analysis Services team, which she joined soon after its creation nine years ago. During her time at Microsoft, Irina has designed and developed many features, was responsible for client subsystems OLEDB and ADOMD.NET, and was in the original group of architects that designed the XML for Analysis specification. Recently she has been working on the architecture and design of calculation algorithms.

 

Alexander Berger was one of the first developers to work on OLAP systems at Panorama. After it was acquired by Microsoft, he led the development of Microsoft OLAP Server through all its major releases. He is one of the architects of OLEDB for the OLAP standard and MDX language, and holds more than 30 patents in the area of multidimensional databases.

 

Py Bateman is a technical writer at Microsoft. She originally hails from Texas, which was considered a separate country on the multinational Analysis Services team.

 

Table of Contents

Foreword	p. xxii
Introduction	p. 1
Introduction to Analysis Services
What's New in Analysis Services 2005	p. 5
Modeling Capabilities of Analysis Services 2005	p. 5
Advanced Analytics in Analysis Services 2005	p. 6
New Client-Server Architecture	p. 7
Improvements in Scalability	p. 7
Development and Management Tools	p. 8
Manageability of Analysis Services	p. 8
Sample Project	p. 9
Customer Data	p. 9
Store Data	p. 9
Product and Warehouse Data	p. 9
Time Data	p. 10
Account Data	p. 10
Currency Data	p. 10
Employee Data	p. 10
The Warehouse and Sales Cube	p. 10
The HR Cube	p. 11
The Budget Cube	p. 11
The Sales and Employees Cube	p. 11
Summary	p. 11
Multidimensional Databases	p. 13
The Multidimensional Data Model	p. 15
The Conceptual Data Model	p. 15
The Physical Data Model	p. 15
The Application Data Model	p. 16
Multidimensional Space	p. 16
Describing Multidimensional Space	p. 16
Summary	p. 23
UDM: Linking Relational and Multidimensional Databases	p. 25
Summary	p. 27
Client/Server Architecture and Multidimensional Databases: An Overview	p. 29
Two-Tier Architecture	p. 30
One-Tier Architecture	p. 31
Three-Tier Architecture	p. 32
Four-Tier Architecture	p. 33
Distributed Systems	p. 34
Distributed Storage	p. 34
Thin Client/Thick Client	p. 34
Summary	p. 36
Creating Multidimensional Models
Conceptual Data Model	p. 39
Data Definition Language	p. 39
Objects in DDL	p. 40
Summary	p. 43
Dimensions in the Conceptual Model	p. 45
Dimension Attributes	p. 46
Attribute Properties and Values	p. 48
Relationships Between Attributes	p. 49
Attribute Member Keys	p. 52
Attribute Member Names	p. 55
Relationships Between Attributes	p. 56
Attribute Discretization	p. 58
Parent Attributes	p. 60
Dimension Hierarchies	p. 61
Types of Hierarchies	p. 61
Attribute Hierarchies	p. 64
Parent-Child Hierarchies	p. 66
Summary	p. 67
Cubes and Multidimensional Analysis	p. 69
Cube Dimensions	p. 71
Cube Dimension Attributes	p. 74
Cube Dimension Hierarchies	p. 76
Role-Playing Dimensions	p. 76
The Dimension Cube	p. 77
Perspectives	p. 78
Summary	p. 80
Measures and Multidimensional Analysis	p. 83
Measures in Multidimensional Cubes	p. 83
Sum	p. 86
Max and Min	p. 87
Count	p. 87
Distinct Count	p. 87
Measure Groups	p. 88
Measure Group Dimensions	p. 90
Granularity of a Fact	p. 91
Indirect Dimensions	p. 95
Measure Expressions	p. 103
Linked Measure Groups	p. 106
Summary	p. 106
Multidimensional Models and Business Intelligence Development Studio	p. 109
Creating a Data Source	p. 110
Creating a New Data Source	p. 110
Modifying an Existing Data Source	p. 111
Modifying a DDL File	p. 112
Designing a Data Source View	p. 113
Creating a New Data Source View	p. 114
Modifying a Data Source View	p. 115
Designing a Dimension	p. 117
Creating a Dimension	p. 117
Modifying an Existing Dimension	p. 121
Designing a Cube	p. 125
Creating a Cube	p. 126
Modify a Cube	p. 128
Build a Cube Perspective	p. 134
Defining Cube Translations	p. 134
Configuring and Deploying a Project So That You Can Browse the Cube	p. 136
Configuring a Project	p. 136
Deploying a Project	p. 138
Browsing a Cube	p. 138
Summary	p. 139
Using MDX to Analyze Data
MDX Concepts	p. 143
The Select Statement	p. 144
The Select Clause	p. 144
Defining Coordinates in Multidimensional Space	p. 145
Default Members and the WHERE Clause	p. 148
Query Execution Context	p. 151
Set Algebra and Basic Set Operations	p. 153
Union	p. 153
Intersect	p. 154
Except	p. 155
CrossJoin	p. 155
Extract	p. 156
MDX Functions	p. 157
Functions for Navigating Hierarchies	p. 158
The Function for Filtering Sets	p. 160
Functions for Ordering Data	p. 162
Referencing Objects in MDX and Using Unique Names	p. 163
By Name	p. 163
By Qualified Name	p. 163
By Unique Name	p. 164
Summary	p. 164
Advanced MDX	p. 165
Using Member and Cell Properties in MDX Queries	p. 165
Member Properties	p. 165
Cell Properties	p. 166
Dealing with Nulls	p. 168
Null Members, Null Tuples, and Empty Sets	p. 168
Nulls and Empty Cells	p. 173
Type Conversions Between MDX Objects	p. 177
Strong Relationships	p. 178
Sets in a Where Clause	p. 180
SubSelect and Subcubes	p. 183
Summary	p. 190
Cube-Based MDX Calculations	p. 193
MDX Scripts	p. 195
Calculated Members	p. 196
Defining Calculated Members	p. 196
Assignments	p. 202
Assignment Operator	p. 203
Specifying a Calculation Property	p. 206
Scope Statements	p. 207
Root and Leaves Functions	p. 209
Calculated Cells	p. 211
Named Sets	p. 212
Order of Execution for Cube Calculations	p. 216
The Highest Pass Wins	p. 218
Recursion Resolution	p. 219
Summary	p. 222
Dimension-Based MDX Calculations	p. 225
Unary Operators	p. 226
Custom Member Formulas	p. 229
Semiadditive Measures	p. 231
ByAccount Aggregation Function	p. 234
Order of Execution for Dimension Calculations	p. 237
The Closest Wins	p. 237
Summary	p. 241
Extending MDX with Stored Procedures	p. 243
Creating Stored Procedures	p. 244
Creating Common Language Runtime Assemblies	p. 245
Using Application Domains to Send-Box Common Language Runtime Assemblies	p. 250
Creating COM Assemblies	p. 251
Calling Stored Procedures from MDX	p. 252
Security Model	p. 254
Role-Based Security	p. 254
Code Access Security	p. 254
User-Based Security	p. 255
Server Object Model	p. 257
Operations on Metadata Objects	p. 259
Operations on MDX Objects	p. 261
Using Default Libraries	p. 263
Summary	p. 264
Key Performance Indicators, Actions, and the Drillthrough Statement	p. 265
Key Performance Indicators	p. 266
Defining KPIs	p. 266
Discovering and Querying KPIs	p. 271
Actions	p. 272
Defining Actions	p. 273
Discovering Actions	p. 279
Drillthrough	p. 284
Drillthrough Statement	p. 285
Defining Drillthrough Columns in a Cube	p. 287
Summary	p. 290
Writing Data into Analysis Services	p. 291
Using the Update Cube Statement to Write Data into Cube Cells	p. 292
Updatable and Nonupdatable Cells	p. 297
Lifetime of the Update	p. 297
Enabling Writeback	p. 299
Converting a Writeback Partition to a Regular Partition	p. 301
Other Ways to Perform Writeback	p. 301
Summary	p. 302
Creating a Data Warehouse
Loading Data from a Relational Database	p. 305
Loading Data	p. 305
Data Source Objects	p. 307
Data Source Object Properties	p. 308
Data Source Security	p. 309
Connection Timeouts	p. 311
Summary	p. 311
DSVs and Object Bindings	p. 313
Data Source View	p. 313
Named Queries	p. 315
Named Calculations	p. 316
Object Bindings	p. 316
Column Bindings	p. 317
Table Bindings	p. 318
Query Bindings	p. 319
Summary	p. 320
Multidimensional Models and Relational Database Schemas	p. 321
Relational Schemas for Data Warehouses	p. 321
Building Relational Schemas from the Multidimensional Model	p. 323
Using Wizards to Create Relational Schemas	p. 323
Using Templates to Create Relational Schemas	p. 328
Summary	p. 330
Bringing Data into Analysis Services
The Physical Data Model	p. 333
Internal Components for Storing Data	p. 334
Data Store Structure	p. 334
File Store Structure	p. 334
Bit Store Structure	p. 336
String Store Structure	p. 336
Compressed Store Structure	p. 337
Hash Index of a Store	p. 338
Data Structure of a Dimension	p. 339
Data Structures of the Attributes	p. 339
Attribute Relationships	p. 343
Data Structures of Hierarchies	p. 347
Physical Model of the Cube	p. 351
Defining a Partition Using Data Definition Language	p. 351
Physical Model of the Partition	p. 354
Overview of Cube Data Structures	p. 361
Summary	p. 362
Dimension and Partition Processing	p. 365
Dimension Processing	p. 365
Attribute Processing	p. 365
Hierarchy Processing	p. 371
Building Decoding Tables	p. 372
Building Indexes	p. 372
Schema of Dimension Processing	p. 373
Dimension Processing Options	p. 373
Processing ROLAP Dimensions	p. 376
Processing Parent-Child Dimensions	p. 377
Cube Processing	p. 378
Data Processing	p. 379
Building Aggregations and Indexes	p. 381
Cube Processing Options	p. 383
Progress Reporting and Error Configuration	p. 388
ErrorConfiguration Properties	p. 389
Processing Error Handling	p. 391
Summary	p. 393
Using SQL Server Integration Services to Load Data	p. 395
Using Direct Load ETL	p. 396
Creating an SSIS Dimension-Loading Package	p. 398
Creating an SSIS Partition-Loading Package	p. 402
Summary	p. 404
Aggregation Design and Usage-Based Optimization	p. 405
Designing Aggregations	p. 407
Relational Reporting-Style Dimensions	p. 408
Flexible Versus Rigid Aggregations	p. 410
Aggregation Objects and Aggregation Design Objects	p. 411
The Aggregation Design Algorithm	p. 414
Query Usage Statistics	p. 415
Setting Up a Query Log	p. 416
Monitoring Aggregation Usage	p. 418
Summary	p. 419
Proactive Caching and Real-Time Updates	p. 421
Data Latency and Proactive Caching	p. 422
Timings and Proactive Caching	p. 424
Frequency of Updates	p. 424
Long-Running MOLAP Cache Processing	p. 425
Proactive Caching Scenarios	p. 426
MOLAP Scenario	p. 426
Scheduled MOLAP Scenario	p. 427
Automatic MOLAP Scenario	p. 428
Medium-Latency MOLAP Scenario	p. 428
Low-Latency MOLAP Scenario	p. 428
Real-time HOLAP Scenario	p. 428
Real-time ROLAP Scenario	p. 429
Change Notifications and Object Processing During Proactive Caching	p. 429
Scheduling Processing and Updates	p. 430
Change Notification Types	p. 431
Incremental Updates Versus Full Updates	p. 434
General Considerations for Proactive Caching	p. 434
Monitoring Proactive Caching Activity	p. 435
Summary	p. 436
Building Scalable Analysis Services Applications	p. 437
Approaches to Scalability	p. 437
The Scale-Up Approach	p. 437
The Scale-Out Approach	p. 438
OLAP Farm	p. 439
Data Storage	p. 439
Network Load Balancing	p. 441
Linked Dimensions and Measure Groups	p. 442
Updates to the Source of a Linked Object	p. 443
Linked Dimensions	p. 443
Linked Measure Groups	p. 447
Remote Partitions	p. 450
Processing Remote Partitions	p. 452
Using Business Intelligence Development Studio to Create Linked Dimensions	p. 453
Summary	p. 456
Analysis Server Architecture
Server Architecture and Command Execution	p. 459
Command Execution	p. 459
Session Management	p. 463
Server State Management	p. 465
Executing Commands That Change Analysis Services Objects	p. 465
Creating Objects	p. 466
Editing Objects	p. 467
Deleting Objects	p. 468
Processing Objects	p. 468
Commands That Control Transactions	p. 471
Managing Concurrency	p. 473
Using a Commit Lock for Transaction Synchronization	p. 474
Canceling a Command Execution	p. 476
Batch Command	p. 478
Summary	p. 484
Memory Management	p. 485
Economic Memory Management Model	p. 486
Server Performance and Memory Manager	p. 487
Memory Holders	p. 487
Memory Cleanup	p. 488
Managing Memory of Different Subsystems	p. 490
Cache System Memory Model	p. 491
Managing Memory of File Stores	p. 491
Managing Memory Used by User Sessions	p. 492
Other Memory Holders	p. 492
Memory Allocators	p. 492
Effective Memory Distribution with Memory Governor	p. 494
Model of Attribute and Partition Processing	p. 496
Model of Building Aggregations	p. 499
Model of Building Indexes	p. 500
Summary	p. 500
Architecture of Query Execution-Calculating MDX Expressions	p. 503
Query Execution Stages	p. 503
Parsing an MDX Request	p. 505
Creation of Calculation Scopes	p. 507
Global Scope and Global Scope Cache	p. 509
Session Scope and Session Scope Cache	p. 510
Global and Session Scope Lifetime	p. 510
Building a Virtual Set Operation Tree	p. 513
Optimizing Multidimensional Space by Removing Empty Tuples	p. 514
Calculating Cell Values	p. 515
Calculation Execution Plan Construction	p. 516
Evaluation of Calculation Execution Plan	p. 517
Execution of the Calculation Execution Plan	p. 518
Cache Subsystem	p. 518
Dimension and Measure Group Caches	p. 518
Formula Caches	p. 521
Summary	p. 522
Architecture of Query Execution-Retrieving Data from Storage	p. 523
Query Execution Stages	p. 524
Querying Different Types of Measure Groups	p. 526
Querying Regular Measure Groups	p. 526
Querying ROLAP Partitions	p. 529
Querying Measure Groups with DISTINCT_COUNT Measures	p. 529
Querying Remote Partitions and Linked Measure Groups	p. 532
Querying Measure Groups with Indirect Dimensions	p. 533
Summary	p. 535
Accessing Data in Analysis Services
Client/Server Architecture and Data Access	p. 539
Using TCP/IP for Data Access	p. 539
Using Binary XML and Compression for Data Access	p. 540
Using HTTP for Data Access	p. 542
Offline Access to Data	p. 543
Summary	p. 545
Client Components Shipped with Analysis Services	p. 547
Using XML for Analysis to Build Your Application	p. 547
Using Analysis Services Libraries to Build Your Application	p. 548
Query Management for Applications Written in Native Code	p. 549
Query Management for Applications Written in Managed Code	p. 549
Using DSO and AMO for Administrative Applications	p. 551
Summary	p. 552
XML for Analysis	p. 553
State Management	p. 554
XML/A Methods	p. 557
The Discover Method	p. 557
The Execute Method	p. 561
Handling Errors and Warnings	p. 567
Errors That Result in the Failure of the Whole Method	p. 568
Errors That Occur After Serialization of the Response Has Started	p. 570
Errors That Occur During Cell Calculation	p. 571
Warnings	p. 572
Summary	p. 573
ADOMD.NET	p. 575
Creating an ADOMD.NET Project	p. 575
Writing Analytical Applications	p. 577
ADOMD.NET Connections	p. 578
Working with Metadata Objects	p. 586
Operations on Collections	p. 586
Caching Metadata on the Client	p. 591
Working with a Collection of Members (MemberCollection)	p. 593
Working with Metadata That Is Not Presented in the Form of Objects	p. 600
AdomdCommand	p. 605
Properties	p. 606
Methods	p. 607
Using the CellSet Object to Work with Multidimensional Data	p. 612
Handling Object Symmetry	p. 619
Working with Data in Tabular Format	p. 622
AdomdDataReader	p. 625
Using Visual Studio User Interface Elements to Work with OLAP Data	p. 628
Which Should You Use: AdomdDataReader or CellSet?	p. 630
Using Parameters in MDX Requests	p. 631
Asynchronous Execution and Cancellation of Commands	p. 634
Error Handling	p. 639
AdomdErrorResponseException	p. 640
AdomdUnknownResponseException	p. 642
AdomdConnectionException	p. 642
AdomdCacheExpiredException	p. 642
Summary	p. 644
Analysis Management Objects	p. 647
AMO Object Model	p. 647
Types of AMO Objects	p. 648
Dependent and Referenced Objects	p. 657
Creating a Visual Studio Project That Uses AMO	p. 663
Connecting to the Server	p. 664
Canceling Long-Running Operations	p. 667
AMO Object Loading	p. 671
Working with AMO in Disconnected Mode	p. 672
Using the Scripter Object	p. 673
Using Traces	p. 676
Error Handling	p. 684
OperationException	p. 685
ResponseFormatException	p. 685
ConnectionException	p. 686
OutOfSyncException	p. 687
Summary	p. 688
Security
Security Model for Analysis Services	p. 693
Connection Security	p. 694
TCP/IP Connection Security	p. 695
HTTP Security	p. 696
External Data Access Security	p. 700
Choosing a Service Logon Account	p. 700
Changing a Service Logon Account	p. 701
Security for Running Named Instances (SQL Server Browser)	p. 702
Security for Running on a Failover Cluster	p. 702
Summary	p. 702
Object Security Model for Analysis Services	p. 705
Server Administrator Security	p. 705
Database Roles and the Hierarchy of Permission Objects	p. 707
Permission Objects	p. 710
Managing Database Roles	p. 713
Summary	p. 714
Securing Dimension Data	p. 715
Defining Dimension Security	p. 718
The AllowedSet and DeniedSet Properties	p. 719
The VisualTotals Property	p. 724
Defining Dimension Security Using the User Interface	p. 725
Testing Dimension Security	p. 727
Dynamic Security	p. 729
Dimension Security Architecture	p. 731
Dimension Security, Cell Security, and MDX Scripts	p. 732
Summary	p. 733
Securing Cell Values	p. 735
Defining Cell Security	p. 735
Testing Cell Security	p. 738
Contingent Cell Security	p. 740
Dynamic Security	p. 742
Summary	p. 744
Management
Using Trace to Monitor and Audit Analysis Services	p. 749
Trace Architecture	p. 750
Types of Trace Objects	p. 751
Administrative Trace	p. 751
Session Trace	p. 752
Flight Recorder Trace	p. 752
Creating Trace Command Options	p. 752
SQL Server Profiler	p. 753
Defining a Trace	p. 754
Running a Trace	p. 756
Flight Recorder	p. 759
How Flight Recorder Works	p. 761
Configuring Flight Recorder Behavior	p. 761
Discovering Server State	p. 762
Tracing Processing Activity	p. 764
Reporting the Progress of Dimension Processing	p. 764
Reporting the Progress of Partition Processing	p. 766
Query Execution Time Events	p. 767
Running a Simple Query	p. 767
Changing the Simple Query	p. 769
Running a More Complex Query	p. 770
Changing the Complex Query	p. 771
Changing Your Query Just a Little More	p. 772
Summary	p. 773
Backup and Restore Operations	p. 775
Backing Up Data	p. 775
Planning Your Backup Operation	p. 776
Benefits of Analysis Server 2005 Backup Functionality	p. 777
Using the Backup Database Dialog Box to Back Up Your Database	p. 111
Using a DDL Command to Back Up Your Database	p. 779
Backing Up Related Files	p. 780
Backing Up the Configuration File	p. 781
Backing Up the Query Log Database	p. 781
Backing Up Writeback Tables	p. 781
Backup Strategies	p. 782
Typical Backup Scenario	p. 782
High Availability System Backup Scenario	p. 783
Automating Backup Operations	p. 784
SQL Server Agent	p. 784
SQL Server Integration Services	p. 784
AMO Application	p. 785
Restoring Lost or Damaged Data	p. 785
Using the Restore Database Dialog Box	p. 786
Using the DDL Command to Restore Your Database	p. 787
Using Discover_Locations to Specify Alternative Locations for Partitions	p. 788
MDX Extensions for Browsing Your File System	p. 789
The MDX Extensions	p. 790
Summary	p. 791
Deployment Strategies	p. 793
Using the Deployment Wizard	p. 793
Synchronizing Your Databases	p. 795
Using the Synchronize Database Wizard	p. 797
Using a DDL Command to Synchronize Databases	p. 798
Similarities Between the Synchronization and Restore Commands	p. 799
Synchronization and Remote Partitions	p. 800
Synchronization and Failover Clusters	p. 802
Summary	p. 802
Index	p. 803
Table of Contents provided by Ingram. All Rights Reserved.

Excerpts

Foreword Foreword It was a pleasure to be asked to write the foreword to this new book, which is remarkable for two reasons: People who have spent five years developing a product are normally more than ready to move on to the next release once the product is finally ready for release. Indeed, long before a new version gets into customers' hands, the developers are normally already working on the next release. So, for the actual developers to spend the considerable time that this book must have taken to write a lengthy, detailed book on it is very rare. In my years as an industry analyst withThe OLAP Report, and much earlier as a product manager, I have rarely come across developers who are prepared to provide such chapter and verse information on exactly how a product works. Even under NDA, few software vendors are prepared to volunteer this level of inside information. But why should this be of interest to anyone who isn't an OLAP server developer? Why should a mere user or even an application developer care about what exactly happens under the hood, any more than ordinary car drivers needs to know the details of exactly how their car's engine management system works? There are some good reasons why this is relevant. Analysis Services is now by far the most widely used OLAP server, which inevitably means that most of its users are new to OLAP.The OLAP Surveyshave consistently found that the main reason for the choice is price and the fact that it is bundled with SQL Server, rather than performance, scalability, ease of use, or functionality. This is not to say that Analysis Services lacks these capabilities; just that typical Analysis Services buyers are less concerned about them than are the buyers of other products. But when they come to build applications, they certainly will need to take these factors into account, and this book will help them succeed. Just because Analysis Services is perceived as being a low-cost, bundled product does not mean that it is a small, simple add-on: particularly in the 2005 release, it is an ambitious, complex, sophisticated product. How it works is far from obvious, and how to make the most of it requires more than guesswork. Many of the new Analysis Services users will have used relational databases previously, and will assume that OLAP databases are similar. They are not, despite the superficial similarities between MDX and SQL. You really need to think multidimensionally, and understand how Analysis Services cubes work. Even users with experience of other OLAP servers will find that they differ from each other much more than do relational databases. If you start using Analysis Services without understanding the differences and without knowing how Analysis Services really works, you will surely store up problems for the future. Even if you manage to get the right results now, you may well compromise the performance and future maintainability of the application. The OLAP Surveyshave consistently found that if there is one thing that really matters with OLAP, it is a fast query response. Slow performance is the biggest single product-related complaint from OLAP users in general, and Analysis Services users are no different. Slow query performance was also the biggest technical deterrent to wider deployment. Many people hope that ever improving hardware performance will let them off the hook: If the application is too slow, just rely on the next generation of faster hardware to solve the problem. But results fromThe OLAP Surveysshow that this will not work--the rate of performance complaints has gone up every year, whether actual query performance has improved or not. In an era when everyone expects free sub-second Web searches of billions of documents, books, and newsgroup postings, they are no longer willing to w