2020/12/02 Council Agenda Packet

Source Database Table 1 FieldFi1 -- f F Table 2 Field 1 F Table n Field 1 Field 2 Field ... Schema Crosswalk Extract, Transform & Load • FMEJESRI Data Interoperability • SQL • Python Target Schema Table A Field 1 F. F Table B Field 1 F. F Table n Field 1 Field 2 Field ... Pilot ble Fled 1 Fir • Fi Table B Foeld 1 Fit' '- F Table n Field 1 Field 2 Field ... .R )pegpaa j aasn User Acceptance Target Database able A Field 1 Table B Field 1 Fi F Table n I ield 1 Field 2 Feld ... Orientation Workshop The conversion process of each legacy system will include a workshop wherein the proposed project team vvill meet with appropriate City staff to review the specific implementations. The discussions will allow the project team to gain an understanding of how the applications are being used, what data has been recorded. At the same time, details associated with the data required as part of the conversion process will be reviewed, documented and approved. During the workshops, the project team will also initiate the process of gaining access to the underlying database and will work with City staff to gather any available documentation (i.e., system specifications, entity relationship diagrams, etc.) specific to the software and specific versions being reviewed. This information will help to streamline the subsequent navigation and interpretation that will be necessary to perform the migration. Database Schema Crosswalk Perhaps the most critical task in a data conversion effort is performing a crosswalk of the source and target schemas to identify and document how various objects between the two systems are related, resulting in a documented "data map" that will guide the migration process. while some of the source systems are well known commercial software packages, the software companies do not typically make database diagrams and workflows publicly available. Data structure even within commercial systems can vary across versions and, more importantly, each implementation can be setup differently based on workflow or data requirements. More data and custom solutions may have an even wider range or completely unknown schema. As such, the discussions and documentation resulting from the workshops will be critical to the completion of a highly detailed system crosswalk. Throughout the process, additional City input or clarification may be solicited as needed and is vital to ensuring that the resulting data mapping will reflect an accurate foundation for all subsequent activities. Translation Scripting Following the schema crosswalks, the project team will develop a series of processes to facilitate the actual migration of the source system data into Cityworks. Depending on the complexity and volume of the source data, the process may be a mix of manual and a scripted solution but will be established in a manner to ensure repeatability. The scripted solutions will be tailored to each specific data conversion effort and may range from native SOL Server scripts to third party migration tools but will ultimately follow a pattern referred to as extract, transform and load (ETL). The ETL. approach is common within the CIS industry, but applies much more generically to moving data between systems. The E T L process will be designed as a onetime process that will result in data migrated into a development Cityworks database. NOTE (I) The project team will be performing a data translation but will not be completing any data generation as part of this process. (2) While the scripts are being developed and data is being translated into development, City departments can use the source systems as always. At the time the data is ready (Page