BPM Admin Guide

Prepare the Database

This section describes the steps you need to follow to prepare the database.

The Pentaho Data Integration tool requires the creation of the actual data warehouse database. To prepare the Pentaho repository, you must:

  • Create database users in either Oracle or SQL Server.
  • Create storage for BPM People Planner.

Create Database Users

To create database users in Oracle:

  1. From the command prompt, run the following: sqlplus system/manager

    It may be necessary to specifically connect to the database to be used.

  2. In sqlplus, enter the following:

    create user MACONOMY_DWH identified by MACONOMY_DWH;
    grant dba to MACONOMY_DWH;

  3. If you do not want to use default database settings for the user's default table space and temporary table space, you can change them with statements such as the following in sqlplus:

    alter user MACONOMY DWH default table space <x1> temporary table space <y1>;
    where <x1>, <x2>, <y1>, <y2> should be replaced by relevant values.

  4. To confirm that the settings work, enter the following:

    connect MACONOMY_DWH / MACONOMY_DWH;

  5. Exit sqlplus.

To create database users in MS SQL Server:

  1. Log in to Microsoft SQL Server Management Studio with the user name sa.
  2. Right-click Databases and create the database MACONOMY_DWH.
  3. In the Options tab, set collation to SQL_Latin1_General_CP1_CS_AS.
    Note: This makes the SQL data warehouse case-sensitive to match with Maconomy database for an accurate comparison of data for the ETL.
  4. Expand Security, right-click Logins, and select New Login.
  5. Enter the name MACONOMY_DWH for the login.
  6. Set Authentication to SQL Server Authentication.
  7. Enter a password.
  8. On the left-hand side of the window, click User Mappings.
  9. On the User Mappings dialog box, map the database to the corresponding user.
  10. Specify the role db_owner.
  11. To check that the databases work, log out and then log in again with the data warehouse database credentials.
Note: The Oracle database for the BPM data warehouse requires a table space called macindex_dwh, where indexes for the DWH are created. This table space must be created in addition to the normal table space of MACONOMY_DWH.

For more information, see Build the BPM Data Warehouse .

Create Storage for BPM People Planner

Use the steps above to create storage for BPM People Planner. Replace (what) with the user PEOPLEPLANNER_DWH.

For BPM People Planner other parameters in the kettle.properties file are needed:

  • #DWH_CONNECTION_TYPE = ORACLE
  • DWH_CONNECTION_TYPE = SQL
  • #PP_CONNECTION_TYPE = ORACLE
  • PP_CONNECTION_TYPE = SQL
  • #PP_STAND_ALONE = YES
  • PP_STAND_ALONE = NO
  • # How many years old AvailableHours data should be loaded into DWH
  • PP_ACTUAL_YEAR_RANGE_MIN = 2
  • # How many years in future since current date
  • # should be loaded for AvailableHours into DWH
  • PP_ACTUAL_YEAR_RANGE_MIN = 2

The "#" symbol means that all strings following it are comments. By changing its position, you can switch which RDBMS system to use to access the DWH database and which one to use for PeoplePlanner.

Parameter PP_STAND_ALONE defines how PeoplePlanner is used, meaning, is it used in integration with Maconomy system or as a standalone system.

Note: Existing Business Performance Management PeoplePlanner Reporting assumes that PeoplePlanner is used in integration with Maconomy - so, the reports do not work for a stand-alone solution (in this case you will be able to use the DWH only) .

The last two parameters define how many years before and after the current date should be loaded in the DWH.

Warning: Remember that for every additional year, a significant number of additional data is loaded. Therefore, use these parameters with care and only include the number of years required to cover the user's business needs.