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