Chapter 5. Modeling Dimensional Data

This chapter will cover the use of dimensional modeling, which is more correctly called Dimensionally Modeled Relational (DMR) data. The dimensional models created can be used with IBM Cognos Analysis Studio, and also with the other studios.

In this chapter we will cover the following:

• Creating regular dimensions

• Creating regular dimensions with multiple hierarchies

• Creating measure dimensions

• Creating scope dimensions

By the end of this chapter, you will be able to create a dimensional data model using relational data sources.

Dimensional modeling is the name for a set of techniques and concepts used in data warehouse design. Dimensional modeling is based on the concepts of dimensions (or context) and facts (or measures). Dimensions are groups of hierarchies and descriptors that define the facts; facts are typically numeric values that can be aggregated.

Dimension modeling is one of the functions available within Framework Manager. With the dimensional modeling...

Regular dimensions

A regular dimension contains one or more user-defined hierarchies, with each hierarchy consisting of the following components:

• Levels: These are used to roll-up measures

• Keys: Each item in a level must have a unique key defined

• Captions: These are used as labels for the levels

• Attributes: These contain additional information regarding the levels

Every regular dimension requires that each level has a key and a caption specified, and that the caption be a string data type.

Regular dimensions are based on data source or model query subjects that have already been defined in your model.

Creating a regular dimension

We will create a time dimension using the great_outdoors_warehouse from the IBM Cognos Samples. From the model, we will use the Time query from the Business view item as a relational data source to build our regular dimension.

Create your regular dimension as follows:

1. Right-click on your namespace, and navigate to Create | Regular Dimension, or alternatively click on...

Dimensions with multiple hierarchies

When you create a dimension and if you are not restricted to a single hierarchy, a dimension can have more than one hierarchy. The only restriction on using hierarchies from the same dimension is that you cannot use one hierarchy for the rows, and another hierarchy for the columns in the same crosstab. If you need to use your hierarchies in this way then each hierarchy must be in a separate dimension.

An example of multiple hierarchies for the same dimension is the time hierarchy, which can be viewed by Quarter or by Month. These hierarchies are separate but are interchangeable, and are bound to the same underlying query.

Tip

You are not limited to two hierarchies in a dimension. A dimension may contain any number of hierarchies.

The following is the Time dimension as a single dimension with two hierarchies, one by Month and the other by Quarter:

The hierarchies are defined in Framework Manager as follows:

Note

Notice that at the lowest level we need to have...

Measure dimensions

A measure dimension is a collection of facts. A measure dimension is created from one or more query subjects that have a valid relationship between them.

Measure dimensions should only be composed of quantitative items and they do not contain any keys with which join them together. Therefore, to join a measure dimension to a regular dimension, it is necessary to create the joins in the underlying query subjects.

Creating a measure dimension

We will create a measure dimension using the great_outdoors_warehouse from the IBM Cognos Samples. From the model, we will use the Inventory Fact query from the Business View as a relational data source to build our measure dimension.

We will create a measure dimension as follows:

1. Right-click on your namespace, and navigate to Create | Measure Dimension, or alternatively click on your namespace and from the Actions menu, navigate to Create | Measure Dimension. This will show the following dimension definition dialog:

   

2. Navigate to Business...

Scope relationships

Scope relationships exist between measure dimensions and regular dimensions. These are used to define the level at which the measures are available for reporting.

Scope relationships are not the same as joins in relational queries. There are no conditions or criteria in a scope relationship that dictate how a query is to be formed. A scope relationship only specifies if a fact can be queried with a specific dimension. If there is no scope relationship between the fact and the dimension, this will result in an error.

Once you set the scope relationship for a measure dimension, the settings apply to all measures within the measure dimension. If data is to be reported at different levels for each measure in a measure dimension, then you can set the scope for each measure.

When setting the scope for a measure you are specifying the lowest level that the data can be reported on.

Tip

If you create the measure dimensions and regular dimensions in the same namespace, Framework Manager...

Summary

In this chapter, we have covered the techniques used to create a dimensional data model using a relational data source. We have covered the creation of dimensions, including dimensions with multiple hierarchies, measures, and scope relationships.

By using the techniques in this chapter, we can produce a Framework model that we can use with IBM Cognos Analysis Studio. We can also use the Framework model with Query Studio and Studio.

In the next chapter, we will look at enhancing our model by creating the business and presentation layers, which we will later publish for use by our report authors.