Skip to main content

Pentaho+ documentation has moved!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Connect to a data source

Parent aricle

This article explains each data source connection used with Report Designer. You must have a report file open in order to proceed, and your data source must be accessible before you can connect to it in Report Designer. If you are using Report Designer on Linux, after you add a data source it will not appear in the Data tab until you double-click the Data Sets item.

Pentaho Report Designer supports the data source types mentioned below.

JDBC

Any JDBC-compliant database will work with Report Designer, but you will probably have to provide your own JDBC driver JAR. This is accomplished by copying the appropriate JAR file to the /pentaho/design-tools/report-designer/lib/ directory.

You may need to obtain database connection information from your system administrator, such as the URL, port number, JDBC connection string, database type, and user credentials.

Follow this procedure to add a standard JDBC data source in Report Designer.

Procedure

  1. Select the Data tab in the upper right pane.

    By default, Report Designer starts in the Structure tab, which shares a pane with Data.
  2. Click the yellow cylinder icon in the upper left part of the Data pane, or right-click Data Sets.

    A drop-down menu with a list of supported data source types appears.
  3. Select JDBC from the drop-down menu.

    The JDBC Data Source window appears.
  4. If you want to provide parameters that contain different database connection authentication credentials, click the Edit Security button in the upper left corner of the window, then type in the fields or variables that contain the user credentials you want to store as a parameter with this connection.

    The role, username, and password will be available as a security parameter when you are creating your report.
  5. Above the Connections pane on the left, click the round green + icon to add a new data source.

    If you installed the Pentaho sample data, several SampleData entries appear in the list. These sample data sources are useless if you do not have the Pentaho HSQLDB sample database installed, so if you don't have that, you can safely delete the SampleData entries. If you do have Pentaho's HSQLDB samples installed, it may be advantageous to leave the sample data sources intact in the event that you want to view the sample reports and charts at a later time.
  6. In the Database Connection dialog, type in a concise but reasonably descriptive name for this connection in the Connection Name field; select your database brand from the Connection Type list; select the access type in the Access list at the bottom; then type in your database connection details into the fields in the Settings section on the right.

    The Access list changes according to the connection type you select; the settings section will change depending on which item in the access list you choose.
  7. Click the Test button to ensure that the connection settings are correct.

    • If they are not, the ensuing error message should give you some clues as to which settings need to be changed.
    • If the test dialog says that the connection to the database is OK, then click the OK button to complete the data source configuration.

Next steps

Now that your data source is configured, you must design or enter an SQL query before you can finish adding the data source. See Create Queries With SQL Query Designer for more details on using SQL Query Designer, or Dynamic Query Scripting for more information on building dynamic queries through scripts.

Passing security information to a report over a JDBC connection

You can use one of two options when you want to pass security-related information, (such as user name and password), associated with a report over a JDBC connection:

  • Choose from the list of predefined environment variables; for example, env::username or env::roles
  • Define your own specific environment variables to pass to the connection, (session or global), using the formula function, ENV, inside a hidden parameter. For example, =ENV("session:xaction_parameter_password") or =ENV("global:xaction_parameter_password") where xaction_parameter_password is the parameter defined in an .xaction.
Passing security information a JDBC connection

The available selections appear as drop-down options under JDBC Security Configuration when you click Edit Security in the JDBC Data Source dialog box.

Metadata

Follow this procedure to add a Pentaho Metadata XMI file as a data source in Report Designer:

Procedure

  1. Select the Data tab in the upper right pane.

    By default, Report Designer starts in the Structure tab, which shares a pane with Data.
  2. Click the yellow cylinder icon in the upper left part of the Data pane, or right-click Data Sets.

    A drop-down menu with a list of supported data source types will appear.
  3. Select Metadata from the drop-down menu.

    The Metadata Data Source Editor window will appear.
  4. Click Browse, navigate to your XMI metadata definition file, then click Open.

  5. Click the round green + icon to add a query, then type in a name for the new query in the Query Name field.

  6. Type in the name of the solution directory this metadata file pertains to into the Domain Id field.

    • If this XMI file was created with Pentaho Metadata Editor, then the domain ID must be the root directory for this solution -- the directory one level above pentaho-solutions, typically.
    • If you created this XMI with Pentaho Data Integration, then the domain ID must be set to the full solution path to the XMI, which would be something like this: example-solution/resources/metadata/mymeta.xmi.
    • If the domain ID is not properly defined, you will be able to preview the report, but you will not be able to publish it to the Pentaho Server.
  7. Click the pencil icon on the right above the Query field to start Metadata Query Editor, or type in your query directly into the Query field.

    See Create Queries With Metadata Query Editor for more details on Metadata Query Editor. You can also design a dynamic query via a script; see Dynamic Query Scripting.
  8. Click OK when your query is complete.

Pentaho Data Integration

