Multi-tenant support for prompts in Dashboard

Many times we require having multi tenant environment for Pentaho customers. There are commonly known methods those are already designed for this purpose like “IDBDataSource” customization can be used to return custom data source name based upon a value in session for currently logged in user. When it comes to achieve multi-tenancy for prompts in dashboard without maintaining different connections for different users; choices are few and not wildly known.

Pentaho v5 comes with a bunch of rest based web services; one of those is to list down and see details of a data source in repository those are created using Data Source Wizard.


Looking at the results of this web service; we can derive the fact that Pentaho stores these data sources in XMI format. If we further look at the rest service that downloads a specific data source definition;


We can find out complete structure of how Pentaho stores a data source looking at the downloaded zip file via above rest service and we come to know that it stores the queries already compiled like prepared statements and are not parameterized.

Fact being that Pentaho stores data sources in XMI format; leads to a conclusion that we can use customization of SQLGenerator to achieve multi tenancy in dashboards directly. We actually tried this and it is working for multi tenant environment. SQL Generator is a special class that is called when Pentaho Metadata queries the database.  There are two methods that can be overwritten.  The first is preprocessQueryModel and is the recommended method to overwrite.  It is called before the SQL is generated.  The usual approach is to simply add an AND condition to the query to restrict data, such as by building.  The second method, processGeneratedSql, is called after the SQL is generated.  While it is possible to modify this query, it would involve parsing and modifying the string query.

Steps for customizing preprocessQueryModel  method are below at a glance.

  1. Create a java class that extends pentaho.metadata.query.impl.sql.SqlGenerator class and override preprocessQueryModel method. Example is following code snippet.tenated_db1
  2. Compile class file and export it in a jar file at tomcat/webapps/pentaho/WEB-INF/lib
  3. pentaho-solutions/system/pentahoObjects.spring.xml should be modified to change the class defined for the sqlGenerator The following shows an example of the bean mapping: <bean id=”sqlGenerator” class=”com.mycompany.CustomSqlGenerator” scope=”prototype” />
  4. webapps/pentaho/WEB-INF/classes/ should be modified as well by defining a new parameter: pentaho.reporting.engine.classic.extensions.datasources.pmd.SqlGeneratorClass=com.mycompany.CustomSqlGenerator

Once done, restart Pentaho and load a XAction file to set SESSION_CUSTOMERNUMBER variable that we are trying to read in java code. Create a new connection using data source wizard that uses SampleData as underlying connection:


Now if we use TenatedDataSource in our dashboard; our SQLGenerator class will automatically pick the session variable SESSION_CUSTOMERNUMBER from current user session and append a where clause in final query like this:tenated_db3

Note that while creating data source, we have not defined a where clause and it is being generated on the fly as per the current session value for SESSION_CUSTOMERNUMBER variable. That is what we wanted to achieve.

Ref :

-Ankit Srivastava & Harsh Kumar

5 thoughts on “Multi-tenant support for prompts in Dashboard

  1. Ankit/Harsh, Thank you for this amazing post. Could you help me out on viewing the logs? I am unable to see anything that I print in preprocessQueryModel.

    • The code in post uses System.out.println() for logging and these prints can be seen in the console (not in any file). But if you want it to be in a file, log4j.xml (biserver-ce\tomcat\webapps\pentaho\WEB-INF\classes) has to be altered as followed:

      <category name =”com.mycompany.CustomSqlGenerator”>
      <priority value="WARN" />
      </category >

      Once done, the logs of preprocessQueryModel should be there in pentaho.log file.

    • I solved the logging problem by modifying logger’s config file. I have one more question, Ankit. This sqlGenerator perfectly works with a dashboard. However, it does not seem to work with interactive reports and Analyzer reports. How do we achieve multi-tenancy in these kinds of reports?

      • Ankit/Harsh : I also solved the above problem – achieving multi-tenancy in analyzer reports by writing a custom Dynamic Schema Processor (DSP). Also, the 4th point in the steps you have mentioned needs a little correction. The line in a should be org.pentaho.reporting.engine.classic.extensions.datasources.pmd.SqlGeneratorClass=com.mycompany.CustomSqlGenerator. 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s