Developing an OLAP Cube
Overview
Using BIDS, after the DSV is developed, the next step is to create dimensions. Dimensions are of two types: Database Dimensions and Cube Dimensions. Database dimensions can be perceived as a master template, and Cube dimensions can be perceived as instances / children of this master template.
We will start our development with the creation of database dimensions. If you consider a dimension as a table, all the fields in this table can be perceived as attributes. Hierarchy in a dimension is a group of attributes logically related to each other with a defined cardinality. Finally we will create a cube using the dimensions we just developed, and fact tables to create dimensions (cube dimensions) and measure groups (from fact tables).
Creating a Dimension
Dimensions are of two types: database dimension and cube dimension. The dimensions that are defined at the solution level can be termed as a database dimension and the ones defined inside the cube are termed as a cube dimension. Dimension Wizard is the primary means of creating a dimension. We will create a dimension using the three dimension tables which we have included in our schema.
Explanation
Right-click the Dimensions folder and select “New Dimension”, this will invoke the Dimension Wizard. The first screen should look like the below screenshot. You have the options of using an existing table, creating a table in the data source and using a template. We already have the dimension table in our schema and we will use this, so select “Use an existing table” and click “Next”.
Select the DSV we created earlier in the DSV selection. We intend to create a dimension from the DimSalesTerritory table, so select the same table. Every dimension table needs to have a key attribute, and in this table SaleTerritoryKey is the primary key column which is guaranteed to identify each record uniquely. It would not make sense to browse this attribute using the Key, instead SalesTerritoryRegion field has unique values. We can also use this field as the key as well as name column. But for the purpose of our exercise, we will use the SaleTerritoryKey field as the key column and SalesTerritoryRegion as the name column. Though it looks inappropriate to use the key field, but when you are starting to develop an understanding of dimensions, this will help to set a rule in your mind that the key field is always required, mostly a surrogate key and you can set a name column to any field to facilitate a convenient browsing mechanism.
In the next screen, you need to make a selection of the attributes that will be present in the dimension. If you uncheck the “Enable Browsing” button, they won’t be visible to client applications when they browse the dimension. Attributes can be of different types and you can specify the type in the Attribute Type field. The Dimension Wizard removes the Name column you set from the key column as that is available due to the key column. So you won’t find that field in this list of available attributes.
Now the next step is to give a name to the dimension, name it “Cube Dim Sales Territory” or anything appropriate. After this step you have completed creating your first dimension.
In a similar manner create Product and Date dimension using the Dimension Wizard.