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