Design a multidimensional cube using Schema Workbench in Pentaho CE BI Suite 3.0

This exercise will teach you how to design a new cube, publishing it into the Pentaho server and viewing the cube via Pentaho user console.

This exercise uses Pentaho Schema Workbench 3.0.4 (stable) for designing the cubes, Pentaho CE BI Suite 3.0 for hosting and viewing the cube we design, and ‘SampleData’, a hypersonic SQL database as data source. This ‘SampleData’ database is available as part of the free download of Pentaho CE BI Suite 3.0.

Please follow the below steps to design and view a new cube.

Step 1:

Make sure the pentaho server is up and running.

Step 2:

Once the pentaho server is started, go to the folder where you have the ‘Schema Workbench’ tool installed in your system.

Step 3:

In the ‘schema-workbench’ folder, double click on the batch file ‘workbench’ to startup the ‘schema-workbench’ tool. Or you can right-click on the batch file ‘workbench’ to do the same process. This will open the schema workbench window along with a command prompt. Please maximize the window.

Step 4:

Click on the menu ‘Tools’ and select ‘Preferences’. This will open the ‘Workbench Preferences’ window. We need to provide the JDBC details based on the datasource we use.

Step 5:

In the ‘Workbench Preferences’ window please provide the following details.

Note: As we are using ‘SampleData’ HSQL DB, the details I’ve given is specific to that database. In case if we need to use Oracle, MySQL, etc this will change.

Driver Class Name: org.hsqldb.jdbcDriver

Connection URL: jdbc:hsqldb:hsql://localhost/sampledata

User Name: pentaho_user

Password: password

Schema (Optional): <leave it blank>

Require Schema Attributes: Check this option.

Click on ‘Accept’ button.

Step 6:

To create a new schema file, in the menu bar select ‘File à New à Schema’ menu item.

This will open the ‘New Schema 1’ window with the schema file name as ‘Schema1.xml’. Please refer to the below screenshot.

Step 7:

Click on ‘Schema’ as shown above and set the required properties for it, for ex, name of the schema, etc. For now, enter name as ‘SchemaTest’.

Step 8:

Right click on element ‘Schema’ and select ‘Add Cube’ option. This will add a new cube into the schema.

Step 9:

Set the name of the cube as ‘CubeTest’. Once it is done, the schema design will look like below.

Step 10:

Basically, a cube is a structure made of number of dimensions, measures, etc. Cubes usually rely on two kind of tables like ‘fact-table’ (for cubes) & ‘dimension-table’ (for cube’s different dimensions). A cube can have only one fact-table and ‘n’ number of dimension tables (based on no: of dimensions in the cube).

So our next step is to set the ‘fact-table’ for the cube ‘CubeTest’. To do so, click on the icon before the cube image as mentioned in #2 in above screenshot. This will expand the cube node like below image.

Step 11:

