- Shopping Bag ( 0 items )
Want a NOOK? Explore Now
Microsoft SQL Server Analysis Services provides fast access to data by means of multidimensional data structures and the multidimensional query languag MDX. Analysis Services provides the capability to design, create, and manage multidimensional cubes based on data warehouse tables, and it serves as the foundation for the Microsoft Business Intelligence strategy.
Microsoft SQL Server 2005 Analysis Services gives the reader insight into the way Analysis Services functions. It not only explains ways to design and create multidimensional objects, databases, dimensions, and cubes, but also provides invaluable information about the reasons behind design decisions made by the development team.
Here's what you will find inside:
All the code for the sample database used in the book can be found at informit.com/title/0672327821.
ForewordForeword
It was a pleasure to be asked to write the foreword to this new book, which is remarkable for two reasons:
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 Surveys have 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 thattypical 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 Surveys have 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 from The OLAP Surveys show 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 wait five or ten seconds for a simple management report from a modest internal database. It is not enough for an OLAP application to be faster than the spreadsheet or relational application it replaced—it must be as fast as other systems that we all use every day.
The good news is that fast query performance is possible if you take full advantage of the OLAP server's capabilities: The OLAP Survey 6 found that 57% of Analysis Services 2005 users reported that their typical query response was less than five seconds. This was the traditional benchmark target query time, but in the new era of instant Web searches, I think the new target should be reduced to one second. This is a tough target, and will require application developers to really know what they are doing, and to take the time to optimize their systems.
This is where this book comes in. The authors—who have been involved with Analysis Services from its earliest days, long before it was called Analysis Services—have documented, in detail, what really happens inside Analysis Services 2005, right down to the bit structure of data records. Along the way, numerous controllable parameters are described, with helpful information about how they cause memory or other computer resources to be used.
This book is not intended to teach new users how to use Analysis Services 2005; it is for technically competent implementers who want to make the most of Analysis Services by understanding how it really works, as described by those who really know, unlike other books written by external authors who sometimes have to speculate. If you are new to Analysis Services, you probably need to start with a "how do I?" book or course, rather than a "what happens inside?" book like this one.
Nigel Pendse
Editor of The OLAP Report
Author of The OLAP Survey
© Copyright Pearson Education. All rights reserved.
Foreword
Introduction
PART I: Introduction to Analysis Services
1 What's New in Analysis Services 2005
Modeling Capabilities of Analysis Services 2005
Advanced Analytics in Analysis Services 2005
New Client-Server Architecture
Improvements in Scalability
Development and Management Tools
Manageability of Analysis Services
Sample Project
Customer Data
Store Data
Product and Warehouse Data
Time Data
Account Data
Currency Data
Employee Data
The Warehouse and Sales Cube
The HR Cube
The Budget Cube
The Sales and Employees Cube
Summary
2 Multidimensional Databases
The Multidimensional Data Model
The Conceptual Data Model.
The Physical Data Model
The Application Data Model
Multidimensional Space
Describing Multidimensional Space
Summary
3 UDM: Linking Relational and Multidimensional Databases
Summary
4 Client/Server Architecture and Multidimensional Databases: An Overview
Two-Tier Architecture
One-Tier Architecture
Three-Tier Architecture
Four-Tier Architecture
Distributed Systems
Distributed Storage
Thin Client/Thick Client
Summary
PART II: Creating Multidimensional Models
5 Conceptual Data Model
Data Definition Language
Objects in DDL
Summary
6 Dimensions in the Conceptual Model
Dimension Attributes
Attribute Properties and Values
Relationships Between Attributes
Attribute Member Keys
Attribute Member Names
Relationships Between Attributes
Attribute Discretization
Parent Attributes
Dimension Hierarchies
Types of Hierarchies
Attribute Hierarchies
Parent-Child Hierarchies
Summary
7 Cubes and Multidimensional Analysis
Cube Dimensions
Cube Dimension Attributes
Cube Dimension Hierarchies
Role-Playing Dimensions
The Dimension Cube
Perspectives
Summary
8 Measures and Multidimensional Analysis
Measures in Multidimensional Cubes
SUM
MAX and MIN
COUNT
DISTINCT COUNT
Measure Groups
Measure Group Dimensions
Granularity of a Fact
Indirect Dimensions
Measure Expressions
Linked Measure Groups
Summary
9 Multidimensional Models and Business Intelligence Development Studio
Creating a Data Source
Creating a New Data Source
Modifying an Existing Data Source
Modifying a DDL File
Designing a Data Source View
Creating a New Data Source View
Modifying a Data Source View
Designing a Dimension
Creating a Dimension
Modifying an Existing Dimension
Designing a Cube
Creating a Cube
Modify a Cube
Build a Cube Perspective
Defining Cube Translations
Configuring and Deploying a Project So That You Can Browse the Cube
Configuring a Project
Deploying a Project
Browsing a Cube
Summary
PART III: Using MDX to Analyze Data
10 MDX Concepts
The SELECT Statement
The SELECT Clause
Defining Coordinates in Multidimensional Space
Default Members and the WHERE Clause
Query Execution Context
Set Algebra and Basic Set Operations
Union
Intersect
Except
CrossJoin
Extract
MDX Functions
Functions for Navigating Hierarchies
The Function for Filtering Sets
Functions for Ordering Data
Referencing Objects in MDX and Using Unique Names
By Name
By Qualified Name
By Unique Name
Summary
11 Advanced MDX
Using Member and Cell Properties in MDX Queries
Member Properties
Cell Properties
Dealing with Nulls
Null Members, Null Tuples, and Empty Sets
Nulls and Empty Cells
Type Conversions Between MDX Objects
Strong Relationships
Sets in a WHERE Clause
SubSelect and Subcubes
Summary
12 Cube-Based MDX Calculations
MDX Scripts
Calculated Members
Defining Calculated Members
Assignments
Assignment Operator
Specifying a Calculation Property
Scope Statements
Root and Leaves Functions
Calculated Cells
Named Sets
Order of Execution for Cube Calculations
The Highest Pass Wins
Recursion Resolution
Summary
13 Dimension-Based MDX Calculations
Unary Operators
Custom Member Formulas
Semiadditive Measures
ByAccount Aggregation Function
Order of Execution for Dimension Calculations
The Closest Wins
Summary
14 Extending MDX with Stored Procedures
Creating Stored Procedures
Creating Common Language Runtime Assemblies
Using Application Domains to Send-Box Common Language Runtime Assemblies
Creating COM Assemblies
Calling Stored Procedures from MDX
Security Model
Role-Based Security
Code Access Security
User-Based Security
Server Object Model
Operations on Metadata Objects
Operations on MDX Objects
Using Default Libraries
Summary
15 Key Performance Indicators, Actions, and the DRILLTHROUGH Statement
Key Performance Indicators
Defining KPIs
Discovering and Querying KPIs
Actions
Defining Actions
Discovering Actions
Drillthrough
DRILLTHROUGH Statement
Defining DRILLTHROUGH Columns in a Cube
Summary
16 Writing Data into Analysis Services
Using the UPDATE CUBE Statement to Write Data into Cube Cells
Updatable and Nonupdatable Cells
Lifetime of the Update
Enabling Writeback
Converting a Writeback Partition to a Regular Partition
Other Ways to Perform Writeback
Summary
PART IV: Creating a Data Warehouse
17 Loading Data from a Relational Database
Loading Data
Data Source Objects
Data Source Object Properties
Data Source Security
Connection Timeouts
Summary
18 DSVs and Object Bindings
Data Source View
Named Queries
Named Calculations
Object Bindings
Column Bindings
Table Bindings
Query Bindings
Summary
19 Multidimensional Models and Relational Database Schemas
Relational Schemas for Data Warehouses
Building Relational Schemas from the Multidimensional Model
Using Wizards to Create Relational Schemas
Using Templates to Create Relational Schemas
Summary
PART V: Bringing Data into Analysis Services
20 The Physical Data Model
Internal Components for Storing Data
Data Store Structure
File Store Structure
Bit Store Structure
String Store Structure
Compressed Store Structure
Hash Index of a Store
Data Structure of a Dimension
Data Structures of the Attributes
Attribute Relationships
Data Structures of Hierarchies
Physical Model of the Cube
Defining a Partition Using Data Definition Language
Physical Model of the Partition
Overview of Cube Data Structures
Summary
21 Dimension and Partition Processing
Dimension Processing
Attribute Processing
Hierarchy Processing
Building Decoding Tables
Building Indexes
Schema of Dimension Processing
Dimension Processing Options
Processing ROLAP Dimensions
Processing Parent-Child Dimensions
Cube Processing
Data Processing
Building Aggregations and Indexes
Cube Processing Options
Progress Reporting and Error Configuration
ErrorConfiguration Properties
Processing Error Handling
Summary
22 Using SQL Server Integration Services to Load Data
Using Direct Load ETL
Creating an SSIS Dimension-Loading Package
Creating an SSIS Partition-Loading Package
Summary
23 Aggregation Design and Usage-Based Optimization
Designing Aggregations
Relational Reporting-Style Dimensions
Flexible Versus Rigid Aggregations
Aggregation Objects and Aggregation Design Objects
The Aggregation Design Algorithm
Query Usage Statistics
Setting Up a Query Log
Monitoring Aggregation Usage
Summary
24 Proactive Caching and Real-Time Updates
Data Latency and Proactive Caching
Timings and Proactive Caching
Frequency of Updates
Long-Running MOLAP Cache Processing
Proactive Caching Scenarios
MOLAP Scenario
Scheduled MOLAP Scenario
Automatic MOLAP Scenario
Medium-Latency MOLAP Scenario
Low-Latency MOLAP Scenario
Real-time HOLAP Scenario
Real-time ROLAP Scenario
Change Notifications and Object Processing During Proactive Caching
Scheduling Processing and Updates
Change Notification Types
Incremental Updates Versus Full Updates
General Considerations for Proactive Caching
Monitoring Proactive Caching Activity
Summary
25 Building Scalable Analysis Services Applications
Approaches to Scalability
The Scale-Up Approach
The Scale-Out Approach
OLAP Farm
Data Storage
Network Load Balancing
Linked Dimensions and Measure Groups
Updates to the Source of a Linked Object
Linked Dimensions
Linked Measure Groups
Remote Partitions
Processing Remote Partitions
Using Business Intelligence Development Studio to Create Linked Dimensions
Summary
PART VI: Analysis Server Architecture
26 Server Architecture and Command Execution
Command Execution
Session Management
Server State Management
Executing Commands That Change Analysis Services Objects
Creating Objects
Editing Objects
Deleting Objects
Processing Objects
Commands That Control Transactions
Managing Concurrency
Using a Commit Lock for Transaction Synchronization
Canceling a Command Execution
Batch Command
Summary
27 Memory Management
Economic Memory Management Model
Server Performance and Memory Manager
Memory Holders
Memory Cleanup
Managing Memory of Different Subsystems
Cache System Memory Model
Managing Memory of File Stores
Managing Memory Used by User Sessions
Other Memory Holders
Memory Allocators
Effective Memory Distribution with Memory Governor
Model of Attribute and Partition Processing
Model of Building Aggregations
Model of Building Indexes
Summary
28 Architecture of Query Execution—Calculating MDX Expressions
Query Execution Stages
Parsing an MDX Request
Creation of Calculation Scopes
Global Scope and Global Scope Cache
Session Scope and Session Scope Cache
Global and Session Scope Lifetime
Building a Virtual Set Operation Tree
Optimizing Multidimensional Space by Removing Empty Tuples
Calculating Cell Values
Calculation Execution Plan Construction
Evaluation of Calculation Execution Plan
Execution of the Calculation Execution Plan
Cache Subsystem
Dimension and Measure Group Caches
Formula Caches
Summary
29 Architecture of Query Execution—Retrieving Data from Storage
Query Execution Stages
Querying Different Types of Measure Groups
Querying Regular Measure Groups
Querying ROLAP Partitions
Querying Measure Groups with DISTINCT_COUNT Measures
Querying Remote Partitions and Linked Measure Groups
Querying Measure Groups with Indirect Dimensions
Summary
PART VII: Accessing Data in Analysis Services
30 Client/Server Architecture and Data Access
Using TCP/IP for Data Access
Using Binary XML and Compression for Data Access
Using HTTP for Data Access
Offline Access to Data
Summary
31 Client Components Shipped with Analysis Services
Using XML for Analysis to Build Your Application
Using Analysis Services Libraries to Build Your Application
Query Management for Applications Written in Native Code
Query Management for Applications Written in Managed Code
Using DSO and AMO for Administrative Applications
Summary
32 XML for Analysis
State Management
XML/A Methods
The Discover Method
The Execute Method
Handling Errors and Warnings
Errors That Result in the Failure of the Whole Method
Errors That Occur After Serialization of the Response Has Started
Errors That Occur During Cell Calculation
Warnings
Summary
33 ADOMD.NET
Creating an ADOMD.NET Project
Writing Analytical Applications
ADOMD.NET Connections
Working with Metadata Objects
Operations on Collections
Caching Metadata on the Client
Working with a Collection of Members (MemberCollection)
Working with Metadata That Is Not Presented in the Form of Objects
AdomdCommand
Properties
Methods
Using the CellSet Object to Work with Multidimensional Data
Handling Object Symmetry
Working with Data in Tabular Format
AdomdDataReader
Using Visual Studio User Interface Elements to Work with OLAP Data
Which Should You Use: AdomdDataReader or CellSet?
Using Parameters in MDX Requests
Asynchronous Execution and Cancellation of Commands
Error Handling
AdomdErrorResponseException
AdomdUnknownResponseException
AdomdConnectionException
AdomdCacheExpiredException
Summary
34 Analysis Management Objects
AMO Object Model
Types of AMO Objects
Dependent and Referenced Objects
Creating a Visual Studio Project That Uses AMO
Connecting to the Server
Canceling Long-Running Operations
AMO Object Loading
Working with AMO in Disconnected Mode
Using the Scripter Object
Using Traces
Error Handling
OperationException
ResponseFormatException
ConnectionException
OutOfSyncException
Summary
PART VIII: Security
35 Security Model for Analysis Services
Connection Security
TCP/IP Connection Security
HTTP Security
External Data Access Security
Choosing a Service Logon Account
Changing a Service Logon Account
Security for Running Named Instances (SQL Server Browser)
Security for Running on a Failover Cluster
Summary
36 Object Security Model for Analysis Services
Server Administrator Security
Database Roles and the Hierarchy of Permission Objects
Permission Objects
Managing Database Roles
Summary
37 Securing Dimension Data
Defining Dimension Security
The AllowedSet and DeniedSet Properties
The VisualTotals Property
Defining Dimension Security Using the User Interface
Testing Dimension Security
Dynamic Security
Dimension Security Architecture
Dimension Security, Cell Security, and MDX Scripts
Summary
38 Securing Cell Values
Defining Cell Security
Testing Cell Security
Contingent Cell Security
Dynamic Security
Summary
PART IX: Management
39 Using Trace to Monitor and Audit Analysis Services
Trace Architecture
Types of Trace Objects
Administrative Trace
Session Trace
Flight Recorder Trace
Creating Trace Command Options
SQL Server Profiler
Defining a Trace
Running a Trace
Flight Recorder
How Flight Recorder Works
Configuring Flight Recorder Behavior
Discovering Server State
Tracing Processing Activity
Reporting the Progress of Dimension Processing
Reporting the Progress of Partition Processing
Query Execution Time Events
Running a Simple Query
Changing the Simple Query
Running a More Complex Query
Changing the Complex Query
Changing Your Query Just a Little More
Summary
40 Backup and Restore Operations
Backing Up Data
Planning Your Backup Operation
Benefits of Analysis Server 2005 Backup Functionality
Using the Backup Database Dialog Box to Back Up Your Database
Using a DDL Command to Back Up Your Database
Backing Up Related Files
Backing Up the Configuration File
Backing Up the Query Log Database
Backing Up Writeback Tables
Backup Strategies
Typical Backup Scenario
High Availability System Backup Scenario
Automating Backup Operations
SQL Server Agent
SQL Server Integration Services
AMO Application
Restoring Lost or Damaged Data
Using the Restore Database Dialog Box
Using the DDL Command to Restore Your Database
Using DISCOVER_LOCATIONS to Specify Alternative Locations for Partitions
MDX Extensions for Browsing Your File System
The MDX Extensions
Summary
41 Deployment Strategies
Using the Deployment Wizard
Synchronizing Your Databases
Using the Synchronize Database Wizard
Using a DDL Command to Synchronize Databases
Similarities Between the Synchronization and Restore Commands
Synchronization and Remote Partitions
Synchronization and Failover Clusters
Summary
Index
Anonymous
Posted Thu Jan 25 00:00:00 EST 2007
The book teaches a means of analysing massive data sets, that is different from SQL, but which can be applied to SQL databases. Called MDX, it lets you, the analyst, define a hyperspace of several dimensions, where the number of dimensions can be greater than 3. Along each axis, there is a discrete set of values. Unlike engineering or physics analysis, where the spaces often take on continuum values. The authors show how MDX comes with a rich set of built in functions. But you can also easily write your own, that use these, or start from scratch. The Analysis Service version 2005 encompasses MDX, along with a user interface. The MDX syntax borrows deliberately in part from SQL, since as a practical matter, many of its users will come from a SQL background. But for analysis, it is often superior, offering a more flexible and intuitive syntax geared towards analysis. One potential 'problem', which is not mentioned, is that if you get used to the MDX syntax, going back to writing code for a strict SQL application might now seem so constricting. Of course, this is scarcely MDX's fault. The book's chapters are often quite short ('bite-sized') and are easy to follow.
Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.Anonymous
Posted Thu Aug 11 00:00:00 EDT 2011
No text was provided for this review.
Overview
Microsoft SQL Server Analysis Services provides fast access to data by means of multidimensional data structures and the multidimensional query languag MDX. Analysis Services provides the capability to design, create, and manage multidimensional cubes based on data warehouse tables, and it serves as the foundation for the Microsoft Business Intelligence strategy.
Microsoft SQL Server 2005 Analysis Services gives the reader insight into the way Analysis Services functions. It not...