BAM example - Work items by Amount categories (buckets)

In our example workflow, we have a sales amount and a location—N, E, S, or W. (See Business Activity Monitor example - data source setup for the procedure for creating the workflow and exposing the data fields.) For this example, we will monitor work items in each location, grouping them into three amount categories:

NOTE  Before you continue with this example, be sure that you have completed the following steps:

  1. Set up the data stream using the procedure in Business Activity Monitor example - data source setup.
  2. Specify the Context for the Location dimension. See BAM example - Sum and average Amount by Location.

Define the BAM event, view, and cube in FileNet Application Workbench

  1. Log on to FileNet Application Workbench.
  2. Under Tables and Views, click the Events folder, then click New Event.
    1. Select Single Event, and click Continue.
    2. Select JDBC, and click Continue.
    3. In the New Event dialog box, enter the following query into the Query box. This query establishes the three categories.
      (You can copy and paste it from this topic.)

      select SUM(Amount_LT_2000Count) as Amount_LT_2000Count,
             SUM(Amount_2000_5000Count) as Amount_2000_5000Count,
             SUM(Amount_GT_5000Count) as Amount_GT_5000Count,
             DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key
      from
      (
      select count(*) as Amount_LT_2000Count,
             0 as Amount_2000_5000Count,
             0 as Amount_GT_5000Count,
             DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key
      from F_DMWIP
      where VMAE_Amount<2000
      group by DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key
      union
      select 0 as Amount_LT_2000Count,
             count(*) as Amount_2000_5000Count,
             0 as Amount_GT_5000Count,
             DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key
      from F_DMWIP
      where VMAE_Amount>=2000 and VMAE_AMOUNT<=5000
      group by DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key
      union
      select 0 as Amount_LT_2000Count,
             0 as Amount_2000_5000Count,
             count(*) as Amount_GT_5000Count,
             DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key
      from F_DMWIP
      where VMAE_Amount>5000
      group by DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key
      )
      as UnionResults
      group by DMUser_key, DMOperation_key, DMStep_key, VMAE_Location_key

    4. Click Continue, then enter a name for the event: Work Item Amount Bucket Event.

      Change the poll interval to 300 seconds

    5. Click Save Event.

      CAUTION  When you create a new event, you must modify the XML source for that event in order for the system to properly calculate the values returned from the query. See Enable "Clear State" for instructions. If you do not make this modification, values for each query will be added to the previous values, rather than reflect the values retrieved by the query.

  3. Click the Contexts folder and confirm that it contains Location_Context. (If not, see BAM example - Sum and average Amount by Location for instructions.)
  4. Click the Views folder, then click New View.
    1. Select Work Item Amount Bucket Event as the basis for the new view.
    2. Click Next.
    3. Click OK—do not add additional contexts.
    4. Enter a view name: Work Item Amount Bucket View
    5. Drag the entire folder from the Workset into the Fields area.
    6. From Clause should contain the name of the bucket event.
    7. Click Save View.
  5. Click the Dimensions folder. Confirm that it contains Location_dimension.
  6. Click the Cubes folder, then click New Cube.
    1. Enter a name:  Work Item Amount Bucket Cube.
    2. Select Fact Table:  Work Item Amount Bucket View
    3. Click Add Measure Column, then enter the following:

      (Note that the Measure names will display in the dashboard object.)


       
      Measure Name Aggregate Expression
      <2000 SUM("Work Item Amount Bucket View".Amount_LT_2000Count)
      ( TIP Use the Field Builder to select the field.)
      2000-5000 SUM("Work Item Amount Bucket View".Amount_2000_5000Count)
      >5000 SUM("Work Item Amount Bucket View".Amount_GT_5000Count)
    4. Click Add Dimension, then select the following.
       
      Dimension Dimension Primary Key Fact Column Foreign Key
      Location_Dimension Location_key VMAE_Location_key
      User_dimension DMUser_key DMUser_key
      Queue_Dimension DMOperation_key DMOperation_key
    5. Click Save Cube.

    This completes the setup tasks in Application Workbench.
     

Configure the dashboard object and dashboard to display the data from the Work Item Amount Bucket Cube

  1. Log on to FileNet Operational Dashboard.
  2. Under Navigation Tree, select Dashboard Objects.
  3. Click the Dashboard Objects folder, then click Create Object.
    1. For the data source, select Work Item Amount Bucket Cube. Click Continue.
    2. Select Chart, and Line and Bar Chart (Basic). Click Continue.
    3. For your chart Value (Y-Axis), select all three items: <2000, 2000-5000, and >5000. Specify Chart Type Bar for each.
    4. For the chart category (X-Axis), select Location_Dimension. You can easily change it later. Click Continue.
    5. Enter a name for this dashboard object: Work Item Amount Buckets. Click Save.
  4. Add your new dashboard object to one of the existing dashboards.
    1. Under Navigation Tree, select Dashboards.
    2. In the right column, select Work Items, then click Edit Dashboard and select Edit Dashboard Content.
    3. In the list of available content, select Work Item Amount Buckets. Click Continue at the bottom of the window.
    4. Click Save to save Work Item Amount Buckets object in the Work Items dashboard.