Practical No.
6
AIM:- Creation of Cubes and Dimensions
Dimensions are the primary organizational unit of data in a star schema.
A dimension consists of a set of levels and a set of hierarchies defined using these levels.
Users often analyze data by drilling down on known dimension hierarchies.
To create a dimension, you must define the following:
1.
2.
3.
4.
Dimension Attributes
Levels
Level Attributes
Hierarchies
A. Create a Dimension called as REL_TIME
Warehouse Builder enables you to not only create but also populate time dimensions. When
you create a time dimension using the wizard, Warehouse Builder automatically creates the
mapping for you to populate the time dimension.
Now, expand IBMDW right-click Dimensions and select New > Using Time Wizard.
The Create Time Dimension Wizard is launched. Click Next on the Welcome Page.
On the Name and Description page, enter REL_TIME as the name of the dimension. Click Next.
On the Storage page, select Relational Storage (ROLAP) to specify relational
implementation of the dimension. Click Next.
On the Data Generation page, you specify the range of time data that is required
for your warehouse.
This information is used to generate a mapping that populates the time dimension.
For Start Year, enter 2003 and for Number of Years enter 3. Ensure that Calendar is
selected, as shown in the screenshot.
On the Levels page, select Normal Hierarchy and select all the levels: Calendar Year, Calendar
Quarter, Calendar Month and Day. Click Next.
On the Pre Create settings page, examine the details and click Next.
A progress bar shows the progress as the wizard creates the necessary objects.
On successful completion, click Next. On the Summary page, examine the details again and click
Finish.
Observe that the wizard has generated four objects necessary for a fully functional TIME
dimension:
REL_TIME dimension object.
REL_TIME_SEQ sequence that populates the surrogate ID of the time dimension levels.
REL_TIME table to support the relational implementation of the time
dimension that physically stores the time data.
REL_TIME_MAP mapping to populate the time dimension.
If you are curious to know how REL_TIME_MAP looks like, Right-click
REL_TIME_MAP under the Mappings node and select Open Editor and this will
launch the Mapping Editor
From the View menu, select Auto Layout. You can see the expanded mapping.
B. Create a Dimension called as REL_CATEGORY
In the EXPENSE_WH module, right-click Dimensions and select New > Using Wizard
The Create Dimension Wizard is launched. Click Next on the Welcome Page.
On the Name and Description page, enter REL_CATEGORY as the name of the dimension. Click
Next
On the Storage Type page, select Relational Storage (ROLAP) to specify the
relational implementation of the dimension. Click Next.
On the Dimension Attributes page, you find three predefined columns, ID, NAME, and
DESCRIPTION.
Note that for the ID attribute, the identifier is set as Surrogate and for the Name
attribute the identifier is set as Business.
You can change these attributes to your liking, but must have a surrogate and a
business identifier in the list.
The surrogate identifier must have the NUMBER data type.
For the NAME attribute, change Length to 30. Click Next.
On the Levels page, specify the following levels for the default hierarchy in the dimension:
CATEGORY (Description: Accounting Category)
TYPE (Description: As entered on Expense Report)
Click Next.
On the Level Attributes page, for each level that you have defined, you select the
dimension attributes that apply to that level. Accept the default selection and click
Next.
On the Slowly Changing Dimension page, leave the default slowly changing dimension
option selected. Click Next.
Click Next.
On the Pre Create Settings Page. The Dimension Creation Progress page appears. On
successful completion, click Next. Click Finish on the Summary page.
Observe that REL_CATEGORY dimension has been added to the EXPENSE_WH target module.
With the dimension, Warehouse Builder also creates a table that implements the dimension in a star
schema and also creates a sequence to load the ID column of the dimension.
C. Create a Cube For Time & Category Dimension.
Cubes contain measures and link to one or more dimensions.
The axes of a cube contain dimension values and the body of the cube contains measure values.
In a relational implementation, a cube is linked to dimension tables by foreign key constraints and
consists of a set of measures.
To create a cube, you must define the following:
1. Cube Measures.
2. Cube Dimensionality.
Now switch to the IBMDW module, right-click Cubes, and select New > Using Wizard
The Create Cube Wizard is launched. Click Next on the Welcome Page.
On the Name and Description page, enter REL_EXPENSE as the name of the cube. Click Next
.
On the Storage Type page, select ROLAP: Relational Storage to specify
relational implementation of the cube.
Click Next
On the Dimensions page, you select the dimensions that the cube will refer to. In the Available
Dimensions list, expand Dimensions, and then expand EXPENSE_WH.
Select EXPENSE_WH, and then click > to move the two dimensions REL_CATEGORY and
REL_EXPENSE to Selected Dimensions list. Click Next.
On the Measures page, enter EXPENSE in the Name field. Accept the default data type. Click
Next.
On the Summary page, review the cube details and click Finish
Observe that REL_EXPENSE cube is added under the Cubes node of the EXPENSE_WH module
To view the cube in the Data Object editor, right-click REL_EXPENSE and select
Open Editor or double-click REL_EXPENSE.
Examine the cube, and then close the Data Object Editor.
From the Design menu, select Save All to commit your work.
So till now, we have created two external tables mapped to the two previously sampled source flat files,
two dimensions, REL_TIME and REL_CATEGORY, and a cube REL_EXPENSE linked to the two
dimensions
