Dynamic Schema Processor : Modify Mondrian Schema in run time

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.

data

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

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.

import

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

analysis

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).

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 FilterDynamicSchemaProcessorsources in GitHub).

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).

edit1

edit2

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

analysis2

Links

20 thoughts on “Dynamic Schema Processor : Modify Mondrian Schema in run time”

  1. Hi,

    I tried to implement this on my localhost. Even though the data is setting in place of %placeholder% but when i am running it through jPivot, the value is not getting replaced with %placeholder%. The below error is coming :

    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘%PLACE_HOLDER%)) group by CUSTOMER_W_TER.COUNTRY order by ISNULL(`CUSTOMER_W’ at line 1

    Kindly help me with this.

    Regards,
    Nitish

    1. Hi Nitish,

      I am sorry but this will not work with jPivot. DynamicSchemaProcessor interface is internal Pentaho BA Platform class and as such the above solution will work only with Pentaho BA (Business Analytics) Platform.

      Kind regards,
      Lukasz

      1. Hi Lukasz,

        Thanks you for the reply. I wasted a whole week just to make the DSP work on the jPivot and I finally gave up yesterday. Meanwhile, I was curious to know whether DSP would work with Saiku Analytics plugin which is present in CE server. Does the DSP only work with Pentaho Analysis tool?

        Kindly help me with this.

        Thanks,
        Nitish

        1. Hi Nitish,

          Sorry to hear that you spent so much time in vain. What do you mean by CE server? Pentaho BA Server Community Edition? I have never played with Saiku Analytics, but I am afraid it will not work with it as well. My memory of Pentaho Platform’s internals executing DynamicSchemaProcessor is blurry now, but I am pretty confident this functionality is highly coupled with Pentaho Analysis.

          However, here (http://thejavatar.com/apply-dynamic-schema-processor-to-a-report-created-in-the-report-designer/) I use the same technique when generating static reports from a template created initially in Report Designer and only later uploaded to BA Server. So maybe (but just maybe) this could also work with Saiku when run inside BA Server.

          Regards,
          Lukasz

          1. Hi Lukasz,

            Sorry for the confusion. Yes i was talking about the Pentaho BA Server Community Edition 6. it isnt working for jPivot but after one fix its working for Saiku Analytics and thankfully now the code is working now. Thanks a lot for this wonderful post.

            Regads,
            Nitish

          2. Hi Lukasz,

            The tag which would read the Dynamic Schema Processor was commented out inside the plugin.spring.xml file present inside the saiku folder. We just needed to uncomment that section and also since saiku has its own mondrian lib configuration, we need to add the JAR inside the lib folder of saiku.

            The location is pentaho solutions/system/saiku.

            Kindly let me know if you need any help.

            Regards,
            Nitish Kumar Mishra

    1. Hello Nitish,

      I guess, inside Mondrian Schema you could put %PLACE_HOLDER% instead of following lines:

      <SQL dialect="generic">
      < ![CDATA[AGE > %PLACE_HOLDER%]]>
      </SQL%gt;

      Then in DSP if you know a ‘where clause’ is required you replace the placeholder with:

      <SQL dialect="generic">
      < ![CDATA[AGE > 23]]>
      </SQL>

      If not you simply replace the placeholder with an empty string.

      Is that what you were looking for?

      Regards,
      Lukasz

      1. Thanks Lukasz for the input. I have already implemented the above approach.

        But now the requirment is, the code should dynamically alter the xml file during runtime instead of adding the %PLACEHOLDER% on the schema.

    2. Hi Nitin

      Need your for Implementing the DSP in Saiku3.0 on BA Server Community Edition 6 i have follow all steps in this article i am getting this Error

      executing sql [
      select
      “dim_client”.”clientname” as “c0”
      from
      “public”.”dim_client” as “dim_client”
      where
      (clientid = %client_id%)
      group by
      “dim_client”.”clientname”
      order by
      “dim_client”.”clientname” ASC NULLS LAST]

      Please Help me.

      Also i have one query how i get the the value from request url querystring

      1. Hi Rohit,

        I am sorry but i couldnt get your error properly. Can you please let me know in detail.

        In order to get the query value from the URL, you have to write the query in a properties and you have add the properties file into the server folder.

        Regards,
        Nitish

  2. How to use this ‘DynamicSchemaProcessor’ in Jasper Report Server. Please suggest us required changes to make in mondrian property file in the server

  3. Hi Lukasz,

    Your blog was of great help to me. It helped me to understand the total DSP properly. Now i am in a fix again. Please help me out of this.

    If i want to add multiple CDATAs in my schema. How can i do that?

    For example: in SQL, when we want multiple conditions then we use AND in where clause. How can we implement the same using the CDATA in mondrian?

    Kindly provide your inputs.

    Thanks,
    Nitish

  4. Thanks a lot for this amazing post. It helped me a lot while writing my first DSP. However, I have a query. Can we enable our custom DSP for all data sources at once? i.e. the example suggests that DSP can be enabled on a data source basis (DynamicSchemaProcessor = customclass). In a similar way, can we enable DSP on a connection level? So that all data sources using that particular connection will have custom DSP enabled.

    1. It’s a yes and no.

      No: I don’t remember seeing anywhere a built-in feature to enable a specific DSP across a particular group of datasources/connections.

      Yes: If the object responsible for applying DSPs on your Mondrian Schemas is a spring defined bean (I don’t remember if that’s the case) it should be fairly easy to override it with your custom implementation.

Please join discussion