Chapter 20. SQL Server Analysis Services (SSAS)

Chapter 20. SQL Server Analysis Services (SSAS)

SQL Server Analysis Services (SSAS) provides online analytical processing (OLAP) and data mining functionality using a combination of client- and server-side components.

A data warehouse is a data repository used to overcome issues arising from performing strategic analysis on data in an online transaction processing (OLTP) database. An OLTP database supports the day-to-day business activity of the organization and is configured to let applications write data for a single transaction as quickly as possible. A data warehouse provides users easy access to information used to make strategic business decisions.

Dimension tables store information used to categorize and hierarchically organize the information stored in fact tables. The columns of a dimension table are called attributes . Attributes are used to hierarchically organize the rows of dimension tables in a way that is meaningful for business users.

OLAP is a combination of products and processes used to aggregate large amounts of heterogeneous data and interactively examine the results in a dimensional model. OLAP evolved from the need to interactively examine large volumes of data warehouse information.

Like a data warehouse, OLAP uses dimensional modeling to represent data. Unlike a data warehouse, which typically uses a relational database to store and access data, OLAP uses cubesmultidimensional data structures organized hierarchically along a business attribute for each dimension of the cube, with each cell containing one or more measures.

This chapter provides an overview of SSAS, the languages used with SSAS, programmatically querying data and metadata, and programmatically administering an SSAS instance and its objects. Because SSAS is a very large topic, the goal of this chapter is simply to provide an introduction to key elements and concepts. See Microsoft SQL Server 2005 Books Online for in-depth information about SSAS.

20.1. Before You Begin

The examples in this chapter use the sample Adventure Works DW (Standard Edition or Enterprise Edition) SSAS database. If this is not installed, follow the instructions in the topic "Running Setup to Install AdventureWorks Sample Databases and Samples" in Microsoft SQL Server 2005 Books Online.

You have to process the database prior to using it. Right-click the database in Object Explorer and select Process from the context menu.

View the SSAS databases in Object Explorer to ensure that the Adventure Works DW database is available by following these steps:

  1. Open SQL Server Management Studio.

  2. Select View Registered Server Types Analysis Services.

  3. Object Explorer from the context menu.

20.2. SSAS Overview

A data warehouse combines data from multiple sources into a single homogenous repository that is organized for efficient analytical query processing rather than transaction processing. Data warehouses use dimensional modeling to represent business information. Data is stored in two types of tablesfact tables and dimension tables .

Fact tables contain numeric performance information from transactional data. The columns of a fact table are one of two typesmeasures or attributes. A measure is quantitative business data and is usually numeric. An attribute is used to associate the measures with a row in the dimension table.

Multidimensional data is represented by structures called cubes, each representing a set of data called a measure, hierarchically organized by one or more dimensions. Dimensions organize data within a cube by using hierarchies and attributes instead of tables. Cubes are typically built from data in relational data sources. A member is an item in a dimension that represents one or more data instances, similar to records in a relational database.

A hierarchy organizes the members of a dimension into one or more levels, and lets you navigate and aggregate data within the cube. Each attribute in a hierarchy definition corresponds to a level in the hierarchy from the most summarized down to the most detailed. For example, you can have a Geography dimension that organizes the hierarchy into levels based on Country, State, and City attributes. The member in the lowest level is called a leaf member, and other members are called nonleaf members.

Multiple data values called measures exist at each intersection of the dimensions. A measure is a special dimension that represents the data organized according to the other dimensions in the cube. Every cube must have a measure dimension. Calculated measures can be created that derive from existing measures.