,

Building Best Practice TM1 Cubes for Cognos BI Reporting

TM1 cubes hierarchy stairs concept
/by

TM1 cubes have now become the OLAP solution of choice for many Cognos BI environments. Because of this emerging trend, there are some techniques and tips that TM1 developers should be familiar with when building a cube for Cognos BI reports. Initially, the Cognos BI platform was built around Transformer OLAP cubes. Transformer cubes are similar to TM1 in many aspects but there are some important structural differences that affect how users consume them in Cognos BI. This article will discuss in detail some of these differences and how to utilize some hidden features of TM1 to build a cube that will mimic the structural features available in Cognos Transformer and make your Cognos BI developer’s job easier.

Measure Dimension

Every TM1 cube needs to have a measure dimension defined before it can be consumed in Cognos BI. If you do not define a measure dimension in TM1, the cube cannot be imported into Cognos Framework Manager. This is a relatively simple task that can be applied to any cube and any dimension. The measure dimension in Cognos BI is treated slightly differently than the other dimensions. Note: If you define a dimension as a measure dimension in TM1, then the dimension attributes are not accessible in Cognos BI.
Steps to define a Measure Dimension:

1. Open TM1 Architect and log on to your TM1 server
2. Locate the cube on which you want to define a measure dimension.
3. Right-click the cube and select Properties from the pop-up menu that appears.

TM1 cubes measure dimension properties
4. In the Cube Properties dialog box that pops up, select your measure dimension from the Measures Dimension dropdown.

TM1 cubes booking measure
5. Click OK to confirm your selection.

Dimension Design

One of the biggest differences between Transformer OLAP cubes and TM1 cubes is how the dimensions are structured. Cognos BI reports generally work best with non-ragged dimensions. This means that every lowest level element should have the same number of parents (or consolidations). In the Cognos BI environment the parent consolidations are referred to as Levels. Note: This does not mean Cognos BI reporting tools cannot handle a truly ragged dimensional hierarchy with uneven/unbalanced levels. The ragged dimension will simply look and act differently. We will further explore this in an upcoming article.

Top Consolidation

It is recommended that you build a top consolidation for element values in your dimension. Generally, the top consolidation should have the word All in the name. Example: All Roles, All Products, All Time.

TM1 cubes top consolidation

Defining Level Names in TM1 Cubes: The }HierarchyProperties Control Cube

TM1 has a special control cube that is used specifically for defining dimension level names for Cognos BI called }HierarchyProperties. You can access this control cube in the same way as any other control cube you would use when developing cubes for TM1.

In the }HierarchyProperties control cube, you can define the levels and level names that will be visible in Cognos BI. By defining levels in TM1, you give your Cognos BI report developers more flexibility in designing reports. For example, if you have a level called LOB, a report developer can simply drag that level into their report to see all LOBs. The concept of levels is not really important to TM1 developers but it’s very useful for Cognos BI developers.

Sample }HierarchyProperties cube:

TM1 cubes sample }HierarchyProperties cube
The screenshot above shows the default view for a sample }HierarchyProperties control cube. This view lists out the dimensions (as rows) and levels (as columns) of the corresponding reporting cube. As you can see, the corresponding reporting cube in this particular case, ISG_ResourcePlanning, is comprised of 4 dimensions (Audit Resources, Project, Role, and Week). The columns defined for this example are as follows:

  • Default Member (optional): Defines the default selection if no elements are selected in a Cognos BI report.
  • Level000: The root level of the dimension. This should be your top consolidation. Usually called All.
  • Level001-Level099: The remaining level names that you want to define. The number listed after level in the column title indicates the order in which the names will be nested.
  • }Hierarchy dimension: Currently you can only define one hierarchy in a dimension. In the future it may be possible to define alternate hierarchies for the same dimension.

Sample Cognos BI view:
TM1 cubes sample Cognos BI view
Refreshing the Level Names

If you makes changes to the }HierarchyProperties cube, you must refresh the metadata before it will be available in Cognos BI. This happens automatically if you restart the TM1 server. Alternatively, if you do not want to restart your server you can run this function in a TI process using the following syntax:

RefreshMdxHierarchy(”);

Any metadata changes (a.k.a. level name changes) require TM1 cubes to be reimported into Framework Manager. The level names will not automatically refresh themselves in Cognos BI. This is why you should define your level names first before publishing the cubes to Cognos BI.

Defining Display Values for Elements (a.k.a. Member Captions)

It is quite common for a dimension to be built using numeric/code-based element values that use descriptive alias names to be displayed in reports . Cognos BI can easily map dimension alias values to the display property in a dimension while still using the original element name as its definition. This is very useful if you have names that change over time but don’t want to break any of your existing reports.

Note: Only alias attributes can be used as default display values in Cognos BI. Also, if you want to change the default display alias in Cognos BI, you must re-import the cube in Framework Manager and map the default display names.

In this example, the project elements values are based off a non-meaningful number (1060, 1061, 1062, etc). This is not useful to the report consumer. Instead, the report should show the project name in Cognos BI. The Project Name alias is what we want to display (green circle) but the report reference should use the original project element code (red circle). The display name of Spade and Archer can change but as long as the 1060 element number doesn’t change, the report will not be broken.

TM1 cubes Defining Display Values for Elements
As a rule, always define an alias for each dimension regardless of whether or not you plan on using aliases. You can always populate the alias value with the original element name if so desired (see LOB 1 in the above image). This will give you flexibility long term to define new display names without breaking reports.

In Cognos BI, you define the default display (Member Caption) when you import TM1 cubes for the first time. You cannot change the default display name later without re-importing the cube. Note: Make sure to always pick a language.

Framework Manager:

TM1 cubes in Framework Manager

Report Studio view:

TM1 cubes Report Studio view

We will continue discussing how to optimize TM1 cubes for use in Cognos BI next month with an article on handling ragged and alternate hierarchies. Stay tuned!