Advertisements
RSS

Tag Archives: BI

Error with Oracle BPM process metric view during deployment

Since we use BI metrics in our Oracle BPM processes we configure these in JDeveloper. However after adding/renaming/deleting a metric we sometimes discover that we are still able to deploy the BPM composite. However the process specific database view (Analytic View Identifier) is gone.

When we check the Enterprise Manager we discover the following error:

EM Error Message


Component soa_server1
Module oracle.bpm.analytics.cube.persistence
User jzoggel
Thread ID [ACTIVE].ExecuteThread: '13' for queue: 'weblogic.kernel.Default (self-tuning)'
ECID 0000K838zKS8xk35vnT4iX1IRqKb000005
Message SQLException from: CREATE VIEW BPM_ACTV_PERF_MYPROCESS_V AS
Supplemental Detail SELECT
PERF.TASKPERFID AS SEQUENCE_ID,
PERF.PROCESSID AS PROCESS_ID,
PRC.PROCESSNAME AS PROCESS_NAME,
PRC.REVISION AS REVISION,
PERF.ECID AS ECID,
PERF.ACTIVITYID AS ACTIVITY_ID,
ACT.LABEL AS ACTIVITY_LABEL,
PERF.COMPONENTINSTANCEID AS PROCESS_INSTANCE_ID,
PERF.COMPOSITEINSTANCEID AS COMPOSITE_INSTANCE_ID,
PERF.DISCRIMINATOR AS ACTIVITY_DISCRIMINATOR,
PERF.ROLEID AS ACTIVITY_ROLE_ID,
PERF.CREATIONDATETIME AS ACTIVITY_START_TIME,
PERF.COMPLETIONDATETIME AS ACTIVITY_END_TIME,
PERF.TASKTIME AS ACTIVITY_RUNNING_TIME_IN_MSEC,
PERF.PARTICIPANT AS ACTIVITY_PARTICIPANT,
PERF.PRIORITY AS ACTIVITY_PRIORITY,
PERF.TENANTID AS TENANT_ID

FROM BPM_CUBE_TASKPERFORMANCE PERF, BPM_CUBE_PROCESS PRC, BPM_CUBE_ACTIVITY ACT
WHERE
PERF.PROCESSID = 61015 AND
PERF.ACTIVITYID = ACT.ACTIVITYID AND
ACT.PROCESSID = PRC.PROCESSID
UNION ALL
SELECT
PERF.TASKPERFID AS SEQUENCE_ID,
PERF.PROCESSID AS PROCESS_ID,
PRC.PROCESSNAME AS PROCESS_NAME,
PRC.REVISION AS REVISION,
PERF.ECID AS ECID,
PERF.ACTIVITYID AS ACTIVITY_ID,
ACT.LABEL AS ACTIVITY_LABEL,
PERF.COMPONENTINSTANCEID AS PROCESS_INSTANCE_ID,
PERF.COMPOSITEINSTANCEID AS COMPOSITE_INSTANCE_ID,
PERF.DISCRIMINATOR AS ACTIVITY_DISCRIMINATOR,
PERF.ROLEID AS ACTIVITY_ROLE_ID,
PERF.CREATIONDATETIME AS ACTIVITY_START_TIME,
PERF.COMPLETIONDATETIME AS ACTIVITY_END_TIME,
PERF.TASKTIME AS ACTIVITY_RUNNING_TIME_IN_MSEC,
PERF.PARTICIPANT AS ACTIVITY_PARTICIPANT,
PERF.PRIORITY AS ACTIVITY_PRIORITY,
PERF.TENANTID AS TENANT_ID,
PERF.FLEXSTRING03 AS D_USER,
PERF.FLEXSTRING02 AS D_CASENUMBER,
PERF.FLEXSTRING01 AS D_PRODUCT
FROM BPM_CUBE_TASKPERFORMANCE PERF, BPM_CUBE_PROCESS PRC, BPM_CUBE_ACTIVITY ACT
WHERE
PERF.PROCESSID IN (20499,32601,27810,47859,20397,9013,15447,33553,8643,46749,48636,47229,47071,47545,46913,47387,33827,47702,57000,54392,57382,57764,57573) AND
PERF.ACTIVITYID = ACT.ACTIVITYID AND
ACT.PROCESSID = PRC.PROCESSID java.sql.SQLSyntaxErrorException: ORA-01789: query block has incorrect number of result columns

The cause of the problem is due to the auto generated SQL script using a non valid union statement. To quick fix is to manually re-run the script and adding the PERF.FLEXxxxx columns in the SQL statement. Or much better, clean up the old process id references in the table BPM_CUBE_PROCESS. Just check the references of processid in the auto generated statement and remove the invalid references.

