Building Active Forms with Hierarchies

Building Active Forms with Hierarchies

IBM Planning Analytics introduced Hierarchies in TM1 v11, allowing a single dimension to contain multiple rollup structures. Slice is the first Excel add-in to fully support hierarchies in Active Forms — including the new DBHW function.

What Are Hierarchies and Why Do They Matter?

Hierarchies sit between a dimension and its elements, allowing one dimension to have multiple views of the same data. For example:

• The Employee dimension can have hierarchies for Region, Status, and Department — without creating separate dimensions.

• The Month dimension can have both Calendar Year and Fiscal Year hierarchies.

This makes TM1 models more flexible and future-proof, reducing dimension sprawl while supporting diverse analytical views.

The problem: Until Slice, there was no easy way to use hierarchies in Excel-based TM1 reports. Perspectives and PA for Excel did not support hierarchy functions like DBH and DBHW in Active Forms.

How Slice Enables Hierarchies in Active Forms

Slice introduces the DBHW function — the hierarchy-aware equivalent of the standard DBRW function. It works identically to DBRW but allows you to specify hierarchies in any order.

Key Functions

Example: Building a Hierarchy-Aware Active Form

Scenario

An Employee cube with 5 dimensions: Version, Month, Currency, Employee, and Employee Measure.

• The Month dimension has 4 hierarchies (e.g. Calendar, Fiscal Year, Quarter, Period).

• The Employee dimension has 7 hierarchies (e.g. Region, Status, Department).

Step 1: Open the Cube in the Slice Cube Viewer

In the Slice Server Explorer, navigate to the Employee cube and open a view. When building the view, you can choose from all available hierarchies across your dimensions.

Example layout:

• Rows: Department hierarchy of the Employee dimension

• Columns: Region hierarchy of the Employee dimension

• Title: Other dimensions (Version, Currency, Employee Measure, etc.)

You can even stack hierarchies from the same dimension on rows and columns simultaneously for multi-dimensional analysis.

Step 2: Export as an Active Form

With your view configured, click the Export Active Form button in the Cube Viewer toolbar. Slice will generate the Active Form in Excel using DBHW for writeback cells.

Step 3: Use DBHW in the Generated Report

The exported Active Form will use DBHW for cells where data can be written back. The function syntax mirrors DBRW but includes the hierarchy dimension:

Slice Is Backwards Compatible

Slice supports all TM1 Excel functions. If you open a report built with Perspectives or PA for Excel, it will work in Slice without modification. Hierarchy support is additive — existing reports are not affected.

Benefits of Using Hierarchies with Slice

Community vs Business Edition

The Slice Community Edition (free) includes hierarchy support and DBHW functionality. The Slice Business Edition adds advanced features such as MDX Forms, Virtual Consolidations, and Virtual Expressions.

Related Pages