The PivotTable Service:The interfaces used by client applications to access OLAP data and data mining data on the server are provided by the PivotTable Service. The PivotTable service is a set of tools that enable the transfer of OLAP cubes to client applications from the OLAP server. Two programming interfaces for querying data are available to the developer from the PivotTable Services:--The OLE DB for OLAP and the ActiveX Data objects Multidimensional (ADO MD). Tools such as those available in VisualStudio.Net are harnessed to create applications that query multi dimensional data sources. The figure below illustrates the relationship scenario.
Using the PivotTable Service
The PivotTable Service architecture is designed to reduce the load on the OLAP server. It provides flexibility for the developer to query the OLAP data source without directly accessing the database every time.
The PivotTable Service has the capability of caching slices of OLAP cubes or whole cubes in the primary storage(RAM or memory) on the Client machine. The PivotTable service can also save the entire OLAP cubes on a secondary storage device on a client machine. The cube is saved as a cube(*.cub) file. In this case data will be available even when the system is disconnected from the network. This is extremely useful in a mobile computing environment. The cube saved on a client machine is known as Offline cube. The service also works on Microsoft Internet Information Server for importing data from an Internet source.
The disadvantage of the PivotTable service is that it is very expensive. It needs constant connection to the network server that provides the OLAP data. The Service also needs to be installed on every client machine wishful of using the service. It needs a minimum of 500KB memory and 5 MB disk space on each machine. Additionally each client machine must have Microsoft Data Access Components (MDAC) 2.6 or later. However, the growing hard disk space availability and memory availability will soon make this requirement seem minimal.
Using OLE DB for OLAP
This OLE DB for OLAP Provider interface helps the user harness the full power of the OLE DB standard API. This is part of the PivotTable service. This interface can be used for accessing multidimensional data. The OLE DB for OLAP provides the developer with an extended OLE DB API for accessing OLAP data sources, data mining and other related tasks. It enables the developer create multidimensional queries that return cubic data
In a COM environment, the core OLE DB standards enable the client application connect to an OLAP provider. The three main interfaces of the OLE DB for OLAP are: “IMDDataset, IMDRangeRowset and IMDFind. The design of the application is engineered to present an elegant structure that can manipulate multidimensional data more efficiently.
Using ActiveX Data Objects Multidimensional
Multidimensional Expressions(MDX) is the standard query language for querying multidimensional data. It is not unlike the T-SQL but is used for querying from multidimensional cubes and not from relational databases. The syntax can be used to query or alter OLAP structures.
The ActiveX Data Objects Multi-dimensional(ADO MD) is an extended ADO API on top of OLE DB for OLAP. The ADO MD object provides two views of a multi dimensional database: The structural view and the Dimensional View.
Comments
Tutorials 