Advertisements
 
Leave a comment

Posted by on 13-01-2014 in BPM, Oracle

 

Tags: , ,

Metrics, business indicators and analytics in Oracle BPM

Oracle BPM is build upon the same Oracle Fusion Middleware infrastructure as Oracle SOA Suite and therefor integrates with many of it components. One of these components, Oracle BAM, can be used for business activity monitoring. But Oracle BPM comes with some integrated features for process analytic purposes as well.

Sooner or later in your BPM project you will find yourself in the situation where the business requires some metrics about the business process and task performance. Or at least, they should be. Oracle documentation about how to use process analytics can be found here. The Oracle BPM product comes with some default metrics out of the box. However you can add upon these by configuring your own business indicators. These metrics can be set on project, process and all the way down to activity level. The interesting thing about the BPM/SOA/BAM infrastructure is that the analytic mechanism works in 2 ways. When configuring the business indicators for your process you have the option to configure 2 data targets, namely:

  • Enable Cubes
  • Enable BAM
Oracle BPM analytics data targets

Oracle BPM analytics data targets

Below picture shows an high level overview of the flows of these metrics through the Oracle Fusion Middleware infrastructure for BPM/SOA & BAM.

Oracel BPM data target overview

Oracel BPM data target overview

Below 2 paragraph give a quick insight regarding the role for both options:

Real-time dashboards: The role for BAM is allowing real-time dashboards to get “RIGHT NOW” insight in your organizations processes. The “enable BAM” option allows you to send metrics to the Oracle BAM adapter which stores them in the ORABAM schema. These metrics will be the primary source for your Oracle BAM Active Data Cache (BAM ADC). The BAM ADC is designed and optimized to handle large amounts of data in real time. Data coming into Oracle BAM ADC immediately updates all defined calculations and aggregates in real time in an in-memory cache so that this data can be pushed as quickly as possible to Oracle BAM dashboards and reports (quote: Oracle). How to configure BAM for BPM is documented by Aldo Schaap in this blogpost. Oracle BAM is a good extension for the BPM infrastructure to get .

Business Intelligence (BI): The Enable Cubes setting has another important but different function. When enabling this setting the Oracle BPMN Engine will start storing the business indicators of the process into the CUBE tables in your SOAINFRA schema. These CUBE tables are full with references, hardly readable, considered internal SOAINFRA infrastructure and better left alone. However they are the source for the Oracle Process Star schema views. These process views are perfectly documented by Oracle and will probably be the most important source for your Business Intelligence (BI) information.

Oracle BPM process star views

Oracle BPM process star views

The views are based upon the CUBE tabels and show the overlapping result of all analytic information from all of your processes. Because many of the processes will have different business indicators, almost none of the views above will have specific custom information (your own metrics) available. To solve this problem, Oracle has added something in BPM version 11.1.1.7 (PS6).

We now have an additional configuration option for our processes during design time. By configuring the Analytics View Identifier the BPM infrastructure this will create views for the specific process with the name mentioned in the Analytic View Identifier.

Analytics View Identifier

Analytics View Identifier

After deploying the BPM application you will find the new database analytics views generated in your SOAINFRA schema. These specific views will contain the named columns for the business indicators you configured in your process. This is an excellent out-of-the-box mechanism to publish your business intelligence information with custom metrics without having to create custom views yourself on the internal cubes.

Oracle BPM proces star analytics view

Oracle BPM proces star analytics view

Since you most likely don’t want to store this information in the SOAINFRA schema for decades, it’s best to create a strategy to extract the necessary information to a simple separate database (warehouse) or a Business Intelligence solution.

Interesting facts:

  • When you do a redeployment of your BPM project, and decide to leave the “keep running instances on after redeployment” not selected, your process information will be removed from the cubes. As a result your BI views will show no historical information anymore. So watch out for this in production.
  • The generic Oracle BPM process star views are always available in SOAINFRA, however the process specific views created due to the analytics view identifier are not (as mentioned earlier). When you redeploy your BPM process (no matter what selection you made mentioned here above regarding keeping instances), your process specific views will be dropped, and recreated. Which is not unexpected due to the fact the view will hold specific columns based on the metrics in your process and can’t keep track of the changes in JDeveloper. However this is a bit annoying when you have created grants on these SOAINFRA views to access them from another schema.

References:

 
3 Comments

Posted by on 05-09-2013 in BPM, Oracle

 

Tags: , , ,