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.
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
- Aggregations cannot be created on the local cube.
- 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.
- Each cube resides as a separate entity in the local machine and does not have a database structure.
- Shared dimensions cannot be used
- Cubes cannot have partitions
- Virtual cubes cannot be created.
- SQL query cannot be used on local cubes only MDX or ADO MD can be used to access local cubes.
- User rights cannot be set for local cubes.
- 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.