Designing the Cube with the Cube Creation Wizard
Posted by - NA - on 30 March 2010 12:34 PM

Step 1. Defining a fact table

In data warehousing, a fact table consists of the "measures", or "facts" of a business process. It is often located at the centre of a star or snowflake schemas, surrounded by dimension tables.

Fact tables provide the (usually) additive values that act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a SALES fact table might be stated as "Sales volume by Day by Product by Store". Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (a region contains many stores).


Step 2. Defining measures

Measures – are a set of values that are based on columns in the cube's fact table and are usually numeric. Measures are the central values that are aggregated and analyzed. Please watch the short video to understand how to define measures in the Cube Creation Wizard.


Step 3. Defining dimensions

At this point you need to select the fields that will later be used in the Cube as "headers" for rows or columns with the measures for analysis. It is better to select only the fields you really need. For instance, if the Customers table contains the CustomerName, CustomerPhone, CustomerEmail, CustomerPhoto fields etc., all you need at this point is to select the CustomerName field. A short video below will help you get a clear idea of what is said in this paragraph.

Note, that for the fields of DateTime type there're two kinds of presentation:

  • native - the DateTime values are formatted as is
  • multilevel hierarchy - the DateTime values are transformed into some kind of hierarchy, for example, "Year-Month-Day"

Step 4. Defining informational attributes

Informational attributes contain additional information about dimension members. For example, for "Personnel" dimension the attributes may include information about home addresses and phone numbers. Usually attributes are presented as fields of the same dimension table that describes the dimension itself. A short video below will demonstrate you how to define attributes with Cube Creation Wizard and the way it looks in OLAP Grid.


Step 5. Beyond the Wizard

The Cube Creation Wizard is meant for you to begin working with the Cube and create the simplest Cubes. There's quite a number of tasks not performed by the Wizard. First of all, it's constructing multi-level hierarchies and creating fact-table-row-based calculated measures. All these tasks are performed in the Cube Structure Editor, described in the following article.

Besides that one, we would strongly recommend you to read "Creating the right Cube Structure" that deals with creating Cubes with the maximum performance.
Moreover, you are able to create Cubes not only in DesignTime or with the Wizard, but in runtime as well. To find out how you can do that, read "Creating the Cube in runtime".

(329 vote(s))
This article was helpful
This article was not helpful

Help Desk Software by Kayako Resolve