The following Case Monitor workbench objects gather and assemble the business data for display in Case Monitor dashboard objects.
The following table lists the Case Monitor Bucket workbench objects for cases, tasks, work items, and workflows.
Objects related to cases | Objects related to tasks | Objects related to work items | Objects related to workflows |
---|---|---|---|
Case Bucket View | Task Bucket View | Number of Work Items Per Bucket View | Number of Workflows Per Bucket View |
Case Bucket Data Stream | Task Bucket Data Stream | Number of Work Items Per Bucket Data Stream | Number of Workflows Per Bucket Data Stream |
Objects related to cases | Objects related to tasks | Objects related to work items | Objects related to workflows |
---|---|---|---|
Dimension | Dimension | Queue_Context | Workflow Lookup |
Dimension | Dimension | Queue_Dimension | Workflow Dimension |
Lookup table | Lookup table | UserName_Context | Workflow Definition Lookup |
Lookup table | Lookup table | User_Dimension | Workflow Definition Dimension |
Objects related to cases | Objects related to tasks | Objects related to work items | Objects related to workflows |
---|---|---|---|
Case Bucket Cube | Task Bucket Cube | Number of Work Items Per Bucket Cube | Number of Workflows Per Bucket Cube |
select SUM(ProcTimeBucket1Count) as ProcTimeBucket1Count,
SUM(ProcTimeBucket2Count) as ProcTimeBucket2Count,
DMUser_key, DMOperation_key, DMStep_key
from
(
select count(*) as ProcTimeBucket1Count,
0 as ProcTimeBucket2Count,
DMUser_key,
DMOperation_key,
DMStep_key
from V_F_DMWIP
where (ProcCurrentMinutes + MinutesSinceLastEvent*IsInProcStatus)< 60
group by DMUser_key, DMOperation_key, DMStep_key
union
select 0 as ProcTimeBucket1Count,
count(*) as ProcTimeBucket2Count,
DMUser_key,
DMOperation_key,
DMStep_key
from V_F_DMWIP
where (ProcCurrentMinutes + MinutesSinceLastEvent*IsInProcStatus)>=60
group by DMUser_key, DMOperation_key, DMStep_key
)
as UnionResults
group by DMUser_key, DMOperation_key, DMStep_key
Because this is an aggregate query, any additions to the selected fields must contain an aggregate function or be added to the group by clause. Because this query performs a union, any additions to the selected fields must be added to both of the union sub-queries as well as the primary query.
The Number of Workflows Per Bucket Data Stream object calculates the number of active workflows whose processing time falls into one of two groups, or buckets.
This data stream uses the following query to retrieve information from the Case Analyzer V_F_DMWorkflowWIP table, with additional reference information retrieved from the Case Analyzer D_DMWorkflow table.
select SUM(ProcTimeBucket1Count) as ProcTimeBucket1Count,
SUM(ProcTimeBucket2Count) as ProcTimeBucket2Count,
DMWorkClass_key
from
(
select count(*) as ProcTimeBucket1Count,
0 as ProcTimeBucket2Count,
DMWorkClass_key
from V_F_DMWorkflowWIP f, D_DMWorkflow d
where f.Workflow_key = d.Workflow_key and MinutesSinceCreation < 4320
group by DMWorkClass_key
union
select 0 as ProcTimeBucket1Count,
count(*) as ProcTimeBucket2Count,
DMWorkClass_key
from V_F_DMWorkflowWIP f, D_DMWorkflow d
where f.Workflow_key = d.Workflow_key and MinutesSinceCreation >= 4320
group by DMWorkClass_key
)
as UnionResults
group by DMWorkClass_key
Because this is an aggregate query, any additions to the selected fields must contain an aggregate function or be added to the group by clause. Because this query performs a union, any additions to the selected fields must be added to both of the union sub-queries as well as the primary query.