Wednesday 23 March 2016

BAM 12c: Extent Data objects

BAM 12c is a huge improvement against 11g. Best thing I think is that it is quite a lot easier to create a dashboard. There are several tutorials on BAM, for instance at the BAM12c site, so I'm not going to explain how to create a dashboard here.

One thing however on business queries: the examples mostly start with a BPM process and then query from the Process of Activity Data Object as created on deployment of the particular process. How-ever, often you'll find that you want to filter on a certain date range, for instance process started less than a day or a week ago. Or activities running less than an hour, or between an hour and two hours, two and three hours or longer. But then you'll find that you can't filter in the Business Queries on a date function. For instance you can't filter on something like  '{process start date} < now() - 7'.

To solve that you can add extra Calculated Fields that return yes or no or  1 or 0 if a certain date calculation condition is met. To do so go to the administration tab of the BAM Composer (eg. http://darlin-vce-db:7006/bam/composer):

 Then you can expand the Data Objects and you'll find that the process that is deployed resulted in two Dataobjects, one for Activities and one or the Process-instances:

By the way, to get those you can need to have the process analytics enabled. I'll explain that in another blog.

Click for instance on the CustomerSurvey Activity, then on the tab 'Calculated Fields' and then on 'Add Calculated Field':
d

You need to provide a name that has no spaces but only lowercase or uppercase letters and underscores. Then you can provide a name that is shown in the designer and in flat queries. The column type can be measure, dimension or attribute, but in this case you'll want attribute, to be able to filter on it. In this case I returned 'J' or 'N' for 'Ja' (Yes) or 'Nee' (No). This is sufficient for filtering. But if you want to count/summarize instances that are running less than one hour, or between one or two hours, etc., then you might want to return 1 or 0.

Click on OK and then save:

By clicking on the pencil-icon you can edit the field.

I'll provide some other examples that I found helpfull for the activity dataobject:



Field NameDisplay NameColumn TypeExpressiondescription
activity_started_lt_week_agoActivity started less than week agoAttributeIF(DATEDIFF(SQL_TSI_DAY,{Activity Start Time},now())<=7)THEN("J")ELSE("N")Is the activity started at most 7 days ago? (J/N)
activity_started_lt_day_agoActivity started less than day agoAttributeIF(DATEDIFF(SQL_TSI_HOUR,{Activity Start Time},now())<=24)THEN("J")ELSE("N")Is the activity started at most 24 hours ago? (J/N)
Activiteit_Looptijd_minActiviteit Loop tijd (min)AttributeIF({Activity Instance Status}=="ACTIVE")THEN(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now()))ELSE(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},{Activity End Time}))Actual running time of the activity instance. If the instance is active, than the result is the difference between the start time and the current time (NOW()), otherwise it is the difference between de  start time and the end time. The "Activity Running Time" is aparently different from the predefined runningtime field, because of the sampling moments. Sometimes the Running time is near to zero, while the instance is still active.
Activiteit_Looptijd_lt_1hrActiviteit Looptijd < 1 uurAttributeIF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())<60 td="">Is Activity Running less than an hour?
Activiteit_Looptijd_lt_2hrActiviteit Looptijd < 2 uurAttributeIF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())>=60&&DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())<120 td="">Is Activity Running more than one but less than two hours?
Activiteit_Looptijd_lt_3hrActiviteit Looptijd< 3 uurAttributeIF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())>=120&&DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())<180 td="">Is Activity Running more than two but less than three hours?
Activiteit_Looptijd_gt_maxActiviteit Looptijd > maxAttributeIF({Activity Instance Status}=="ACTIVE")&&(DATEDIFF(SQL_TSI_MINUTE,{Activity Start Time},now())>180)THEN(1)ELSE(0)Is Activity Running 3 hours or longer?
Activiteit_is_openActiviteit is open?AttributeIF({Activity Instance Status}=="ACTIVE")THEN("J")ELSE("N")Is the activity still Open?

For the process Data Objects these are a good starting point:
Field NameDisplay NameColumn TypeExpressiondescription
Process_Running_Time_Min_attrProcess Running Time (Min) AttrAttribute{Process Running Time (millisecs)}/600000Number of minutes a process is executed. There is another comparable field already defined, but that is of type 'Measurement'. You can't use that for  analytid functions as AVG, MIN, MAX, etc.
process_started_lt_week_agoProcess started less than week agoAttributeIF(DATEDIFF(SQL_TSI_DAY,{Process Start Time},now())<=7)THEN("J")ELSE("N")Is the process instantie started at most 7 days ago? (J/N)
process_started_lt_day_agoProcess started less than day agoAttributeIF(DATEDIFF(SQL_TSI_HOUR,{Process Start Time},now())<=24)THEN("J")ELSE("N")Is the process instance started at most 24 hours ago? (J/N)
Process_Looptijd_in_minProcess Looptijd (min)AttributeIF({Process Instance Status}=="ACTIVE")THEN(DATEDIFF(SQL_TSI_MINUTE,{Process Start Time},now()))ELSE(DATEDIFF(SQL_TSI_MINUTE,{Process Start Time},{Process End Time}))Actual running time of the process instance. If the instance is active, than the result is the difference between the start time and the current time (NOW()), otherwise it is the difference between de  start time and the end time. The "Process Running Time" is aparently different from the predefined runningtime field, because of the sampling moments. Sometimes the Running time is near to zero, while the instance is still active.
So these help you in filter and aggregate on activity and process running times. Sorry for the dutch names, but I figure you can get the meaning.

The expressions are based on info I got from the user guide. You can find the 12.2.1 user guide over here. The 12.1.3 can be found here. Look for chapter 13.8 (in the 12.2.1 user guide) or 14.8 (in the 12.1.3 user guide).

No comments :