OLAP Cube Wizard: Overview

This wizard steps you through the process of organizing external data you've selected in a query into an OLAP cube, which is a way of organizing summarized data for analysis. The wizard helps you prepare the data for use in a database report, such as a PivotTable or PivotChart report, a PivotTable form, or a PivotTable list on a Web page.

This wizard is designed for people who manage databases and create database reports. If you are unfamiliar with database management or new to creating these kinds of reports and need assistance, you may want to consult the person who manages the network database that you want to work with.

How will this wizard organize my data?

Summary of the wizard steps

Preparing to use the wizard

More information

How will this wizard organize my data?

When you finish the wizard, you will have a cube on which you can base reports. After you create and view reports, if you find that you want to organize the cube differently or include different data in it, you can return to this wizard to make changes.

What is OLAP? On-Line Analytical Processing (OLAP) is a way to organize data to fit the way users analyze it — in hierarchical categories and precalculated summary values. An OLAP cube provides only the summarized results to your reports, instead of a large number of individual records, so that you can analyze a larger amount of data than your system's resources could otherwise handle. When you create an OLAP cube from data in a relational database, you perform the initial steps of creating the reports that you or your users need.

Data fields and dimensions The wizard lets you take a flat series of records, apply a hierarchical organization to the fields, and define the summary values you want to calculate.

The resulting cube contains two types of data: summarized values and descriptive facts about these values. The values to be summarized, such as the number of items sold, are called data fields. The descriptive facts, such as the date and location of each sale, are organized into levels of detail called dimensions.

When you organize the fields, you can use the same kinds of categories that you or your users employ when you analyze the data in reports. Data fields and dimensions make it easy for reports to display high-level summaries, such as sales totals across an entire country or region, and also display the lower-level details, such as sites where sales are particularly strong or weak.

Return to top

Summary of the wizard steps

The following is a brief description of how you use the wizard. For more complete instructions, see the More information section later in this topic.

Step 1: Define the data fields In the first step of the wizard, you identify the fields that you want to use as data fields, choose a summary function to calculate the values for each field, and name each field.

Step 2: Define the dimensions and levels In the second step, you organize the rest of the fields into dimensions and levels, and name the dimensions and levels.

Step 3: Select the type of cube The wizard may or may not have a third step, depending on the program you used to access the data and run the wizard. In the third step, you decide whether you want to build the cube in memory or save it as a cube file. If you don't have this step, see Help in the program from which you ran the wizard to find out what kind of cube is created.

Changing a finished cube After you finish running the wizard, you can change any of the options you chose. You can add or remove fields, change how data fields are summarized, and change how dimensions are organized. For information about changing an existing cube, see Help in the program you used to access the data for which you are using the wizard.

Return to top

Preparing to use the wizard

Before you create the cube, first consider what fields in the data contain values to be summarized (to use as data fields) and what fields contain descriptive data (to use as dimensions).

Which fields should be data fields The data fields, which contain values that you can total or count, will form the body of the reports that will be based on the cube. For example, if a sales database includes fields that show the revenue from each sale and stock on hand, you could report total sales and inventory levels (data fields) for each product by region (dimensions).

Your cube can summarize data fields in four ways, by using the following summary functions: Sum, Count, Minimum, and Maximum.

If you want to summarize a field in more than one way, you must add the field to your query or selection of source data once for each type of summary you want. For example, if you want to total the sales revenue (Sum) and tally the number of sales (Count) for the Sales field, you must add the Sales field twice. You might need to exit this wizard and add more instances of data fields before you can continue.

Which fields should be dimensions Consider how a report might organize your information in levels of detail. Descriptive fields that you can organize in a categorical hierarchy make up a dimension. For example, a sales database might have separate fields identifying the country, region, city, and site where each sale took place. A dimension could organize this geographical information from the lowest to highest level of detail, like this:

Levels in geography dimension

Levels in the geography dimension

If your data contains date or time fields, the wizard can set up dimensions for these fields automatically, organizing the data by year, half-year, quarter, month, week, and day, or by hour, minute, and second.

You can designate fields that provide isolated or discrete facts and don't belong in a hierarchy — such as a field that determines whether a product is taxable — as dimensions with a single level.

Specifying source data efficiently All fields in a cube must be either data fields or dimensions, and each field from your data must be designated as one or the other type. To make the query or selection of source data run more efficiently and make reports based on it refresh more quickly, you should include only the fields that you plan to use in the cube. Before you continue with this wizard, you might want to make sure your query or selection of source data includes all the data you want, and only that data.

Deciding what type of cube you need Whether the wizard lets you specify the type of cube to create depends on the program you used to create your query or select your source data. See Help in the program from which you ran the wizard to determine whether the wizard has this step or instead makes this choice automatically for you.

If included, this step gives you three alternatives that provide different tradeoffs of speed of operation versus use of memory and disk space.

  • You can create a temporary cube in memory that's associated with the report you're currently using and retrieve all of the data for the cube from the original database at one time.

  • You can create the same type of temporary cube as in the first alternative but retrieve data only when the report calls for it.

  • You can create a cube file that you can reuse for other reports.

You might need to experiment with these options to determine the best tradeoff between speed and the allocation of memory and disk resources for your reports. The optimal choice depends on the amount of data you want to include in the cube and the complexity of the reports you want to create.

