List of data sources

This is the list of all the available data sources, grouped in the left pane:

• Wizards - A setup assistant to guide you through the steps of creating an OLAP selector or chart.

• Community Data Access (CDA) - CDA allows data to be retrieved from multiple data sources and combined in a single output which can easily be passed on to dashboard components.

• Legacy Datasources - Legacy data sources include PDI/Kettle transformations, OLAP MDX queries, SQL queries, and Xaction result sets.

• Pentaho App Builder Endpoints - The PAB's internal Kettle transformations and jobs.

• MDX Queries - You can retrieve data from a Mondrian cube via an MDX query.

• OLAP4J Queries - These data sources execute queries using the olap4j specification.

• Compound Queries - These queries allows you to combine the result of two distinct queries. Compound queries can be either JOIN or UNION.

• SCRIPTING Queries - Create ad hoc result sets for prototyping purposes using Beanshell scripts.

• KETTLE Queries - Define a Kettle transformation file to fetch data.

• MQL Queries - Pentaho Metadata defines a business model and query implementation so business users can query data sources using Pentaho reporting tools.

• SQL Queries - Use this type of data source to access data from SQL databases if you have a JNDI connection or a JDBC driver setup.

• XPATH Queries - Provides the ability to read data from any type of XML file using XPath specifications.

Wizards

These wizards can be used to create either a selector or a chart by setting a few properties. You can use the following types of wizards:

• OLAP Selector wizard

• OLAP Chart wizard

• Saiku OLAP Wizard

Using the configuration pane, you can select an MDX Cube, and from that cube you can select measures and metrics to generate a result set which you want to display in a selector or chart. The configuration pane features a preview area where you can view how your chart or selector will work.

There are specific settings which can be set for either the selector or the chart wizard. The selector wizard allows you pick from select, radio box, or multiple selector options whereas the chart wizard provides selection options for bar, pie, line, and dot charts. After setting your options, clicking Ok adds a mdx over mondrianJndi data source to the Datasources pane.

The wizard creates this data source, setting all the necessary parameters as well as the query for the data source to properly execute. The selector wizard also creates a parameter and a select component from the selections we made in the wizard, in the Components pane on the Components perspective.

When creating a chart using the OLAP Chart wizard, a chart component is generated rather than the OLAP parameter and select component.

Community Data Access

CDA allows you to access any of the many Pentaho data sources as well as allowing you to join different data sources just by editing an XML file, caching queries to boost performance, or delivering data in different file formats, such as CSV and XLS, through the Pentaho User Console. These tasks can be accomplished by selecting a CDA data source in this category.

Legacy Datasources

The following options are available under this heading:

• Kettle transformation

  This data source executes a PDI (Kettle) transformation. Theoretically, you can get data from any source through a Kettle transformation, such as from plain files, Excel spreadsheets, and web services. To access data from Kettle, you will need to provide the name and location of the KTR file and the name of the transformation step which will provide the data. You will also need to define a kettle.TransFromFile connection.

  
• OLAP MDX query

  This data source executes an MDX query when you provide the JNDI connection string, the Mondrian schema, Mondrian cube, and the MDX query itself.

  
• SQL query

  This data source executes a SQL query when you provide the JNDI connection string and the SQL query.

  
• XAction result set

  This data source retrieves a result set returned from an Xaction call to the Pentaho Server when you provide the location, path, parameters, and name of the Xaction you wish to execute.

  

The Pentaho App Builder endpoints

Pentaho App Builder (previously known as SPARKL) is a Community Plugin Kickstarter (CPK) plugin which allows you to easily build other CPK plugins. Kettle transformations or jobs of a CPK plugin are automatically exposed as rest endpoints. While you can view these endpoints in CDE, they are internal to Pentaho App Builder and are not necessary when developing dashboards.

MDX Queries

You can fetch data from a Mondrian cube through an MDX query. To access the data through a Mondrian cube, provide the JNDI or JDBC connection properties, the name of the Mondrian schema file (XML), and the MDX query which will return the data. There are four types of MDX data sources:

• denormalizedMdx over mondrianJdbc

