Populating Cubes

The INSERT INTO statement is used to populate data cubes with dimension structure and data. The syntax of the INSERT INTO statement is described in Appendix A. If the cube file specified in the DBPROP_INIT_DATASOURCE initialization property does not exist, it will be created during processing of the INSERT INTO statement.

The INSERT INTO statement may perform the following operations on a cube:

• Bind data cube elements to source relational view columns.
  
  
• Populate the structures of the data cube dimensions.
  
  
• Populate the data.

Binding

Binding specifies the column of the relational table that will be used to populate an element (level, property, or measure) in the cube. Binding is specified in the INSERT INTO statement.

Populating the Dimensions Structure

The dimensions structure of a data cube is populated during the execution of the INSERT INTO command by a series of SELECT statements executed on the data source to generate a dimension tree. After all of these SELECT queries have been executed, the dimensions of the data cube are fully populated and become static.

Populating the Data

Data may be read from the server in one of two modes:

• Immediate population of cube data (default mode): The entire table is read from the data source to the client machine. If a local cube file is specified, the data is persisted in the local cube file, and the data source is then disconnected.
  
  
• Deferred data population: In client-server situations when DEFER DATA is specified, data is sent to the client only when necessary to fulfill a request of the client application. No data is persisted on the client, although the data cube definition and the dimension members may be persisted in a local file. The DEFER DATA clause is used in the INSERT INTO statement to defer data population. See INSERT INTO in Appendix A for additional details, including the related ATTEMPT_DEFER_DATA option.

Aggregations

When Microsoft PivotTable Service is connected as a client to a Microsoft OLAP Server, aggregations that were created for the cube on the server will be automatically created in the cached cube on the client. This improves the performance of the local cube.