Show All

Hide All

1. 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.) 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. Is this the first time you're going offline? If so, 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 local hard disk.

   How?

   If your server database is not an 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.) database, and you accessed the data by creating a query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.) in Microsoft Query, use Query to create the cube file. For full instructions, see Help in Microsoft Query.

   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.) for which you want to 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.).

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

   2. On the PivotTable toolbar, click PivotTable, and then click Offline OLAP.
   3. Click Create offline data file, or if an offline cube file already exists for the report, click Edit offline data file.
   4. In step 1 of the Offline Cube Wizard, click Next.
   5. 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 you want to include in the offline cube file. Click the 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.) you want to include.
      • 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've grouped items, so that Microsoft 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 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. For each dimension listed below Measures, click the box beside 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.
      • 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 to save the file, select only the items you need to view in the report. Any item properties (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.) available for the items you select are automatically included in the cube.
      • If items you want to include are missing, you may not have included the dimension containing them in the previous step. Click Back and select the missing dimension in step 2 of the wizard, then return to step 3.
   7. In step 4 of the wizard, specify a name and location for the .cub file, and then click Finish.

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

   When Microsoft Excel finishes saving the file, proceed to the last step of this procedure.

3. Have you taken this report offline before, so that you already have an offline cube file on your hard disk? If so, reconnect to it.

   How?

   1. Click the PivotTable report, or for a PivotChart report, 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.).
   2. Click PivotTable on the PivotTable toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), and then click Offline OLAP.
   3. Click On-line OLAP, and then click OK.

4. Does the server have new data since you created the offline cube file? If so, click Refresh Data , and wait for the file to be recreated and saved with the new data.

5. Disconnect from your network.

Note  Do not put the .cub file in the Windows briefcase. Excel does not use the briefcase to synchronize the offline cube file with the server database.