* Rubber band drill down * Remove X-axis * Remove Y-axis * Pre-selection of bars * Conditional coloring and etc. :-)

Hello Every one..!!! :-)

This article is a collection of various tips from the pentaho forum and presented here as a document..

This will be useful as a POC on below topics & the best and easiest way of exploring all these is to download the example from this site and open in Edit mode in you PUC and navigating through all the properties & functions.

This example is an outcome of my R&D.

Features of this work out :

1) Remove X-Axis
2) Remove Y-Axis
3) Pre Select 'N' number of Bars
4) Base Axis(or X-axis) label font color, font size
5) Base Axis label rotation
6) Data label color for Bars
7) $ signed value(lable style) on data bar with bold font.
8) Second Y Axis(plot2) with line chart
9) Line color and value on the line is same.

10) Pre selected related information is displayed
    in pie chart as intra chart commnication when the dashboard loads

11) Rubber band based selection of intra chart drill down
   (i.e., multiple bars selection drill down). 

12) Giving multiple default values for a parameter
   (Example of custom parameter).

Image 1: Initial Loading of Dashboard

Image 2:Applying Rubber Band drill down(i.e., Selection of multiple Bars for drill down)

Image 3: After releasing rubber band check how the drill down happened

Download the Example :
Click Me

NOTE :
Queries are taken to display some data for charts.. might not related to requirement. 

Thank you :-)

Source of R&D :
Pentaho forum & experiments.

Core part of this Example is explained here : 

Main Feature : Rubber band drill down on the same dashboard (Intra chart rubber band based drill down ) OR multiple bars selection drill down on the same dashboard. 

 Scenario:
Lets say you have a bar chart and pie chart on your dashboard. 
Bar Chart : 
X-axis : Cities ( you can call cities as categories here).
Pie Chart:
Cities are categories for pie chart

i.e., Pass multiple cities from Bar chart to Pie chart by selection (i.e., select multiple bars and display the related information on pie chart with the same categories).

Step 1 : Create a custom parameter (param_city) and give default values for it. 

The way of giving default values(multiple values are input to a parameter) using custom parameter is:
In JavaScriptCode window write below

["Lebanon","Burbank","Portland"] 

NOTE : the names inside double quotes are city names

Step 2 : Rubberband selection functionality happens in 2 places and pre selection of bars happens in 1 place as given below respectively

userSelectionAction : 

function f(selectingDatums){
    var selectedCatsSet = {};
    var selectedCats = [];
    selectingDatums.forEach(function(datum) {
        var cat = datum.atoms.category.value;
        if(selectedCatsSet[cat] !== true) {
             selectedCatsSet[cat] = true;
             selectedCats.push(cat);
        }
    });

    // Return a new array, containing all datums having the same categories
    return this.chart.data.datums([{category: selectedCats}]).array();
} 

selecitonChangedAction:

function f(selectedDatums) {
  // Extract the category values of selected datums, assuming that there is only one
  // category dimension and that the name of the dimension is "category" (the default)
  var categories = selectedDatums.map(function(datum) { return datum.atoms.category.value; })
 
  Dashboards.fireChange('param_city', categories);
   
}  

renderCallback:

function f(){
    //var datums = this.chart.data.datums([{series: 'sum'}]);
    var datums = this.chart.data.datums([{category: ["Lebanon","Burbank","Portland"] }]);
    pvc.data.Data.setSelected(datums);
}
 
 Step 3 : Pie Chart & It's Query
1) Add parameter(param_city) to chart component and also give it as listener.
2) For the query of pie chart add parameter(the type here is StringArry - Not just String) and pass the parameter in SQL.
3) Note that the parameter has to catch multiple values.. i.e., your WHERE clause should be as follows WHERE IN (${param_city}) . If you ignore () parenthesis you will not find the result properly. 
 
 That's it.. you have done with multiple bar selection drill down..

:-)

Sadakar
BI developer(Jasper/Pentaho/Talend ETL)

Access Control Concept in Pentaho Schema Workbench & Saiku Analytics tool: Display/Restrict Role based Cubes, Dimensions, Hirachies & levels

It's been a long time, I have explored some concepts in Pentaho Analysis using PSW(Pentaho Schema Workbench).

Here is a small workout on giving access of CUBES, Hierarchies,members to different users based on roles assigned to them.

You may not find what you need exactly in this article but I can say  surely that,  you will get some idea on below topics


1) Creating Users & Roles in the BA Server.
2) Assigning Roles to Users.
3) How BA server will identify the roles created in PSW with the roles in BA Server.
4) Developing a Schema of 2 sample CUBES
5) Scenario on the schema


