Work with offline cube files

An offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) (.cub) stores data in the form of an Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) cube. This data may represent a portion of an OLAP database from an OLAP server or it may have been created independently of any OLAP database. Use an offline cube file to continue to work with PivotTable (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) and PivotChart reports (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) when the server is unavailable or when you are disconnected from the network.

Security   Be careful using or distributing an offline cube file that contains sensitive or private information. Instead of a cube file, consider keeping the data in the workbook so that you can use Rights Management to control access to the data.

For more information, see Information Rights Management in the 2007 Microsoft Office system.

 Note   The creation and usage of offline cube files from Microsoft SQL Server Analysis Services is based on the Microsoft OLAP OLE DB provider and subject to the term and licensing of your Microsoft SQL Server installation.

For more information, see SQL Server 2005 Licensing Frequently Asked Questions.

What do you want to do?


Learn more about offline cubes

When you work with a PivotTable (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or PivotChart (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) report that is based on source data from an OLAP server, you can use the Offline Cube Wizard to copy the source data to a separate offline cube file on your computer. To create these offline files, you must have the OLAP data provider that supports this capability, MSOLAP from Microsoft SQL Server Analysis Services, installed on your computer.

Using the Offline Cube Wizard

To create the offline cube file, you use the Offline Cube Wizard to select a subset of the data in the OLAP database and then save that subset. Your report doesn't have to include every field that you include in the file, and you can select from any of the dimensions (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) and data fields that are available in the OLAP database. To keep the size of your file to a minimum, you can include only the data that you want to be able to display in the report. You can omit entire dimensions and, for most types of dimensions, you can also omit lower-level detail and top-level items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) that you don't need to display. For any items that you include, the property fields (property fields: Independent attributes associated with items, or members, in an OLAP cube. For example, if city items have size and population properties stored in the server cube, a PivotTable report can display the size and population of each city.) that are available in the database for those items are also saved in your offline file.

Taking data offline and then bringing the data back online

To do this, you first create a PivotTable or PivotChart report that is based on the server database, and you then create the offline cube file from the report. You can then switch the report between the server database and the offline file whenever you want; for example, when you use a portable computer to take work home or on a trip, and later reconnect the computer to your network.

The following procedure provides the basic steps for taking data offline and then bringing the data back online.

  1. Create or open a PivotTable (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) that is based on the OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) data that you want to access offline.
  2. Create an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) on your computer. See the section Create an offline cube file from an OLAP server database.
  3. Disconnect from your network and work with the offline cube file.
  4. Reconnect to your network and reconnect the offiline cube file. See the section Reconnect an offline cube file to an OLAP server database.
  5. Refresh the offline cube file with new data and then recreate the offline cube file. See the section Refresh and recreate an offline cube file.
  6. Repeat this procedure, beginning with step 3.

Top of Page Top of Page

Create an offline cube file from an OLAP server database

 Note   If your OLAP database is large and you want the cube file to provide access to a large subset of the data, you will need to provide ample disk space, and you will find that saving the file may prove to be time consuming. To improve performance, consider creating the offline cube file by using an MDX script.

  1. Click the PivotTable report for which you want to create an offline cube file — you can also click the associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) for a PivotChart report.
  2. On the Options tab, in the Tools group, click OLAP tools, and then click Offline OLAP.

Outlook Ribbon Image

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.

  1. 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.

  1. In step 1 of the wizard, click Next.
  2. In step 2 of the wizard, select each dimension (dimension: An OLAP structure that organizes data into levels, such as Country/Region/City for a Geography dimension. In a PivotTable or PivotChart report, each dimension becomes a set of fields where you can expand and collapse detail.) from your server cube that has data that you want to include in the offline cube file. Click the Plus box box next to each such dimension, and select the levels (level: A part of an OLAP dimension. Within a dimension, data is organized into lower and higher levels of detail, such as Year, Quarter, Month, and Day levels in a Time dimension.) 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 Plus box box don't allow you to exclude levels. You can only include or exclude all of this type of dimension.
  1. In step 3 of the wizard, click the Plus box box next to Measures, and select the fields that you want to use as data fields (data field: A field from a source list, table, or database that contains data that is summarized in a PivotTable report or PivotChart report. A data field usually contains numeric data, such as statistics or sales amounts.) 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 Plus box box next to the dimension, and then select the top-level items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.) to include in the offline cube file.

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

  1. 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.

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

ShowIssue: 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:

Top of Page Top of Page

Reconnect an offline cube file to an OLAP server database

  1. Click the PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) or associated PivotTable report (associated PivotTable report: The PivotTable report that supplies the source data to the PivotChart report. It is created automatically when you create a new PivotChart report. When you change the layout of either report, the other also changes.) for a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.).
  2. On the Options tab, in the Tools group, click OLAP tools, and then click Offline OLAP.

Outlook Ribbon Image

  1. Click On-line OLAP, and then click OK.
  2. If you are prompted to locate the data source, click Browse to find source, and then locate the OLAP server on your network.

Top of Page Top of Page

Refresh and recreate an offline cube file

Refreshing an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), 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. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.

Excel Ribbon Image

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

Make sure the original database is available    The offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) 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.

Top of Page Top of Page

Include different data in an offline cube file

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 Options tab, in the Tools group, click OLAP tools, and then click Offline OLAP.
  1. Click Offline OLAP, and then click Edit offline data file.
  2. 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.

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

Top of Page Top of Page

Delete an offline cube file

 Warning   If you delete the offline cube file for a report, you can no longer use the report offline or create a new offline cube file for the report.

  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).

Top of Page Top of Page

 
 
Applies to:
Excel 2007