Export Oracle BPM metrics to a data warehouse

Managers and teamleads rely on measurements to know how processes and teams are performing. With an increased BPM effort and automated support of processes, more and more questions on solid information from these systems rise. Oracle BPM offers an easy way to export metrics to a data warehouse. Oracle BPM captures standard and business specific metrics and exposes these through a BPM process star schema.

Export Oracle BPM metrics to a data warehouse

To export Oracle BPM metrics to a data warehouse the SOAINFRA schema in which all configuration and (runtime) instance data is stored offers a number fact tables. These standard views enable the extraction of information for BI usage. For Oracle BPM release prior to PS4FP (11.1.1.5.1) the views have to be created manually. From patchset 4 onwards the process has been automated. Please note that it is not recommended to run the BI reports on the BPM process database.

An overview of the standard fact tables:

  • BPM_PROCESS_PERFORMANCE_V – offers standard metrics (like start and end time and running time in seconds) for completed processes.
  • BPM_ACTIVITY_PERFORMANCE_V – offers standard metrics for completed activities, completed intervals, measurement marks and counters for both in-flight and completed process instances.
  • BPM_PROCESS_INSTANCE_V – offers standard metrics for in-flight process instances. Because of that the information is only relevant at the time the view is queried. When processes move forward the information in this view refelcts the progress.
  • BPM_ACTIVITY_INSTANCE_V – offers standard metrics for in-flight activities and interval instances.

An overview of the dimension tables:

  • BPM_PROCESS_DEFINITION_V – including data on domain, composite, label and revision.
  • BPM_ACTIVITY_DEFINITION_V – including the type of activity: UserTask, Gateway, Event, Measurement interval, etc.
  • BPM_ROLE_DEFINITION_V – also allows you to see whether the role is the process owner.
  • CUBE_INSTANCE
  • COMPOSITE_INSTANCE

Example queries of BPM process metrics

Average process running time by process:

SELECT process_name
,      avg(process_running_time_in_msec)
FROM   bpm_process_performance_v 
GROUP  BY process_name
;

The number of faults by process:

SELECT process_name
,      COUNT(sequence_id)
FROM   bpm_process_performance_v
WHERE  process_discriminator = 'instance_system_fault'
GROUP BY process_name
;

When you run the process in multiple domains on the same server join with the BPM_PROCESS_DEFINITION_V and differentiate on DOMAINNAME.

Example queries of task performance metrics

Average, minimum and maximum time taken by a participant in a process per activity:

SELECT process_name
,      activity_label
,      role_name
,      avg(activity_running_time_in_msec)
,      MIN(activity_running_time_in_msec)
,      MAX(activity_running_time_in_msec)
FROM   bpm_activity_performance_v
GROUP BY process_name
,      activity_label
,      role_name
;

You could add the revision to see whether certain improvements in the process resulted in faster handling of activities by joining with the BPM_PROCESS_DEFINITION_V. It could also be usefull to join with the BPM_ACTIVITY_DEFINITION_V and discriminate on ACTIVITYTYPE.

3 thoughts on “Export Oracle BPM metrics to a data warehouse

  1. Pingback: deltalounge » Oracle BPM enable BAM

  2. Pingback: New features for Oracle Business Activity Monitoring 12c – BAM 12c

  3. Pingback: Oracle BPM enable BAM by Peter Paul | SOA Community Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>