Now click on the ‘Table’ element, this will list out the attributes specific to the ‘Table’ element. Clicking on the ‘name’ attribute will display all tables available under current datasource (the database we set in Step 5. Select the table ‘Customers’.

Once you choose the table ‘PUBLIC -> CUSTOMERS’, the ‘schema’ attribute value will be filled in automatically as ‘PUBLIC’.

Note: If the fact-table doesn’t belong to the schema mentioned in step 5, then you must explicitly specify the schema to which your fact-table belongs to.

Step 12:

Now add a new dimension called ‘CustomerGeography’ to the cube by right clicking the cube element ‘CubeTest’.

Step 13:

For the new dimension added, set the required attribute values like name, foreign key, etc.

Set name of the dimension as ‘CustomerGeography’, and foreign key as ‘CUSTOMERNUMBER’.

Just double click on the dimension name ‘CustomerGeography’.  This will expand the node and display the ‘Hierarchy’.

Click on the ‘hierarchy’ in the left side pane, you can find the attribute properties for the hierarchy.

Set name -> CustomerGeo; allMemberName = ‘All Countries’

Step 14:

Double click on the ‘Hierarchy’ element in the left side pane, will expand the node further and show the ‘Table’ element. Click on the ‘Table’ element to set the dimension-table for the dimension ‘CustomerGeography’. This will list the related attributes on the right side pane. Clicking on the ‘name’ attribute’s value field will list the tables available in the current schema.

Select it as ‘CUSTOMERS’. This will automatically fill the ‘schema’ field as ‘PUBLIC’.

Step 15:

Right click on the element ‘Hierarchy’ on the left side pane and select ‘Add Level’.

This will add a new level with name ‘New Level 0’. Refer to the below screenshot.

To rename and set other attributes, set the attribute values (as listed below) for the newly created level in the right side pane.

Name -> CustomerCountry

Column -> COUNTRY

Type -> string

uniqueMembers -> true

Now we have added a level called ‘CustomerCountry’.

Step 16:

To add another one level, right-click on ‘Hierarchy’ in the left side pane (as we did in Step 15), and select ‘Add Level’. This will add a new level with name ‘New Level 1’. To rename and set other attribute values, set the attribute values in the right side pane as below,

Name -> CustomerCity

Column -> CITY

Type -> String

So far, we have created a cube with a dimension which will show two hierarchical level of details.

Step 17:

To add a new dimension to the cube, right-click on the cube item (CubeTest) in the left side pane then, select ‘Add Dimension’.

This will add a new dimension to the cube with a default name. To rename it and set other attribute values, click on the newly created dimension in the left side pane. This will list out the attributes for the dimension.

Set name -> CustomerContact; foreign key – ‘CUSTOMERNUMBER’.

Step 18:

To add hierarchy and levels for this dimension, double click on the dimension name which will expand the dimension node ‘CustomerContact’. Click on the ‘hierarchy’ element in the left side pane, then on the right side pane set the below attribute values.

Set name -> ‘’; allmembername = ‘All Contacts’.

Step 19:

Double click on the element ‘hierarchy’, will expand the node ‘hierarchy’ where you can set the dimension-table for the dimension ‘CustomerContact’.

Click on the ‘Table’ element and select the table as ‘CUSTOMERS’.

Step 20:

To add a new level for this dimension or hierarchy, right-click on the element ‘hierarchy’ and select ‘Add Level’. This will add a new level to the hierarchy with name ‘New Level 0’. We can rename it by changing the attributes’ values like below,

name -> ‘CustomerNames’

Column -> CONTACTFIRSTNAME

Type -> String.

Step 21:

To add new measure to the cube, right click on the cube ‘CubeTest’ and select ‘Add Measure’. This will add a new measure with name ‘New Measure 0’. You can rename it by changing the attribute values.

For ex, we are trying to achieve the number of customers under each country/city.

Set attribute values like below,

Name -> CustomerCount

Aggregator -> count

Column -> CUSTOMERNUMBER

Format string -> ####

Datatype -> Integer

After setting up the measure, the cube (CubeTest) schema structure will look like below,

Step 22:

Now, click ‘File -> Save’ menu to save the cube schema. You can save it in your desired path.

For ex, save it as ‘TestCube.mondrian.xml’

Step 23:

Once saved the schema, you can then publish the cube into the Pentaho server (we are using Pentaho Community Edition BI Suite 3.0 Stable version).

Select ‘File -> Publish…’ menu item to publish the cube. This will open a publish dialog like below

.

Follow the instructions in the above screenshot and click ‘OK’ button. Click ‘Cancel’ button to cancel the publishing action. Once clicked OK button, you can see the processing action like below,

Then, a publish dialog will open. Here you have to specify, in Pentaho server, where we need to publish the cube.

Choose the location where you want to publish and click on ‘Publish’ button.

On successful publishing, the system will display you a dialog box with message ‘Publish successful’.

Click ‘OK’ button.

Step 24:

To view this published cube via Pentaho, please open or browse or visit the Pentaho server URL.

For ex, http://localhost:8080/pentaho

Click on ‘Pentaho User Console Login’ button, you can find a login dialog box.

Login as user ‘joe(admin)’ and password ‘password’ or any other users with administrative privileges. After selecting the user (or) entering the user credentials, click on ‘Login’ button to login or click on ‘cancel’ button to cancel the login process.

Step 25:

After logging in, the system will be redirected to the Pentaho BI home page.

Click on ‘New Analysis View’ button. This will list of schema accessible to the currently logged in user.

By default, this schema list would include ‘SampleData’, ‘SteelWheels’ along with the schema we published early.

Step 26:

Select the ‘Schema’ as ‘SchemaTest’ and ‘Cube’ as ‘CubeTest’. Click ‘OK’ button.

Step 27:

This will generate the cube in an ‘Analysis View’ window like below.

Result:

A new cube has been designed, configured and published into the Pentaho Server. Also, we viewed the cube via Pentaho User Console.

(function(i,s,o,g,r,a,m){i[‘GoogleAnalyticsObject’]=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,’script’,’//www.google-analytics.com/analytics.js’,’ga’);

ga(‘create’, ‘UA-44966059-1’, ‘wordpress.com’);
ga(‘send’, ‘pageview’);