In this step, you decide what kind of cube you want the wizard to create. The best choice depends on several factors, including the amount of data your cube will contain, the type and complexity of reports you plan to create from the cube, and the resources (memory and disk space) available on the systems where users will create or view the reports. You may need to experiment with these options to determine which choice works best for your reports.
What it does This option stores instructions for creating the cube along with your query or report, and creates the cube itself only when you open the report. The cube initially retrieves only the data that's needed to display the report, and it retrieves more data as needed when you change what the report displays.
When to use Use this option for reports that you or the report's users open and view but seldom change, and when you have problems with running out of memory or disk space with the other choices.
Speed and resource tradeoffs Because the cube retrieves only the data that the report initially displays, the amount of time to open the report is minimized. For example, if the cube organizes data about sales in four regions, but your report initially displays only data pertaining to sales in the West region, the cube retrieves only the data from the West region. However, each time you change what the report displays, you must wait for the cube to retrieve new data, so changing the report may require more time.
What it does Like the first option, this option stores instructions for creating the cube along with your query or report, and creates the cube itself only when you open the report. But in this case, the cube retrieves all of the data for the report at one time, when you first open the report, so that you can change what the report displays without waiting for the cube to retrieve more data.
When to use Use this option for reports that will receive heavy interactive use or that you or your users will change repeatedly, when you do not want to allocate substantial disk space for the reports.
Speed and resource tradeoffs Because this option makes all of the data available immediately, you can change the view and display different data rapidly. However, this type of cube requires more memory and temporary disk space than a cube that retrieves data on demand, and you might not have enough resources if the cube includes a large amount of data.
What it does This option creates a separate cube file on disk, retrieving all of the data for the cube and storing it in this file.
When to use Use this option for interactive reports that you will change frequently, when you aren't concerned about the amount of disk space used by the report, or when you want to store the cube on a network server where others can access it to create their own reports. A cube file can provide some of the data from the original source database while omitting sensitive or confidential data that you don't want other users to access. A cube file also allows you to work with the reports offline by using a snapshot of the original source database.
Speed and resource tradeoffs Initial creation of a cube file may take more time than the other choices, but opening and changing reports may be faster. The size of the cube file affects the speed with which you can open and change reports. The amount of data you include and the way you organize the cube into data and dimension fields determine the size of the file. If you have limited disk space, you may need to experiment with different organizations and selections of data to reduce the cube file size, or you may need to use one of the other options instead of creating a cube file.
Saving a cube file When you choose the third option, a cube file with a .cub extension is created in the location you specify. This file contains all of the data for the cube. This file is not created immediately when you click Finish; it's created either when you save the cube definition in a file or when you create the report that is based on the cube.
Saving the cube definition in a file This section applies only if you run the OLAP Cube Wizard from Microsoft Query. For all three options, Query prompts you to save a cube definition (.oqy) file, which you can open in Excel to create reports that are based on your cube. It is separate from any .cub file that you create to store the actual cube data. If you want to make changes to your cube, you can open the .oqy file in Query to run the OLAP Cube Wizard again. For more information about working with .oqy files, see Microsoft Query Help.