How to create an efficient framework manager package from TM1 cubes

Check list of all posts

To create an efficient FM package from TM1, we need to make cubes be prepared. This document is intended to provide some tips and tricks during the report authoring based on TM1 cubes. It will include TM1 data source connection, TM1 cube measure dimension, TM1 member caption, TM1 level and TM1 MUN.

TM1 data source connection

Make TM1 data source is not a big issue. However, we need to pay attention to name space. As behind the scene, a TM1 cube (or database) is fully security aware, same as traditional relational database. For example in Cognos express, we can’t use default Cognos express name space to make TM1 connection. If you use the default setting, our report can NOT be accessed probably by any users with other integrated name space, as these users don’t have permission to access cubes, even they can access report. It is the best to integrated namespace with Sign on. This way will guarantee that user can run report as long as he is permitted to access report.

TM1 cube measure dimension

To make cube eligible for framework manager, a measure dimension must be defined. Actually, there is no measure concept in TM1, we can naturally choose a appropriate dimension as measure dimension.

TM1 member caption

To prepare member caption for report to use, either we don’t need to define any alias, or we define one alias as caption. Member caption must be defined as alias. At the same time, we can define domain properties as well.

When populating in framework manager, we can specify member caption as below

All cube defined attributes become dimension member properties.

TM1 level

TM1 can setup dimension with any hierarchies, including balanced, ragged hierarchy. By nature, we don’t have levels in framework manager for TM1. Such model do present some difficult for report authors, or business power user for Cognos workspace advanced, because we need to use MDX, for example

descendants ([PNLCube].[region].[region] ->:[TM].[region].[region].[@MEMBER].[World],3)  to get countries. If we want to get ID, then report becomes even more difficult as we need to use property to display. Such a structure will get a big problem for business users.

The solution is to setup levels for TM1 dimensions as below

Once setup, we need to run RefreshMdxHierarchy('region');

Then framework manager is generated as below

This model will give business user much easier to use by dragging any property into work space.

TM1 MUN, MUN …

MUN is specification for report; many changes in TM1 can have unexpected impact on report authoring. For example, MUNs with level and without level are listed below

[PNLCube].[region].[region].[Country]->:[TM].[region].[region].[@MEMBER].[Canada]

[PNLCube].[region].[region]->:[TM].[region].[region].[@MEMBER].[Canada]

[PNLCube].[region].[region].[Level1]->:[TM].[region].[region].[@MEMBER].[World]

[PNLCube].[region].[region]->:[TM].[region].[region].[@MEMBER].[World]

You can see any changes of level will have impact on reports. In addition, measure dimension can be very sensitive MUN as well. From report point of view, any alias of measure dimension should NOT be used at all.

Special note:

Obviously, report problems based on TM1 is actually not problems with report, it is problems with TM1 cube design. We need to pay attention to these attributes before starting report development. On the other hand, it is understandable as TM1 cubes behave as database. When there are any changes of their properties, it will have impact on framework manager.