1.1 IDEA - Solution Architecture Overview The IDEA solution portfolio includes a data warehouse, data from twelve (12) City agency source systems, and Oracle PL/SQL code (stored procedures, packages, etc.). The IDEA Integrated Data Solution consists of the following core solution components which require adjunct staff to manage under the direction of the DMO and based on changing priorities: Data Warehouse 3TB; Oracle Database 19c Enterprise (19.0.0.0.0) operating under RHEL (Linux) OS version 8.10 ETL Load Processes Metadata sets used for staging data PL/SQL code packages used in processing staged data into the warehouse Identity Resolution Management (Matching); PL/SQL and .NET applications for back-end matching processes. Used in both back-end (ETL) processing as well as manual matching. Source Agency Data (data feeds daily/weekly/monthly) from the following operating environments: SQL Server 2019 Flat file source data files loaded via metadata definitions The Data Warehouse features are: Relational Highly abstracted and normalized Requirements generalized Driven by flexibility, metadata, controls Enterprise focus Data Integration Process Extract, Transform, Load (ETL) Data Acquisition Function Both batch and real time capabilities Detection of adds, changes, and deletes in source Accepts many types of data formats Conforms various data types (especially dates) Data Validation and Cleansing Functions Name and address cleansing Metadata-driven checking of codes Exception Logging Automatic recycling of exceptions to accommodate data latency and permit metadata updates to clear exceptions. Maintains referential integrity and logs exceptions to report to source systems Process Controls and Static Maintenance Metadata for tracking and controlling functions Client Matching Function Customized to deal with anomalies in social data Auto rematch when attributes change Transformation from any source to common data stores without any need for database changes Metadata driven Attribute Handling Functions Complete traceability and data lineage to maintain integrity to source data. Maintains complete archive of processed and unprocessed data to enable traceability, reporting and re-run. Intelligent “undo” capability at a file level and complete traceability of any database fixes.
