1 / 14

Data: Migrating, Distributing and Audit Tracking

Data: Migrating, Distributing and Audit Tracking. Michelle Ayers, Advisory Solution Consultant Email: michelle.ayers@visionsolutions.com Mark Scanlon, UKI Regional Director. October 2013. Vision Solution Offerings. Data Migration. All Users Off System. Users Back On System.

wells
Download Presentation

Data: Migrating, Distributing and Audit Tracking

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. Data: Migrating, Distributing and Audit Tracking Michelle Ayers, Advisory Solution Consultant Email: michelle.ayers@visionsolutions.com Mark Scanlon, UKI Regional Director October 2013 visionsolutions.com

  2. Vision Solution Offerings

  3. Data Migration All Users Off System Users Back On System Traditional Approach – ‘Big Bang’ • Backups • Copy Data • Transform Data • Testing/verify DOWNTIME Sunday Saturday EOD Friday Monday AM • Issues: • Hope you can complete all steps in available time window… • Data volumes becoming ever larger – takes longer to copy and convert data • Business becoming more 24 x 7 - reduced downtime window opportunities • Final testing/verification limited to what remaining time you have • Many stages are dependant on completion of previous stage visionsolutions.com

  4. Data Migration Alternative Approach – ‘Continuous Replication’ visionsolutions.com

  5. Data Migration Benefits visionsolutions.com

  6. Data Migration LAN / WAN Data path Source Data Target Data Administration & Monitoring (Director, MonCon & Console) More Complex: IBMi Oracle DT Share Applies trans to Staging Database DT Share Captures Changes Staging Data Custom Programs Custom Programs Staging Data LAN / WAN DT Share Captures Changes DT Share Applies trans to Staging Database Data path visionsolutions.com

  7. Data Sharing Typical Approaches: Apps ERP Application Application Database User Queries Business Intelligence Website • Lots of demands on the central database • All users/applications using same data therefore all should be consistent • However: • Performance issues – too much access of same data • Users running inefficient SQL statements • Increased security risks from many different communication jobs • Can lead to productivity issues for core business processes visionsolutions.com

  8. Data Sharing Typical Approach Using Offloaded Data Warehouse: ERP Application Data Warehouse Application Database User Queries Business Intelligence • Offloaded data warehouses have been around some years • However: • Data often loaded by batch overnight • May even need to reload whole tables each night • Issues: • Data is out of date quickly • Every day the database grows a little bit bigger, therefore each night the load job takes a little bit longer… visionsolutions.com

  9. Data Sharing Alternative Approach – ‘CDC’ Change Data Capture Customer Story – Red Wing Shoe Company - USA OFFLOAD QUERY & REPORTING PROJECT Many cost effective tools available on MS SQL server platform for query reports Manufacturing Query reports Offload Query System Production System Double-Take Share Real time CDC replication with transformation Data Warehouse load IBM System i DB2 Lawson M3 (Movex) MS SQL Server Data is already partially ‘scrubbed’ and available for loading data warehouses and data marts without performance impact on production system Reduce CPU and I/O overhead on production system improve user response times visionsolutions.com

  10. Data Sharing Alternative Approach – ‘CDC’ Change Data Capture Customer Example – Application Interface APPLICATION INTEGRATION PROJECT Double-Take ShareReplaced batch reload of data warehouse with DT Share creating incremental extracts of last changed row, and deleted transactions. Production System Double-Take Share Real time CDC replication with transformation Bi-directional replication IBM System i DB2 Manufacturing ERP MS SQL Server Production line information is transformed and sent to ERP MS SQL Server Third Party Inspection Application Query reports Introduced automated manufacturing inspection tracking and also faster Data Warehouse Load. visionsolutions.com

  11. Data Auditing Traditional Approach to Field Audit Tracking Before and after changes captured via embedded program code Or trigger programs Application Database Audit Files • Older applications may be using embedded code to capture changes – high overhead to maintain and ensure nothing missed • Trigger based capture can have database overhead though should ensure call changes captured • ERP provided solution can be limited: • May only support pre-selected tables/columns • Restricted data filtering capabilities visionsolutions.com

  12. Data Auditing Alternative Approach – ‘CDC’ Change Data Capture FIELD AUDIT TRACKING PROJECT Double-Take Share Real time CDC replication captures before and after values IBMi uses journal receiver entries Application Database Audit Files Target audit files can be held in same database or different, say MS SQL Server • Capture changes made within or outside the ERP application • Optionally add row/column filtering • Choose columns from any database table, including local modification • Target audit files are custom designed • Add target columns to hold trans type, timestamp, user id • Access to audit data via custom queries/reports • IBMi M3 java database has journaling switched on by installation – DT Share can use these journal receivers. visionsolutions.com

  13. Double-Take Share visionsolutions.com

  14. Thank You! facebook.com/vsichannel visionsolutions.com youtube.com/vsichannel businesscontinuitytoday.com twitter.com/vsichannel linkedin.com/companies/6542 visionsolutions.com

More Related