Chapter 15 - Introducing OLAP Services

Microsoft SQL Server OLAP Services is a new middle-tier server for online analytical processing (OLAP). The OLAP Services system includes a powerful server that constructs multidimensional cubes of data for analysis and provides rapid client access to cube information. PivotTable Service, the included OLE DB compliant provider, is used by Microsoft Excel and applications from other vendors to retrieve multidimensional data from the server and present it to the user.

OLAP Services organizes data from a data warehouse into multidimensional cubes with precalculated summary information to provide rapid answers to complex analytical queries.

Among the key features of OLAP Services are:

• Ease of use provided by the user interface and wizards. 

• A flexible, robust data model for cube definition and storage. 

• Write-enabled cubes for "what if" scenario analyses. 

• Scalable architecture that provides a variety of storage scenarios and an automated solution to the "data explosion syndrome" that plagues traditional OLAP technologies. 

• Integration of administration tools, security, data sources, and client/server caching. 

• Widely supported APIs and open architecture to support custom applications. 

For more information, see "Features of OLAP Services" in this volume.

Features of OLAP Services

Many features are incorporated into Microsoft SQL Server OLAP Services to address the complexity of online analytical processing (OLAP) technology. Specific features are grouped in these topics:

• Ease of Use 

• Flexible Data Model 

• Scalability 

• Integration 

• Widely Supported APIs and Open Architecture 

Ease of Use

OLAP Services provides wizards, editors, and information to make OLAP technology easier to use.

Tutorial and Overview Material

You can use the online tutorial to master the OLAP Manager in about an hour. Designed for both beginners and experienced online analytical processing (OLAP) users, the tutorial walks you through the steps to create a basic cube as well as more advanced operations such as creating partitions and virtual cubes. The tutorial is an excellent tool to learn about OLAP and the operation and features of the OLAP Manager.

You can also find information about OLAP and Microsoft SQL Server OLAP Services version 7.0 in the right pane of the OLAP Manager.

Metadata and Data View

You can view object properties and metadata, and browse cube data in the right pane of the OLAP Manager as you traverse the tree view in the left pane.

Cube Wizard

You can build all the necessary structures to create an OLAP cube with an easy-to-use wizard. The wizard walks you through the entire cube design and implementation process, from mapping data sources and creating dimensions to defining measures.

Cube Editor

You can edit existing cube structures and create new ones using simple drag-and-drop operations. The Cube editor complements the Cube wizard, enabling you to revise cubes you created with the wizard or to quickly create new ones. The editor integrates various views of the cube:

• The tree view displays a hierarchical view of the cube's dimensions, dimension levels, measures, and calculated members. 

• The Schema pane displays the data warehouse fact tables, dimension tables, and the relationships between them that define the cube's schema. You can modify the cube's schema in this pane. 

• The Browse pane displays the cube's data. You can switch to this view to check your work in progress while you make changes to the cube's structure. Sample data is generated if the cube's new structure has not yet been processed; actual data appears if the cube's structure has not changed since the cube was last processed. 

• The Properties pane provides access to properties of objects such as measures, calculated members, and elements of private dimensions. 

Dimension Wizard

You can create a shared dimension for use by any cube or a private dimension for use in a single cube quickly and easily with the Dimension wizard. You can map database dimension table columns to dimension levels or use the built-in time dimension generator to create a variety of time dimensions based on a date-time column in the database. You can use the Dimension wizard with flat, star, or snowflake data warehouse schemas.

Dimension Editor

You can edit existing shared dimension structures and create new ones using simple drag-and-drop operations. The Dimension editor complements the Dimension wizard, enabling you to revise dimensions you created with the wizard or to quickly create new ones. You can also preview dimension data in the editor.

Incremental Update Wizard

You can use this wizard to guide you through the process of incorporating new data into your cube. An incremental update adds new data to a cube without the necessity of rebuilding aggregations and reloading all data.

Partition Wizard

This wizard helps you create a new partition to contain a portion of the data in your cube. Partitions enable you to distribute and optimize a cube's data into discrete segments on a single server or across multiple servers.

Note User-defined partitions are available only if you install Microsoft SQL Server OLAP Services, Enterprise Edition.

Storage Design Wizard

You can use this wizard to specify the mode of storage for cube data and to help you design aggregations appropriate to the intended use of your cube. The options available to you in this wizard enable you to optimize the tradeoff between response time and storage requirements according to the needs of your application and users.

Usage Analysis Wizard

The Usage Analysis wizard helps you understand how a cube is being used by displaying logged query information such as date, user, query response time, and frequency in tabular and graphical format.

