Data warehousing and PerformancePoint Server

Updated: 2008-09-29

Business intelligence, as defined in What is business intelligence?, provides a broader view that covers the data warehouse, tools, and applications that show data from various sources like the data warehouse. This article and the following diagram introduce Microsoft Office PerformancePoint Server 2007 as it relates to the data warehouse.

Tools for business intelligence

The diagram was inspired by the book Microsoft® SQL Server™ 2005 Analysis Services Step by Step, ISBN: 9780735621992. Microsoft Press. All rights reserved.

How it all fits together

What is a data warehouse? A data warehouse is a repository for storing and analyzing numerical information. Core data in the data warehouse are typically numeric values that can be summarized or aggregated. One reason a database structure differs from a transactional database structure is that pulling data can otherwise prove to be very resource expensive. Data warehouses allow you to store aggregated data rather than performing ad-hoc queries to sum. This simplified definition is explained further in many books written specifically for data warehouse professionals.

What is OLAP and how does it relate to a data warehouse? Online Analytical Processing, or OLAP, usually describes specialized tools that make warehouse data easily accessible. An OLAP cube is a logical structure that defines the metadata. It is a borrowed term to describe existing measure groups and dimension tables. A cube is a combination of all existing measure groups. A measure group is another logical structure that defines metadata so that client tools can access the data. It is the group of measures that share the same grain. Each measure group contains the detail values stored in the fact table (copied or dynamically retrieved values). OLAP cubes contain lots of metadata; metadata in its simplest definition is data about data. Multidimensional expressions or MDX is a metadata-based query language that helps you query OLAP cubes.

What is SQL Server Analysis Services (SSAS) and how does it relate to OLAP? SQL Server Analysis Services, once called OLAP Services, provides server technologies that help speed up query and reporting processing. Analysis Services implements OLAP with technologies that simplify and quicken the process of designing, creating, maintaining, and querying aggregate tables while avoiding data explosion issues.

How does PerformancePoint Server relate to data warehouses, OLAP, or SSAS? SQL Server Analysis Services data supplies the client tools in PerformancePoint Server with data that feeds into the larger business intelligence (BI) suite provided by Microsoft.