BPM Admin Guide

Set Templates for Web Intelligence

This section describes the steps you need to follow to work with templates for Web Intelligence.

You must change the template for Webl when there is a new document. This is not an option for existing documents. The Maconomy standards are implemented in the defaultConfig.xml released file.

To set templates for WebI, follow these steps:

  1. Make a backup copy of the AnalyticalReporting.war file from (default) C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\java\applications.
  2. Open the AnalyticalReporting.war file and replace the three versions of defaultConfig.xml with the changed versions. Note the original paths:
    • webiApplet\AppletConfig\defaultConfig.xml
    • webiApplet\AppletConfig\AppletConfig\defaultConfig.xml
    • WEB-INF\classes\defaultConfig.xml
  3. From services, Stop tomcat.
  4. From a command-line prompt, change the current directory to C:\Program Files\Business Objects\deployment.
  5. Enter the command wdeploy tomcat55 predeployall.
  6. If no errors are displayed, enter the command wdeploy tomcat55 deployall.
  7. From services, Start tomcat.
  8. Create a WebI document to check the template's performance.
  9. Restart the server if necessary.

Enabling Cache and Pre-Load of Cache in ETL

Enabling cache on ETL effectively improves the load speed of both incremental and full loads in the data warehouse. It stores the values of keys that had been looked up at least once and returns these values the next time that these are looked up again. This reduces the need to do a database call every time a lookup step is processed.

Note: Cache uses memory to store the looked-up key, and you risk running out of memory when it is enabled on very large tables.

You can enable cache:

  • Manually
  • Using an SQL Script

Enable Caching Manually

To enable caching manually, follow these steps:

  1. Open the transformations that contain dimensional or database lookup steps.
  2. Right-click the lookup step and select Edit Step or double click it.
  3. Select the Enable the cache? check box.
  4. If Pre-load the cache? is enabled, select it as well. This check box is disabled if the Update the dimension? check box is selected.
  5. Set Cache size in rows to 0 to enable caching of all rows.
  6. Click OK to apply changes.
  7. Apply these changes to all other lookup steps except Pre-load the cache? on TASK_ID because this is typically a very large table and will result in an out of memory error. Set Cache size in rows to 1 for this lookup and then append ORDER BY TASK_LIST, TASK_NAME on the SQL statement of the Table Input step. TASK_ID lookup step is present on the following transformations:
    • Job Entry
    • Purchase Order Line
    • Job Invoice Line
    • Budget Entry
    • Finance Entry
    • Job Budget Line

Enable Caching Using an SQL Script

Another way to enable the cache and pre-load the cache is by using an SQL script.

