Pentaho Schema Workbench & Saiku Analytics - Quick Introduction

Working with PSW

Example:

Schema Workbench - Creating an OLAP Schema

Database Connection

Connecting to postgres database.

·         Download the type4 driver from the following location http://jdbc.postgresql.org/download.html

·         Add this driver in the “ drivers” folder which is located at C:\Program Files (x86)\psw-ce-3.2.1.13885\schema-workbench\drivers

·         Restart the schema work bench.

·         Now do the following steps.

·         Go to “Options” then Click on “Connection”. i.e, OptionsàConnection

·         Give the database details. The connection details are depicted in the following figure.

Schema

 Step 1:-  File->New->Schema

And give the Schema Name(our schema name is foodmart)  as follows

CUBE

Right click on Foodmart schema and do Add cube. Click on New Cube 1 and write Products for name attribute.

Dimension

Right click on Products cube and do Add Dimension. Click on New Dimension 1 and write Product for name attribute. Choose product_idcolumn for foreignKey attribute.

Right click on default hierarchy and do Add table. Click on Table and write product for name attribute.

Level

Right click on default hierarchy and do Add Level. Click on New Level 1 and fill out the following fields:

Brand Level Attributes

Name:Brand

Table: product

Column:brand_name

Repeat the previous step  to create two levels as below:

Name and SKU Levels Attributes
name	Name	SKU
table	product	product
column	product_name	SKU

Measure

Right click on Products cube and do Add Measure twice. Click on New Measure 0 and New Measure 1 then fill out the following fields:

Product and Class Numbers Attributes

name	product number	class number
aggregator	distinct-count	distinct-count
column	product_id	product_class_id

Save your Schema

Click on File > Save As.  Choose a path and a name for your schema.

You have done with designing the CUBE.

Working with Saiku Analytics

Example:

Steel Wheels CUBE demonstration in Saiku Analytics

·         CUBE is designed using PSW and published to the BA server.

·         Images below describe how to create Ad-hoc reporting using Saiku Analytics and the features of Saiku Analitics.

1) Selecting CUBE  to be used for Ad-hoc reporting after publishing to the BA Server.(Example of Steel Wheels CUBE)

·         We can select the cube on which we want to perform ad-hoc reporting.

 

 2. Drag and drop Dimension and measures to the Columns and Rows

·We can drag drop the fields(Dimensions) to the rows and values to the columns(Measures).

 

3. Swapping Axis Feature –

·         Swapping the same output of above image

4. Exporting to Spread Sheet

·         We can export the data/charts displayed on the console to Excel format or csv format.

5. Filter data (Year wise – 2004 data only displayed)

·         Filtering the data – Click on particular row or column by which we want to filter.

·         We can place the dimension in “ Filter” section to filter data.

·         Image below gives the idea on how to do this. 

6. Data Visualization – Interactivity

·         Visualizing data on the charts.

·         It is interactive.. On hovering to the slices it’ll give the details of that slice as shown in below image.

·         Supports stacked bar, bar, line and heat grid types.  

7. MDX Query Analysis

·         Saiku engine internally generates MDX queries.

·         Also pentaho mondrain and sql log files we can find in the installation folder.

·         For doing this we need to enable the log files.

·         Mondrian engine internally converts the MDX queries to SQL queries.