Pentaho Data Integration (Kettle) .KTR files can act as a data source, but you must copy all the JAR files from pentaho/design-tools/data-integration/lib/ and all of its subdirectories except the JDBC subdirectory to pentaho/design-tools/report-designer/lib/.

Use the Pentaho Data Integration data source option if you want to create a report that contains data from any step in a Data Integration transformation. This is particularly useful if you want to create a report that includes data from transformation steps such as Splunk Input or Splunk Output. You must have a report file open in order to proceed, and your data source must be accessible before you can connect to it in Report Designer. As mentioned previously in this section, the first time you create a Kettle data source you must also copy all the JAR files from pentaho/design-tools/data-integration/lib/ and all its subdirectories except the JDBC subdirectory to pentaho/design-tools/report-designer/lib/ in order to access it through Report Designer.
NoteYour data source must not contain multi-select parameters. Data Integration does not accept array parameters; only strings are accepted.

Follow this procedure to add a Pentaho Data Integration (Kettle) data source in Report Designer.

Procedure

  1. Select the Data tab in the upper right pane.

    By default, Report Designer starts in the Structure tab, which shares a pane with Data.
  2. Click the yellow cylinder icon in the upper left part of the Data pane, or right-click Data Sets.

    A drop-down menu with a list of supported data source types will appear.
  3. Select Pentaho Data Integration from the drop-down menu.

    The Kettle Datasource window will appear.
  4. Click the round green + icon to add a new query.

  5. Type a concise yet sufficiently descriptive name into the Name field.

  6. Click Browse and navigate to your Pentaho Data Integration KTR file.

  7. Review the imported steps and modify their parameters accordingly, then click OK.

    • OLAP

      Report Designer only supports Pentaho Analysis (Mondrian) OLAP sources at this time.

      • Pentaho Analysis

        A Mondrian schema file.

      • Pentaho Analysis Denormalized

        A Mondrian schema file, denormalized.

      • Pentaho Analysis Legacy

        A Mondrian data source imported from a report created with a version of Report Designer older than 3.5.0.

    • XML

      An XQuery file.

    • Table

      Create your own data table by entering information manually, or importing it from an Excel spreadsheet file (XLS).

    • MongoDB

      Use data stored in this document-oriented NoSQL database.

    • Advanced

      The data sources in this category are typically for software developers and special-use cases.

      • JDBC (Custom)

        Allows designers to dynamically create a query from a formula or function.

      • Scriptable

        Allows designers to generate a data set via JavaScript, Bean Shell, Groovy, Netrexx, XSLT, JACL, or Jython.

      • External

        Used only if the report is going to run on the Pentaho Server, which means the data is retrieved via a component in an action sequence. The query name for the report must be mapped to the result set in the .xaction file.

OLAP

Before you begin

You may need to obtain database connection information from your system administrator, such as the URL, port number, JDBC connection string, database type, and user credentials
Follow this procedure to add a Pentaho Analysis (Mondrian) data source in Report Designer.

Procedure

  1. Select the Data tab in the upper right pane.

    By default, Report Designer starts in the Structure tab, which shares a pane with Data.
  2. Click the yellow cylinder icon in the upper left part of the Data pane, or right-click Data Sets.

    A drop-down menu with a list of supported data source types will appear.
  3. Select OLAP from the drop-down menu, then select one of the following:

    • Pentaho Analysis
    • Pentaho Analysis (Denormalized)
    • Pentaho Analysis (Legacy)
    The Mondrian Datasource Editor window will appear.
  4. If you want to provide parameters that contain different Mondrian connection authentication credentials, click the Edit Security button in the upper left corner of the window, then type in the fields or variables that contain the user credentials you want to store as a parameter with this connection.

    The role, username, and password will be available as a security parameter when you are creating your report.
  5. Click Browse, navigate to your Mondrian schema XML file, then click Open.

  6. Above the Connections pane on the left, click the round green + icon to add a new data source.

    If you installed the Pentaho sample data, several SampleData entries will appear in the list. You must have HSQLDB to view the sample data.
  7. In the subsequent Database Connection dialog box, type in a concise but reasonably descriptive name for this connection in the Connection Name field; select your database brand from the Connection Type list; select the access type in the Access list at the bottom; then type in your database connection details into the fields in the Settings section on the right.

    The Access list will change according to the connection type you select; the settings section will change depending on which item in the access list you choose.
  8. Click the Test button to ensure that the connection settings are correct.

    • If they are not, the ensuing error message should give you some clues as to which settings need to be changed.
    • If the test dialogue says that the connection to the database is OK, then click the OK button to complete the data source configuration.

Next steps

Now that your data source is configured, you must enter an MDX query before you can finish adding the data source. You can also create a dynamic query through scripts; see Dynamic Query Scripting for more information.

OLAP (advanced)

