< Previous | Next >

Lesson 4: Modifying the Time hierarchy

In this lesson, you will refine your cube model by modifying the Time hierarchy to include two new levels, Year and Month.

Overview

You previously modified the Retailers hierarchy within the cube model. You applied this hierarchy to a cube that you created. The Time hierarchy of the cube model was created from the Time dimension of the GOSALESDW schema. The existing Time hierarchy includes only the Quarter and Day levels. You want to add levels to the Time hierarchy so that you can query at the Year and Month levels also.

When you add levels to your hierarchy, you add levels of granularity. You will be creating this granularity by dividing the Time hierarchy into different levels: Year, Quarter, Month, and Day. You will be able to drill down through this hierarchy and query information from all levels of the cube. For example, you could query all sales of a product that were made by a specific retailer during the first quarter of 2004.

In this lesson, you will modify the Time hierarchy to include Year and Month levels. The steps are similar to the steps that you took when you added the levels to the Retailers hierarchy. However, this lesson will introduce level types, sort order, and composite keys. A level has a composite key when more than one column or attribute is needed to identify a member within the hierarchy. This lesson illustrates how making changes to the cube model affects the cube. This lesson will also show you how you can iteratively refine existing cube models and cubes to make them more useful.

Tasks in this lesson

To modify the Time hierarchy, do these tasks:

  1. Adding attributes to the Time dimension

  2. Creating Year and Month levels

  3. Adding the Year and Month levels to the Time hierarchy

  4. Validating the Sales cube model

Adding attributes to the Time dimension

You will add year and month attributes to the Time dimension from the cube model.
Learn more about attributes
An attribute represents the basic abstraction of a database table column. An attribute contains an SQL expression that can be either a simple mapping to a table column or a more complex expression. These more complex expressions can combine multiple columns or attributes and can use all SQL scalar functions.

The SalesCube cube references the Time dimension of the cube model. The Time dimension currently contains only two levels, Quarter and Day. You will modify the Time dimension to include two new attributes. These attributes will map to information in the GOSALESDW.GO_TIME_DIM table of the GSDB database. You will be able to use these attributes later in this lesson to create new Year and Month levels within your hierarchy.

Procedure

To add attributes to the Time dimension:

  1. Open the Add Attributes from Columns window:
    1. Locate the Attributes folder of the Time dimension:
      • Expand the tree in the Data Project Explorer: Cubing_Tutorial > Data Models > Database Model.dbm > GSDB > OLAP Objects > Cube Model Sales > Time > Time dimension Time > Attributes.
    2. Right-click the Attributes folder and select Add Attributes from Columns.
  2. Add the CURRENT_YEAR, DAY_OF_MONTH, MONTH_EN, and MONTH_KEY columns with the Add Attributes from Columns window as shown in the following table.
    Page Action
    Select columns to create attributes
    1. Expand the GOSALESDW.GO_TIME_DIM table.
    2. Select the following columns:
      • CURRENT_YEAR
      • DAY_OF_MONTH
      • MONTH_EN
      • MONTH_KEY

    These columns will be used to define levels inside the Time dimension.

    In the Data Project Explorer, you will see that for each column you selected, there is an attribute that you can reference when you define the level. It will appear in the following format: Attribute (Table). For example, you will see CURRENT_YEAR (GO_TIME_DIM) as one of your new attributes.
You modified the Time dimension to include Year and Month attributes. You can now add levels to the Time dimension by using these attributes.

Creating Year and Month levels

Now that you have added attributes to the Time dimension, you can create levels within the Time hierarchy that are based on these attributes.
Learn more about dimension and level keys

A level consists of a set of attributes that work together as one logical step in the ordering of a hierarchy. A level contains one or more attributes, and the level defines the relationship between those attributes. Levels also have a type. You can specify a level type as Unknown, Regular, or Time or Date. MDX provides a number of special time-specific functions. To make these functions available within a hierarchy, you must explicitly set the level time to be Time or Date and choose one of the options.

