Different level of drill down based on measures

[sreesuku]

Hi Team,

My TM1 cube has the below dimensions.

Period
Measures
Sectors

Sector Hierarchy is Sectors --> Industries --> Dept heads --> Accounts --> IDs

Availability of measures are at different levels. Eg : Measure1 is ID level, Measure 2 at Dept head level, Measure 3 at Industry level.
I need to display measure 1,2,3 by Sectors in a view. From this view we should be able to drill down. But for measure 3 it should not show any values against Dept Heads or ID level

How can I create the dimension for this kind of a view in TM1 ?

Regards
Sree

[rmackenzie]

It sounds though if you make Measure1, Measure2 and Measure3 string measures then you can load data to the consolidation points of the Sector hierarchy to get the 'effect' you require. If you're doing this with numeric data which would normally aggregate up through the dimensions then be aware that numbers entered as strings don't consolidate.

I assume this is a cube that serves a very specific reporting outcome? Otherwise, you might want to check your design.

[sreesuku]

Hi,

I am working with Numeric data.
You are right. I am using my TM1 cube which is used to create a Cognos BI Report.

[rmackenzie]

There was a post recently with a method of how to make a BI report output TM1 string data which you could search for. It could solve your problem if you load your data as strings.

[rmackenzie]

A poster called Sachin talks about it here

[sreesuku]

Hi All,

I was able to load data at consolidated level using a string measure. Its displaying fine in reports. As per my understanding, these values don't aggregate.
Now I am becoming greedy here

Is there a way I can use 2 string measures for calculation ? Eg : for Addition or Percentage.
May be by converting them to a number ?

[rmackenzie]

Is there a way I can use 2 string measures for calculation ? Eg : for Addition or Percentage.
May be by converting them to a number ?

You can do something like:

Code: Select all

['Calc_Measure'] = S:
STR ( 
 NUMBR ( DB ( 'Cube', !Period, 'Input_Measure_1', !Sector ) ) +
 NUMBR ( DB ( 'Cube', !Period, 'Input_Measure_2', !Sector ) ),
10, 2 );

It's hard to recommend doing it that way, even though it is technically possible. It's actually really counter-intuitive for TM1 - you should possibly consider looking at a numeric cube with normal aggregations and rules etc and then build a custom string cube just for the reporting via BI. The reporting cube with string measures can cherry pick the results from the numeric cube rather than doing a bunch of string->number->string conversions in the single cube.