To enable caching using an SQL script, complete the following steps:

  1. Log in to the database repository for your ETL and run the following SQL statements.

    -- enables cache for Dimensional Lookup steps except TASK_ID
    UPDATE R_STEP_ATTRIBUTE
    SET VALUE_NUM = 0 , VALUE_STR = NULL
    WHERE ID_STEP_ATTRIBUTE IN (
    SELECT ID_STEP_ATTRIBUTE
    FROM R_STEP_ATTRIBUTE
    WHERE ID_STEP IN (
    SELECT ID_STEP
    FROM R_STEP
    WHERE ID_STEP_TYPE IN
    (SELECT ID_STEP_TYPE
    FROM R_STEP_TYPE
    WHERE CODE IN ( 'DimensionLookup' )) AND
    NAME <> 'TASK_ID' ) AND
    CODE IN ( 'cache_size' )
    );
    - -enables pre-load cache for Dimensional Lookups except TASK_ID
    UPDATE R_STEP_ATTRIBUTE
    SET VALUE_STR = 'Y'
    WHERE ID_STEP_ATTRIBUTE IN (
    SELECT ID_STEP_ATTRIBUTE
    FROM R_STEP_ATTRIBUTE
    WHERE ID_STEP IN (
    SELECT ID_STEP
    FROM R_STEP
    WHERE ID_STEP_TYPE IN
    (SELECT ID_STEP_TYPE
    FROM R_STEP_TYPE
    WHERE CODE IN ( 'DimensionLookup' )) AND
    NAME <> 'TASK_ID' ) AND
    CODE IN ( 'preload_cache' )
    );
    --set cache-size to 1 for Dimensional Lookup TASK_ID
    UPDATE R_STEP_ATTRIBUTE
    SET VALUE_NUM = 1 , VALUE_STR = NULL
    WHERE ID_STEP_ATTRIBUTE IN (
    SELECT ID_STEP_ATTRIBUTE
    FROM R_STEP_ATTRIBUTE
    WHERE ID_STEP IN
    (     SELECT ID_STEP
    FROM R_STEP
    WHERE ID_STEP_TYPE IN
    ( SELECT ID_STEP_TYPE
    FROM R_STEP_TYPE
    WHERE CODE IN
    (     'DimensionLookup' )) AND
    NAME = 'TASK_ID' ) AND
    CODE IN ( 'cache_size' )
    );
    --disables pre-load cache for TASK_ID Dimensional Lookup steps
    UPDATE R_STEP_ATTRIBUTE
    SET VALUE_NUM = 0 , VALUE_STR = 'N'
    WHERE ID_STEP_ATTRIBUTE IN (
    SELECT ID_STEP_ATTRIBUTE
    FROM R_STEP_ATTRIBUTE
    WHERE ID_STEP IN ( SELECT ID_STEP
    FROM R_STEP
    WHERE ID_STEP_TYPE IN
    ( SELECT ID_STEP_TYPE
    FROM R_STEP_TYPE
    WHERE CODE IN
    (     'DimensionLookup' )) AND
    NAME = 'TASK_ID' ) AND
    CODE IN ( 'preload_cache' )
    );
    --enables cache for Database Lookup Steps (VALUE_STR = 'Y')
    UPDATE R_STEP_ATTRIBUTE
    SET VALUE_STR = 'Y'
    WHERE ID_STEP_ATTRIBUTE IN (
    SELECT ID_STEP_ATTRIBUTE
    FROM R_STEP_ATTRIBUTE
    WHERE ID_STEP IN (
    SELECT ID_STEP
    FROM R_STEP
    WHERE ID_STEP_TYPE IN
    ( SELECT ID_STEP_TYPE
    FROM R_STEP_TYPE
    WHERE CODE IN ( 'DBLookup' )))
    AND
    CODE IN ( 'cache' )
    );
    --set cache size to 0 for Database Lookup Steps (VALUE_NUM = 0)
    UPDATE R_STEP_ATTRIBUTE
    SET VALUE_NUM = 0 , VALUE_STR = NULL
    WHERE ID_STEP_ATTRIBUTE IN (
    SELECT ID_STEP_ATTRIBUTE
    FROM R_STEP_ATTRIBUTE
    WHERE ID_STEP IN (
    SELECT ID_STEP
    FROM R_STEP
    WHERE ID_STEP_TYPE IN
    ( SELECT ID_STEP_TYPE
    FROM R_STEP_TYPE
    WHERE CODE IN ( 'DBLookup' )))
    AND
    CODE IN  ( 'cache_size' )
    );
    COMMIT;
  2. After running the provided SQL statement, commit the changes. Then you must manually update transformations that contain TASK_ID (see step 7 on how manually enable cache.)

Servers with Low Memory (RAM)

Pentaho Data Integration 5.4 has a high memory consumption. This is due to Pentaho Data Integration 5.4 installation, as well as installing Tomcat 6 as a necessary component to run the Data Integration Server. However, it consumes a steadily increasing amount of memory, which could be detrimental to systems with low memory.

Data Integration Server is mainly used for scheduling, execution, security, and content management. Since Deltek currently uses a different method of scheduling ETL loads, you can disable it.

Note: This process is optional and is only necessary for servers with low memory (RAM).

To disable Data Integration Server, complete the following steps:

  1. Click Start, then type Services.msc.
  2. The Services window is displayed. Locate a service called Data Integration Server.

  3. Right-click and select Properties . The Date Integration Server Properties dialog is displayed.
  4. In the Startup type field, change the selection to Manual so that the service does not run automatically when you restart the system.

  5. Click Stop to disable the service, then OK.