Introduction to Dynamic Schema Processor- Modifying Mondrian Schema in Runtime
What is Mondrian?
Mondrian is an open source OLAP Server which is written in Java. It supports MDX (Multi Dimensional Expression) query language and the XML for analytical and olap4j interface specifications. It reads from SQL and other data sources and aggregates data in a memory cache.
What is Dynamic Schema Processor?
A dynamic schema processor is used to dynamically change a Mondrian schema at runtime. Once the Mondrian schema is uploaded on the BA Server and you’d like to modify it on the runtime, then we need to implement Dynamic Schema Processor. 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 modification aims to limit records that users are able to see in reports based on the Mondrian Schema.
Why do we need Dynamic Schema Processor(DSP)?
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.
Architecture of Dynamic Schema Processor:
Above is the basic flow of how Dynamic Schema works in the Pentaho Server:
- User logs in Pentaho BA Server.
- The JAR loaded inside the server is called by the server internally and the class calls the DSP.
- The DSP class is called.
- The session variable sets the username which is passed into the SQL Query.
- The username is passed to the SQL query defined in the properties file(upload the property file in the classes folder) and it fetches the corresponding ID from the user table.
- The query is generated and the ID of the corresponding user is fetched.
- DSP returns the ID to the schema.
- User then access the Analyzer report inside the BA Server.
- The cube calls the DSP class (the class name is defined inside the datasource configuration).
- The DSP modifies the cube by adding a where clause condition to the schema to filter out the data based on the ID for the corresponding user.
- The data for that particular ID is generated.
- The analyzer report is generated for the particular user logged in.
Exception: In case of the Admin user, the schema file will not add the where clause for filtering and the user will have access to the whole data.
So this is how the Dynamic Schema Processor works in a Pentaho server. It is an added feature that Pentaho has provided to provide enhanced security on the data that are getting generated on the reports. The reports gets modified dynamically based upon the user who logs into the server.
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Nitish Kumar Mishracubes DSP dynamic schema processor Mondrian Cube pentaho User roles and security in pentaho