When you have a PivotTable or PivotChart report that's based on source data from an OLAP server, you can use the Offline Cube Wizard to copy the source data to a separate .cub file on your local disk. To create these files, you must have an OLAP provider, such as Microsoft SQL Server OLAP Services, that supports this capability. The Offline Cube Wizard is available from Excel.
To create the file, you use the Offline Cube Wizard to select a subset of the data in the OLAP database and save that subset in a separate file. To do this, you first create a PivotTable or PivotChart report based on the server database, and 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 to; for example, when you use a portable computer to take work home or on a trip, and later on reconnect the computer to your network.
Your report doesn't have to include every field that you include in the file. You can select from all of the dimensions and data fields 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 that you don't need to display. If you include items, any property fields available in the database for the items are also saved in your offline file.
If your OLAP provider doesn't support offline cube files, the Offline OLAP command on the PivotTable menu will be unavailable. Contact the vendor for your OLAP provider for more information.
Creating a cube from the records in a query
For some types of source data, you can create an OLAP cube from records returned by a query, providing the benefits of OLAP organization and retrieval for other types of external data. When you create an OLAP cube from a query, you turn the flat set of records into a structured hierarchy that allows reports to focus on the desired level of detail. You also predefine the summary values for the reports, which speeds up report calculation.
With a cube, you can work with more data in your reports than you could otherwise return to Excel without running out of system resources, and you can create and update reports faster than if you based them on the individual records from the database.
The data from your OLAP cube can be returned to Excel only as a PivotTable report, unlike the records in your query, which can also be returned to Excel as an external data range.
To create an OLAP cube, you first create a query in Microsoft Query that includes all of the fields you want to use in the cube, and save the query in a .dqy file in case you need to make changes in the future. You then run the OLAP Cube Wizard in Query to create the cube.
The wizard lets you create two types of cubes. One type is a cube definition that the wizard saves in an .oqy file. When you open a report that's based on this type of .oqy file, the cube is built temporarily in memory. The second type is a separate offline cube file that allows you to continue working with the data when you are disconnected from the network. For help deciding which type of cube to create, and full information about using the wizard, click the Help button in the OLAP Cube Wizard.
When your original database is updated, you can refresh both types of cubes to incorporate any new or changed data that meets the criteria of the original query. However, you cannot add more fields from the original database or the query to the cube. If you need more fields, you can open and modify your original .dqy query file in Microsoft Query and then run the OLAP Cube Wizard again to create a new cube.