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?
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
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
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 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 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
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