Methods of Interactive Reporting

Skip to end of metadata
Go to start of metadata

Methods of interactive reporting

This section summarizes methods of reporting that BI administrators, developers, management, executive level, casual and power users can work with. Reporting methods similar to what is described and more are available when using the Pentaho Enterprise Edition. The term "interactive" is being used to describe how these users can provide input and manipulate output as part of their analysis and report creation. In order for your organization to be successful when designing and deploying a BI solution, your organization must have the ability to leverage and deliver BI with the proper internal or third-party consulting skill sets, no matter what underlying technology is being used. With Pentaho's Enterprise Edition BI Suite, a subscription customer will realize a lower total cost of ownership (TCO) and a higher return on investment (ROI) because funds are used for Pentaho's value added services instead of exorbitant  software license, CPU, maintenance and user based costs.

Building your BI solutions using Pentaho desktop development tools

The Pentaho BI Suite includes desktop development tools that can run on Windows, MAC, and Linux workstations. The desktop design tools consist of:

  • Pentaho Report Designer (reporting / charting reports, templates, pixel perfect layouts, published, dashboard content)
  • Pentaho Design Studio (Process Flow (advanced) scheduling, distribution, BI process, analysis, integration and more)
  • Mondrian Schema Workbench (Pentaho Analysis and Analyzer schema design)
  • Pentaho Metadata Editor (business views, semantic layer, used by all design tools Dashboard Designer and Web Ad hoc reporting)
  • Pentaho Data Integration (ETL, EII - jobs & transformations)
  • Pentaho Aggregation Designer (visually creates aggregate tables to be used with Pentaho Analysis models) - not covered

The primary purpose of these tools is to create and deploy Business Intelligence content, solutions and BI process work-flows that are either published to or used with the Pentaho BI Platform. These tools are provided as separate distributions since they are not required for all BI solutions and organizations may choose which ones they wish to use.

Pentaho Report Designer: Pixel Perfect Reports, Parameterized templates and dashboard "content" development, enterprise based operational reporting

Pentaho Design Studio: Action sequences, process actions, Legacy Parameterization, work-flow, BI Processes, distribution, result set traversing using the JavaScript API and more.

Mondrian Schema Workbench: Pentaho Analysis ROLAP (Relational Online Analytical Processing) schema design

The Mondrian Schema Workbench was originally created by a few organizations to easily create Mondrian Schemas to be used with the Mondrian Analysis engine. Pentaho has updated the Schema Workbench with additional stabilization, validation and Pentaho BI Server publish capabilities to work with the Pentaho BI Suite. The "Mondrian" Schema Workbench is the "recommended" design tool included with the Pentaho design tools suite to create a Mondrian Schema. A Mondrian Schema is really nothing more than an XML document that the Mondrian Analysis engine uses. (You could essentially manually develop a Mondrian schema using Windows Notepad, but would still have to manually deploy this to the server.) The Mondrian Analysis engine is embedded as part of the complete Pentaho BI Platform.  Documentation for the actual Mondrian Schema design can be found at the links on this site, or in the Mondrian Technical guide (located in the Schema Workbench installation "..\design-tools\schema-workbench\doc".  The Mondrian Schema Workbench is really nothing more than a properties editor which easily creates the Mondrian schema "and" publishes it to the Pentaho BI Server to be accessed by the Pentaho User Console.

To make this a bit easier for people evaluating Pentaho Analysis, a few resources were created. These were designed to help one evaluate and understand some of the fundamentals of the Pentaho Analysis offering. However, training is available to further your knowledge on the subject:

Pentaho Data Integration:  ETL and EII, transformations and jobs

Pentaho Metadata Editor: Creation and maintenance of business views, business models (semantic layer, layer of abstraction)

At a high level here is the concept of Pentaho Metadata and the Pentaho Metadata Editor:

1)    We import RDBMS Tables or RDBMS Views from the connected RDBMS via a JDBC connection, (currently a Business View can  
       contain tables from one connection only)
       a. (If you want a Business View to contain fields from different RDBMS connections and their tables, this is possible but needs
           to use Pentaho Data Integration to define the joins/merge and create and load the new merged table.)

2)    These defined RDBMS tables or RDBMS views are then brought in to a Business Tables section under the Business Model view
       where you can create relationships between them, add derived and calculated columns, set up security on columns and row
       level, identify model descriptors, friendly business names and descriptions, etc.

       Derived columns (calculated columns etc.) can use either the specific RDBMS dialect functions or Open Formula specific 

