Mondrian Schema - Calculated Member dimension lookup

Question

I am hoping someone can help me with my latest hickup in design my mondrian schema. I use Mondrian 4, Pentaho along with Saiku analytics 3.7.

I am designing a performance metric system based on different KPI's which seems to be working fine. My problem is calculating ratio values in the mondrian schema where I need to lookup a specific dimension and then return the appropriate measure value for a given KPI calculation

<CalculatedMember name="Actual Performance" dimension="Measures">
      <Formula>IIF([Performance].currentmember.name = 'Occupancy', 

      ([Performance].currentmember.prevMember ,[Measures].[Actual Performance1])          

      , [Measures].[Actual Performance1])</Formula> 
      <CalculatedMemberProperty name="FORMAT_STRING" value="#,###"/>
      <CalculatedMemberProperty name="DATATYPE" value="Numeric"/>
</CalculatedMember>

My question is this:

For the line of code above:

([Performance].currentmember.prevMember ,[Measures].[Actual Performance1])

How do I change or specify the (dimension) Performance Metric "Connected Time" to return the "Actual Performance" measure value instead of using the previous member/value?

In pseudocode I want this:

a) find dimension "Connected Time"

b) return the [Measures].[Actual Performance1] value for the "Connected Time" dimension

I actually need to calculate the ratio "Occupancy" As "Total Call Time" divided by "Connected Time" but just need a start for the dimension lookup.

Currently my line of code returns the previous member value as per the screenshot so I am guessing I am sort of on the righ track but stuck now as I am still learning Mondrian. I have lots of these KPI's that needs to be calculated this way.

I need to find a method of calculating the KPI ratios this way so cannot change the star schema the performance metric system is a complete balance scorecard approach for a call centre and works nicely except for getting the ratios calculated correctly.

Nick Nick · Accepted Answer · 2016-04-13T14:39:25

Okay I have found a way to do this after a long struggle.

<CalculatedMember name="Actual Performance" dimension="Measures">
        <Formula>
        IIF
        (
            [Performance].currentmember.name = 'Occupancy',             
            (
                ([Performance].[Total Call Time] ,[Measures].[Actual Performance1]) / ([Performance].[Connected Time] ,[Measures].[Actual Performance1]) * 100              
            ),
            [Measures].[Actual Performance1]
        )           
        </Formula>  
      <CalculatedMemberProperty name="FORMAT_STRING" value="#,###"/>
      <CalculatedMemberProperty name="DATATYPE" value="Numeric"/>
</CalculatedMember>

Mondrian Schema - Calculated Member dimension lookup

1 Answers