262150
262150 - GP-7PVK3S/ Quote fo MIS Extracts for HCR
Copyright OBS Logistics © 2009
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
Functiona Overview
Project Background
About two years ago Visual Metrics (VM) were commissioned to build a data warehouse for MTS and MTS_CIM for Consumer. From this Paul Calvert has proposed an MIS system to provide across-application reporting.
A central data warehouse based on SQL Server, traditionally VM pull the data as required, however, DHL would prefer it to be message-driven, i.e. pushed out as then DHL do not need to understand the underlying schema or take into account changes in that schema, this remains the responsibility of the system / service provider.
MTS consists of a number of databases including one for the DHL customer Healthcare UK (HCLV), see appendix A for list of databases. It has been decided that this extract will be taken from HCLV and based on the CMLV extract.
Client Requirement
Provide standard MIS extracts from Healthcare MTS as per Staples and Aramco.
Solution
Providing users with timely MIS information A suite of functions will be developed to extract new and updated transactions from the Healthcare MTS database in the last 4 days. The data set will be formatted into delimited flat text files which will be moved to a folder ready to be pulled into the MIS infrastructure by ftp. The file transfer will be controlled by the MIS solution. The scope of tables and data referenced and created for transfer into the MIS solution will be documented in the specification of the MIS extract solution. The functionality will be written so that this scope is controlled dynamically by administration data tables. This means changes to the scope of data extracted can (in principle) be made quickly without re-coding. Most of the tables in scope already have insert and update date and time stamps but those that do not will be changed to include these fields. This will avoid full table transfers on all tables included, including the master data like locations. OBS will analyse the current data map provided that was created from the existing UK Secondary MIS solution (to be superseded) and document all data structure differences on the Healthcare UK data model. The Healthcare UK system does not require additional tables to be included for a booking module (unlike Saudi Aramco). OBS will where appropriate apply new database indexes on the insert and created columns to improve the efficiency of data extract queries depending on volumes. N.B. This MIS extract will be based on the current MIS extract set up for UK Secondary. Any changes to the format of the current MIS extract may incur additional costs.
Focus Area
Provide data extraction run on a number of Oracle DMBS_JOB that will produce delimited flat files that will be collected from the MTS server(s) from the following tables. The MIS solution will manage the file transfer on a pull basis and it is assumed will delete the files it collects or moves them to an archive store on the OBS database server.
Business Area Trip
SCH_HAULAGE_ACTIVITY SCH_TRIP SCH_TRIP_STOP
Business Area Resources
IMP_DECODE_ENTRY ORG_CUSTOMER RES_CARRIER RES_CARRIER_TYPE RES_PERSON RES_TRAILER_TYPE
Business Area Orders
SCH_ORD SCH_ORD_NON_CONFORM SCH_ORDER_LINE
Business Area Locations
GEO_LOCATION GEO_LOCATION_USAGE
Business Area Contracts
CNT_CHARGE CNT_CHARGE_TIER CNT_CONTRACT CNT_TARIFF
Business Area Bookings
INT_BOOKING_DETAIL SAP_PRODUCT_ITEM SCH_BOOKING SCH_PRODUCT_SUMMARY SCH_REASON_CODE
Business Area Schedule
SCH_SCHEDULE
The export will include all columns on the above tables.
Scope
This development will be applied to system version 10.6 on HCRTST and once approved HCRPRD.
The basic building blocks used for CMLV will be re-used in this instance.
The MTS extract will form an intrinsic module of the transport solution and any future development / release of MTS must consider the impact on data extract for the MIS solution, in particular table changes or additions to the underlying data structure.
A data take on exercise will be conducted that will create the same file formats but for historic date ranges. It is anticipated that this data will be sent to VM as a one-off using DVD media. The larger tables might need to be split down and provided as monthly history to keep the take-on file size manageable.
Functional Description
The data for the export will be based upon all columns in the following tables for the HCLV database.
The following tables will be required:
CNT_CHARGE CNT_CONTRACT CNT_CHARGE_TIER CNT_TARIFF GEO_LOCATION GEO_LOCATION_USAGE IMP_DECODE_ENTRY INT_BOOKING_DETAIL ORG_CUSTOMER RES_CARRIER RES_CARRIER_TYPE RES_PERSON RES_TRAILER_TYPE SAP_PRODUCT_ITEM SCH_BOOKING SCH_HAULAGE_ACTIVITY SCH_ORD SCH_ORD_NON_CONFORM SCH_ORDER_LINE SCH_PRODUCT_SUMMARY SCH_REASON_CODE SCH_SCHEDULE SCH_TRIP SCH_TRIP_STOP
The tables are listed below with any new columns with database triggers required highlighted in red. It is expected that each column will be delimited with a '®' symbol and that extract will be run for records created or updated in the last 4 days.
The columns may be added as follows, for example:
alter table RES_PERSON add created_by varchar2(40); alter table RES_PERSON add created_date date; alter table RES_PERSON add updated_by varchar2(40); alter table RES_PERSON add updated_date date;
References
| EST-262150 GP-7PVK3S Quote for MIS extracts for HCR v1.0.doc |
Document History
| Initial version | ||||
| Reviewed and Issued |
Authorised By
| Matt Crisford | Development Manager | |
| Suk Sandhu | TMSCC MTS Product Manager |
