Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation - Page 5

Cube Design and Creation with the OLAP Cube Wizard

We now enter the cube design and creation phase of our second approach for creating a local cube. Our next steps will focus upon organization of the external data we have defined for extraction, and the manner in which we want it to summarize, and to appear for analysis and reporting. As we have learned, many reporting options exist, including PivotTable reports (see Reporting Options for Analysis Services Cubes: MS Excel 2002 of this series for basic creation and use), PivotTable Lists (see Reporting Options for Analysis Services Cubes: MS FrontPage 2002 for an introduction to this option), PivotChart reports, and others. Many options exist, as well, outside of the MS Office suite, as various third-party reporting tools can access the OLAP Cube that we will generate (see my Database Journal articles index for other reporting options).

The OLAP Cube Wizard allows us to begin with the output (a flat series of records) of the query we have designed in MS Query, and to then apply a hierarchical organization to the fields. It also allows us to define the summary values we want to calculate for optimal reporting purposes. In addition to summarized values, our cube will contain descriptive facts surrounding those values.

The values to be summarized, or measures as we know them from other OLAP scenarios, are called data fields within the context of the OLAP Cube Wizard. The descriptive facts, such as the date and location of a transaction, are organized into the hierarchical levels of detail that we know as dimensions.

The successful definition of the dimensions and their associated levels depends upon determining the kinds of categories that the information consumers employ (or want to be able to employ) when they analyze the data in reports and browsers. We can organize data fields and dimensions to endow organizational reports with high-level summaries (such as total costs worldwide, or at country or regional levels), while also enabling the presentation of lower-level details, filtered for a myriad of criteria (such as locations or areas of management responsibility where costs are particularly high, or, alternatively, well controlled and minimal).

As we have discussed, the local cube design and creation process is easy, flexible and, best of all (from the perspective of "proof of concept" and other prototyping exercises) fast. After we create and view reports based upon a new version of a local cube, we can return immediately to the OLAP Cube Wizard to make changes to adjust for consumer suggestions and comments regarding usability and performance, as well as to test ideas we formulate on an ad hoc basis. A local cube means isolation of the development process and uninterrupted operation of any production cubes that we have in place. It also means ultimate portability and convenience, both in the design phase and in a distributed production scenario.

Let's begin exploring the processes involved in working with the OLAP Cube Wizard to create our local cube. The steps consist of the following:

• Defining the data fields
• Defining the dimensions and levels
• Selecting the type of cube

The OLAP Cube Wizard - Step 1 of 3 dialog appears, as partially shown in Illustration 11.