Crystal TIME & Connect: Snapshot and Historical Trend Reporting Solution

1. CRYSTAL TIME & CONNECTThe solution for snapshot and historical trend reporting from Crystal Computing Solutions Ltd October 2017 Confidential – this material may only be shared with explicit authorisation of Crystal Computing

2. Problem Statement – Historical Trend Reporting The Business Problem Consistent Data - It is often a requirement to be able to generate the management reports from a consistent data set (usually based on the previous day) to ensure reporting integrity when producing various reporting packs. Trend Reporting - Businesses need to know whether they are getting better or worse at managing their operational risk. To determine this, they must be able to compare their key metrics historically across various periods such as month on month, quarter on quarter and year on year. Retention Policy - To comply with IT data retention policies, it must be possible to roll-off historical reporting data inline with agreed business rules. The Technical Problem The OpenPages solution does not create snapshots of the data to enable trend reporting on historical data. Note: OpenPages Reporting Periods do not solve the business problem above as these periods are typically aligned to SOX periods or other significant business events and periods cannot be rolled off.

3. Crystal TIME & Cognos Connect Solution The Data Layer (TIME) In order to run reports with consistent data or to run reports that compare business data period by period (month, quarter, year), there must be a data layer that contains the data at the end of each period upon which reports can be generated. Crystal TIME is the data layer solution that automates the snapshots of the OpenPages reporting data. Further more, it automatically adapts the snapshot schema to any changes made in the OpenPages schema and rolls off data based on configurable retention period rules. This provides the customer with a maintenance free data layer solution. The Reporting Layer (TIME:Connect) In order for Cognos reports to use the historical data (TIME module), a Cognos reporting framework must connect to the TIME schema. Crystal TIME:Connect Layer is the automated solution to effectively ‘copy’ the automated Cognos OpenPages reporting framework and point it to the TIME module. Most existing* reports can also be simply re-configured to use TIME:Connect and with minimal additional effort, extended to select which period to report on. * Some OpenPages Report Authoring functions may not be available in TIME:Connect without additional development work.

4. What Types of Reports Require Historical Snapshots? Most reports that intend to show a comparison or trend over a period of time will require a data snapshot solution. In particular, when reporting by business data that changes over time (such as a workflow status) where I want to report the data as it was during that period, not re-stating it as it is today. Example 1: Overdue Issues Report by Month Issues might be overdue when they are still ‘Open’ and their ‘Due Date’ is in the past. Using the current OpenPages reporting data, when they become ‘Closed’, they would no longer appear on an overdue issues report. But if they were overdue in any previous months, I would still want to report them as overdue in those months. Using TIME and TIME:ConnectI can now create a report that accurately shows the Count of Overdue Issues by Month. Example 2: Highest Impact Events Report by Month The high impacting risk events might be those that have an Open High Priority Issue attached. Using the current OpenPages reporting data, when the Issue gets ‘Closed’, the Risk Event would no longer appear on the highest impact events report as it reports on the data is it is now, not as it was during a previous period. Using TIME and TIME:Connect I can now create a report that accurately shows the Highest Impact Events by Month

5. Demo 1 – Installation Configuration A quick look at the configuration options for the TIME module. 2 – Snapshot Report. Let’s take the OOTB Loss Event Summary report, easily switch it to use the TIME:Connect schema and then it runs as a snapshot report using previous nights data. 3 – Snapshot Report with Reporting Period Prompt. We can now easily further extend the capability Loss Event Summary report to prompt the user to select a TIME Reporting Period. This allows users to run reports and compare one period with another with relative ease. 4 – Trend report. A trend report can be created that will have two or more reporting periods side by side. An example might be an Issue Status Report that shows the number of Open Issues at the end of each month along with the number created in that month. This would give an indication of whether the business is getting better or worse at closing down issues.

6. 1- Setup • Schedule and run the ETL job to start capturing data snapshots into the TIME Schema.

7. 1 - Publish a COPY of the standard framework— using a new data source to the TIME:Connect database Schema

8. 1 – Example Reporting Periods captured in demo data

9. 2 – Snapshot Report – Before changes (Out of the Box)

10. 2 – Snapshot Report – Before changes (Out of the Box)

11. 2 – Snapshot Report – Add Reporting Period Prompt and use TIME Connection

12. 2 – Snapshot Report – Add Reporting Period Prompt

13. 3 – Trend Report – Overdue Issues by Month

14. Q&A • Questions on TIME and TIME:Connect? For more information, contact Crystal Computing Solutions Ltd: Info@ccsl.uk.com