Software  Setup:
Pentaho BA Server 5.x
PostgreSQL Server 9.x [Food Mart Database]
Pentaho Schema Workbench :3.8.0.0-209
Saiku Analytics : 2.6-PENTAHO5-SNAPSHOT (TRUNK)


Assuming you have initial idea on how to design a sample CUBE..
(For creating sample cubes you can follow this tutorial :
http://pentaho-bi-suite.blogspot.in/2013/12/pentaho-schema-workbench-simple-example.html
)

 Concept : Access Control
Problem Statement : 
1) Give Access of CUBE 1 to User 1 who is assigned with Role 1
       Schema grant, Cube grant, Hierarchy grant, member grant .
2) Give Access of CUBE 2 to User 2 who is assigned with Role 2


Step 1 :  How to built role mapper communication in BA Server for created roles in mondrian ?

• Enable mondrian user/role mapper in pentahoObjects.spring.xml 
• File location : /Pentaho/biserver-ce/pentaho-solutions/system

i.e., Uncomment   below code :

  <!--  This mondrian user/role mapper assumes that roles from the platform also exist in mondrian -->

  <!--
  Disabled by default in 3.5.2. In trunk, this should be enabled.
    -->
   
  <bean id="Mondrian-UserRoleMapper"
        name="Mondrian-One-To-One-UserRoleMapper"
        class="org.pentaho.platform.plugin.action.mondrian.mapper.MondrianOneToOneUserRoleListMapper"
        scope="singleton" />
 
NOTE : If your BA server is already running , just stop and restart it.

That's it.. Now the Roles assigned in schema will mapped to the roles assigned in BA server.(for users).



Step 2 :  Creating Schema with 2 CUBES
Let us take food-mart database(modelled for multidimensional database) and design below CUBES.

CUBE 1 ---> Sales
CUBE 2----> HR

Cube Design Image is shown below :

 

Download the schema file here :  Click Me
Open it with your PSW...

The design has the following concepts :
 i) Dimension Usage  : 
Create a dimension outside the CUBES( i.e., Right Click Schema -> Create Dimension) and make use of it in CUBES as Dimension Usage.

(i.e, If 2 CUBES use the same dimension, we need not to create it 2 times. We can create one time and use that dimension which ever the CUBE needs for it's specific purpose).

In this example  schema :  I made use of two shared dimensions as Dimension Usage. Those are  Store and Time

ii) Calculated Member(CM).
To calculate the measures.. for example : To find profit in this example.
It will results as a new measure in the output.


Step 3 : How to create Users  & Roles?  
Users :  

• Log-in into your server with Admin/password  first(login as Administrator).
• In BA Server Click on "Administration" from the dropdown Home menu. 
• Create 2 Users as shown in below image.

  

Roles:  
Also create 2 Roles in Manage Roles Tab
Sales User  and
HR User   as shown in below image.

Step 4 : Assign roles to Users
As shown in below image
i) HR user is assigned with "HR user" role.
ii) Sales user is assigned with "Sales user" role.

Scenario : 
CASE1) Give Access to CUBE 1 to User 1 who is assigned with Role1
    i) Schema grant
    ii) CUBE grant
    iii) Hierarchy grant  

Notes : 

• Role Name in PSW should match with the role name in BA Server. 
• Role Creation: In PSW "Sales User" role is created for Sales CUBE.
• Cube Grant: Right click on " Sales User" role and select "Add Cube Grant", give attributes : access =all , Cube=Sales
• Hierarchy Grant: Find below image.

•  Member Grant: access=all , member=[Store].[USA].[CA]
• Another   Member Grant: access=none , member=[Store].[USA].[CA].[Los Angeles]

• USA country in current example is having 3 states, CA,WA,OR.
• Out of these 3 states the access is given to only CA state.
• The cities other othn "Los Angeles" will be displayed with the other member grant. 

Output:
1) login to the BA Server as "Sales" user.
2) New -> Saiku Analytics

CASE2) Give Access of CUBE 2 to User 2 who is assigned with Role2
   
As a demonstration just given CUBE Grant to "HR User".

"HR User" is the role name given in PSW as well role created in BA server.
For this role the user is "HR" in this example. 

References :
1) http://mondrian.pentaho.com/documentation/schema.php#Defining_roles
 
2) http://infocenter.pentaho.com/help/index.jsp?topic=%2Fanalysis_guide%2Ftopic_mondrian_security.html


:-)

Sadakar
BI developer