Usage-Based Optimization Wizard

You can use the Usage-Based Optimization wizard to help you tune cube performance based on actual usage of the cube by users. You can direct the wizard to create aggregations to improve performance based on any combination of users, the number of times a query was executed, query response time, the mode of storage where the data resides, or a date range.

Virtual Cube Wizard

You can use this wizard to join cubes and select dimensions and measures from them to create a virtual cube. A virtual cube enables a single query to be routed to multiple cubes, including cubes running on different servers. A virtual cube appears to users as if it were a real cube, but does not require additional storage space; it is similar to a view in a relational database that joins tables.

Virtual Dimension Wizard

You can create shared virtual dimensions quickly and easily with the Virtual Dimension wizard. You can select member properties defined for shared dimensions to create a virtual dimension, and then use the virtual dimension in any cube that also uses the dimension from which the member properties were selected.

Browse Views of Data

You can view cube data without leaving the OLAP Manager. You do not need to switch to another application to check your cube designs.

OLE DB Data Source Locator Integration

OLAP Services uses the Microsoft Data Source Locator component for selecting OLE DB or ODBC data sources.

Flexible Data Model

OLAP Services supports various data and storage models to help you create and maintain an OLAP system that meets your organization's needs.

Multiple Data Storage Options

Microsoft SQL Server OLAP Services offers three storage modes for your system:

• MOLAP (multidimensional OLAP) 

  The underlying data for a cube is stored along with aggregation data in a high-performance multidimensional structure. MOLAP storage provides excellent performance and data compression. 

• ROLAP (relational OLAP) 

  The underlying data for a cube is stored along with the aggregation data in a relational database. ROLAP storage enables you to take advantage of your investment in relational technology and enterprise data management tools. 

• HOLAP (hybrid OLAP) 

  The underlying data for a cube is stored in a relational database and the aggregation data is stored in a high-performance multidimensional structure. HOLAP storage offers the benefits of MOLAP for aggregations without necessitating duplication of the underlying detail data. 

  Virtual cubes and partitions are other forms of hybrid OLAP that enable you to tailor cube storage alternatives to meet your needs.

Partitioned Cube Storage

You can partition a cube into separate physical sections. You can store each partition in a different mode, in a different physical location, and with a level of aggregations appropriate to the data in the partition. The result is that you can fine tune the performance and data management characteristics of your system.

Note User-defined partitions are available only if you install Microsoft SQL Server OLAP Services, Enterprise Edition.

Partition Merging

You can combine a cube's multiple partitions back into a single physical partition. For example, you can use partition merging to consolidate portions of cube data such as data for a just completed quarter into a single partition for the year.

Note User-defined partitions are available only if you install Microsoft SQL Server OLAP Services, Enterprise Edition.

Writable Cubes

You can enable a cube for write access by multiple simultaneous users. User-initiated changes to the cube data are logged to a special, physically separated partition table associated with the cube and applied automatically as cube data is viewed. To the user it appears as if the data in the cube has changed. The changes can be discarded or made read-only at the discretion of the database administrator (DBA).

Virtual Cubes

You can join cubes into virtual cubes, much like tables can be joined with views in a relational database. A virtual cube provides access to data in the combined cubes without necessitating the construction of a new cube, while it allows you to maintain the best design for each individual cube.

Calculated Members

You can create calculated measures and calculated dimension members by combining multidimensional expressions (MDX), mathematical formulas, and user-defined functions (UDFs). This facility enables you to define new measures and dimension members based upon a rich yet easy-to-use expression syntax. You can register additional libraries of UDFs to use in calculated member definitions.

Member Properties

You can define properties for dimension members and use data for these properties within a cube. For example, if the members of a Product dimension are SKUs, there are likely to be several properties associated with SKUs such as size, color, fabric, and so forth. You can specify such properties as member properties and use them in analytical queries.

Virtual Dimensions

Virtual dimensions can be created from member properties associated with shared dimensions. A virtual dimension can be used to evaluate the properties of a dimension's members against the members themselves. For example, measures can be evaluated for SKUs against size, color, fabric, and so forth. Virtual dimensions and member properties are evaluated as necessary for queries and require no physical cube storage.

Scalability

OLAP Services provides a scalable OLAP architecture to address a variety of data warehousing scenarios.

Customized Aggregation Options

Using the Storage Design wizard, you can optimize the trade-off between system performance and disk space allocated to storing aggregations. Microsoft SQL Server OLAP Services uses a sophisticated algorithm to determine the optimum set of aggregations from which other aggregations can be derived. As a result, you can focus on application design issues and leave the complex management of aggregation design up to the system.

