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.

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.

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.

The catalog object defines the identity of the database and the server to which the user is connected. Its functionality is to allow the user to connect to OLAP services.

The cubedef object defines the individual cube while the CubeDefs object defines all the cubes in the database. Dimension refers to any dimensions of the Cubedef object.

The Hierarchy defines each hierarchy of the dimension. The list of hierarchies can be found in the hierarchies collection.

The level object gives information about the level of the hierarchy. The list of levels is found in the Levels collection.

The member object details the member property while the list of members is found in the Members object.

The PivotTable View or the Dimensional View

The structural view cannot give the developer access to the data. With the PivotTable View the developer can connect to the OLAP data source and get at the data. This is illustrated in Part B of the figure above. The summary of the objects in this model are:

The CellSet allows the developer to express a query and provide a connection to one cube. Its functionality is analogous to the ADO’s connection object. A Cell object refers to a single cell in a cube. This is a multidimensional structure generated by the query. It can be used to review the data returned by the MDX query that generated the CellSet object. The Axis object is a collection of one or more members of a dimension in a cube and represents the axis of the cube. All available Axes for a cube are represented by a CellSet object and are stored inside the object’s collection property. The Position object refers to one position or coordinate along the axis represented by the Axis object. An Axis object can have several Position objects stored in the Positions collection object.

The PivotTable Service which is included as part of the Analysis Services, is also installed in Office 2000. The service functions as client liaison to the Analysis server. It can also be used to create and communicate with local cubes that don’t require Analysis server at all.

The local cubes created with PivotTable Service are different from cubes created using the Analysis services

1. Aggregations cannot be created on the local cube. 
2. Only MOLAP or ROLAP cubes can be created. While a .cub file is created for a MOLAP cube, the dimension members alone are stored for the ROLAP cube and the data remains at its source. 
3. Each cube resides as a separate entity in the local machine and does not have a database structure. 
4. Shared dimensions cannot be used 
5. Cubes cannot have partitions 
6. Virtual cubes cannot be created. 
7. SQL query cannot be used on local cubes only MDX or ADO MD can be used to access local cubes. 
8. User rights cannot be set for local cubes. 
9. Explicit multiple hierarchies can be declared within one dimension in local cubes.

Eight MDX statements are used to create a local cube

• CREATE CUBE defines the structure of the local cube.
• INSERT INTO inserts data into a local cube from a relational data source or from an Analysis Services cube. 
• CREATE MEMBER and DROP SET is used for creating a calculated member or dropping it. 
• CREATE SET and DROP SET is used to create or drop a user defined set. 
• REFRESH CUBE is used to rebuild the cube. 
• CREATE CACHE populates the local cache with a portion of the cube data.

The Web Client

The internet and corporate intranets provide the network for the OLAP applications. The OLAP client connects to the server, database and cube and displays the cube metadata to the user. It uses HTML form components and variables to allow the user specify the parameters of an MDX query. The user does not have to directly create or edit an MDX query.

The client does not directly interface with the OLAP data provider. All interaction is managed through the web server. This architecture does not require an interface with an ADO connection.