Setup Access Controls For A Multidimensional Cube


Dalam membuat sebuah data analytic, biasanya cube data diakses oleh banyak user dari berbagai jenis role. Oleh sebab itu, diperlukan sebuat pengaturan akses agar view data oleh user tersajikan sesuai role user.

Setelah searching di mbah google dan milis, ada beberapa informasi yang bermanfaat terkait access control untuk multidimensional cube :

Bagian I

dari milis pentaho indonesia (Pak Rio Bastian)

1. Backend
untuk mengimplementasikan hal tersebut maka di sini kita mengasumsikan bahwa role yang kita daftarkan melalui schema workbench adalah role yang terdapat di dalam BA server.
oleh karenanya kita harus “memberitahu” Pentaho. caranya :
– buka file ..\biserver-ce\pentaho-solutions\system\pentahoObjects.spring.xml
– cari bean dengan nama Mondrian-UserRoleMapper
– normalnya ada 3 pilihan untuk bean tersebut dan di comment,
– uncomment pada bean dengan name = “Mondrian-One-To-One-UserRoleMapper

<bean id=”Mondrian-UserRoleMapper”
name=”Mondrian-One-To-One-UserRoleMapper”
color: rgb(34, 34, 34); font-family: Arial, Helvetica, sans-serif; line-height: normal; background-color: rgb(255, 255, 255);”>plugin.action.mondrian.mapper.MondrianOneToOneUserRoleListMapper”
scope=”singleton” />

– save kemudian restart BI server.

2. Frontend
Pak Aji hanya perlu menambahkan Role ke dalam schema workbench yang di buat,
kemudian tentukan hak aksesnya, *hak akses yang di maksud adalah hak akses ke dalam element2 schema seperti Cube, Hierarchy, Level, dan Member.
kemudian publish.
sebelum publish pastikan role yang di gunakan di schema workbench sudah di daftarkan di BA server.

Bagian II

dari http://herwinrayen.wordpress.com/2011/05/09/setup-access-controls-for-a-multidimensional-cube/

Step 1:
Open the tool Pentaho Mondrian Schema Workbench to design a cube. Instead of designing a new cube from the start, lets use the cube (SampleData.mondrian.xml) that is available as part of the Pentaho download.

(To know how to design a cube using Pentaho Schema Workbench, please refer the previous blog)

For this exercise, use the sample cube ‘SampleData.mondrian.xml’ in the following path ‘~/bi-developers/analysis/’

The [x] mark mentions the schema elements are not defined properly. To resolve this, expand the node ‘Quadrant Analysis’ by double clicking on it. Select the table ‘QUADRANT_ACTUALS’ and on the right hand side pane, set the schema property for the table as ‘PUBLIC’.Similarly, expand each of the dimension nodes, select its table element and set the schema property for the tables as ‘PUBLIC’. Once resolving these errors, the schema structure will look like below

Step 2:
This ‘SampleData’ cube has been defined with dimensions and measures. We need to add the access controls (roles) for the cube. To do that, after you open the Mondrian schema xml file for the cube in Schema Workbench tool, right click on the ‘Schema’ element and select ‘Add role’

This will add a new role to the cube with name ‘New Role 0’.

Step 3:
Once adding the role, change its name property value to ‘Admin’ in the right hand side pane.

Step 4:
Right click on the ‘Admin’ role and select ‘Add Schema Grant’. This will add a new element named ‘Schema Grant’ under the ‘Admin’ role. Let the ‘Schema Grant’ access property as it is (all).

Step 5:
Right click on the ‘Schema Grant’ element and select ‘Add Cube Grant’. This will add a new element under the ‘Schema Grant’ element like ‘Cube Grant’. Select the ‘Cube Grant’ element and in the right hand side pane, set its property values like
Access –> all
Cube –> Select the cube name as ‘Quadrant Analysis’. (As a schema can have more than one cube, this ‘cube’ property will list out all the cubes the current schema contains. So we need to choose one of those cubes). Our ‘SampleData.mondrian.xml’ schema has only one cube.

In Steps 3 – 5, we have defined a role called ‘Admin’ and users with this role can have complete access over the cube ‘Quadrant Analysis’.

Step 6:
Again, to add another role as ‘Authenticated’, right click on the ‘Schema’ element at the top of the schema file, and select ‘Add Role’. This will add a new role to the schema with name ‘New Role 1’. Select the newly added role ‘New Role 1’ and change its name property to ‘Authenticated’.

Step 7:
Right click on the ‘Authenticated’ role and select ‘Add Schema Grant’. This will add a new element under the ‘Authenticated’ role. Let the ‘Schema Grant’ access property as it is (ie. ‘All’).

Step 8:
Right click on the ‘Schema Grant’ element and select ‘Add Cube Grant’. This will add a new element ‘Cube Grant’ under the ‘Schema Grant’ element. Select the ‘Cube Grant’ and set the ‘cube’ as ‘Quadrant Analysis’.

Step 9:
Right click on the ‘Cube Grant’ element and select ‘Hierarchy Grant’. This will add a new element ‘Hierarchy Grant’ under the ‘Cube Grant’ element. Select the ‘Hierarchy Grant’ and set the following properties in the right hand side pane.
Access –> none
Hierarchy –> Region (This property ‘hierarchy’ will list out all the hierarchies the cube has.)

In steps 6-9, we have added another role called ‘Authenticated’ to the cube and users with this role will have limited access to the cube i.e. the user cannot able to view the ‘Region’ column in the cube.

Step 10:
Repeat the steps 6 – 9, by adding another role called ‘ceo’.

Step 11:
Repeat the steps 6 – 9, by adding another role called ‘dev’.

Now save the cube using ‘File –> Save’ option.

Step 12:
Publish the schema file into the Pentaho server, using ‘File –> Publish’ option.

Step 13:
Open the Pentaho User Console in the browser by hitting the URL http://localhost:8080/pentaho

Step 14:
Login the application as user ‘joe (admin)’ and password as ‘password’.

Step 15:
Click on ‘New Analysis View’ button, this will open a dialog listing the schema accessible for the user. Select the Schema as ‘SampleData’ & Cube as ‘Quadrant Analysis’.

This will open the cube as shown below.

Step 16:
Clicking on the ‘MDX’ button in the tool bar will show the MDX query for the current view of the cube by opening a ‘MDX Query Editor’ window. For the above cube, the MDX query will be like below,

select NON EMPTY {[Measures].[Actual]} ON COLUMNS,
NON EMPTY {([Region].[All Regions], [Department].[All Departments], [Positions].[All Positions])} ON ROWS
from [Quadrant Analysis]

Modifying the above query like below to hide the Region column

select NON EMPTY {[Measures].[Actual]} ON COLUMNS,
NON EMPTY {([Department].[All Departments], [Positions].[All Positions])} ON ROWS
from [Quadrant Analysis]

Click on ‘Apply’ button in the MDX Query Editor to apply the above query change into the cube.

After applying the query change, the cube will look like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: