How to dynamically change a Mondrian schema at runtime in Pentaho

Dynamic Schema Processor: Modify Mondrian Schema at runtime in Pentaho

Business Need

Consider we have a Pentaho Mondrian Schema uploaded to the Pentaho BA Server and we would like to modify it in the runtime.

For instance, let’s say we have to show the data related to the user logging into the server then we have to modify the schema dynamically in order to achieve this. This aims to limit records that users are able to see in reports based on the Mondrian Schema.

Solution

Mondrian allows us to define user roles on the Schema itself. But we cannot add the data restriction dynamically.

We can add various roles and then we can restrict the data based on that role. However, it is a very lengthy process and it is not advisable if we have many users.

Hence, Mondrian has provided a dynamic schema processor which dynamically changes the schema at runtime.

To do that we need to create a class that implements a DynamicSchemaProcessor interface (we need to extend the filterDynamicSchemaProcessor/ localizingDynamicSchemaProcessor which implements the interface).

Once the class is created, we need to upload the JAR into the server and also we need to call the class (the class created for Dynamic Schema) where the Schema is uploaded.

Steps

Test data and Mondrian Schema

This is the table we will be using for the demo.

And then Mondrian Schema on top of it, created using Schema Workbench.

Original Mondrian Schema

<Schema name="TestSchema"
  <Cube name="TestCube" visible="true" cache="true" enabled="true" &gt
    <Table name="TEST_TABLE" schema="AISDWH" alias=""&gt
     </Table&gt
    <Dimension type="StandardDimension" visible="true" foreignKey="NAME" name="Name"&gt
      <Hierarchy name="Name" visible="true" hasAll="true" primaryKey="NAME" &gt
       <Table name="TEST_TABLE" schema="AISDWH"&gt
       </Table&gt
        <Level name="Name" visible="true" table="TEST_TABLE" column="NAME" uniqueMembers="false"&gt
        </Level&gt
      </Hierarchy&gt
   </Dimension&gt
   <Dimension type="StandardDimension" visible="true" foreignKey="NAME" name="Age"&gt
     <Hierarchy name="Age" visible="true" hasAll="true" primaryKey="NAME"&gt
        <Table name="TEST_TABLE" schema="AISDWH" alias=""&gt
        </Table&gt
        <Level name="Age" visible="true" table="TEST_TABLE" column="AGE" uniqueMembers="false"
        </Level&gt
     </Hierarchy&gt
     </Dimension&gt
    <Measure name="Number of people" column="NAME" aggregator="distinct-count" visible="true"
    </Measure&gt
   </Cube&gt
 </Schema&gt

Now let’s import the schema to Pentaho BA Server. Press Manage Data Source button in Home Page, then open the link Import Analysis from dropdown menu under the icon on the left to New Data Source button. Select the file with our Mondrian Schema and a data source that links to our database.

If we create an analyzer report using it we will get something like this.

Now we will slightly modify the original Mondrian Schema (we could do this modification directly from DSP, but for the sake of the demo implementation it is just easier to do it like that and then modify in the runtime only the part that cannot be evaluated before).

Modified Mondrian Schema

 <Schema name="TestSchema"&gt
   <Cube name="TestCube" visible="true" cache="true" enabled="true"&gt
     <Table name="TEST_TABLE" schema="AISDWH" alias=""&gt
    </Table&gt
     <Dimension type="StandardDimension" visible="true" foreignKey="NAME" highCardinality="false" name="Name"&gt
      <Hierarchy name="Name" visible="true" hasAll="true" primaryKey="NAME"&gt
        <Table name="TEST_TABLE" schema="AISDWH"&gt
         </Table&gt
         <Level name="Name" visible="true" table="TEST_TABLE" column="NAME" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never"&gt
         </Level&gt
       </Hierarchy&gt
     </Dimension&gt
     <Dimension type="StandardDimension" visible="true" foreignKey="NAME" highCardinality="false" name="Age"&gt
       <Hierarchy name="Age" visible="true" hasAll="true" primaryKey="NAME"&gt
         <Table name="TEST_TABLE" schema="AISDWH"&gt
          < SQL dialect="generic"&gt
            ![CDATA[AGE > %PLACE_HOLDER%]]
          </SQL&gt
         </Table&gt
         <Level name="Age" visible="true" table="TEST_TABLE" column="AGE" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never"&gt
         </Level&gt
       </Hierarchy&gt
     </Dimension&gt
     <Measure name="Number of people" column="NAME" aggregator="distinct-count" visible="true"&gt
    </Measure&gt
   </Cube&gt
 </Schema &gt

As we can see the modified Table, so that SQL query when retrieving the Age will have a where clause. The where clause will filter data using Age column by the value that will be specified using Dynamic Schema Processor which will change the placeholder into a proper value. Also at this point, we have to reimport the schema in the Pentaho BA Server (the same way as we added it originally).

Creating Dynamic Schema Processor

Now let’s create a Dynamic Schema Processor that will modify the original Mondrian Schema so that when we use age dimension in the analyzer only records for people above certain age are displayed. To do that we need to create a class that implements a DynamicSchemaProcessor interface (in our case we extend FilterDynamicSchemaProcessor class that implements this interface). As we can see below the code is really simple and does not need much clarification, we can check the entire code on GitHub (to see for example how to import Mondrian dependencies with FilterDynamicSchemaProcessor –sources in GitHub).

Dynamic Schema Processor

package com.thejavatar.blog.mondriandsp;

import mondrian.olap.Util;
import mondrian.spi.impl.FilterDynamicSchemaProcessor;

import java.io.InputStream;

/**
* Created by Lukasz Janicki (contact@thejavatar.com) on 22/07/2015.
*/
public class ChangeAgeDynamicSchemaProcessor extends FilterDynamicSchemaProcessor {

@Override
protected String filter(final String schemaUrl, final Util.PropertyList connectInfo, final InputStream stream)
throws java.lang.Exception {
String originalSchema = super.filter(schemaUrl, connectInfo, stream);
String modifiedSchema = originalSchema.replace("%PLACE_HOLDER%","24");
return modifiedSchema;

}

}

Configure Dynamic Schema Processor in Pentaho BA Server

In order to use the newly created Dynamic Schema Processor we have to add the jar to the following location: /biserver-ee/tomcat/webapps/pentaho/WEB-INF/lib/. Then we have to edit the configuration of the schema in the BA Server to specify that this particular DSP should be used for this Schema. We need go again to Manage Data Sources select the schema and then click Edit from drop-down menu and then add a new parameter called DynamicSchemaProcessor with value linking to your class (see picture below).

Now if we create the same report we will get different results than in the beginning.