For each level in a dimension, you must define a level key. A level key consists of one or more level key attributes. Level key attributes should uniquely identify each member in the level.

A composite level key can consist of level attributes from all ancestors of the member, such as CURRENT_YEAR and CURRENT_MONTH. The composite level key will uniquely identify each member in the level. For example, every year has a month "01," so having a level key of just Month does not uniquely identify each member in the Month level. It is the combination of the year and the month that makes a month unique. Therefore the level key, Month is actually two keys: Year and Month.

A composite level key is typically less efficient when compared to a compact level key such as MONTH_KEY. It is recommended that you use compact, single-member level key attributes to improve the efficiency of your cube and cube model.

Procedure

To create the Year and Month levels:

  1. Open the Add Level wizard:
    1. Locate the Levels folder of the Time dimension:
      • Expand the tree in the Data Project Explorer: Cubing_Tutorial > Data Models > Database Model.dbm > GSDB > OLAP Objects > Cube Model Sales > Time > Time dimension Time > Levels.
    2. Right-click the Levels folder and click Add Level.
  2. Add the Year level by using the Add Level wizard as shown in the following table.
    Page Action
    Properties
    1. Specify the name: 
      Year
    2. Specify the type:
      • Select the Time or Date radio button, then select the Years radio button.
      Best practice: MDX provides a number of special time-specific functions. To make these functions available within a hierarchy, you must explicitly set the level type to be Time or Date and choose one of the options.
    Select attributes and columns
    1. Select CURRENT_YEAR (GO_TIME_DIM). Add the attribute by clicking the arrow (Arrow icon) icon.
    2. Set the CURRENT_YEAR (GO_TIME_DIM) attribute as the Key and Default Related attribute.
    3. Click Next.
    Select the ordering attributes No changes are necessary.
    The Year level is created.
  3. Add the Month level by using the Add Level wizard:
    1. Right-click the Levels folder and click Add Level.
    2. Complete the pages of the Add Level wizard as shown in the following table.
      Page Action
      Properties
      1. Specify the name: 
        Month
      2. Specify the type:
        • Select the Time or Date radio button, then select the Months radio button.
      Select attributes and columns
      1. Add the MONTH_EN (GO_TIME_DIM) and MONTH_KEY (GO_TIME_DIM) attributes. Select the attributes and use the arrow (Arrow icon) icon to add the attributes.
      2. Set the MONTH_EN (GO_TIME_DIM) attribute as the Default and Related attribute by selecting the check boxes. Make sure that the Key check box is not selected.
      3. Make sure that the MONTH_KEY (GO_TIME_DIM) attribute is set as the Key attribute by selecting the check box.
      4. Click Next.
      Select the ordering attributes Set MONTH_KEY as the ordering attribute.
      Tip: By default, members appear in alphabetical order. You can override the order in which members appear within the hierarchy by setting a member as the order key. In this example, set MONTH_KEY as the order key to display the data in a time sequence, ordered by year and month.
      1. Click the white space under the Order column, next to the MONTH_KEY attribute. A menu opens.
      2. Select 1 from the list of options.
You created the Year and Month levels.
Next, you will modify the Time hierarchy to include these new levels.

Adding the Year and Month levels to the Time hierarchy

Now that you created the Year and Month levels, you can add them to the Time hierarchy. After the levels are added to the hierarchy, you will arrange the levels into a logical order (Year > Quarter > Month > Day).
The levels that you created currently exist only in the cube model. In order to obtain more detailed information when you query the cube, you must add the level to the hierarchy in the SalesCube cube.

Procedure

