Saturday, April 19, 2014

How to make use of TM1 dimension default member in report

When using TM1 as Cognos data source, we use framework manager to publish all cubes for Cognos studios, such as report studio, query studio and analysis studio. As Cognos workspace becomes more and more popular, many business users can directly access Cognos workspace with framework manager. However, sometimes we don’t get any data when drag dimensions into data container, and wonder what the reason behind the scene is. Business users are even more confused with such a situation. This document is intended to answer why there is no data sometimes, and how to make cube design more friendly to studios and workspace..

The rule of default member

All studios allow us to drag some of dimensions from a cube. From cube point of view, we submit only partial tuples to TM1 server. Within a partial tuple one or more member references are omitted. Because a complete tuple is required to locate a pint in the cube space, TM1 server engine needs to fill the missing references. This is very much done by applying the following rules for missing member reference:

If the member reference is not selected, use the hierarchy’ default member.

If the member reference is not selected, and no default member is specified, use the total member if there is one

If the member reference is not selected, and no default member is specified, and there is no total number in hierarchy, then the first member will be used.

As seen, it is possible we get no data for some situations as some dimensions are not selected, but with default member. The following dimensions cause no data very often:

Year dimension without total year in hierarchy

Version dimension without total version hierarchy

Scenario dimension without total Scenario hierarchy

Account dimension without total account hierarchy

Those dimensions should not build as hierarchy from business point of view. However, image that this first year has not data, the first version has no data, the first scenario has no data, and the first account has no data. This is where business user or even we get confused about data availability. In addition, given the fact that there are many dimensions in a cube, we need to specify a default member for each dimension hierarchy.

Setup default member

To setup default member, you need to Control cube }HierarchyProperties in architect

Make default member (plus named levels) showing up in studios

Based on instruction from TM1 manual, function RefreshMDXHierachy() can be used to get default member in effect. however it doesn’t work at all. The easy and quick solution is to reboot the TM1 server. The problem is not about republish framework manager, or in cleaning up cache in Cognos. (It looks like studios retrieve metadata information on the fly from TM1). IBM has confirmed this problem and provided solution below:

{

Unable to see change in Analysis Studio after RefreshMDXHierarchy in TM1 (2013-11-16)

Problem(Abstract)

User goes into the }HierarchyProperties cube in TM1 and changes a default member for a dimension, then runs a Turbo Integrator process with RefreshMDXHierarchy('dimension name') in the Prolog tab to refresh, so that the change is visible on the BI side.

Symptom

The RefreshMDXHierarchy does not appear to work, as the new value is not available in Analysis Studio, which requires a service restart.

Cause

BI package is still using the old 'run time model'.

Resolving the problem

Determine whether CQM or DQM (Compatable Query Mode or Dynamic Query Mode) is being used in BI.

-If using CQM, navigate to CognosInstallDIR\data\cqe\RTModels and remove everything within it (folders and files) so that there is an empty RTModels folder.

-If using DQM, then navigate to CognosInstallDIR\data\mfw4j\PMCs and remove everything within it (folders and files) so there is an empty PMCs folder.

NOTE that you will need to stop BI to perform the actions above.

Alternatively, try republishing the package via FM.

}

Dynamically setup default member

As default member is very useful, we could enhance it to make it even more business user friendly and report friendly. The typical sample is Fiscal year. If we can dynamically specify fiscal year, then business user will get sales, budget or forecast for needed fiscal year by default. The solution is to use rules to specify data from “system parameter” cube. The “system parameter” cube saves all setting for whole system operation.