Follow this procedure to add a Pentaho Analysis (Mondrian) data source in Report Designer.
NoteOLAP (Advanced) data sources differ from standard OLAP data sources only in the method by which you design and enter the MDX query. Standard OLAP data sources allow for Report Designer's built-in Metadata Query Editor, whereas advanced OLAP data sources require you to build a formula to calculate the query, which gives you more power over report parameterization functionality. You can also create a dynamic query through scripts; see Dynamic Query Scripting for details.

Procedure

  1. Select the Data tab in the upper right pane.

    By default, Report Designer starts in the Structure tab, which shares a pane with Data.
  2. Click the yellow cylinder icon in the upper left part of the Data pane, or right-click Data Sets.

    A drop-down menu with a list of supported data source types will appear.
  3. Select OLAP from the drop-down menu, then select one of the following:

    • Pentaho Analysis
    • Pentaho Analysis (Denormalized)
    • Pentaho Analysis (Legacy)
    The Mondrian Datasource Editor window will appear.
  4. If you want to provide parameters that contain different Mondrian connection authentication credentials, click the Edit Security button in the upper left corner of the window, then type in the fields or variables that contain the user credentials you want to store as a parameter with this connection.

    The role, username, and password will be available as a security parameter when you are creating your report.
  5. Click Browse, navigate to your Mondrian schema XML file, then click Open.

  6. Above the Connections pane on the left, click the round green + icon to add a new data source.

    If you installed the Pentaho sample data, several SampleData entries will appear in the list. These sample data sources are useless if you do not have the Pentaho HSQLDB sample database installed, so if you don't have that, you can safely delete the SampleData entries. If you do have Pentaho's HSQLDB samples installed, it may be advantageous to leave the sample data sources intact in the event that you want to view the sample reports and charts at a later time.
  7. In the subsequent Database Connection dialog box, type in a concise but reasonably descriptive name for this connection in the Connection Name field; select your database brand from the Connection Type list; select the access type in the Access list at the bottom; then type in your database connection details into the fields in the Settings section on the right.

    The Access list will change according to the connection type you select; the settings section will change depending on which item in the access list you choose.
  8. Click the Test button to ensure that the connection settings are correct.

    • If they are not, the ensuing error message should give you some clues as to which settings need to be changed.
    • If the test dialogue says that the connection to the database is OK, then click the OK button to complete the data source configuration.

Next steps

Now that your data source is configured, you must enter an MDX query before you can finish adding the data source. This is done by selecting the Master Report in the Structure pane, then clicking the Attributes pane. See the Query attribute reference for more information.

XML

Follow this procedure to add a data source in Report Designer.

Procedure

  1. Select the Data tab in the upper left pane.

    By default, Report Designer starts in the Structure tab, which shares a pane with Data.
  2. Click the yellow cylinder icon in the upper left part of the Data pane, or right-click Data Sets. .

    A drop-down menu with a list of supported data source types will appear
  3. Select XML from the drop-down menu.

    The XML Datasource Editor window will appear.
  4. Click the round green + icon above the Available Queries field.

  5. Type a concise yet reasonably descriptive name into the Query Name field.

  6. Enter your XQuery into the Query field, then click Preview to ensure that it is valid.

  7. Click OK when your query is in order.

Table

Follow this procedure to add a Table data source in Report Designer.

Procedure

  1. Select the Data tab in the upper right pane.

    By default, Report Designer starts in the Structure tab, which shares a pane with Data.
  2. Click the yellow cylinder icon in the upper left part of the Data pane, or right-click Data Sets.

    A drop-down menu with a list of supported data source types will appear.
  3. Select Table from the drop-down menu.

    The Table Datasource Editor window will appear.
  4. Click the round green + icon to add a query, then type in a name for the new query in the Name field.

  5. Use the four buttons below the Name field to add or remove rows and columns appropriately, or click the Import Spreadsheet button in the lower left corner to pull in data from an XLS file.

  6. Enter or change any tabular data by selecting, then double-clicking a cell.

  7. Click OK when you're done creating your Table data source.

Other Data Sources

The following data sources require additional knowledge and skills:

  • JDBC Custom

    This is much like a standard JDBC connection, except you create a formula-based query through the Master Report's Attributes pane instead. See the Query attribute reference for more information.

  • Scriptable

    Select your language, then add a query by clicking the round green + button, give your query a name, then type in your script in the Query field.

  • External

    This data source is used when a report (.prpt - simple reporting component) is used in an .xaction. In the report, you must specify the result-set name for the query name attribute on the report. Also, you must add a report parameter using the same name as the result-set name and set the parameter to the tablemodel parameter type. The .xaction result-set can be MQL, SQL, MDX or JavaScript.

org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.​StaticDataFactorySample#createSubQuery(${Var1})

Advanced topics

The following topics help to extend your knowledge of data sources:

  • JNDI

    You can connect to a JNDI data source by editing the default JNDI properties file.

  • MongoDB

    You can connect to a MongoDB data source through additional configurations.