To add the Year and Month levels to the Time hierarchy:

  1. Open the Time hierarchy in the Properties view:
    • Expand the tree in the Data Project Explorer: Cubing_Tutorial > Data Models > Database Model.dbm > GSDB > OLAP Objects > Cube Model Sales > Time > Time dimension Time > Hierarchies > Time hierarchy Time.
    The Time hierarchy properties open in the Properties view.
  2. Add the Year and Month levels to the hierarchy:
    1. Open the Levels page of the Properties view.
    2. Click the Add (Add) icon to add the levels. The Add Levels window opens.
    3. In the Add Levels window, select the Year and Month levels and click OK.
    The levels are added to the hierarchy.
  3. Set the level order of the hierarchy in the Properties view:
    1. Promote the Year level to the parent position in the Time hierarchy:
      • Click the Up (Up) arrow to promote the Year level above the QUARTER_KEY level.
    2. Promote the Month level above the DAY_KEY level, but below the QUARTER_KEY level.
    The final order of the levels of the hierarchy should be Year > QUARTER_KEY > Month > DAY_KEY.
  4. Add a related attribute to the DAY_KEY level. Related attributes provide more information about the level key attribute. You will add a DAY_OF_MONTH attribute to change the date format of information when it is queried.
    1. Select the DAY_KEY level:
      • In the Data Project Explorer, locate and select the DAY_KEY level: Cubing_Tutorial > Data Models > Database Model.dbm > GSDB > OLAP Objects > Sales > Time > Time > Levels > DAY_KEY.
      The DAY_KEY level displays in the Properties view.
    2. Open the Add related attributes window:
      • In the Properties view, click the Related tab, then click the Add (Add) icon.
    3. In the Add related attributes window, select the DAY_OF_MONTH (GO_TIME_DIM) attribute and click OK.
    4. Make the DAY_OF_MONTH (GO_TIME_DIM) attribute the related attribute:
      • Select the check box next to the DAY_OF_MONTH (GO_TIME_DIM) attribute.
  5. Save your cube model:
    • Click File > Save All.
  6. Browse the changes that you made to the Time hierarchy:
    1. Locate the Time hierarchy:
      • Expand the tree in the Data Project Explorer: Cubing_Tutorial > Data Models > Database Model.dbm > GSDB > OLAP Objects > Sales > Cubes > SalesCube > Time.
    2. Open the Browse Dimension Members wizard:
      • Right-click the Time hierarchy and click Browse Members.
    3. Specify GSDB as the data source for your cube model and click Finish.
    4. If prompted, click OK to allow the wizard to complete.
    You will see the additional levels, Year and Month, that you created.
You can now view your data at different levels of granularity. For example, you can view at Year level, Quarter level, Month level, or Day level.

Validating the Sales cube model

You will validate the Sales cube model to ensure that you did not introduce errors.

If your cube contains errors, a number of these errors will be found by the model analyzer when you validate the cube model.

Best practice: Validate your cube model whenever you make changes to your cube or cube model. You can validate the cube model at any point to ensure that the changes that you made are valid. You can find problems and solve them easily by validating often.

Procedure

To validate the Sales cube model in the Data Project Explorer:

  1. Open the Analyze Model wizard:
    1. Locate the Cube Model Sales cube model in the Data Project Explorer:
      • Expand the tree in the Data Project Explorer: Cubing_Tutorial > Data Models > Database Model.dbm > GSDB > OLAP Objects > Cube Model Sales.
    2. Right-click the Cube Model Sales cube model and click Analyze Model.
    The Analyze Model wizard opens.
  2. Select the Physical Data Model check box. All of the rules beneath the Physical Data Model object are also checked.
  3. Click Finish.
  4. Check the Problems view for errors or warnings:
    • Click the Problems tab in the lower right pane of the Design Studio to open the Problems view.
    Important: At this point, you should expect to see some warnings in the Problems view. Warnings will not be resolved until you run the Optimization Advisor wizard to resolve any issues with indexes and constraints. You can continue to work on the cube model and cube if you have warnings.

    If you have errors, however, you should resolve them before you continue to work on the cube model and cube.

You validated your cube.
Tip: Some problems, like non-unique level key combinations, are data dependent and will not be caught until run time.

What's next?

In the next lesson, you will learn how to create SQL-based and MDX-based calculated measures and members.
< Previous | Next >


Feedback | Information roadmap