1 / 24

DM230 Data Archiecture Replication and Transformation (DART)

DM230 Data Archiecture Replication and Transformation (DART). Fuesane Cheng Sr. Information System Architect, ADS., Enterprise Technology, The St. Paul Companies fuesane.cheng@stpaul.com. Table of Contents What and Why Subject Area Database (SADB) SADB Architecture Consideration

abla
Download Presentation

DM230 Data Archiecture Replication and Transformation (DART)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DM230Data Archiecture Replication and Transformation (DART) • Fuesane Cheng • Sr. Information System Architect, • ADS., Enterprise Technology, The St. Paul Companies • fuesane.cheng@stpaul.com

  2. Table of Contents What and Why Subject Area Database (SADB) SADB Architecture Consideration Project Overview Data Architecture Replication and Transformation (DART) Technology Summary Data Architecture Replication and Transformation For SADB

  3. What a database contains cohesively related data to support information required by a subjective business function. Why Customer vs Product Line Oriented Systems Integrated business subject area data view Bridge between OLTP and DSS/Data warehouse systems What and Why Subject Area Database

  4. Product Oriented OLTP Systems - Customer vs Product Lines Product 1 OE/BL Sys. Data Architecture Product 2 OE/BL sys. Data Architecture Product 3 OE/BL Sys. Data Architecture Srv Typ Consolid Customer No Master Cus Id Corp Id Sub Srv Typ Customer Service View Product Product Bill Id Customer Id Bill Payer Id Cust No Feature Srv Id Cust Product Srv Loc Id Cust Srv Srv Loc Id

  5. Product Oriented OLTP Systems - Lacking Integrated data view Application System 1 Customer Customer Application System 3 Product Product Application System 2 Product Product Customer Product Customer Customer Customer Product

  6. Data redundantly exist in different stove pipe systems. Single business entity different implementation Lack seamless transition among systems for better customer service. Prolong time-to-market for new products and services More urgent needs on integrated customer view for service, billing, reporting, and pricing arrangements new products and service alliance venture merger Problems with the Product Oriented OLTP Systems

  7. DSS and data warehouse require integrated data Different data architecture and structure Different database configuration Bridge between OLTP and DSS/Data Warehouse Systems

  8. OLTP Application Systems Operational Source Database Replication Process Data Warehouse or DataMart Data ExtractionProcesses Other OLTP Application Systems (developed in ST, Java, VB,...) Download Processes Warehouse DataMart Application WareHouse DB Downloaded DB for OLTP DataMart DB SADB Data Service General Architecture SADB Data Service Subject Area Database SADB Stored Procedures SADB API Object Classes

  9. Infrastructure/ Technology Architecture WAN St. Paul Baltimore LAN LAN Order Legacy DB Entry Customer Profile DB Billing OE Data Systems Extraction CPD' Functional Architecture Customer Product Report Customer 0 Billing Subscribed Account Service Report Service Billing Service Data/Object Architecture Architecture Models

  10. Issues with previous informational database Daily BCP data out from OLTP Flush and rebuild entire MIS database Require long processing window Architecture not robust Data not optimized or not normalized OLTP Databases 80+ core business transaction tables 16G data 2500+ business transactions daily Project Overview

  11. Subject Area Database 130+ tables 1000+ columns Near fully normalized Require data architecture transformation Project Overview (continued)

  12. SADB Requirements: Non-intrusive process to OLTP databases Near real time replication Delta processing either by event triggering or scheduling Support complicate data architecture replication and transformation Less development effort and easy to maintain Decision: Sybase Replication Server using RCL & Stored Procedures Why Sybase Replication Server

  13. Document OLTP databases as-is Physical Data Model (PDM) Develop SADB Logical Data Model(LDM) Develop and Implement SADB PDM and Database Specify Data Architecture Replication and and Transformation (DART) Develop and Implement replication code generation for DART Implement API stored procedures or object classes SADB Development Tasks

  14. What: A database contains data replication and transformation rules Support Replication Control Language(RCL) and stored procedure code generation Easy rule maintenance through UI front end Central place for documenting data replication and transformation among databases Data Architecture Replication and Transformation Rulebase

  15. Components: Table Map Type Key Subject, Full, New Key - Full, New Key - Subset Source DB Operation/Target DB Operation Conversion, Insert, Update, Delete Table Map Condition and Operation Column Map Operation Data Architecture Replication and Transformation Rulebase

  16. Full Source and target tables have the same key Source target table row mapping is 1:1 Key Subset Target table is the normalized parent of source table Source target table row mapping is M:1 Table Map Type - Full and KeySubset

  17. New Key Full Source and target tables have different key fields Target table represents super type of source tables Source target table row mapping is 1:1 New Key Subset Target table is the normalized parent of super type table with subset key fields Source target table row mapping is M:1 Table Map Type - New Key Full and New Key Subset

  18. Table Map Type - Key Subset And Full OLTP Database SADB Database Key Subset Policy Policy Policy Client Cash Nbr Nbr Appl Amt Policy Term Policy Renewal Quote Client Cash Id Efct Nbr Term Itr Nbr Appl Dt Nbr Cd Nbr Amt N1 C1 $200 Full P1 Dt1 N1 001 001 C1 $200 P2 Dt1 N1 001 002 C1 $200 P3 Dt2 N1 002 001 C1 $200 PolicyVersion Policy Term Policy Renewal Quote Id Efct Nbr Term Itr Dt Nbr Cd Nbr P1 Dt1 N1 001 001 P2 Dt1 N1 001 002 P3 Dt2 N1 002 001

  19. Table Map Type - New Key Subset And New Key Full OLTP Database SADB Database PolicyStateCoverage New Key Subset Coverage CoverageAtPolicy PolicyCovg State IdId Nbr PolicyCovg Covg Sub IdId Cd Type PolicyTrans Covg State Covg Prm Id Seq Cd Nbr Id Amt Nbr P1 1 S1 P1 1 S2 P1 1 Cd1 PS P2 1 Cd1 PS P2 2 Cd2 S x P1 1 Cd1 S1 1 $200 P2 1 Cd1 S2 1 $200 P1 2 Cd1 S1 1 $300 New Key Full StructureCoverage PolicyCovg Loc Str IdId Id Id New Key Subset P2 2 1 1 CoverageHistory New Key Full CoverageWitchStructure PolicyTrans Covg Prm Id Seq Id Amt Nbr PolicyTrans Covg LocStr Covg Prm Id Seq Cd IdId Id Amt Nbr P1 1 1 $200 P2 1 1 $200 P1 2 1 $300 P2 1 2 $100 P2 2 2 $200 P2 1 Cd2 1 1 2 $100 P2 2 Cd2 1 1 2 $200

  20. Table Map - Full Example

  21. Table Map - Key Subset Example

  22. Table Map - New Key Subset Example

  23. Table Map - New Key Full Example

  24. Subject Area Database Supports: Customer oriented data view Retaining existing, and attracts new customers Faster time to market for new products and services DSS and dataware house application data quality SADB requires Data Architecture Replication and Transformation(DART) DART rulebase simplify implementation and maintainence Sybase Replication Server make DART possible and easy Summary

More Related