Ad-hoc reporting on multidimensional database introduction - PSW and Saiku Analytics quick Introduction

Why this info in my blog post :

You can better introduce the usage of tools to your client  
You can learn theory as well

Web-based Ad Hoc Query and Reporting
Pentaho web-based ad hoc query and reporting is a capability that extends Pentaho Reporting to provide end-user self-service for report creation.

How does Ad-Hoc Reporting work?
Web-based ad hoc query and reporting provides a browser-based wizard that allows end-users to create their own reports.

The wizard uses web services and components in the Pentaho platform to utilize metadata models that have been published from the Pentaho metadata editor.

Pentaho metadata allows the content of the reporting database to be presented to business users in familiar business terms.

Reports created by users use metadata queries to define the data that user wants to see.

These metadata queries are translated into SQL every time a report is run so any changes to the database are hidden from the users.

 What are the tools to be used for Ad-hoc reporting?
1)      Mondrian – PSW –Pentaho Schema workbench – OLAP cube creation
2)      Saiku Analytics
  Mondrian – PSW –Pentaho Schema workbench – OLAP cube creation

• It is a designer interface that allows you to create and test Mondrian OLAP cube schemas visually.
• The Mondrian engine processes MDX requests with the ROLAP (Relational OLAP) schemas.
• These schema files are XML metadata models that are created in a specific structure used by the Mondrian engine.
• These XML models can be considered cube-like structures which utilize existing FACT and DIMENSION tables found in your RDBMS.
• It does not require that an actual physical cube is built or maintained; only that the metadata model is created.

Features of PSW:

• High performance, interactive analysis of large or small volumes of information
• Dimensional exploration of data, for example analyzing sales by product line, by region, by time period
• Parsing the MDX language into Structured Query Language (SQL) to retrieve answers to dimensional queries
• High-speed queries through the use of aggregate tables in the RDBMS
• Advanced calculations using the calculation expressions of the MDX language

Saiku Analytics

• On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.
• OLAP functionality is characterized by dynamic multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities (The OLAP Council)
• By harnessing the power of OLAP, Saiku allows users to choose the measures and dimensions they need to analyse and “slice and dice” the data and drill into the detail to uncover relationships, opportunities and issues.
• The intuitive user interface lets users drill down and up, filter, pivot, sort, and chart against OLAP and In-Memory engines.
• Utilizing the Olap4J library, Saiku is the first application on the market to offer support for Mondrian’s Scenario feature allowing non destructive editing of query results, giving users the ability to adjust the figures and perform “what-if” analysis over their data.
•  By harnessing the power of Mondrian, Saiku offers scalable in-memory analysis.
• Large amounts of data can be stored in memory in a distributed manner across the local network, offering greatly improved performance over large data warehouses as the aggregated data is retrieved from the network instead of reading from disk.

NOTE:

• Saiku Analytics for pentaho is a web-based Ad-hoc reporting tools.
• It’s a plug-in for pentaho. You can download and install from Penthao Market Place in Penthao Server.

Features of Saiku Analytics

• Drag & Drop Report-Design
• Export to: PDF,CSV,XLS,CDA,PRPT
• Uses Pentaho Report Designer PRPT-Templates
• Grouping
• Aggregation
• Totals
• OpenFormula Support

References :
Saiku web pages
Pentaho web pages
Pentaho wikipedia
Saiku wikipedia

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.