3)    Next the fields for these structures can be brought into a Business View where you can categorize the fields into easy to
       view folders or sub-categories that are represented in the Pentaho Web Ad hoc Query Interface. Here you can also setup
       additional layers of security on the categories.

Methods of Ad hoc

For these methods, the phrase "ad hoc" can have different meanings to different organizations and its people. I will discuss some methods of what can be construed as ad hoc reporting or what can be described as self-service reporting, structured ad hoc or guided reporting. All 3 adhere to different user types in the organization which will meet different needs of those individuals. Once you have defined your reporting requirements, you must categorize exactly what is necessary and what is a nice to have. The ultimate win is that the Pentaho Platform offers complete flexibility in customization.

(1)  Guided Ad hoc / Parameterized Reporting / Structured Ad hoc / Business User Self Service

  • Can be used by novice users, casual users, power users, analysts and executive level
  • Security can be provided to secure filters and data based on user or role
  • 80% of business intelligence reporting in an organization can be and is usually achieved this way
  • IT or consulting partnership development can create the dynamic query template and user interface to hold parameters
  • The developer can builds the report template based on discussions with the various business users or departments using Pentaho design tools
  • The reports can be created from a business view (metadata layer MQL) or from direct access to the RDBMS tables using SQL(JNDI) and/or stored procedures.
  • The developer creates the web user interface (using web authoring technologies and existing Pentaho samples if desired) to pass input to interact with the report based on user selections
  • Users can export and save local copies of the report to their computers in a variety of formats such as HTML, PDF, EXCEL, etc.
  • Additional functionality can be added such as annotation, user defined thresholds, filtering, what if analysis as well as the ability to distribute the report
  • The design of the user interface and the reports are really limited to creativity, imagination and technical skill set of the organization
  • The report's fields and filter criteria can be limited and structured by what the business users and developers agree upon
  • User defined drill paths from summary level to detail can be added to further the analysis
  • Numerous report combinations can be created based on the available columns, measures, filters and option provided in a structured manner
  • On demand, file, email and solution repository distribution are available
  • See a fundamental example here




(2) Pentaho Web Ad hoc Reporting interface

  • Can be used by the casual, power or analytical business user
  • Security can be provided to secure categories and data based on user or role
  • Is provided as an "Out of the box" interface
  • The query is created by the user and is not predefined
  • IT development effort only involves creating the business view using the Pentaho Metadata Editor
  • Current version provides the most common Ad hoc query functions
  • User can create reports from a selection of fields provided in the business view
  • Uses friendly business terms and provides additional business view security
  • Grouping, summarizing, filters and formatting
  • Continued added functionality with each release
  • Complexities of data structures and relationships are transparent to the user
  • Rapid development and quicker to deploy in an organization
  • Users can export reports in a HTML, PDF, EXCEL and CSV
  • Users can save locally to their machines and in the Pentaho Solution Repository
  • Saved reports can be run in the background and archived
  • User can schedule and subscribe to report to be run later
  • Numerous report combinations can be created based on elements in business view
  • No limits on fields, fields are listed in categories and are limited by developer who created the business view
  • Reports provide are tabular without charts or graphs when using this method, though can be enhanced later by IT using the Report Designer


(3) Pentaho Analysis

  • Can be used by the power or analytical business user
  • Security can be provided to secure Dimensions, members and data based on user or role
  • Is provided "Out of the box"
  • Web Based HTML view or Microsoft Excel (using optional Pentaho Spreadsheet Services) based interfaces to access the multidimensional query
  • Developer only creates a ROLAP schema and the query using design tools
  • No limit on content displayed
  • Sort, pivot, group, column selection, filtering, exporting to formats, drilling and more
  • Summary level and detail level reporting in one interface
  • Ability to save report perspective to formats locally or to report repository
  • Ability to add charts and output to PDF and EXCEL immediately
  • Drill down and through from summary to detail defined in hierarchies of the schema

(4) Pentaho Synchronized Dashboards

  • Can be used by the executive, manager, novice, power or analytical business user
  • Can be easily created with little to no training
  • Utilizes existing Pentaho Repository Content
  • Provided layout and theme panels for easy customization
  • Can easily create synchronization filter panel to pass filter(s) to single or multiple content windows
  • Next release (July time frame )will prove interactive data grid, thin client flash based chart editor and URL tools

(5) Pentaho Report Analyzer

The new analytical front-end for Pentaho's OLAP analysis product line, dramatically simplifies the analysis experience for business users with:
See the Techcast on the Pre-Sales Consulting Portal main menu.

  • Drag-and-drop view creation
  • User-defined calculations
  • One-click sorting and filtering
  • Top/bottom calculations, and more

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.