Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Monday, 30 December 2013

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.