Create dimensional objects for OLAP-style reporting - IBM Cognos

The IBM Cognos Framework Manager product allows you to create dimensionally modeled relational (DMR) models. A DMR refers to the dimensional information that a modeler supplies for a relational data source to allow for OLAP-style queries, which allow for navigational functionality and access to dimensional functions in studios that support dimensional functions, such as IBM Cognos Report Studio and IBM Cognos Business Insight Advanced.

Dimensional information is defined through the following dimensions:

  • Regular Dimensions
  • Measure Dimensions
  • Scope Relationships

A dimensionally modeled layer can be applied to any metadata in star schema format. When your metadata is in star schema format, you can provide hierarchy information to dimensions and measure scope for each Regular Dimension created.

Create Regular Dimensions

Regular Dimensions consist of one or more user-defined hierarchies. Each hierarchy consists of the following components:

  • Levels
  • Keys
  • Captions
  • Attributes

Level information is used to roll up measures accurately when performing queries or analyses. Regular Dimensions require that each level have a key and caption specified, and that the caption be a string data type. These items are used to generate members in the studios data trees (where applicable) and retrieve the members at run time.

The following steps show one example of how to create a Regular Dimension. In this case, we model a Time dimension.

  1. In the Project Viewer, create a new namespace under Model called Dimensional View.
  2. Right-click Dimensional View, point to Create, and then click Regular Dimension. The Dimension Definition window opens.
  3. In the Available items pane, expand Business View Time, and then drag Year (formerly CURRENT_YEAR) into the Hierarchies pane.
  4. Right-click the top Year in the hierarchy column, and then click Rename.
  5. Type Time, and then press Enter.
  6. Rename Year(All) to Time (All). If needed, rename CURRENT_YEAR to Year. The results display.

    Dimension Definition dialog box
    Dimension Definition dialog box

  7. You need to assign a business key and member caption to each level. Remember, a caption must be a string data type. In this case Year is an integer; therefore, you need to create an item to cast Year into a string.

  8. In the Hierarchies pane, click the Year level in the bottom pane, click the ellipsis (...) in the Role column, and then select _business Key.

    Specify Roles dialog box for Regular Dimension
    Specify Roles dialog box for Regular Dimension

  9. Click Close, and then click Add in the bottom-right corner.
  10. In the Name box, type Year Caption. Then, under Available Components, expand Business View Time, and drag Year to the Expression definition pane.
  11. Edit the expression to display as follows: cast([Business View].[Time].[Year],char(4)) This expression casts the integer value to a string.
  12. Click OK, click the ellipsis (...) for Role for the new Year Caption item, select _memberCaption, and then click Close. The Year level now has a business key and member caption assigned
  13. Year level roles
    Year level roles

  14. From the Available items pane, drag Quarter Key under the Year level in the Hierarchies pane.
  15. Rename the level to Quarter, and then assign a Role of _businessKey to Quarter Key in the bottom pane.
  16. Add a new item for this level called Quarter Caption with the following expression. Note that Quarter (numeric) was formerly CURRENT_QUARTER.
  17. cast([Business View].[Time].[Quarter (numeric)], char(1))
  18. Assign the _member Caption role to Quarter Caption, and then click Unique Level
  19. Unique Level check box
    Unique Level check box

The Unique Level check box indicates that the keys in the levels above the current level are not necessary to identify the members in a level. If they are, then leave this check box clear. The top level does not need this setting, because it has no parent keys.

  • Drag Month Key below the Quarter level in the Hierarchies pane, rename it to Month, and then assign the _businessKey role to Month Key in the bottom pane.
  • Drag Month to the bottom-right pane, select _memberCaption as the role, and then select the Unique Level check box.
  • Drag Day Key below the Month level in the Hierarchies pane, and rename it to Day.

Notice that the _businessKey role is already assigned to Day Key in the bottom pane, because Day Key is an identifier as it is the primary key in the underlying table.

  • Drag Day Date to the bottom-right panel, set it as the _memberCaption, and then rename it to Day Caption.
  • Click the ellipsis (...) for the Source column for Day Caption to edit its definition, and change the expression as follows:
cast([Business View].[Time].[Day Date], char(10))
  • Click OK, select Unique Level

    Time dimension hierarchy completed
    Time dimension hierarchy completed

For some dimensions, such as the Time dimension, ensure specific sorting of the data in all scenarios to take advantage of dimensional functions that navigate the data. For example, you might want to use the Lag function, which allows you to view the current month and the previous month by lagging one month from the current month in a calculation. For these types of functions to work, the order of the data must be correct and consistent. If your business keys, captions, or attributes are sortable so that there is a logical order to the data, you can use the Member Sort feature for Regular Dimensions to ensure the correct structure for you data.

  • Click the Member Sort tab, and then, under Sorting Options, selectMetadata, Data, and Always (OLAP compatible)

    Dimension Definition, Member Sort tab
    Dimension Definition, Member Sort tab

  • Click Detect to detect which item will be used in the Level Sort Properties pane to sort the data. In this case, the business keys for the levels are used, which is correct.

