Skip to main content
Pentaho Documentation

Create Database Sources

When you create a Database Table data source, you are presented with two options: Reporting Only or Reporting and Analysis. The choice you make depends on the structure of the database tables you are accessing and the User Console tools being used.

Before you begin working with Database Table data sources, there are a few key terms that you should know.

  • Fact table - records measurements or metrics for a specific event, and usually consists of numbers.
  • Star schema - has one or more fact tables that reference any number of dimension tables. Star schemas are good for simpler queries.
  • Snowflake schema - has centralized fact tables that are connected to multiple dimensions. Snowflake schemas are good for more complex queries.
Table 1. Database Data Sources Decision Table
Explore Considerations Choose Options
Reporting Only Reporting and Analysis
Summary Choose the Reporting Only option if you are accessing data in a relational database that is operational or transactional in nature, or if you need to show detailed rows and then sort and filter the details.

When you choose the Reporting Only option, a relational model is automatically generated and can be refined in the Data Source Model Editor or further extended using Metadata Editor.

Note: Relational models on large databases are sometimes slow and benefit from a powerful database.

Choose the Reporting and Analysis option if you are accessing a multidimensional database that contains tables arranged in a star schema (tables are separated into dimensions related to a single fact table, such as a data warehouse), or if you need summaries and details.

When you choose the Reporting and Analysis option, a multidimensional model is automatically generated and can be refined in the Data Source Model Editor or further extended using Schema Workbench.

Additionally, you can create a Reporting and Analysis data source using a single table where the table acts as the single fact table and also contains dimensional data.

Expertise Knowledge of relational database structures. Knowledge of multidimensional database structures.
Time Approximately 15 minutes. Approximately 30 minutes.
Recommendation Select this if you want to use for Report Designer, Interactive Reports, and Metadata Editor. Select this if you want to use for Report Designer, Analyzer (PUC), Dashboard Reports, Schema Workbench, and Aggregation Designer.

Create a Database Table Data Source for Reporting Only

Your database must be up and running in order for you to complete these steps.

  1. Login to the User Console.
  2. Click the Create New button, then choose Data Source from the menu.
  3. Click the New Data Source button. TheData Source Wizard appears.
  4. Enter a name that identifies your new data source in the Data Source Name field. The following characters are not allowed in Data Source names:
    %/:[]*|\t\r\n
    

    ssReportingOnly1x.png

  5. Select Database Table(s) from the Source Type drop-down menu. Under Connection, click to choose a database connection.
  6. Click to select the Reporting Only radio button. Click Next.
  7. Choose a schema from the Schema drop-down menu. A list of Available Tables appears.

    ssReportingOnly2x.png

  8. Choose a table from Available Tables and click the right arrow (Add) to move the table into the Selected Tables field. If you add a table you decide not to use, highlight the table and click the left arrow (Remove) to remove it from the list of Selected Tables. To choose multiple tables, press the CTRL key down as you make your selections.
  9. Click Next. The Define Joins window appears. While you can create a data source from a single table, the more common scenario is that you will choose multiple tables which must then be joined.

    ssReportingOnly3x.png

  10. Choose the fact table from the Left Table drop-down menu, then choose a table to join to it from the Right Table drop-down menu. Click Create Join to define the inner join between each table.
  11. Create join conditions for each entry in the Left Table and Right Table drop-down menus. The join relationship for each table is based on the field selected and is displayed in the lower portion of the dialog box. To delete a join, select the join and click Delete Join.
  12. Click Finish.The Data Source Created window appears. You can choose to Keep default model or click Customize model now now to launch the Data Source Model Editor and refine the model. Click OK.

ssGettingStartedDataSourceCreatedx.png

A relational model is generated for use in Interactive Reports and Dashboard reports, or the Data Source Model Editor appears.

Create a Database Table Data Source for Reporting and Analysis

Your database must be up and running in order for you to complete these steps.

  1. Login to the User Console.
  2. Click the Create New button, then choose Data Source from the menu.
  3. Click the New Data Source button. TheData Source Wizard appears.
  4. Enter a name that identifies your new data source in the Data Source Name field. The following characters are not allowed in Data Source names:
    %/:[]*|\t\r\n
    

    ReportingAndAnalysis1x.png

  5. Select Database Table(s) from the Source Type drop-down menu. Under Connection, click to choose a database connection.
  6. Click to select Reporting and Analysis. Click Next.
  7. Choose a schema from the Schema drop-down menu. A list of Available Tables appears.

    ReportingAndAnalysis2x.png

  8. Choose a table from Available Tables and click the right arrow (Add) to move the table into the Selected Tables field. If you add a table you decide not to use, highlight the table and click the left arrow (Remove) to remove it from the list of Selected Tables. To choose multiple tables, press the CTRL key down as you make your selections.
  9. Choose a Fact Table from the drop-down list and click Next. While you can create a data source from a single table, the more common scenario is that you will choose multiple tables which must then be joined.

    ReportingAndAnalysis3x.png

  10. Choose the fact table from the Left Table drop-down menu, then choose a table to join to it from the Right Table drop-down menu. Click Create Join to define the inner join between each table.
    1. Create join conditions for each entry in the Left Table and Right Table drop-down menus.
    The join relationship for each table is based on the field selected and is displayed in the lower portion of the dialog box. To delete a join, select the join and click Delete Join.
  11. Click Finish. The Data Source Created window appears. You can choose to Keep default model or click Customize model now to launch the Data Source Model Editor and refine the model. Click OK.

    ssGettingStartedDataSourceCreatedx.png

A multidimensional model is generated for use in Analyzer, Interactive Reports, and Dashboard reports, or the Data Source Model Editor appears.

Before trying to access your new data source in the PDI client (also known as Spoon), you must clear out any shared objects in the PDI client by selecting Tools > Repository > Clear Shared Object.