CODEWITHSUNDEEP
mdxpentahomondrian
NorRen
NorRen

Reputation: 721

Group Calculated Member Aggregates by partly related Dimension

I'm using Pentaho-CE 4.8 with Saiku Plugin 2.6 which uses Mondrian 3.6.5.

In a Mondrian Schema I defined a virtual Cube with a calculated Member which consists of two Virtual Measures. These Virtual Measures come from two Cubes which have two Dimensions in common. One of the Cubes has a degenerated Dimension which is also used in the virtual Cube.

I want to group the Calculated Member by a Dimension which only one of the Virtual Measures is related to but I'm failing at this point.

Pseudo Schema:

<Time Dimension>

<Cube 1>
  <Dimension Usage: "Time Dimension"> 
  <Degenerated Dimension> 
  <Measure 1>
</Cube1>

<Cube 2>
  <Dimension Usage: "Time Dimension"> 
  <Measure 2>
</Cube 2>

<Virtual Cube>
  <Virtual Measure "Cube 1 Measure 1"> 
  <Virtual Measure "Cube 2 Measure 2"> 
  <Virtual Dimension "Time Dimension"> 
  <Virtual Dimension "Cube 1 Degenerated Dimension" 
  <Calculated Member: [Virtual Measure "Cube 1 Measure 1"] / [Virtual Measure "Cube 2 Measure 2"]
</Virtual Cube>

In saiku I get results for the virtual and calculated measures as long as I do not use the "Cube 1 Degenerated Dimension". If I use it on Rows/Colums or as a filter, only values for <Virtual Measure "Cube 1 Measure 1"> are shown, since this Measure comes from a cube which has a relation to that dimension.

Is there a way / workaround how I can achieve that also the CM is shown for this Dimension? Because in theory Mondrian could do the following:

  1. Get Virtual Measure "Cube 1 Measure 1" in dependence of "Cube 1 Degenerated Dimension" and the Time Dimension and finally aggregate the values.
  2. Get Virtual Measure "Cube 2 Measure 2" in dependence of the Time Dimension only.
  3. Do the calculation (Devide "Cube 1 Measure 1" / "Cube 2 Measure 2")

Upvotes: 0

Views: 1177

Answers (1)

NorRen
NorRen

Reputation: 721

I found a solution for the problem:

Use ValidMeasure() Function like this:

    <Calculated Member: [Virtual Measure "Cube 1 Measure 1"] / ValidMeasure([Virtual Measure "Cube 2 Measure 2"])>

The Valid Measure Function will tell Mondrian, that this Measure has non-joining dimensions which can be ignored. Measure 2 will get joined against other applied dimensions and a value will be obtained, which then can be used for the calculation.

Upvotes: 0

Related Questions