Usage-Based Optimization

You can tune the performance of a cube to provide quick response to the queries most often executed by directing the Usage-Based Optimization wizard to design aggregations appropriate to those queries while maintaining reasonable storage requirements. Thus, you can quickly build a system with a minimum number of aggregations, and then later optimize performance according to the actual usage of the system.

Data Compression and Storage Optimization

In MOLAP and HOLAP storage modes, OLAP Services stores all or some of the cube information in multidimensional structures. In these structures, storage is not used for empty cells, and a sophisticated data compression algorithm is applied to data that is stored. When combined with the flexible options for the design and optimization of precalculated aggregations, these techniques help to minimize the impact of the "data explosion syndrome" inherent in OLAP technology.

Distributed Calculation

PivotTable Service incorporates functionality from the server so that calculations can often be performed on the client instead of the server. This spreads the computational load between the server and the client, thus increasing the capacity of the server, reducing network traffic, and improving performance for the clients.

Partitions

You can spread a cube over multiple servers by dividing it into partitions. OLAP Services can then retrieve data in parallel to answer queries. Partitioning enables you to manage your storage strategy, increase scale with multiple servers, and increase performance.

Note User-defined partitions are available only if you install Microsoft SQL Server OLAP Services, Enterprise Edition.

Incremental Updates

A cube can be updated by processing only data that has been added rather than the entire cube; you can use incremental update to update OLAP cubes while they are in use.

LAN, WAN, Internet, and Mobile Scenarios

Intelligent cache management integrates the OLAP server with the PivotTable Service client, minimizing traffic over LAN and WAN connections. PivotTable Service contains an efficient multidimensional calculation engine to further minimize network traffic and to enable analysis of local multidimensional data when the client is not connected to the server.

Microsoft ActiveX controls, Active Server Page (ASP) scripting, and ActiveX Data Objects (ADO) APIs provide a variety of solutions for querying OLAP data over the Web.

Support for Windows NT on the DEC Alpha Platform

In addition to the Intel platform, all of the client and server technologies of OLAP Services are supported on Microsoft Windows NT for the DEC Alpha platform.

Client Support for Windows 95 and Windows 98

PivotTable Service runs on Microsoft Windows 95 and Windows 98, supporting client applications available for these platforms as well as for Windows NT.

Integration

OLAP Services works with other components and programs to ensure enterprise-level robustness.

Integrated Management Console

Microsoft SQL Server OLAP Services includes a graphical administration tool called the OLAP Manager. This is a snap-in to Microsoft Management Console (MMC). As such, it provides a common framework and user interface for defining, accessing, and managing OLAP servers and databases. You can use multiple snap-in components in MMC; for example, you can install SQL Server Enterprise Manager and other snap-in components along with the OLAP Manager.

Integrated Security

Cube access is based on Microsoft Windows NT security, providing integration with SQL Server via Windows NT user and group definitions.

OLE DB Data Sources

A variety of OLE DB and ODBC data sources can be used for data warehouses, such as Oracle versions 7.3 and 8.0. You can use multiple sources at the same time.

Server-Side Cache

User queries, metadata, and data are stored in the OLAP server's cache. Cached query definitions and metadata make it possible to answer new queries by calculating answers from cached data rather than retrieving data from the disk.

Client-Side Cache

Client applications connect to the OLAP server through the client-based PivotTable Service component. Because PivotTable Service receives metadata with data from the server in response to a query, it can often use data in the client cache to calculate the answer to subsequent queries without sending a new query to the server. For example, if the client cache contains values for the four quarters of a specific year and the user asks for the total for the same year, PivotTable Service calculates the answer from the cached data.

PivotTable Service shares much of the same functionality as the server, enabling it to bring the server's multidimensional calculation engine, caching features, and query management directly to the client computer. This client/server data management model optimizes performance and minimizes network traffic.

Widely Supported APIs and Open Architecture

OLAP Services provides various tools you can use to programmatically extend its functionality.

OLE DB

Microsoft SQL Server OLAP Services is designed to meet the requirements of the OLE DB 2.0 and later specification, which includes OLAP-specific provisions. This specification was developed with the input of over 40 OLAP client and server vendors and is a widely supported standard for multidimensional data access.

User-Defined Functions

You can extend the comprehensive list of built-in functions by creating libraries of functions using Component Object Model (COM) automation languages such as Microsoft Visual Basic or Microsoft Visual C++. You can register these libraries and use your functions in calculated member definitions. This architecture enables you to add custom analysis tools to your OLAP applications.

Decision Support Objects

