PivotTable Service

PivotTable® Service, which is included with Microsoft® SQL Server™ OLAP Services, is an OLE DB provider that supports the optional OLE DB for OLAP extensions introduced in OLE DB 2.0 and ADO 2.0. It is an in-process desktop OLAP server designed to provide online and offline data analysis and online access to OLAP data. PivotTable Service functions as a client of OLAP Services.

PivotTable Service functionality includes data analysis, cube building, and cache management functionality. It provides interfaces to data as well as functionality to manipulate data. PivotTable Service can store data locally on the client computer for offline analysis and offers connectivity to the multidimensional data managed by OLAP Services, other OLE DB-compliant providers, and to non-OLAP relational data sources.

PivotTable Service serves the following purposes:

• Connects to OLAP Services as a client component. Thus, client applications connect to the same component both for OLAP Services and for stand-alone desktop analysis services.
• Functions as a service provider for relational data sources to implement multidimensional functionality and expose the OLE DB interfaces with OLAP extensions.
• Functions as a tabular data provider by supporting a subset of SQL.
• Functions as a multidimensional data provider by supporting multidimensional expressions (MDX).
• Enables client applications to create a local cube directly from a relational data source.
• Functions as a mobile desktop OLAP client that enables users to download from data sources and store the data in a multidimensional structure on a local computer for offline analysis.

PivotTable Service can work with only a single local cube partition. PivotTable Service does not implement an internal aggregation management system for defining or storing aggregations, so its performance is directly related to the amount of data it manages.

PivotTable Service does not offer typical database administrator (DBA) support for activities such as defining users, data access rules, databases, virtual cubes, and global calculated members. However, PivotTable Service offers data definition language (DDL) and other statements that enable you to:

• Define new local cubes and populate them with data.
• Maintain calculated members, user-defined sets, and cache.

PivotTable Service supports MDX and a subset of SQL. For more information, see MDX, SQL in OLAP Services, and the Microsoft OLE DB documentation. PivotTable Service also extends the language defined in the OLE DB documentation by adding statements to create local cubes. For more information, see Data Definition Language.

Intended Audience

This topic and its subtopics are a guide for developers who want to write custom client applications for use with OLAP Services or create local cubes. They provide a description of PivotTable Service architecture and the interfaces that client applications can use. They assume a working knowledge of the following:

• Multidimensional data warehousing and online analytical processing (OLAP) concepts
• Microsoft OLE DB, including the OLE DB for OLAP extensions
• MDX