XL2000: Editing Local OLAP Cube Creates New Cube

When you edit the local offline OLAP cube (.cub) file for your PivotTable, Excel creates a new .cub file instead of updating the original .cub file.

This problem occurs when you are working with a PivotTable connected to an OLAP data source, and you do the following:

• You create a local OLAP data cube.
  
  -and-
• You reconnect to the OLAP server-based data.
  
  -and-
• You save your workbook containing the PivotTable to a different drive and/or folder.
  
  -and-
• You edit the local cube file.

To work around this problem and update the local cube file, use either of the following methods.

Method 1: Copy the Local Cube to the New Folder

If you save your workbook to a different drive and/or folder, copy the local .cub file from the original folder to the new folder where you saved your workbook. Use the Windows Explorer to locate and copy the .cub file.

Method 2: Reconnect/Edit the Local Cube File

You can reconnect to the local cube and then edit the cube to include any changes. However, if you've made changes to the layout of your PivotTable while connected to the server, including adding new fields (levels) that are not part of the local cube, these layout changes will be lost in your PivotTable. After reconnecting locally, edit the local cube and then re-create the PivotTable for layout changes that were removed.

To reconnect to the local cube, follow these steps:

1. Click anywhere in your PivotTable.
2. On the PivotTable menu of the PivotTable toolbar, click Client-Server Settings.
3. In the Client-Server Settings dialog box, click Local data file and click OK.

To edit the local cube, follow these steps:

1. Click anywhere in your PivotTable.
2. On the PivotTable menu of the PivotTable toolbar, click Client-Server Settings.
3. In the Client-Server Settings dialog box, click Edit local data file.
4. In the Create Cube File dialog box, add the new dimensions, levels, and measures that you want added to your local offline cube file.

NOTE: If you plan to distribute the PivotTable to other users that may need to make changes to the PivotTable layout, and the PivotTable is connected to an offline cube file (.cub), then you will also need to distribute the offline local cube file with the workbook. You cannot make any changes to the PivotTable without the source data.

In order to modify the layout or update the data in the PivotTable, the .cub file must be available. When you share or distribute a workbook containing a PivotTable, and the data in the PivotTable is based on a local OLAP cube (.cub), you must distribute the .cub file with the workbook. Because of this requirement, when you create a local .cub file, the file is stored in the same folder as the workbook. The following table lists where the .cub will be saved under different situations: