Building Local Cubes
The process of creating a local cube can be summarized in a few steps.
- Define dimensions.
- Define measures.
- Define calculated members.
- Define other objects such as levels, member properties, and so on.
- Populate the dimensions.
- Populate the measures and calculated members.
- Map the dimensions and measures into the cube structure.
- Process the cube by connecting to the source provider.
To accomplish steps 1 through 6, use strings that consist of data definition language (DDL) statements such as CREATE CUBE. Assign each statement to its corresponding connection string and then assemble all the connection string properties into a single connection string. The local cube is then created during the process of connecting to the data source.
Before creating a local cube, you must decide which storage mode to use. You can create local cubes in multidimensional OLAP (MOLAP) or relational OLAP (ROLAP) storage mode. For more information about OLAP storage modes, see Flexible Data Model.
To create a MOLAP cube, use the CREATE CUBE statement in the connection string to define the cube's dimensions, levels, members, and measures. The INSERT INTO statement is then used to populate the cube with data. The result is a local cube saved on the client computer, which can then be connected to and analyzed offline.
To create a ROLAP cube, use the CREATE CUBE statement in the connection string to define the cube (as described in the previous paragraph). Then use the INSERT INTO statement with the OPTIONS DEFER_DATA clause to populate its dimensions and members. This saves the structural definition of the local cube (that is, the cube and dimension definitions) on the local computer, but does not save the member data. The client application can then connect to the local cube and analyze its data (while connected to its data source) without a connection to an Analysis server.
Local MOLAP cubes generally take longer to create than ROLAP cubes because the cube data must be added to the meta data. These cubes are usually much larger than ROLAP cubes. However, local MOLAP cubes provide better performance during query execution than local ROLAP cubes do.
PivotTable® Service can only be used to define local cubes. You cannot create cubes on an Analysis server using PivotTable Service.
The source data used to create a local cube must be to a tabular data provider, such as a relational database, or from an Analysis server, which can act as a tabular data provider. The name of the local cube file to be created is defined using the Datasource property in the connection string. The file extension of the cube file is .cub. You cannot specify other extensions for files of this type.
Caution If the specified cube file already exists on the local computer, PivotTable Service overwrites this existing file with the new local cube unless you set the UseExistingFile property of the Microsoft® ActiveX® Data Objects (ADO) Connection object to a value that begins with Y (for YES), T (for TRUE), or a nonzero numerical value.
If the name of a cube is different from the name of a cube already in a cube file, this new cube is appended to the old one.