Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube - Page 3

September 3, 2002

Designing Our Cube

Cube design first entails a determination of what we want to capture as measures, or the quantitative values from our database that we want to analyze / monitor as indicators of business activity. "Actual" measures, such as revenues / sales, as well as expense / costs, are typically desirable, along with "Budget", "Plan", or "Forecast" values, to achieve a good analysis of organizational performance.

Moreover, measures are analyzed against the different dimension categories of a cube. Dimensions represent perspectives, or "views" within the context of which measures have relevance and (hopefully) actionable meaning. The lowest levels of detail for the values that we choose as measures typically reside in a more-or-less relational fact table. While operational data often comes from a variety of original data repositories, the most common way of managing relational data for multidimensional reporting in the business environments of today is with a star schema-based warehouse / mart, or similar storage concept. A star schema in its simplest form consists of a single fact table, linked to multiple dimension tables through a common key or keys shared between each member of a linked-table pair. While the many possible variations of the basic star schema are mercifully beyond the scope of our excursion into Analysis Services in this series, we can, for the purposes of our examination, rely upon the circumstance that the Analysis Services cube we design will have only one fact table, with a simple arrangement of dimensions that will be selected, across which to analyze its measures.

Our next objective is to select a few measures from our data source's specified fact table, and to also stipulate the dimensions we wish to capture. One dimension that is virtually always useful is a time dimension. We will specify a time dimension, together with a few others, to illustrate the design and construction of a simple cube. We are fortunate in this pursuit to be assisted by the Analysis Services Cube Wizard, which prompts us to make selections of these values in a straightforward manner.

Selecting the Fact Table and Measures for Our Cube

The Cube Wizard first guides us through the selection of a single fact table; once we tell it where to find this table, we are prompted to select the measures, or numerical values, that our cube will summarize for analytical purposes. We begin our design process with the following steps.

We initialize the Cube Wizard with a right-click to the Cubes folder that appears within the MyFirstCube database we created in the foregoing steps. We will select New Cube, then Wizard, from the popup menu, to raise the Cube Wizard Welcome dialog box shown below in Illustration 8 (which we can skip by checking the box at the lower bottom half of the Welcome dialog, once we become old hands at the process).

Illustration 8: The Cube Wizard Welcome Dialog

We click Next, and are presented with a list of tables from our newly established data source, MyFoodMart, from which we will select the sales_fact_1998 table. As shown below (Illustration 9), we are then presented with the various columns in the table.

Illustration 9: The Cube Wizard Welcome Dialog

We can also verify our selection by taking a peek at the actual data via the Browse Data... button, as pictured in Illustration 10 below. We will select the table by clicking Next, at which point we are greeted with a dialog box that prompts us to select the contained measures that we wish to incorporate into our cube design.

Illustration 10: A Small Subset of Cube Wizard Browse Data Results

Let's select the store_sales, store_cost, and unit_sales measures, as shown in Illustration 11. We click Next, and the Wizard moves into the Dimension selection phase of the design process. Having selected the values that we want the cube to summarize for us, we now need to tell it what dimensions we want to use as perspectives, or views, of those values / measures.

Illustration 11: Our Selected Measures in the Cube Wizard Column Selection Dialog

Page 4: Making the Cube Multi-Dimensional

Go to page: Prev   1  2  3  4  5  6  7  8  9  10  Next