An Introduction to OLAP in SQL Server 2005

f you're a database developer, you've no doubt heard of On-Line Analytical Processing (OLAP) and the advantages of analysis using multi-dimensional, pre-aggregated data. Maybe you've even thought about creating your own multidimensional cubes to give your end users true ad hoc capabilities, including the creation of calculated measures/KPIs. If you've relegated that task to the back burner because it was too complex, you'll be happy to know that SQL 2005 has made the process easier.

This article discusses the major OLAP components of Analysis Services, all of which can be implemented by even a first-time cube builder. A follow up article by Mark Frawley will examine the differences between Analysis Services in SQL 2000 and SQL 2005.

Why Use OLAP?
OLAP is useful because it provides fast and interactive access to aggregated data and the ability to drill down to detail. OLAP lets users view and interrogate large volumes of data (often millions of rows) by pre-aggregating the information. It puts the data needed to make strategic decisions directly into the hands of the decision makers, not only through pre-defined queries and reports, but also because it gives end users the ability to perform their own ad hoc queries, minimizing users' dependence on database developers.

What's the Secret?
OLAP leverages existing data from a relational schema or data warehouse (data source) by placing key performance indicators (measures) into context (dimensions). Once processed into a multidimensional database (cube), all of the measures are pre-aggregated, which makes data retrieval significantly faster. The processed cube can then be made available to business users who can browse the data using a variety of tools, making ad hoc analysis an interactive and analytical process rather than a development effort. SQL Server 2005's BI Workbench substantially improves upon SQL Server 2000's BI capability.

Figure 1. Analysis Services Architecture: The figure shows the relationship of the various technology tiers involved in Analysis Services.

SQL Server 2005 BI Workbench Platform
The SQL Server 2005 BI Workbench suite consists of five basic tools:

• SQL Server Relational Database—used to create relational database
• Analysis Services—used to create multidimensional model (measures, dimensions and schema)
• Data Transformation Services (DTS)—used to extract, transform and load data from source(s) to the data ware house or schema
• Reporting Services—used to build and manage enterprise reporting using the relational or multidimensional sources
• Data Mining—used to extract information based on predetermined algorithms.

This remainder of this article focuses on multidimensional modeling using Analysis Services and briefly touches upon DTS's role. Figure 1 shows the Analysis Services architecture.