Tutorial: Creating dimensions and hierarchies

This tutorial uses multiple dimensions and hierarchies to model data about car sales.

About this task

Now it's time to create a new view of your sample data, and then create new dimensions and hierarchies that make it easier to model your data. Hopefully, this tutorial demonstrates some of the following benefits of using hierarchies:

  • Hierarchies can improve query performance
  • You can turn attributes into hierarchies.
  • Modeling attributes as hierarchies instead of dimensions can save memory space. This is because you can have cubes with fewer dimensions if you use hierarchies. Hierarchies act as virtual dimensions.
  • Hierarchies give you greater flexibility. A simgle dimension can contain multiple hierarchies, and you can display them in the same view.
  • Hierarchies conform to existing standards that already use hierarchies
Note: Think of a dimension as a container of hierarchies instead of a container of members. Now that you can create more than just one hierarchy in a dimension, the hierarchies are the containers of members and the dimension is a container of those hierarchies.

Let's start with the server and look at some data about car sales. In the SData sample in IBM® Planning Analytics Local version 2.0.0, the EnableNewHierarchyCreation configuration parameter in the tm1s.cfg file is set to true so this is a good place to start creating hierarchies.

Procedure

  1. Create a book and call your sheet Sales Variances by Target.
  2. Click New View icon.
  3. In the content tree, expand SData > SalesCube > Dimensions
  4. Drag the model dimension onto the rows, the actvsbud dimension on to the columns, and the region dimension onto the context.
    animation showing how to create the sales variance by target view

    This view is showing you the variance between actual sales and budgeted sales for each model of car that is broken down by series (S, L, and T Series).

  5. In the content tree, expand the SalesCube cube, and then expand the model dimension.
    You might notice that the model dimension has only one hierarchy right now and it is called model.
    Screen showing the model dimension in the SalesCube view.

    The model hierarchy is the default hierarchy that was created when this dimension was created. You are going to create some more hierarchies and see how they impact the view of your data.

  6. Right-click the model dimension and click Create hierarchy.
    Don't right-click the model hierarchy because you can't add a hierarchy under a hierarchy. You can add a hierarchy only to a dimension.
    Tip: Remember, your dimension is a container of hierarchies.
  7. Name the new hierarchy CustomerTarget and click Create.
    You are going to use this hierarchy to see a different view of the models of cars that might help you analyze the demographics of your customers better.

    Instead of looking at sales by series, you are going to view car sales based on whether they are targeted at sports car purchasers, off-road drivers, drivers with families, or budget-conscious customers.

  8. To make things easier to view, in the Dimension Editor of the CustomerTarget hierarchy, click Add icon and create a member that is called All Customer Targets.
  9. In the hierarchy editor, create four new members in the CustomerTarget hierarchy and add them as children of the All Customer Targets member.
    • Budget
    • Family
    • OffRoad
    • Sport
    Screen showing the CustomerTarget hierarchy.

    Because your hierarchy has no data about cars, there's nothing useful about this hierarchy yet.

  10. Populate your new hierarchy with members. For tips and tricks, see Data entry.
    1. Right-click the model hierarchy and click Edit Hierarchy.
    2. Search for all the members of the model dimension and find all the wagons using the filter
      filter
      and copy them by using right-click Copy member.
    3. Paste each of the wagons into the Family member of the CustomerTarget hierarchy by using CTRL+V or CMD+V.
      You can copy and paste multiple members at a time if they are in a continuous selection. You should find 12 members to add.
    4. Find all the convertibles and add them to the hierarchy as Sport members of the CustomerTarget hierarchy. There should be a total of four convertibles.
    5. Find all the coupes and add them to the hierarchy as Sport members of the CustomerTarget hierarchy. There should be a total of four coupes.
    6. Add all the 4WD vehicles to the OffRoad member of the CustomerTarget hierarchy. You should find eight 4WD vehicles to add as members here.
    7. Add all the Sedans to the Budget member of the CustomerTarget hierarchy. There are 13 sedans to add.
    If you want to, you can right-click the model dimension and click Add as selector widget. This makes it easier to jump to data points in your view. For more information, see Create selectors.
  11. Drag your CustomerTarget hierarchy beside your model hierarchy in your view.
  12. Find the intersection of the model and CustomerTarget hierarchies to find out the sales variance of S-series family vehicles that were sold in 2015.
    Screen showing the variance value of the S-series family vehicles.
  13. In the Dimension Editor of the CustomerTarget hierarchy, click Attributes icon.
    Notice that the members have attributes already because these members exist in the model hierarchy also.
  14. Right-click all the attributes that contain translated model names and click Hide.
    Notice that there is a member attribute called Engine Size. Let's populate this attribute with some useful data.
  15. For each member, in the Engine Size attribute, enter the engine size of the car by using the series name. For example, for the L Series 1.8 L Sedan, enter 1.8 in the Engine Size attribute if it is not already filled in.
    Screen showing the attributes entered in the member attributes pane.
  16. Now that you have all the Engine Size attributes entered, right-click the Engine Size attribute and click Create hierarchy and click OK.
    Screen showing the Create hierarchy option when you right-click an attribute.

    A new hierarchy that is named Engine Size opens in a Dimension Editor widget. This hierarchy contains members that were previously member attributes.

    Screen showing the Engine Size hierarchy created from attributes.
  17. Add the Engine Size hierarchy to your view beside the model and CustomerTarget hierarchies.

What to do next

Find the intersection of the data for all models that are targeted at sports car owners who want at least a 3.2-liter engine. Duplicate your view with the ProjectedSales dimension instead of the actvsbud dimension and see whether you can figure out which series and engine type is projected to have the most sales to families.

Without hierarchies, this intersection of the data would not be possible.