Monthly Archives January 2011

User-sensitive Dashboard in Pentaho

Posted by admin on January 06, 2011  /   Posted in BI and Custom Development

This time I’d like to address a common task that most of us will encounter at one time or another when we deal with this Business Intelligence (BI) stuff.

The task: Creating info dashboard(s) that presents relevant information based on who the user is.

Implicit in that task is the importance to 1) present information for that user and 2) *only* information for that user.  A good example would be a dashboard for a salesperson.  It should contain information about contacts and customers as it pertains to the said salesperson. No less, not more.

Depending on which way you choose to build your dashboard, the method to accomplish the task differs a bit.  Now you may ask, what are the ways to create dashboards in Pentaho?  As of this writing, there are at least two common ways, using CDF or CDE.  CDF is the mainstay method which has been around for a while, CDE is a new interactive tool produced and contributed by the talents at Webdetail.pt (that’s a web site in Portugal).

NOTE: To read on, it’s best to familiarize yourself with one of the building blocks of Pentaho, the xaction.  In a nutshell, xaction is an XML-based declarative mini-language that allows us to fetch data either from a data source or from a mondrian cube, and outputs the data someplace else (the session object is a good example).

The Question of Identity

To be able to tailor the content of the dashboard with user-sensitive information, we have to get the currently logged in user from somewhere.  From within Pentaho, you can get this information (and more) by tapping into built-in objects provided by Spring Security which is the backbone of the Pentaho authentication system.  The next question is how do we access this information?

The Road Map

Before we answer the previous question, let’s lay out the steps to accomplish this task.  First, we need to decide if we need to do some lookup to map the username (the name users use to log in) into people-friendly name, for example username=jdoe into ‘John Doe’.  If your mondrian schema requires this (as do mine), there is an extra step that you need to do.  Otherwise, skip the next section, or read it anyway since it can also be useful in other scenarios.

Seeding the Session

While your login username will already be contained inside the ‘security’ object, we need a place to put the people-friendly name.  A good one is inside the user session, which will retain this information as long as the session is still valid.  And fortunately for us, there is already a hook to put some xaction in Pentaho.  Let me introduce you to pentaho-solutions/system/sessionStartupAction.xml.

This file allows us to insert an xaction that will guaranteed to be executed when a user login.

Insert the following snippet:

<bean>
<property name=”sessionType” value=”org.pentaho.platform.web.http.session.PentahoHttpSession”/>
<property name=”actionPath” value=”Analysis/rules/salesman-username-to-name.xaction”/>
<property name=”actionOutputScope” value=”session”/>
</bean>

Explanation: We want the Pentaho biserver to execute the actions defined in salesman-username-to-name.xaction which is located inside a path starting from the solution called Analysis.  And the third line states that the output of the xaction will be available to be accessed in the ‘session’ object.  This bean definition represents one action, if you need two, define two beans.

Next, we need to write the .xaction file.  By the way, the best way to know the mini-language in which you script these files, there are good and working examples under pentaho-solutions/bi-developers.

Here, following the example, I created a directory called ‘rules’ under my main solution path (which itself is right under the pentaho-solutions directory).

I will highlight the important parts (pentaho-solutions/Analysis/rules/biz-rule.xaction):

<inputs>
<user type=”string”>
<sources>
<security>principalName</security>
</sources>
</user>
</inputs>

Explanation: This section defines what would be available in the .xaction as the variable ‘user’ which is seeded with the current value from Spring Security’s principalName object.  The tag surrounding the principalName input defines the scope where the input will be searched within.

<outputs>
<userFullName type=”string”>
<destinations>
<session>fullName</session>
</destinations>
</userFullName>
</outputs>

Explanation: This section tells us that at the end of the execution of this .xaction, the session object will contain an attribute called ‘salesmanName’ whose value will be determined by the section below.

<component-name>SQLLookupRule</component-name>
<action-type>fetch full name</action-type>
<action-inputs>
<user type=”string”/>
</action-inputs>
<action-outputs>
<query-result type=”string” mapping=”rsFullName”/>
</action-outputs>
<component-definition>
<query>SELECT name FROM some_transactional_table where user_login_id='{user}'</query>
<live>true</live>
<jndi>JNDIDataSource</jndi>
</component-definition>

Explanation: This code defines a SQL query which accepts an input called ‘user’, and pass it into a sql query whose result set will be available to the rest of the .xaction as ‘rsFullName’.

<component-name>JavascriptRule</component-name>
<action-type>Extract </action-type>
<action-inputs>
<rsFullName type=”result-set”/>
</action-inputs>
<action-outputs>
<userFullName type=”string”/>
</action-outputs>
<component-definition>
<script>
var userFullName = rsFullName.getValueAt( 0, 0 );
userFullName= userFullName + ”;
</script>
</component-definition>

Explanation: Different from the previous code that is a SQL query, an .xaction can also contain javascript code that acts on the available variables.  The one above simply fetch the value out of the SQL query result, and assign it to a declared variable, which has to have the same name as the <output> section above.

There you have it, because we invoke this .xaction files in the startup of a user session, any dashboards created can use any values initialized by it.

Using the Session variables in Dashboards

Now we are ready to create the user-sensitive dashboards.  I am going to illustrate this using CDE which has a clean separation between layout, behavior and data source.

Using CDA (yet another project from Webdetails.pt) to manage the data source, we can simply use the session variables initialized by the above .xaction within the queries that made up the CDA contents.

If you are using MDX, it will look something like this:

select {[Measures].[Total Month Sales]} ON COLUMNS,
{[Region].[All Regions]} ON ROWS
from [Sales]
where [Salesperson].[${fullName}]

The resulting dashboard will dutifully display information that is relevant to the logged in user.  It’s pretty impressive when you think about it.


We serve businesses of any type and size
Contact Us Today