Create the Data Warehouse

This section describes how to create and load the data warehouse.

To prepare for creating the data warehouse, you must set up the connections to the Maconomy/People Planner database and the data warehouse database. Then you must run the ETL job that creates the data warehouse itself. All this is done in Spoon.

To create the data warehouse, you must:

• Set up a connection to Maconomy / People Planner
• Set up the Table Space
• Create the date warehouse

Set Up a Connection to Maconomy / People Planner

To set up a connection to Maconomy / People Planner, follow these steps:

1. Select Tools » Repository » Explore...
2. Switch to the Connections tab.
3. Select the Maconomy connection and click the Edit icon.

   Fill in the following fields:

   • Host Name - Enter the name of the Maconomy / People Planner server.
   • Database Name - Enter the name of the Oracle SID or MS SQL Server database name.
   • Tablespace for Data - Enter the name of the tablespace if the default is not used.
   • Tablespace for Indices - Enter the name of the tablespace for indices if the default is not used.
   • Port Number - Enter the port of Maconomy, typically 1521.
   • User Name - Enter the name of the Maconomy database user (short name).
   • Password - Enter the password for accessing the database user.
4. Click Test to test the connection. Click OK.
5. Select the DWH connection and click the Edit icon.
6. Fill in the following fields:
   • Host Name - Enter the name of the Data warehouse server.
   • Database Name - Enter the name of the Oracle SID or MS SQL Server database name.
   • Tablespace for Data - Enter the name of the tablespace if the default is not used.
   • Tablespace for Indices - Enter the name of the tablespace for indices if the default is not used.
   • Port Number - Enter the port of Maconomy, typically 1521.
   • User Name - Enter MACONOMY_DWH.
   • Password - Enter MACONOMY_DWH.
7. Click Test to test the connection. Click OK.
8. Click Close.

Set Up the Table Space

For BPM People Planner, set up the right table space.

To set up the table space, follow these steps:

1. In this transformation you should edit either do the Oracle DWH Variables step or the Oracle DWH Variables step (depending upon your DWH connection type) and set the right tablespace for the constant DWH_INDEX_TABLESPACE:

   

2. Click OK and Save.

Create the Data Warehouse

Before you begin this section, note the following:

• Because the Create job starts by dropping the full data warehouse, it should not be used for upgrading a data warehouse.
• Drop indexes on facts could fail the first time you run this job. If the enhanced reporting views are not in place, the job fails. This may take a long time to finish, depending on the size of the Maconomy database.
• Only run the Drop or DropTablesAndViews jobs if you really want to remove the full data warehouse completely. Performing a full load does not require running DropTablesAndViews first.

To create the data warehouse, follow these steps:

1. Select Tools > Repository > Explore.
2. Double-click the job Create.
   Note: For BPM People Planner the jobs are PP_CreateTablesAndViews and PP_LoadData.
3. Run this job.
   Note: For BPM People Planner first run PP_CreateTablesAndViews and then PP_LoadData. The PP_DropTablesAndViews removes the People Planner data warehouse.

   The above Create job drops the full data warehouse (in case one should already exist), then creates tables and views and performs a full load. This could take some time depending on the size of the Maconomy / People Planner database.

   The job initiates by dropping the data warehouse. If this is the first time this is done, errors may occur because the job tries to drop something that does not exist. Ignore these errors.

   Note: To verify, open the command prompt, type sqlplus maconomy_dwh/maconomy_dwh , and then select table_name from user_tables.
   Note: The People Planner reports and universe use data from Maconomy data warehouse and therefore cannot be used when Business Performance Management BI for PP is installed in standalone. The universe for standalone mode has not been implemented at the moment.