Skip to main content
Pentaho Documentation

Set Up DI Operations Mart

Overview

Explains how to set up the DI Operations Mart on PDI.

The DI Operations Mart is a centralized data mart that stores job or transformation log data for easy reporting and analysis. The data mart is a collection of tables organized as a data warehouse using a star schema. Together, dimension tables and a fact table represent the logging data. These tables need to be created in the DI Operations Mart database. Pentaho provides SQL scripts to create these tables for MySQL, Oracle, and PostgresSQL databases. A Data Integration job populates the time and date dimensions.

Unzip DI Operations Mart Files

The files needed to install the DI Operations Mart are already on your computer. Here is how to find and unzip those files, then set up the database.

  1. Unzip the pentaho-operations-mart-<version number>.zip file, which is located in the data-integration-server/pentaho-solutions directory. <version number> indicates the version number Pentaho assigns to the build of the DI Operations Mart. We recommend that you unzip the file in a temporary directory outside of the pentaho directory structure.
  2. When unzipped, the directory structure looks like this.
  • pdi-operations-mart/
  • DDL/
  • etl/
  • models/
  • pentaho_operations_mart/
  • samples/
  • exportManifest.xml

Create Logging Tables and Datamart

Logging tables must be created in the database.

  1. From any program that can run scripts against the logging data database, execute pentaho_logging_<database>.sql. (<database> is MySQL, Oracle, or PostgreSQL). pentaho_logging_<database>.sql is in the pdi-operations-mart/DDL/<database> directory. The script generates several logging schemas. 
  2. If you installed PDI using the custom or manual method, the pdi-operations-mart database must be created. Execute the DDL script called pentaho_mart_<database>.sql, where <database> is is the database vendor, such as MySQL, Oracle, or PostgreSQL. The DDL script can be found in the pdi-operations-mart/DDL/<database>  directory. 
  3. When complete, the following tables are created.
Dimension Tables Fact, Pro-Audit, and Staging Tables
  • dim_batch
  • dim_component
  • dim_content_item
  • dim_date
  • dim_execution
  • dim_executor
  • dim_instance
  • dim_log_table
  • dim_session
  • dim_state
  • dim_step
  • dim_time
  • fact_component
  • fact_execution
  • fact_instance
  • fact_jobentry_execution
  • fact_perf_execution
  • fact_session
  • fact_step_execution
  • pro_audit_staging
  • pro_audit_tracker
  • stg_content_item

 

  1. Execute the following SQL script in the tool of your choice.  The script enables DI Operations Mart to run on PostgreSQL.

ALTER TABLE pentaho_dilogs.jobentry_logs DROP COLUMN result;
ALTER TABLE pentaho_dilogs.jobentry_logs ADD COLUMN "result" boolean;
ALTER TABLE pentaho_operations_mart.fact_jobentry_execution DROP COLUMN result;
ALTER TABLE pentaho_operations_mart.fact_jobentry_execution ADD COLUMN "result" CHAR(5);

Set Global Kettle Logging Variables

Set up Kettle logging variables so that by default, transformation, job, step, and performance logging data is captured in tables you specify.

  1. In Spoon, select Edit > Edit the kettle.properties file.
  2. In the Kettle properties window, set the following variable names and values. The variable names and values in this section are the default. If you have set up your operations mart differently, change the variable names and values so they match your environment.
Variable Name Value
KETTLE_JOB_LOG_DB live_logging_info
KETTLE_JOB_LOG_TABLE job_logs
KETTLE_TRANS_LOG_DB live_logging_info
KETTLE_TRANS_LOG_TABLE trans_logs
KETTLE_STEP_LOG_DB live_logging_info
KETTLE_STEP_LOG_TABLE step_logs
KETTLE_JOBENTRY_LOG_DB live_logging_info
KETTLE_JOBENTRY_LOG_TABLE jobentry_logs
KETTLE_TRANS_PERFORMANCE_LOG_DB live_logging_info
KETTLE_TRANS_PERFORMANCE_LOG_TABLE transperf_logs
KETTLE_CHANNEL_LOG_DB live_logging_info
KETTLE_CHANNEL_LOG_TABLE channel_logs
KETTLE_METRICS_LOG_DB live_logging_info
KETTLE_METRICS_LOG_TABLE metrics_logs
  1. If you choose to use a non-default schema, set these variables as well. Their values should match the name of your schema.
  • KETTLE_JOB_LOG_SCHEMA
  • KETTLE_TRANS_LOG_SCHEMA
  • KETTLE_STEP_LOG_SCHEMA
  • KETTLE_JOBENTRY_LOG_SCHEMA
  • KETTLE_TRANS_PERFORMANCE_LOG_SCHEMA
  • KETTLE_CHANNEL_LOG_SCHEMA
  • KETTLE_METRICS_LOG_SCHEMA
  1. Click OK.
  2. Restart Spoon and the DI Server.

Import DI Operations Mart

