Database views

You can use the database views while designing Online Analytical Processing (OLAP) reports using Progress Analyst Studio. These database views are BPM Process Store tables that store application data. Business Process Server provides the following database views.

PROCESS_INSTANCE_VIEW

This view helps you to analyze process data on various process parameters like duration, count, and status. Attribute names and description of each column in this table are given in Table 34.

Table 34. PROCESS_INSTANCE_VIEW table
Attribute name	Column name	Table name	Description
ProcessInstanceId	PROCESS_ INSTANCE_ID	PROCESS INSTANCE	Process instance ID.
ProcessName	PROCESS_ TEMPLATE_NAME	PROCESS TEMPLATE	OpenEdge application template name.
ProcessStatus	STATUS	PROCESS INSTANCE	Status of the process instance.
ProcessPriority	PRIORITY	PROCESS INSTANCE	Priority of the process instance.
Start	START_TIME	PROCESS INSTANCE	Start time of the process instance.
End	END_TIME	PROCESS INSTANCE	End time of the process instance.
DueDate	DUE_DATE	PROCESS INSTANCE	Due date of the process instance.
ActualDuration	DURATION	PROCESS INSTANCE	Actual duration of the process instance.
EstimatedDuration	ESTIMATED_ DURATION	PROCESS INSTANCE	Estimated duration of the process instance.
Creator	CREATOR	PROCESS INSTANCE	Creator of the process instance.

PROCESS_WORKSTEP_VIEW

This view helps you to analyze process data on various workstep parameters like duration, count, and status. Attribute names and description of each column in this table is given in Table 35.

This view is filtered on the following conditions.

Type of workstep must be ATOMIC or NESTED, for Monitoring workstep it must be EXTERNAL. All other types of worksteps are filtered.

Workstep status must not be W_EVENTACTIVATION_WAIT.

Table 35. PROCESS_WORKSTEP_VIEW table
Attribute name	Column name	Table name	Description
ProcessInstanceId	PROCESS_ INSTANCE_ID	WORKSTEP	Process instance ID.
ProcessName	PROCESS_ TEMPLATE_NAME	PROCESS TEMPLATE	OpenEdge application template name.
ProcessStatus	STATUS	PROCESS INSTANCE	Status of the process instance.
ProcessPriority	PRIORITY	PROCESS INSTANCE	Priority of the process instance.
WorkstepName	WORKSTEP_ NAME	WORKSTEP	Workstep name.
WorkstepPriority	PRIORITY	WORKSTEP	Priority of the workstep.
WorkstepStatus	STATUS	WORKSTEP	Status of the workstep.
LoopCounter	LOOPCOUNTER	WORKSTEP	Loop counter of workstep.
ProcessTemplateId	PROCESS_ TEMPLATE_ID	WORKSTEP	OpenEdge application template ID.
Start	START_TIME	WORKSTEP	Start time of the workstep.
End	END_TIME	WORKSTEP	End time of workstep.
ActualDuration	DURATION	WORKSTEP	Actual duration of the workstep.
EstimatedDuration	ESTIMATED_ DURATION	WORKSTEP	Estimated duration of the workstep.
DueDate	DUE_DATE	WORKSTEP	Due date of the workstep.
Type	TYPE	WORKSTEP	Workstep type.
IsMonitorStep	MONITOR_STEP	WORKSTEP	Whether it is a monitoring workstep. Value '1' indicates it is a monitoring workstep.
Creator	CREATOR	PROCESS INSTANCE	Creator of the process instance.
Performer	PERFORMER	WORKSTEP	Workstep performer.

PROCESS_WORKITEM_VIEW

This view helps you to analyze process data on the work item duration, count, status, and performer load. Attribute names and description of each column in this table are given in Table 36.

Table 36. PROCESS_WORKITEM_VIEW table
Attribute name	Column name	Table name	Description
WorkItemId	WORKITEM_ ID	WORKITEM	Work item ID.
ProcessTemplateId	PROCESS_ TEMPLATE_ID	WORKITEM	OpenEdge application template ID.
ProcessName	PROCESS_ TEMPLATE_NAME	PROCESS TEMPLATE	OpenEdge application template name.
ProcessInstanceId	PROCESS_ INSTANCE_ID	WORKITEM	Process instance ID.
WorkstepName	WORKSTEP_ NAME	WORKITEM	Workstep name.
LoopCounter	LOOPCOUNTER	WORKITEM	Loop counter of the workstep.
WorkItemStatus	STATUS	WORKITEM	Status of the work item.
Performer	PERFORMER	WORKITEM	Performer of the work item.
Priority	PRIORITY	WORKITEM	Priority of the work item.
StartTime	START_TIME	WORKITEM	Start time of the work item.
EndTime	END_TIME	WORKITEM	End time of the work item.
DueDate	DUE_DATE	WORKITEM	Due date of the work item.
ActualDuration	DURATION	WORKITEM	Actual duration of the work item.
EstimatedDuration	ESTIMATED_ DURATION	WORKITEM	Estimated duration of the work item.