Learn more about these options and the advantages and disadvantages of each in Help for Step 3 of the wizard.

Return to top

More information

The following topics provide specific information about using each of the wizard steps.

Step 1: Define the data fields.

Step 2: Define dimensions and levels.

Step 3: Select the type of cube. (Depending on the program from which you ran the wizard, you may or may not have this step.)

Return to top


OLAP Cube Wizard: Step 1

In this step, you decide which of the fields in your source data you want to use as data fields. Data fields contain values that you want to summarize, such as sales amounts that you want to total. Select at least one field to be a data field.

The wizard has determined which fields may contain this kind of data and proposes them as data fields. The proposed fields are the checked ones that appear at the top of the list. You need to verify whether the wizard's proposals are correct and make any changes you want.

You must leave at least one field unchecked in this step. The fields you don't check in this step will be available for use as dimension fields, which are the fields that contain descriptive data and facts that serve as categories and line items in your reports. You'll select and organize the dimension fields in the next step.

To complete this step:

  1. Select the check boxes for any additional fields that you want to use as data fields, and clear the check boxes for the fields that you don't want to use. At least one field must be selected.

  2. To choose a different summary function from the one the wizard proposes, click the proposed function in the Summarize by column, and then click the function that you want. The summary functions are Sum, Count, Min (returns the smallest value in the data being summarized), and Max (returns the largest value).

    To summarize the same field by using more than one summary function, your query or selection of source data must contain one instance of the field for each summary function that you want. To add more instances of the field, click Cancel to exit the wizard; then add the fields to your query or selection, and run the wizard again.

  3. To change the name of a field from the name the wizard proposes, click the name in the Data field name column, and type the new name.

    The field names you assign in the wizard will appear in the reports you create from the cube. Whether you can change the field names in the reports depends on the type of reports you create.

More information

Overview of the OLAP Cube Wizard


OLAP Cube Wizard: Step 2

In this step, you organize the descriptive data into dimensions. Each dimension becomes a field in the reports that are based on the cube. By organizing the fields in levels of detail, you allow users of the reports to select the level of detail to view, starting with a high-level summary and then focusing on details, and vice versa.

You must designate at least one dimension for your cube. You can designate fields that provide isolated facts and don't belong in a hierarchy — such as a field that determines whether a product is taxable — as dimensions with a single level. Your cube will be more useful for reports, however, if you organize some of the fields into levels. For example, you could organize three fields into a Product dimension that includes three levels: product division, product group within the divisions, and individual product.

If you use date or time fields as dimensions, the wizard automatically creates levels for these dimensions. You can then select the levels to make available in reports. For example, you could select weeks, quarters, and years, or you could select weeks, months, and years.

To complete this step, do the following:

  1. From the Source fields list, drag each top-level dimension field to the Dimensions box onto the area marked Drop fields here to create a dimension. You must create at least one dimension.

    You can include each field only once. To use the same field in more than one dimension, your query or selection of source data must include one instance of the field for each dimension in which you want to use it. To add more instances of the field, click Cancel to exit the wizard; then add the fields to your query or selection, and run the wizard again.

  2. To create a level within a dimension, drag a field from the Source fields list onto an existing dimension or level in the Dimensions box, as in the following example.

    Example of dragging a field onto a level

    To create a City level under the Region level, drag the City field onto the Region level.

    Click the Plus box or Minus box boxes to display or hide levels.

    Fields containing greater detail should be at lower levels. To move a field to a lower or higher level, click the field in the dimension and drag it onto a lower or higher field within the dimension.

    For date and time fields, the wizard creates levels automatically only when you create a new top-level dimension with the field. When you add these fields as levels in existing dimensions, the wizard does not create the date and time levels for you.

  3. For date and time dimensions, select the check boxes for the levels you want to make available in reports and clear the check boxes for levels you don't want to include. You can include either the Week level or the Month level; but not both because months do not contain an even number of weeks.

  4. To change the name of a dimension or level, click the name once to select it and a second time to rename it, a2nd then type the new name. You cannot change the names of levels in date and time dimensions.

    The field names you assign in the wizard will appear in the reports you create from the cube. Whether you can change the field names in the reports depends on the type of reports you create.

More information

Keyboard shortcuts for this step of the OLAP Cube Wizard

Overview of the OLAP Cube Wizard


OLAP Cube Wizard: Step 3

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.

Option 1: Retrieving data on demand

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.

Option 2: Retrieving all data at once

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.

Option 3: Saving a cube file

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.

Cube files and cube definition files

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.

More information

Overview of the OLAP Cube Wizard


Keys to use with the OLAP Cube Wizard

Use these keys to complete Step 2 in the OLAP Cube Wizard.

To move a field from the Source fields list to the Dimensions box

  1. Press the TAB key to select the Source fields list.

  2. Press the UP ARROW or DOWN ARROW key to select the field you want to move.

  3. Press the TAB key to select the > button, and then press ENTER.

  4. To move other fields, repeat steps 1 through 3.

To move a field to a lower or higher level within a dimension in the Dimensions box

  1. Press the TAB key to select the Dimensions box.

  2. Press the UP ARROW or DOWN ARROW key to select the field you want to move.

  3. Press CTRL+X.

  4. Press the UP ARROW or DOWN ARROW key to select where you want to move the field.

  5. Press CTRL+V.