Follow these instructions to import DI Operations Mart tables, schema, and database connection into your database.

  1. Start Spoon, then connect to the DI Repository.
  2. Select Tools > Repository > Import Repository.
  3. Select etl/pdi-operations-mart.xml then click Open. The etl directory is where you unzipped the pentaho-operations-mart-<version number>.zip file.
  4. When prompted about setting rules for the import, click No.
  5. The Directory Selection window appears. To create a new directory where the PDI Operations Mart will be stored:
    1. Right-click the public folder and select New sub-directory.
    2. In the Enter the Directory Name window, type pdi_operations_mart then click OK.
    3. pdi_operations_mart appears. Make sure it is highlighted, then click OK.
    4. Click OK in the Enter comment window.
  6. The Repository Import window appears with a status of the import. When the Import finished message appears, click the Close button.

Set Up Database Connections

The connection information must match the credentials needed for your database.

  1. In Spoon, close open jobs and transformations.
  2. Select Tools > Repository > Explore from the drop-down menu.
  3. In the Repository Explorer window, select the Connections tab.
  4. Create a database connection and name it ive_logging_info. In the Database Connection window enter information for the connection that gets the online logging data. The default connection information appears in the following table, but yours might be different, depending on the ports and password assigned during PDI installation.
Item Default Value
Connection Name live_logging_info
Hostname localhost
Database Name di_hibernate
Port Number 5432
User Name hibuser
Advanced/Preferred Schema pentaho_dilogs
Password See your system administrator for the default value.
  1. Click OK to save the information.
  2. If you need to have multiple connections to get the online logging information, create new connections and enter the information for each connection.
  3. Select the pentaho_operations_mart from the Connections tab and edit the entry by clicking the pencil icon in the upper-right corner. The Database Connection window opens.
  4. Enter the information for the connection. When finished, click OK.
Item Default Value
Connection Name pentaho_operations_mart
Hostname localhost
Database Name di_hibernate
Port Number 5432
User Name hibuser
Advanced/Preferred Schema pentaho_operations_mart
Password See your system administrator for the default value.

Update Links

Update the Fill in DIM_Date and DIM_Time job so it points to the version in the repository, then run the Fill in DIM_Date and DIM_Time job to finish setting up the DI Operations Mart.   

  1. In Spoon, select Tools > Repository > Explore.
  2. In the Browse tab, click the pdi_operations_mart.
  3. Double-click Fill in DIM_Date and DIM_Time to open it in Spoon.
  4. Click Close to close the Repository Explorer.
  5. Double-click Generate DIM_DATE.
    1. In the Transformation specification tab, click Specify by name and directory.
    2. There are two fields that become active. The first is where you specify the directory. The second is where you specify the name of the transformation. Position the cursor in the first field, then press [CTRL]+[SPACE] and select ${Internal.Job.Repository.Directory} from the options that appear.
    3. In the second field enter Generate_DIM_DATE.ktr.
    4. Click OK.
  6. Double-click Generate DIM_TIME and repeat step 5a - 5d.  For step 5c, enter Generate_DIM_TIME.ktr instead of Generate_DIM_DATE.ktr.
  7. Save the job, then run it.
  8. When complete, close the job.

Test DI Operations Mart 

To run a test, build and run a simple transformation or job, then run the DI Operations Mart.

  1. In Spoon, create and save a transformation and a job. For more information on how to create a transformation or job, see the Get Started with DI tutorial.
  2. Select Tools > Repository > Explore, then select pdi_operations_mart.
  3. Select the Update Dimensions then Logging Datamart.kjb job, then run it.

Schedule DI Operations Mart Job

As a best practice, you should schedule the updating of Pentaho Operations Mart data so that you have up-to-date information for Operations Mart reports. It is recommended that you schedule log updates at least daily to ensure you have the freshest data.

  1. In Spoon, select Tools > Repository > Explore, then select pdi_operations_mart.
  2. Select the Update_Logging_Datamart.kjb job and open it.
  3. Set a schedule for this job by following the instructions in Scheduling Transformations and Jobs from Spoon.
  4.  When finished, close and save the schedule and the job.

Give Users Access to the DI Operations Mart

You must have previously mapped user roles, as described in Mondrian Role Mapping in the BA Server.

By default, only users who have the Admin role can access the Pentaho Operations Mart. The Admin role has access to all capabilities within all Pentaho products, including the Pentaho Operations Mart. If you want to allow users to view and run the Pentaho Operations Mart only, you can assign them the Pentaho Operations role. For example, a user who has been assigned the Pentaho Operations user role is able to open and view a report within the DI Operations mart, but does not have the ability to delete it.

To give users access to view the DI Operations Mart, assign the Pentaho Operations role to those users.

  1. From within the Pentaho User Console, select the Administration tab.
  2. From the left panel, select Security > Users/Roles.
  3. Select the Roles tab.
  4. Add the new role called Pentaho Operations by following the instructions in Adding Roles.
  5. Assign the appropriate users to the new role, as described in Adding Users to Roles.
  6. Advise these users to log in to the Pentaho User Console, create a Pentaho Analyzer or Pentaho Interactive Report, and ensure that they can view the Pentaho Operations Mart in the Select a Data Source dialog.