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 and 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 the query 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.
    5. Enter a name for the event: Work Item Amount Bucket Event.

      On the Polling tab, set the poll interval to 300 seconds.

      Click the Clear State Interval tab. Select Always Clear State (Every Event).

      (For work in progress, you generally want the current value of items in the queues—you don't want to add the current value detected at each polling interval to the previous value.)

    6. Select Treat all rows in the ResultSet as a single Event.
    7. Click Save Event.
  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 and click New View.
    1. Select Work Item Amount Bucket Event as the basis for the new view. Click Next.
    2. Click OK—do not add additional contexts.
    3. Enter a view name: Work Item Amount Bucket View
    4. Drag the entire folder from the Workset into the Fields area.

      From Clause should contain the name of the bucket event.

    5. Click Save View.
  5. Click the Dimensions folder. Confirm that it contains Location_dimension.
  6. Click the Cubes folder and 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 and 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 and 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 and click Create Object.
    1. For the data source, select Work Item Amount Bucket Cube. Click Continue.
    2. Select Chart, and Line and Combination Chart. Click Continue.
    3. For the 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 the new dashboard object to one of the existing dashboards.
    1. Under Navigation Tree, select Dashboards.
    2. In the right column, select Work Items. 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.