Ideally, the approach to the provision of calculating days in a queue would be to use an integer workflow field and then assigning a value to this field in a workflow step to store the time the work was held in a queue step. This field could then be exposed as a measure in Process Analyzer, and this would provide the maximum flexibility for pivot table reporting.

When this approach cannot be used for retrospective reporting requirements or where the calculations require the system date for a current “view” of the status of the queues, then the following mechanism can be used to create a “dynamic” shared dimension based on a table view.

Time Dimension SLA Calculations on Cognos

First, we need to start the IBM Cognos Analytics services. We will use the host Windows system with the network access to the CASTORE1 Database which is installed on the ecmukdemo6 Linux RHEL 8.0 VMware server.

Figure 5-2The IBM Cognos Configuration program is run as the Windows Administrator

We need to run the IBM Cognos Configuration program to start the supporting services.

Figure 5-3The highlighted green arrow icon is clicked to start the supporting Services

The services take around 6 minutes to start on a Windows server with an 8-core, 16-thread processor, running at base frequency of 1.8 GHz.

a) In IBM Data Studio 4.1.x, on the Linux server, a new table view can be created in the Db2 CASTORE database, CASTORE1, to utilize an existing date dimension as the basis of the calculation.
This query calculation was tested with the SQL as follows:
SELECT
days(current date) – days(TIMEINTERVAL) as DaysIn,
DMCASE_KEY
FROM CAUSER.F_DMCASELOAD
ORDER BY DaysIn

Figure 5-4The query is tested in the SQL query window of IBM Data Studio 4.1.3

The Create View statement used to create the View, CAUSER.DMDaysIn_Case:

CREATE VIEW CAUSER.DMDaysIn_Case AS

SELECT

days(current date) – days(TIMEINTERVAL) as DaysIn,

DMCASE_KEY

FROM CAUSER.F_DMCASELOAD;

Figure 5-5The CREATE VIEW SQL status is shown in IBM Data Studio 4.1.3

The access to the ecmukdemo6 Linux Server, Db2 V10.6, CASTORE1 database is configured in the IBM Cognos Configuration program on the Windows server. This configuration is in the Data Access section as shown in Figure 5-6.

 

Figure 5-6The Linux server CASTORE1 Database connection configuration

The connection uses a JDBC URL which requires the key parameters as shown in Figure 5-7.

 

Figure 5-7The DB2 Database connection parameters required to connect to CASTORE1

The connection to the Linux CASTORE1 is checked using the Test connection menu displayed by using the right-mouse click on the CASTORE entry as shown in Figure 5-8.

 

Figure 5-8The CASTORE database connection is tested using the right-mouse click

The CAUSER.DMDaysIn_Case View is checked in the Linux IBM Data Studio 4.1.3 program to ensure the expected rows are returned.

Figure 5-9The CAUSER.DMDaysIn_Case View is checked to show the expected rows

NoteIt is important to note here that the custom view we created earlier may need to be reapplied if the IBM Case Analyzer CASTORE1 database system is updated.