Dimension Definition, Member Sort tab
Dimension Definition, Member Sort tab

For more information about the Member Sort feature, refer to the IBM Cognos Framework Manager User Guide.

  • Click OK, and then rename New Dimension to Time .

    New Regular Dimension for Time
    New Regular Dimension for Time

The new Time dimension is now complete and when expanded in the Project Viewer displays.

Time dimension expanded in Project Viewer
Time dimension expanded in Project Viewer

Again, if you are feeling adventurous, create a dimension for Products and Order Method We include the results in the model that we provide with this book.

Products dimension configuration
Products dimension configuration

Order Methods dimension configuration
Products dimension configuration

Create Measure Dimensions

A Measure Dimension is a logical collection of facts that enables OLAP-style analytical querying and is related to Regular Dimensions within scope.

To create Measure Dimensions:

  1. Right-click Dimensional View, point to Create, and then click Measure Dimension
  2. In the Model Objects pane, expand Business View Sales Fact.
  3. Click Quantity, Shift+click Break Even Point, and then drag all the selected measures to the Measures pane.
  4. Sales Measure Dimension
    Sales Measure Dimension

  5. Click OK, and rename the new Measure Dimension to Sales Fact.
  6. Repeat the steps to create a Measure Dimension called Sales Target Fact that is based on the Sales Target Fact data source query subject in the Business View. Select only the Sales Target measure. The results display
  7. Sales Fact and Sales Target Fact Measure Dimensions expanded in the Project Viewer
    Sales Fact and Sales Target Fact Measure Dimensions expanded in the Project Viewer

Define scope for measures

Measure Dimensions are related to Regular Dimensions through scope relationships that define at what levels a measure is in scope. However,underlying join relationships are still required to generate the SQL that is sent tothe data source.

A scope relationship is created automatically between a dimension and a measure dimension whose underlying query subjects have a valid JOIN relationship defined and are required to achieve predictable roll ups.

To define the scope of a measure or group of measures, you can use the Dimension Map in the Project Info pane. To define the scope for the Sales Fact and Sales Target Fact MeasureDimensions:

  1. Double-click the Dimensional View namespace to give it focus. Notice the scope relationships
  2. Scope relationships
    Scope relationships

    You can double-click the relationships to edit scope. However, in our example, we use the Dimension Map because it is a central location to define scope easily for all measures. You can also create, edit, and delete Regular Dimensions and Measure Dimensions in this pane.

  3. In the middle pane, click the Dimension Map tab, and then click Sales Fact in the Measures pane
  4. Sales Fact scope
    Sales Fact scope

    All levels in all dimensions are highlighted indicating that they are all currently in scope, which is correct for the Sales Fact measures. However, this is not the case for Sales Target Fact, which is at the Month level for the Time dimension and Product Type level for the Products dimension. Sales Fact is also not in scope at all for the Order Methods dimensions. We set the appropriate scope for the Sales Target measure.

  5. Click Sales Target Fact, under Time click the Month level, and then on the toolbar click the Set Scope icon
  6. Set Scope toolbar icon

    Set Scope toolbar icon

  7. Click Product Type in the Products dimension, and then click Set Scope on the toolbar.
  8. Click the Order Methods dimension, and then on the toolbar click the Remove Scope icon.

The Day level for the Time dimension and the Product Name level for the Products dimension are no longer highlighted and are out of scope for Sales Target Fact. The Order Methods dimension is completely out of scope.

Scope settings for Sales Target Fact
Scope settings for Sales Target Fact

  • Now that the dimensional objects are created and scope is set, we create a presentation view for authors and analysts. In the Project Viewer, right-click Presentation View, point to Create, and then click Namespace.
  • .Name the new namespace Query.
  • .Create another namespace, and name it Analysis.

These namespaces are used to organize the relational and dimensional presentation views.

  • Drag the Sales Target and Sales namespaces into the Query namespace. The results display

Presentation View organized
Presentation View organized

Next, create star schema groupings for the DMR objects and place them in the Analysis namespace.

  • In the Dimensional View, right-click Sales Fact, and then click Create StarSchema Grouping. The result displays
  • Create Star Schema Grouping dialog box
    Create Star Schema Grouping dialog box

The Measure Dimension and all related Regular Dimensions are selected automatically based on the scope relationships.

  • Click OK, and then repeat these steps to create a star schema grouping for Sales Target Fact.
  • Drag the two new star schema grouping namespaces to the Analysis namespace. The final Presentation View displays
  • Final Presentation view
    Final Presentation view

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

IBM Cognos Topics