• denormalizedMdx over mondrianJndi

• mdx over mondrianJdbc

• mdx over mondrianJndi

MDX queries can be normalized or denormalized. The specifics of each type of query are detailed in the CDA documentation.

OLAP4J Queries

These data sources execute queries using the olap4j specification, which is an open Java API for accessing OLAP data. This type of data source can be denormalizedOlap4j over olap4j or olap4j over olap4j.

As with the MDX queries, OLAP4J queries can be normalized or denormalized.

Compound Queries

This type of query allows you to combine the result of two distinct queries. Compound queries can be one of two types, JOIN and UNION.

A JOIN compound query merges the result of two queries, using a specified set of keys. You can specify one of four join types: Inner, Left Outer, Right Outer, Full Outer. The result of this join will contain the columns of both queries if they are of the same type. Both the left and right side queries must be identified by an ID. You must also specify which keys (column IDs on the source queries) are used to join the data. This data source has the following properties:

A UNION compound query takes the results of two queries with the same number of columns and returns the compounded result set from both queries. A union query data source has the following properties:

If the columns on both data sets have different names, the name of the column in the top result set will be used in the union’s resulting data set.

SCRIPTING Queries

These data sources allow you to create ad hoc result sets, such as a small table, for prototyping purposes using Beanshell scripts. These result sets are useful during the dashboard development phase for generating data for a dashboard’s components when real data is not yet available. This data source can be one of two types:

• scriptable over scripting

  Using the Beanshell scripting language, we can define a data structure and then create a result set based on this same structure to use in a component. You will need to define the column names, column types, and the result set rows.

  Copy

  import org.pentaho.reporting.engine.classic.core.util.TypedTableModel;
  String[] columnNames = new String[]{
      "value","name2"
  };
  Class[] columnTypes = new Class[]{
      Integer.class,
      String.class
  };
  TypedTableModel model = new TypedTableModel(columnNames, columnTypes);
  model.addRow(new Object[]{ new Integer("0"), new String("Name") });
  return model;

• JSONscriptable over scripting

  This data source is similar to the scriptable data source in that it uses Beanshell script to generate a result set. However, rather than specifying the column names and column types, you just need to define the metadata and create the result set you want to use. This is simple and less prone to bugs than using the scriptable data source.

  Copy

  {
    "resultset":[
          ["Name", 0]
    ],
    "metadata":[
      {"colIndex":0,"colType":"String","colName":"value"},
      {"colIndex":1,"colType":"Integer","colName":"name2"}
    ]
  }

KETTLE Queries

Using Pentaho Data Integration transformations, you can fetch data from virtually any data source such as plain text files, Excel spreadsheets, and web services.

• kettle over kettleTransFromFile

  To access data from Kettle, you will need to define the Kettle transformation file (KTR) you want to use and the name of the transformation step which will provide the data. You can also pass parameters and variables to the KTR transformation to filter the data.

  

MQL Queries

Pentaho Metadata defines a business model and query implementation which makes it easy for business users to query data sources in Pentaho tools such as Report Designer and Ad Hoc Reporting. This metadata can be accessed through a MQL query. MQL is the syntax Pentaho Metadata uses for generating SQL queries based on metadata.

• mql over metadata

  To access the data, provide the name and location of the metadata domain file (XMI) and the domain where the data belongs.

SQL Queries

Use this type of data source to access data from SQL databases provided you have a JNDI connection or a JDBC driver setup. You can access a SQL database by defining the connection and providing the query to be executed.

• sql over sqlJdbc

  Besides specifying the query to be used, you also need to specify the information needed to access the data such as the driver, user name and password of a user with access to the data.

  
• sql over sqlJndi

  This type of data source employs the Java Naming and Directory Interface (JNDI) which allows software clients to discover and look up data and object via a name, in this case a SQL database. To set up this type of data source you just need to specify the JNDI identifier and the query to be used.

  

XPATH Queries

This data source provides the ability to read data from any type of XML file using XPath specifications.

• xPath over xPath

  You need to provide a query as well as the path to the data file on which to apply the xPath query.