Replace a data cube column with a hierarchy

Contents[Hide]

1. Overview

The last step you need to perform when designing a data cube is to configure the Process Result transform. This transform doesn't do any data processing but it allows you to configure the output elements that will be made available to downstream items such as metric sets.

Configuring the Process Result transform involves removing elements (i.e., columns) you don't need and replacing (or promoting) certain columns to hierarchies that have already been defined outside of the data cube. The latter point is important here. If your data cube outputs a bunch of non-measure columns without any replacement, they will be interpreted by default as 1-level implicit hierarchies. For example, a ProductID column that is generated by your data cube is a single-level implicit hierarchy which is not very useful by itself. But when you replace or promote it to an existing, multi-level Product hierarchy, your data cube becomes much more useful when building metric sets in terms of support for filtering, sorting, and drill downs.

2. Walkthrough

2.1. Set up a data cube

For this walkthrough, start with a data cube that is almost completed, with just the configuration of the Process Result transform remaining. This data cube has two output columns, ProductID and OrderDate, that are to be replaced with multi-level hierarchies.

Configure Process Result transform
Configure Process Result transform

2.2. Define hierarchies

The figure below shows a Product hierarchy that was previously defined. This type of hierarchy is sometimes referred to as a user hierarchy.

Product hierarchy with three levels
Product hierarchy with three levels

Additionally, a time hierarchy is needed and you can generally just use one of the hierarchies from the default time dimension in your project. In some cases, you may need to edit the default time dimension and change the range of dates to suit your data.

Default time dimension with two time hierarchies
Default time dimension with two time hierarchies

2.3. Replace ProductID with Product hierarchy

In the Data Cube Designer, click the Process Result transform to open the Data Cube Elements panel.

Locate the ProductID column and click its Edit button.

Edit the ProductID column
Edit the ProductID column

In the Edit Data Cube Output Element dialog, expand the Hierarchies folder and locate the Product hierarchy.

Drag the Product hierarchy upwards and drop onto the Drop a hierarchy from below to use as a replacement region.

Drag Product hierarchy to drop region
Drag Product hierarchy to drop region

By default, the ProductID column will be matched against the lowest (most detailed) level of the Product hierarchy. In most situations, this will work fine but in case your column to replace was ProductSubcategoryID, for example, you can drag the corresponding hierarchy level to the drop region instead of dragging the hierarchy.

Return to the Process Result transform to see that the ProductID column has been replaced with Product.

ProductID has been replaced
ProductID has been replaced

2.4. Replace OrderDate with Time hierarchy

In the Data Cube Designer, click the Process Result transform to open the Data Cube Elements panel.

Locate the OrderDate column and click its Edit button.

Edit the OrderDate column
Edit the OrderDate column

In the Edit Data Cube Output Element dialog, expand the Time Dimensions folder and locate a time hierarchy.

Drag the time hierarchy upwards and drop onto the Drop a hierarchy from below to use as a replacement region.

Drag time hierarchy to drop region
Drag time hierarchy to drop region

Return to the Process Result transform to see that the OrderDate column has been replaced with a time hierarchy.

OrderDate has been replaced
OrderDate has been replaced

2.5. Use the data cube

You can now use the data cube's measures and hierarchies to build metric sets or data visualizations with support for expandable members and drill down.

Table shows expandable Product and Time hierarchy members
Table shows expandable Product and Time hierarchy members

3. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri