JPivot with role 'California Manager' set

Contents

  1. 1 Introduction
  2. 2 Configuration
    1. 2.1 Prerequisites Articles
    2. 2.2 File Configuration
    3. 2.3 Foodmart.xml
    4. 2.4 Testing
  3. 3 Still Getting Errors ?
  4. 4 Mondrian Training

Introduction

Mondrian Version : mondrian-3.0.4.11371

This is a further sample which querying Sales Cube with Role limitation using JPivot interface. The cube data is derived from MySQL foodmart sample data which we can generate from Mondrian's zip bundle. The sales cube schema is defined in Foodmart.xml file.

The page shown will by default show 3 dimensions, they are :
  • Marital Status
  • Customers
  • Product
And 3 measures are displayed, they are :
  • Unit Sales
  • Store Cost
  • Store Sales
But the important of this sample is the role feature that we implied into JPivot tag. This page shows data that is only available to role 'California Manager'.

Configuration

Prerequisites Articles

File Configuration

Open and edit testrole.jsp file - which should be located in your [TOMCAT_HOME]/webapps/mondrian/WEB-INF/queries folder - with your favorite text editor,  and edit <jp:mondrianQuery> with these following configurations :

testrole.jsp

<jp:mondrianQuery
    id="query01"
    jdbcDriver="com.mysql.jdbc.Driver"
    jdbcUrl="jdbc:mysql://localhost/foodmart?user=your_username&password=your_password"
    catalogUri="/WEB-INF/queries/FoodMart.xml"
    role="California manager">
select
  {[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
  {([Marital Status].[All Marital Status], [Customers], [Product].[All Products])} on rows
from Sales
where ([Time].[1997])
</jp:mondrianQuery>


Note : [TOMCAT_HOME] is the folder where your Apache Tomcat installed. For example : C:\apache-tomcat-6.0.16

Foodmart.xml


To understand this sample you need to look at some part of the Foodmart.xml - which should be located in [TOMCAT_HOME]/webapps/mondrian/queries/Foodmart.xml - which describe the role.

Foodmart.xml

............
<Role name="California manager">
  <SchemaGrant access="none">
    <CubeGrant cube="Sales" access="all">
      <HierarchyGrant hierarchy="[Store]" access="custom"
          topLevel="[Store].[Store Country]">
        <MemberGrant member="[Store].[USA].[CA]" access="all"/>
        <MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="none"/>
      </HierarchyGrant>
      <HierarchyGrant hierarchy="[Customers]" access="custom"
          topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]">
        <MemberGrant member="[Customers].[USA].[CA]" access="all"/>
        <MemberGrant member="[Customers].[USA].[CA].[Los Angeles]" access="none"/>
      </HierarchyGrant>
      <HierarchyGrant hierarchy="[Gender]" access="none"/>
    </CubeGrant>
  </SchemaGrant>
</Role>
............


Intuitively enough, this role configuration tells Mondrian that 'California Manager' can access all California state parts except Los Angeles.

Testing

Open your Mondrian web application and click on the fourth sample from the index page (JPivot with role 'California Manager' set). You should see an OLAP data table with a JPivot toolbar. Drill through some of the dimension level nodes.

For example, my web application is located at http://localhost:8080/mondrian and my "JPivot with role 'California Manager' set" sample url is http://localhost:8080/mondrian/testpage.jsp?query=testrole. Screenshot below shows the page that have been drillthrough at some nodes.


To make a comparison, I take a screenshot of the same page without any role shown below.


Still Getting Errors ?


If you already following all the instructions and still got some errors or you have other issues that has to do with other database sources then you can post your problem to our Mondrian user group at http://groups.google.com/group/phi-mondrian-olap. Or you can send a blank email to phi-mondrian-olap-subscribe@googlegroups.com to join the group. 

Note that you must get approval first before you can post.

Mondrian Training

For those of you  in Indonesia we have a regular training on OLAP and data warehousing subject using Pentaho Data Integration and Mondrian.

If you want to enroll for the session please feel the form at http://pentaho.phi-integration.com/pendaftaran-training and we will send you the training schedule.

And for those who like to have a remote training instead using Webex please contact us by email to support@phi-integration.com.
Comments