IC21: Data Warehouse and Reporting

1. What is the data warehouse

The data warehouse contains the daily transactions (for updating the databases) and the
snapshot databases at the end of each day.

The cost of storage is very low. It is possible to keep a lot of data in the data warehouse and
to use the data for generating reports and analysing the business results.

2. What is a snapshot database?

This is a copy of the live database, but is made at around midnight. Only the key fields need to
be copied. The snapshot shows the status of the database at a certain point of time.

It should be faster to process records in the snapshot database, compared to the production database.

The snapshot database is used for generating control totals (of the records in the database) to
be reconciled against the totals of the transactions during the day.

If there is any error in the processing of the transactions, they are likely to be captured during the reconciliation.

3. What types of reports are produced regularly?

The main categories of reports are:

* action report
* mail to customer
* workflow summary
* management report
* small database

4. What are action reports?

These are reports directed at specific personnel to remind them on the work that have to be done.

Examples are:

* follow up on customers, eg  on payment of bills, renewals
* outstanding cases to be processed, eg claims, underwriting

The relevant information are included in this report to make it easy for the servicing personnel
to do the work.

This can be made in a soft copy, or can be printed out by the recipient.

5. Mail to customer

The mails to the customers can be printed from the data warehouse, using the snapshot database. The details to be used to print the mails are also captured in the transaction records.

This heavy volume work can be done the following day. It reduces the load on the production database.

The mails are stored in the mail server. The actual sending of the mails is controlled by the mail server program.

6. Workflow summary

This shows a summary of the action report. It allows the manager to know what is the pending work that needs to be completed and how many cases have exceeded certain timelines. It
helps the manager to plan the work and to put in more personnel resouces on the work load. Special attention can be made on the work that is behind schedule.

7. Management Reports

The management reports analyses the business results. They are used to prompt management to take strategic actions on the sales, cost and other aspects of the business.

8. Small Database

These are subsets of transaction and database records that are extracted from the data warehouse and provided to a specific manager.

The manager can analyse the data, using a database program such as MS Accesss, to gain special insight into the trend of the business.

This allows the manager to develop an insight into the business that may not be evident from the pre-defined management reports.

9. Delivery of the reports

Most of the reports are stored in the mail server, and are identified as to specific recipients. The recipient can retrive his own report from the server. The reports can be stored on his
personal computer or a thumb drive. They can also be printed out for easy reference.