Adding Data to a Cube with Cube Update

After you have built a cube with either a detail table or a star schema, you can update the cube with the Incremental Update function in SAS OLAP Cube Studio. You can choose to update the original cube or generate an updated generation (copy) of the cube. You can then coalesce (consolidate) the aggregation racks (SPD Engine tables) into a single SPD Engine table.

The Update In-Place function enables you to update the original cube. In SAS OLAP Cube Studio, select a cube from the tree view that you want to update. You can then right-click the cube and select Incremental UpdateUpdate In-Place. You can also select the function from the Actions menu.

On the Update General page of the Incremental Update wizard, select the source table that you will use to update the cube with. If you are updating a star schema cube, the check box Add data during update is available. If selected, it activates the New source table box. Select a new source table. You can also click Select and choose a table that is not in the drop-down list.

If a drill-through table is included in the cube update, click Select beside the New drill-through table text field and choose a drill-through table. If the cube was originally built without a drill-through table, the option (none) is listed in the Current drill-through table text field.

If you are updating a star schema cube, you can also specify how missing dimension keys are handled if encountered. Select one of the three radio button options under When searching through new members in a table and a missing key is encountered.

You can also click Advanced and specify aggregation performance options, any needed SAS code, and a search path for user-written formats to include with the cube update. Select Next when you are finished.

On the Dimension Selection page, you can click a dimension in the Select a dimension list. You can then select one of the radio buttons under Select an update process method. When the cube was originally built, you could also specify whether a dimension could be updated at a later time or not. Any dimensions that cannot be updated are listed in the Blocked from new member updates list and are not available for selection.

If you are updating a star schema cube, you can select a dimension table that can be used to update an individual dimension. Each dimension has a default table assigned from the original cube build. Using the left and right arrow keys, you can move tables between the Available tables and the Selected table lists. You can also view statistics about the table that you are selecting with the Properties, View Data, and Table Options buttons. If the table that you want to use is not included in the Available Tables list, you can register the table with the Register Table button. Select Next when you are finished.

On the Summary of Selections page, review the generated PROC OLAP code that is used to update the cube. You can save the PROC OLAP code to a file by selecting Export Code. Select the Finish to run the update of the cube.

The Generate New Cube function enables you to create an updated copy of the original cube without modifying the original cube. It is useful for cubes that are in a production environment, as it enables you check the cube generation before users submit queries. In SAS OLAP Cube Studio, select a cube from the tree view that you want to update. You can then right-click the cube and select Incremental UpdateGenerate New Cube. You can also select the function from the Actions menu. This opens the Update General page of the Incremental Update wizard.

Because you are creating a generation of the original cube, you must enter a cube name that is different from the original cube in the Cube Name text box. You can also change the OLAP schema and the folder location for the new cube.

You can then select the source table that you will use to update the cube with. If you are updating a star schema cube, the check box Add data during update is available. If selected, it activates the New source table box. Select a new source table. You can also click Select and choose a table that is not in the drop-down list.

If a drill-through table is included in the cube update, click Select beside the New drill-through table text field and choose a drill-through table. If the cube was originally built without a drill-through table, the option (none) is listed in the Current drill-through table text field.

If you are updating a star schema cube, you can also specify how missing dimension keys are handled if encountered. Select one of the three radio button options under When searching through new members in a table and a missing key is encountered.

You can also click Advanced and specify aggregation performance options, any needed SAS code, and a search path for user-written formats to include with the cube update. Select Next when you are finished.

On the Dimension Selection page, you can click a dimension in the Select a dimension list. You can then select one of the radio button options under Select an update process method. When the cube was originally built, you could also specify whether a dimension could be updated at a later time or not. Any dimensions that cannot be updated are listed in the Blocked from new member updates list and are not available for selection.

If you are updating a star schema cube, you can select a dimension table that can be used to update an individual dimension. Each dimension has a default table assigned from the original cube build. Using the left and right arrow keys, you can move tables between the Available tables and the Selected table lists. You can also view statistics about the table that you are selecting with the Properties, View Data, and Table Options buttons. If the table that you want to use is not included in the Available Tables list, you can register the table with the Register Table button. Select Next when you are finished.

On the Summary of Selections page, review the generated PROC OLAP code that is used to update the cube. You can save the PROC OLAP code to a file by selecting Export Code. Select Finish to run the update of the cube.

After a cube has been updated, you can coalesce the aggregation racks (SPD Engine tables) into a single SPD Engine table. In SAS OLAP Cube Studio, select a cube from the tree view. You can then right-click the cube and select Incremental UpdateCoalesce Incremental Data. You can also select the function from the Actions menu. This opens the Aggregations page of the Coalesce Incremental Data wizard.

On the Aggregations page, you can view the aggregations that will be coalesced from the Selected aggregations list. You can also observe the levels of the selected aggregations in the Select aggregations to preview levels list. The preview list is display only. Select Next.

On the Performance Options page, you can specify query performance settings for the cube aggregations. The Default tab enables you to change performance settings for all aggregations of the cube. Enter any needed setting values. Select the Next when you are finished.

On the Summary of Selections page, review the generated PROC OLAP code that is used to coalesce the aggregations. You can save the PROC OLAP code to a file by selecting Export Code. Select Finish to run the update of the cube.

A progress dialog box displays after you click Finish.

The SAS log that is generated contains status information for the coalesce process.