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.

http://localhost:8080/pentaho/plugin/data-access/api/datasource/dsw/domain

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;

http://localhost:8080/pentaho/plugin/data-access/api/datasource/dsw/domain/{DSWID}

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/classic-engine.properties 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:

tenated_db2

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 : http://javadoc.pentaho.com/bi-platform530/webservice530/

-Ankit Srivastava & Harsh Kumar