Problem

You have a Mondrian Schema uploaded to the BA Server and you would like to modify it in the run time. For instance in our project we change it for every user authenticated to the platform to apply security restrictions. This modification aims to limit records that users are able to see in reports based on that Mondrian Schema.

Solution

This is possible by creating a jar with a class extending Dynamic Schema Processor from Mondrian library.

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"> <Table name="TEST_TABLE" schema="AISDWH" alias=""> </Table> <Dimension type="StandardDimension" visible="true" foreignKey="NAME" name="Name"> <Hierarchy name="Name" visible="true" hasAll="true" primaryKey="NAME"> <Table name="TEST_TABLE" schema="AISDWH"> </Table> <Level name="Name" visible="true" table="TEST_TABLE" column="NAME" uniqueMembers="false"> </Level> </Hierarchy> </Dimension> <Dimension type="StandardDimension" visible="true" foreignKey="NAME" name="Age"> <Hierarchy name="Age" visible="true" hasAll="true" primaryKey="NAME"> <Table name="TEST_TABLE" schema="AISDWH" alias=""> </Table> <Level name="Age" visible="true" table="TEST_TABLE" column="AGE" uniqueMembers="false"> </Level> </Hierarchy> </Dimension> <Measure name="Number of people" column="NAME" aggregator="distinct-count" visible="true"> </Measure> </Cube> </Schema> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 <Schema name = "TestSchema" > <Cube name = "TestCube" visible = "true" cache = "true" enabled = "true" > <Table name = "TEST_TABLE" schema = "AISDWH" alias = "" > </Table> <Dimension type = "StandardDimension" visible = "true" foreignKey = "NAME" name = "Name" > <Hierarchy name = "Name" visible = "true" hasAll = "true" primaryKey = "NAME" > <Table name = "TEST_TABLE" schema = "AISDWH" > </Table> <Level name = "Name" visible = "true" table = "TEST_TABLE" column = "NAME" uniqueMembers = "false" > </Level> </Hierarchy> </Dimension> <Dimension type = "StandardDimension" visible = "true" foreignKey = "NAME" name = "Age" > <Hierarchy name = "Age" visible = "true" hasAll = "true" primaryKey = "NAME" > <Table name = "TEST_TABLE" schema = "AISDWH" alias = "" > </Table> <Level name = "Age" visible = "true" table = "TEST_TABLE" column = "AGE" uniqueMembers = "false" > </Level> </Hierarchy> </Dimension> <Measure name = "Number of people" column = "NAME" aggregator = "distinct-count" visible = "true" > </Measure> </Cube> </Schema>

Now let’s import the schema to 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.

Modifying Mondrian Schema

Now I will slightly modify the original Mondrian Schema (you 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 run time only the part that cannot be evaluated before).

Modified Mondrian Schema <Schema name="TestSchema"> <Cube name="TestCube" visible="true" cache="true" enabled="true"> <Table name="TEST_TABLE" schema="AISDWH" alias=""> </Table> <Dimension type="StandardDimension" visible="true" foreignKey="NAME" highCardinality="false" name="Name"> <Hierarchy name="Name" visible="true" hasAll="true" primaryKey="NAME"> <Table name="TEST_TABLE" schema="AISDWH"> </Table> <Level name="Name" visible="true" table="TEST_TABLE" column="NAME" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never"> </Level> </Hierarchy> </Dimension> <Dimension type="StandardDimension" visible="true" foreignKey="NAME" highCardinality="false" name="Age"> <Hierarchy name="Age" visible="true" hasAll="true" primaryKey="NAME"> <Table name="TEST_TABLE" schema="AISDWH"> <SQL dialect="generic"> <![CDATA[AGE > %PLACE_HOLDER%]]> </SQL> </Table> <Level name="Age" visible="true" table="TEST_TABLE" column="AGE" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never"> </Level> </Hierarchy> </Dimension> <Measure name="Number of people" column="NAME" aggregator="distinct-count" visible="true"> </Measure> </Cube> </Schema> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 <Schema name = "TestSchema" > <Cube name = "TestCube" visible = "true" cache = "true" enabled = "true" > <Table name = "TEST_TABLE" schema = "AISDWH" alias = "" > </Table> <Dimension type = "StandardDimension" visible = "true" foreignKey = "NAME" highCardinality = "false" name = "Name" > <Hierarchy name = "Name" visible = "true" hasAll = "true" primaryKey = "NAME" > <Table name = "TEST_TABLE" schema = "AISDWH" > </Table> <Level name = "Name" visible = "true" table = "TEST_TABLE" column = "NAME" type = "String" uniqueMembers = "false" levelType = "Regular" hideMemberIf = "Never" > </Level> </Hierarchy> </Dimension> <Dimension type = "StandardDimension" visible = "true" foreignKey = "NAME" highCardinality = "false" name = "Age" > <Hierarchy name = "Age" visible = "true" hasAll = "true" primaryKey = "NAME" > <Table name = "TEST_TABLE" schema = "AISDWH" > <SQL dialect = "generic" > <![CDATA[AGE > % PLACE _ HOLDER % ] ] > </SQL> </Table> <Level name = "Age" visible = "true" table = "TEST_TABLE" column = "AGE" type = "String" uniqueMembers = "false" levelType = "Regular" hideMemberIf = "Never" > </Level> </Hierarchy> </Dimension> <Measure name = "Number of people" column = "NAME" aggregator = "distinct-count" visible = "true" > </Measure> </Cube> </Schema>

As you can see I modified Table so that sql query when retrieving the Age will have a where cluase. The where clause will filter data using Age column by the value that will be specified using Dynamic Schema Processor which will change the place holder into a proper value. Also at this point we have to reimport the schema in the 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 my case I extend FilterDynamicSchemaProcessor class that implements this interface). As you can see below the code is really simple and does not need much clarification, you can check the entire code on my 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; } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 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 BA Server

In order to use the newly created Dynamic Schema Processor you have to add the jar to the following location: /biserver-ee/tomcat/webapps/pentaho/WEB-INF/lib/. Then you have to edit the configuration of the schema in the BA Server to specify that this particular DSP should be used for this Schema. You 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.

Links