You can use the server object model, Decision Support Objects (DSO), to create applications that define and manage cubes and other objects. This object model can be used to extend the functionality of the OLAP Manager or to automate the ongoing maintenance of your system.

Add-In Support

You can use the OLAP Add-in Manager interface to create applications that extend the functionality of the OLAP Manager user interface. Using the AddIn interface and DSO, you can create custom extensions, dialog boxes, wizards, and other applications that integrate with the OLAP Manager.

System Overview

Microsoft SQL Server OLAP Services provides an online analytical processing (OLAP) architecture for rapid access to data warehouse data. Data from the data warehouse is extracted, summarized, organized, and stored in multidimensional structures for rapid response to user queries.

OLAP Services and PivotTable Service provide the capability to design, create, and manage cubes from data warehouses and to provide client access to OLAP data. The OLAP server manages the data; PivotTable Service works with the server to provide client access to the data.

OLAP Cube Architecture

The primary OLAP object is the cube, a multidimensional representation of detail and summary data. A cube consists of a data source, dimensions, measures, and partitions. You design cubes based on the analytical requirements of users. A data warehouse can support many different cubes such as a Sales cube, an Inventory cube, and so on.

A cube's data source identifies and connects to the database containing the data warehouse data that is the source of data for the cube.

Dimensions map data warehouse dimension table information into a hierarchy of levels, such as a Geography dimension with levels of Continent, Country, State-Province, and City. Dimensions can be independently created and shared among cubes for ease of cube construction and to ensure consistency of analysis data summarization. For example, if a shared dimension is used for a product hierarchy in all appropriate cubes, the organization of summarized product information will be consistent among the cubes that use the dimension.

A virtual dimension is a special type of dimension that maps the properties of members of another dimension into a dimension that can then be used in cubes. For example, a virtual dimension of a product's size property enables a cube to summarize data such as sale quantity by product by size, such as the quantity of shirts sold by style by size. Virtual dimensions and member properties are evaluated as necessary for queries and they require no physical cube storage.

Measures identify the numerical values from the fact table that are summarized for analysis such as price, cost, or quantity sold.

Partitions are the multidimensional storage containers that hold cube data. Each cube contains at least one partition, and a cube's data can be combined from multiple partitions. Each partition can take its data from a different data source and can be stored in a separate location. A partition's data can be updated independently of other partitions in a cube. For example, a cube's data can be divided by time, with a partition for current year's data, another partition for the previous year's data, and a third partition for all data prior to the previous year.

A cube's partitions can be independently stored in different storage modes with different degrees of summarization. Partitions are invisible to the user, to whom the cube appears to be a single object, yet they provide the administrator with a wide variety of options to manage the underlying OLAP data.

Note User-defined partitions are available only if you install Microsoft SQL Server OLAP Services, Enterprise Edition.

Roles enable the management of user access to cube data by mapping Microsoft Windows NT user group and user accounts to cube access privileges.

A virtual cube is a logical view of portions of one or more cubes. A virtual cube can be used to join relatively unlike cubes that share a common dimension, such as a Sales cube and a Warehouse cube, for special analysis purposes while retaining the separate cubes for simplicity. Dimensions and measures can be selected from the joined cubes to be presented in the virtual cube.

Server Architecture

Microsoft SQL Server OLAP Services provides server capabilities to create and manage multidimensional OLAP data and to provide data to clients via PivotTable Service. Server operations include creating multidimensional data cubes from relational data warehouse databases and storing cubes in multidimensional cube structures, in relational databases, and in combinations of both. Metadata for multidimensional cube structures is stored in a repository in a relational database.

A user interface is provided by the OLAP Manager add-in that runs under a snap-in to Microsoft Management Console (MMC). Programming interfaces are provided to enable custom applications to interact with the object model that controls the server, as well as with the OLAP Manager.

 

For more information about creating custom programs, see "Building OLAP Services Applications" in Microsoft SQL Server Building Applications.

Client Architecture

PivotTable Service communicates with the OLAP server and provides interfaces for use by client applications accessing OLAP data on the server. Client applications connect to PivotTable Service using OLE DB interfaces for C++ or the Microsoft ActiveX Data Objects (ADO) object model for Component Object Model (COM) automation languages such as Microsoft Visual Basic.

PivotTable Service can also create local cube files that contain data from a cube on the server or from OLE DB relational databases. Local cubes can be stored as multidimensional cube files on the client computer. Local cubes can be used offline with PivotTable Service for portable analysis.

For more information about programming the client, see "PivotTable Service" in Microsoft SQL Server Building Applications.