IBM FileNet P8, Version 5.2.1            

Define the Case Monitor data stream, lookup table, view, dimension, and cube

You can define the Case Monitor data stream, lookup table, view, dimension, and cube for monitoring current data or for monitoring recent historical data in IBM® Cognos® Real-time Monitoring Workbench.

About this task

Important: If you plan to define a Case Monitor data stream, lookup table, view, dimension, and cube for monitoring recent historical data, wait until the beginning of a new quarter. If you define the data stream, lookup table, view, dimension, or cube a month or two after the beginning of the quarter, Case Monitor must retrieve all the historical data since the beginning of the quarter, which might cause a negative impact on system performance.

Procedure

To define the Case Monitor data stream, lookup table, view, dimension, and cube:

  1. Log on to IBM Cognos Real-time Monitoring Workbench.
  2. If you did not already do so, import the Case Monitor project from the CaseMonitor.jar file. See Import the IBM FileNet Case Monitor project.
  3. Modify the Case Monitor data stream object Workflow Data Stream.
    1. On the Workbench tab, expand the IBM FileNet® Case Monitor folder and select the Workflow Data Stream object.
    2. Click Edit, then modify the JDBC Query (Java™ Database Connectivity) as follows:
      select  d.DMWorkClass_key, Count(*) as WorkflowCount,
        avg(f.MinutesSinceCreation * 1.0) as avgProcTime,
        avg(f.VMAE_Amount) as avgAmount,
        sum(f.VMAE_Amount) as sumAmount,
        f.VMAE_Location_key as Location_key
      from V_F_DMWorkflowWIP f, D_DMWorkflow d 
      where d.Workflow_key = f.Workflow_key 
      group by d.DMWorkClass_key, f.VMAE_Location_key
    3. Click Resubmit Query. Notice that avgAmount, sumAmount, and Location_key are added to the columns for the event.
    4. Click Save Data Stream.
  4. Create a lookup table for the Location dimension.
    1. On IBM Cognos Real-time Monitoring Workbench, select the IBM FileNet Case Monitor folder.
    2. Click Activities and click Create New > Lookup Table.
    3. Select JDBC (Java Database Connectivity) for the type of lookup table source. Click Continue.
    4. Select an agent from the JDBC Connection list. If you already imported the preconfigured Case Monitor objects, you can use the provided CADB_JDBC_Agent for monitoring current data, or the CADB_Dimension_JDBC_Agent for monitoring recent historical data.
    5. Enter the following query in Query:
      select * from D_DMDataField_Location

      Click Continue.

    6. On the dialog, Configure Lookup Table - JDBC Source, enter the name and description for the new lookup table, such as Location_LookupTable.
      Tip: For clarity, use the name of the Case Analyzer dimension in the lookup table name. For example, for a Case Analyzer dimension named Location, name the lookup table Location_LookupTable.
      On the Data Caching tab, do the following tasks:
      • Enable Cache data for this lookup table.
      • Clear the Enable prefetch option.
      • Set Number of result sets to cache: 50 (enter a value based on the expected number of entries)

        The expected number of results is small to avoid excessive memory usage.

      • Set the Invalidation Schedule by clicking Add Schedule and set an interval value so that it is never invalidated.

        For example, set an invalidation schedule that runs once a year on February 31. There is no reason to invalidate the cache because the Case Analyzer dimension members will not change.

    7. Click Save Lookup Table.
  5. Edit the Workflow View object.
    1. Expand the IBM FileNet Case Monitor folder, select Workflow View, and click Edit.
    2. To add the new fields avgAmount, sumAmount, and Location_key, do the following for each field:
      1. Click Add Field.
      2. Click Edit on Field Expression.
      3. Select the field from the Field Expression list, click OK to add the field.
      4. Provide a name for the field.
    3. Click Save View.
  6. Create a dimension.
    1. Select the IBM FileNet Case Monitor folder.
    2. Click Activities and click Create New > Dimension.
    3. Enter the following values:
      • Name: Location_Dimension
      • Lookup Table Name: Location_LookupTable
      • Under Select Fields for hierarchy, select Location and move it to the Selected Fields table.
      • Under Select Key field, select the Key Field check box for Location_key.
    4. Click Save Dimension.
  7. Edit the Workflow Cube object.
    1. Expand the IBM FileNet Case Monitor folder, select the Workflow Cube object, and click Edit.
    2. On the dialog, Configure Cube, click Add Measure Column and enter the following values:
      • Measure Name: Average Amount

        Aggregate Expression for monitoring current data: AVG("IBM FileNet Case Monitor"."Workflow View".avgAmount)

        Aggregate Expression for monitoring recent historical: AVG("IBM FileNet Case Monitor"."Workflow Load View".avgAmount)

        Tip: Click Field Builder, then click Add Fields and select the appropriate field.
      • Measure Name: Total Amount

        Aggregate Expression for monitoring current data: SUM("IBM FileNet Case Monitor"."Workflow View".sumAmount)

        Aggregate Expression for monitoring recent historical: SUM("IBM FileNet Case Monitor"."Workflow Load View".sumAmount)

    3. Click Add Dimension and select the following values:
      • Dimension: Location_Dimension
      • Dimension Primary Key: Location_key
      • Fact Column Foreign Key: Location_key
    4. Click Save Cube.


Last updated: October 2015
bas_example_a_total_by_location_1.htm

© Copyright IBM Corporation 2015.