Page 1 of 1
Different level of drill down based on measures
Posted: Wed Jul 23, 2014 7:25 am
by 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
Re: Different level of drill down based on measures
Posted: Wed Jul 23, 2014 8:32 am
by 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.
Re: Different level of drill down based on measures
Posted: Wed Jul 23, 2014 11:12 am
by 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.
Re: Different level of drill down based on measures
Posted: Wed Jul 23, 2014 11:33 am
by 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.
Re: Different level of drill down based on measures
Posted: Wed Jul 23, 2014 11:41 am
by rmackenzie
A poster called Sachin talks about it
here
Re: Different level of drill down based on measures
Posted: Wed Jul 30, 2014 11:46 am
by 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 ?
Re: Different level of drill down based on measures
Posted: Wed Jul 30, 2014 12:46 pm
by rmackenzie
sreesuku wrote: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.