Dynamic process view

For every installed process, Business Process Server creates a non-materialized view with name <PROCESSNAME>_VIEW in the database. This view helps you to analyze process data associated with instances of that process. Attribute names and description of each column in this view are given in Table 37.

Table 37. Dynamic process view
Attribute name	Column name	Table name	Description
ProcessInstanceId	PROCESS_ INSTANCE_ID	PROCESS_ INSTANCE	Process instance ID.
ProcessName	PROCESS_ TEMPLATE_NAME	PROCESS TEMPLATE	OpenEdge application template name.
ProcessStatus	PROCESS_STATUS	PROCESS_ INSTANCE	Status of the process instance.
ProcessPriority	PROCESS_ PRIORITY	PROCESS_ INSTANCE	Priority of the process instance.
Start	START_TIME	PROCESS_ INSTANCE	Start time of the process instance.
End	END_TIME	PROCESS_ INSTANCE	End time of the process instance.
Duration	DURATION	PROCESS_ INSTANCE	Actual duration of the process instance.
Creator	CREATOR	PROCESS_ INSTANCE	Creator of the process instance.
EIID	EIID	<ProcessName> - BPM Process Store table with process name	External instance ID of the process instance. This column is added only for a monitoring process.
Dataslot_1	<Dataslot_1>	<ProcessName>	Value of dataslot <Dataslot_1>.
Dataslot_2	<Dataslot_2>	<ProcessName>	Value of dataslot <Dataslot_2>.
Dataslot_n	<Dataslot_n>	<ProcessName>	Value of dataslot <Dataslot_n>.

If the process is a monitoring process, then its External instance ID (EIID) value is stored in column 'EIID' which is the first column after column 'CREATOR'.

For example, Business Process Server creates the dynamic process view for the sample application 'AccountsReceivable' with the name 'ACCOUNTSRECEIVABLE_VIEW'. Attribute names and description of each column in this view are given in Table 38.

Table 38. ACCOUNTSRECEIVABLE_VIEW
Attribute name	Column name	Table name	Description
ProcessInstanceId	PROCESS_ INSTANCE_ID	PROCESS_ INSTANCE	Process instance ID.
ProcessName	PROCESS_ TEMPLATE_NAME	PROCESS TEMPLATE	OpenEdge application template name.
ProcessStatus	PROCESS_STATUS	PROCESS_ INSTANCE	Status of the process instance.
ProcessPriority	PROCESS_ PRIORITY	PROCESS_ INSTANCE	Priority of the process instance.
Start	START_TIME	PROCESS_ INSTANCE	Start time of the process instance.
End	END_TIME	PROCESS_ INSTANCE	End time of the process instance.
Duration	DURATION	PROCESS_ INSTANCE	Actual duration of the process instance.
Creator	CREATOR	PROCESS_ INSTANCE	Creator of the process instance.
EIID	EIID	ACCOUNTS RECEIVABLE	'AccountsReceivable' is a monitoring process, and dataslot 'OrderNumber' is mapped as external instance ID of this process. This column contains the value of dataslot 'OrderNumber'.
Dataslot	PAYMENT AMOUNT	ACCOUNTS RECEIVABLE	Value of dataslot 'PaymentAmount'.
Dataslot	PAYMENT RECEIVED	ACCOUNTS RECEIVABLE	Value of dataslot 'PaymentReceived'.
Dataslot	ORDERDATE	ACCOUNTS RECEIVABLE	Value of dataslot 'OrderDate'.
Dataslot	ORDERNUMBER	ACCOUNTS RECEIVABLE	Value of dataslot 'OrderNumber'.
Dataslot	INVOICECOST	ACCOUNTS RECEIVABLE	Value of dataslot 'InvoiceCost'.

If a process template name contains more than 25 characters, then BPM Process Store does not create a dynamic process view for it in the database. BPM Process Store does not remove, update, or re-create this manually created dynamic process view when you uninstall, refresh, or re-install the process template.

When you refresh or re-install the process template, BPM Process Store drops the existing view and creates a new view. However, if the process template name contains more than 25 characters, then you must drop, and create the view manually.

While installing a process template, ensure that the database does not contain a view with the name <PT_NAME>_VIEW, where <PT_NAME> is the name of the process template you are installing. Even in this case, BPM Process Store processes the events. However, you must drop the existing view, and re-install the process template.