PivotTable Service Overview

The Microsoft^® PivotTable^® Service is an OLE DB for OLAP provider. It is an in-process desktop OLAP server designed to provide online and offline data analysis functionality, and online access to OLAP data as a client of the Microsoft Decision Support Services OLAP Server.

Microsoft PivotTable Service contains data analysis, cube building, and smart cache management functionality. It provides interfaces to data as well as functionality to manipulate data. Microsoft PivotTable Service stores data locally on the client for offline analysis, and also offers connectivity to the multidimensional data managed by the Microsoft OLAP Server as well as to non-OLAP relational data sources.

Microsoft PivotTable Service exposes SQL and multidimensional expressions (MDX) defined by Microsoft OLE DB and Microsoft OLE DB for OLAP as its native consumer interfaces. Microsoft PivotTable Service also extends the language defined in the OLE DB for OLAP specifications by adding data definition language (DDL) statements to define and manipulate the structure of multidimensional data cubes.

This document describes the way client applications use the interfaces provided by Microsoft PivotTable Service by discussing the methods to be invoked and statements to be executed in typical usage scenarios. The purpose of this document is to allow Microsoft PivotTable Service consumers to gain understanding of the general architecture and as an implementation guide for developers.

The following diagram describes the Microsoft PivotTable Service architecture:

Microsoft PivotTable Service serves several important goals:

• Enables client applications to create a local multidimensional cube directly from a relational data source.
  
  
• Connects to the Microsoft OLAP Server as a client component. This means that client applications connect to the same component both for Microsoft OLAP Server services and for stand-alone desktop analysis services.
  
  
• Functions as a service provider for relational data sources that implements multidimensional functionality and exposes the OLE DB for OLAP interfaces with MDX extensions.
  
  
• Functions as a desktop mobile OLAP client that enables users to download multidimensional data from data sources and store the data on the local machine for offline analysis.

Microsoft PivotTable Service is a local, in-process multidimensional data server. The functionality available to the data consumer client from the PivotTable Service is a subset of that provided by the Microsoft OLAP Server:

• Scalability & Performance: Microsoft PivotTable Service is single-threaded and can work with only a single data cube partition. The Microsoft PivotTable Service does not implement an internal aggregation management system for defining or storing aggregations, so its performance has a direct relationship to the amount of data it manages.
  
  
• Backend functionality: Microsoft PivotTable Service does not offer typical DBA support for activities such as defining users, data access rules, databases, virtual cubes, and global calculated members. However, Microsoft PivotTable Service offers DDL substitutes that enable operations for defining new data cubes and the population of these cubes with data.