Work with offline cube files

1. Click the PivotTable report for which you want to create an offline cube file — you can also click the associated PivotTable report for a PivotChart report.

2. On the Analyze tab, in the Calculations group, click OLAP tools, and then click Offline OLAP.

   The Offline OLAP settings dialog box is displayed.

   Note: If your OLAP provider doesn't support offline cube files, the Offline OLAP command is unavailable. Contact the vendor for your OLAP provider for more information.

3. Click Create offline data file or, if an offline cube file already exists for the report, click Edit offline data file.

   The Offline Cube Wizard is displayed.

4. In step 1 of the wizard, click Next.

5. In step 2 of the wizard, select each dimension from your server cube that has data that you want to include in the offline cube file. Click the box next to each such dimension, and select the levels that you want to include.

   Notes: 

   • You cannot skip intermediate levels within a dimension.

   • To reduce the size of the cube file, omit lower levels that you don't need to view in the report.

   • Be sure to include any dimensions where you have grouped items, so that Microsoft Office Excel can maintain these groupings when you switch between the server database and the offline file.

   • Dimensions that do not have a box don't allow you to exclude levels. You can only include or exclude all of this type of dimension.

6. In step 3 of the wizard, click the box next to Measures, and select the fields that you want to use as data fields in the report. You must select at least one measure; otherwise the dimensions associated with the measure will contain no data. For each dimension listed under Measures, click the box next to the dimension, and then select the top-level items to include in the offline cube file.

   • To limit the size of the cube file so that you don't run out of disk space and to reduce the amount of time required to save the file, select only the items that you need to view in the report. Any property fields that are available for the items that you select are automatically included in the cube.

   • If items that you want to include are missing, you may not have included the dimension that contains them in the previous step. Click Back in the wizard and select the missing dimension in step 2, and then return to step 3.

     Note: In the OLAP Cube Wizard, the only summary functions available for data fields are Sum, Count, Min, and Max.

7. In step 4 of the wizard, enter a name and location for the .cub file, and then click Finish.

   To cancel saving the file, click Stop in the Create Cube File – Progress dialog box.

8. After Excel has finished creating the offline cube file, click OK in the Offline OLAP Settings dialog box.

Issue: My computer ran out of disk space while saving a cube.

OLAP databases are designed to manage very large amounts of detailed data and, as a result, the server database may occupy a much larger amount of disk storage than your local hard disk provides. If you specify a large subset of this data for your offline cube file, your hard disk may run out of space. The following strategies can help reduce the size of your offline cube file.

Free up disk space or find another disk    Delete files that you don't need from your disk before saving the cube file, or save the file on a network drive.

Include less data in the offline cube file    Consider how you can minimize the amount of data in the file and still have what you need for your PivotTable or PivotChart report. Try the following:

• Eliminate dimensions    In step 2 of the Offline Cube Wizard, select only the dimensions that are actually displayed as fields in your PivotTable or PivotChart report.

• Eliminate levels of detail     Click the box next to each selected dimension in step 2 of the wizard, and then clear the check boxes for levels lower than those displayed in your report.

• Eliminate data fields    In step 3 of the wizard, click the box next to Measures, and then select only the data fields you're using in the report.

• Eliminate items of data    Click the box next to each dimension in step 3, and then clear the check boxes for items that you don't need to see in the report.

1. Click the PivotTable report or associated PivotTable report for a PivotChart report.

2. On the Analyze tab, in the Calculations group, click OLAP tools, and then click Offline OLAP.

3. Click On-line OLAP, and then click OK.

4. If you are prompted to locate the data source, click Browse to find source, and then locate the OLAP server on your network.

Refreshing an offline cube file, which re-creates it by using the most recent data from the server cube or new offline cube file, can be a time consuming process and require a lot of temporary disk space. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

1. Click the PivotTable report that is based on the offline cube file.

2. In Excel 2016: On the Data tab, in the Queries & Connections group, click the arrow next to Refresh All, and then click Refresh.

Issue: New data doesn't appear in my report when I refresh.

Make sure the original database is available    The offline cube file may not be able to connect with the original server database to retrieve new data. Check that the original server database that supplied the data for the cube hasn't been renamed or moved. Make sure the server is available and that you can connect to it.

Make sure new data is available    Check with the database administrator to determine whether the database has been updated in the areas included in your report.

Make sure the database organization hasn't changed    If an OLAP server cube has been rebuilt, you may need to reorganize your report or create a new offline cube file or OLAP Cube Wizard cube to access the changed data. Contact the database administrator to find out about changes that were made to the database.

Saving a revised offline cube file can be a time consuming process, and you cannot do other work in Excel while the file is being saved. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

1. Make sure that you are connected to your network and that you can access the original OLAP server database that supplied the data for the offline cube file.

2. Click a PivotTable report that is based on the offline cube file, or click the associated PivotTable report for a PivotChart report.

3. On the Analyze tab, in the Calculations group, click OLAP tools, and then click Offline OLAP.

4. Click Offline OLAP, and then click Edit offline data file.

5. Follow the steps in the Offline Cube Wizard to select different data for the file. In the last step, specify the same name and location as the existing file that you are changing.

1. Close all workbooks containing reports that use the offline cube file, or make sure all such reports have been deleted.

2. In Windows, locate and delete the offline cube file (.cub).