Get the Real-Time Information Advantage The Right Time

Get the Real-Time Information Advantage The Right Time for Real Time Echt Zeit für Echtzeit Georg Sehrt

TOP DOAG Regionaltreffen Rhein-Main 14. 06. 2005 - Data. Mirror : Warum Integration Suite 2005 ? Unternehmensszenario Wer ist Data. Mirror Wie funktioniert das in Echtzeit www. datamirror. com 2

Warum Integration Suite 2005? Unternehmensanforderungen für Integration Suite 2005: § Gesetzliche Vorschriften § Operationale Entscheidungen § Informationszuverlässigkeit § Informationsgewinnung § Effektivität, Zusammenschlüsse von Firmen § Absicherung www. datamirror. com 3

End-to-End Information Integration www. datamirror. com 4

Die Landschaft www. datamirror. com 5

Über Data. Mirror • Founded in 1993 • Stock Symbols: • Nasdaq: DMCX • TSX: DMC • Over 250 employees worldwide • Over 2. 000 customers across all industry verticals • International Offices in North America, Europe, and Asia • DACH Office in Darmstadt Germany www. datamirror. com 6

ETL vs. DTC Extract, Transform, Load Detect, Transform, Communicate E – Extract D – Detect information change • Uses SQL queries to retrieve data from source system • Captures changes from logs incrementally (Change Data Capture) • Significant impact on database/application performance • Minimal impact on database/application performance BATCH T – Transform • • Stages the data Performs computationally-intensive transformations Only after-image available May require annotation with date/timestamp REALTIME T – Transform VS. • • No staging required Lightweight transformation on either source or target system Access to before and after image No impact on source system architecture L – Load C – Communicate • • • Batch-loading operation for entire data set Potential interruption to application execution www. datamirror. com • Continuous flow of transformed information No service interruption 7

Real-Time Integrations Beispiel SAP Transactions Real-Time Integration Target Engine SQL DW Oracle Real-Time Integration Source Engine Database Log Real-Time Auditing www. datamirror. com Sybase Audit 8

Flexibler Daten Fluss 1 -way 2 -Way Cascade Distribute Consolidate Multi-thread Bi-directional www. datamirror. com 9

“Two Way” vs “Bi-Directional” 2 - Way System A System B CRM ERP Different database schemas updated on each system. ERP Bi-Directional System A System B CRM ERP www. datamirror. com The same database schemas updated on each system. Often complex implementation 10

Recursive Updates System A CRM 2. 3. CRM ERP 1 1. ERP User Enters Transaction On System A 2 Log Journal/Log Entry Created On System A Journal/Log Entry Created On System B 6. Transaction Applied On System A www. datamirror. com Log “Ping” System B CRM ERP 7 Transaction Replicated Back To System A 7. 3 System A Transaction Applied On System B 5. 4 0011 Transaction Replicated To System B 4. System B Log 5 6 “Pong” Log 11

Transformation Server Architektur Central Point of Control Subscribers (Targets) Publishers (Sources) Metadata Database Change Log Metadata Broker (Engine) Database www. datamirror. com Broker (Engine) TCP/IP Asynchronous Database 12

Was ist Live. Audit Application Database Product ID Action Qty Drug 001 Drug 001 Make Calibrate Test Eqmt Test Initiated 1000 Test Result: Passed Bottle Ship 1000 LA Live. Audit Database Date / Time 05/31/01 -0800 05/31/01 -1300 05/31/01 -1500 06/01/01 -0800 Actn I I User jwalker Product ID Drug 001 06/01/01 -0900 06/01/01 -1100 06/02/01 -0800 06/01/01 -1600 06/05/01 -0800 D U U I I jwalker swilson jwalker Drug 001 Drug 001 www. datamirror. com Mfg Action Qty Make Calibrate Test Eqmt Test Initiated 1000 Test Result: 1000 Particles Found Test Initiated 1000 Test Result: Pass Bottle Ship 1000 - 1000 13

DB/XML Transform Architektur Readers Mapping Object Writers Database XML Text File EDI File Any Format www. datamirror. com Engine Xpath expressions Xpath/XSLT Functions DB/XML functions Formatting Objects Default value assignment external Java objects database key generation db incremental update Text File EDI File Any Format 14

GUI - EA Monitor • • Evaluate the health of the network through operational status metrics • www. datamirror. com Visualize complex networks through user-defined network diagrams Troubleshoot issues and fine-tune performance using latency metrics 15

Filtering CUST_NO L_NAME F_NAME PHONE REP_NO 58699 Smith John 404 -555 -3874 45 37283 Duggan Ira 613 -555 -8367 25 89863 Quinn Fran 905 -555 -1296 11 89732 Muntz Josie 704 -555 -2738 25 ROW SELECT • Row filtering allows you to select rows REP_NO = 25 • Column filtering allows you to select columns CUST_NO L_NAME F_NAME REP_NO 37283 Duggan Ira 25 89732 Muntz Josie 25 www. datamirror. com 16

Transformations EMP LAST FIRST HIRE_DATE STAT SALARY MAX 1234 Moreiro Nicole 01/05/97 A $55, 000 $60, 000 2345 Ellison Val 04/12/97 I $40, 000 $50, 000 Century Dates Transform Fields Increase Field Size Concatenation EMP_ID FULL_NAME HIRE_DATE STATUS 001234 Nicole Moreiro 01/05/1997 Active 92% 002345 Val Ellison 04/12/1997 Inactive 80% www. datamirror. com Derived Fields %SALARYMAX 17

Joins at Publisher CUSTOMER TABLE Cus_ID Order Number Order Date Transporter Code 12/3/99 TRA 001 CUS 001 ABC Ltd. London Customer Code ORD 001 Customer Address CUS 001 PUBLISHER ORDER TABLE Customer Name TRANSPORTER TABLE Courier Code Transporter Name TRA 001 AK Road Masters SUBSCRIBER ORDER TABLE Order Number Order Date Transporter Name Customer Address ORD 001 12/3/99 TRA 001 ABC Ltd. London www. datamirror. com 18

Derived Expressions DERIVED EXPRESSIONS JOURNAL CONTROL COLUMNS -----------------------------------&CCID An identifier for the transaction with the update. &CNTRRN Source table relative record number &CODE Always “U” for refresh. Always “R” for mirror. &ENTTYP Indicates the type of update. &JOB The name of the source job that made the update. &JOBNO The operating system user Id of the update process. &JOBUSER The operating system user at the time of the update. &JOURNAL The name of the journal, as described in Properties. &JRNFLG Indicates if before image is present &JRNLIB The name of the journal schema. &LIBRARY The source table schema or its alias. &MEMBER The source table name or its alias. &PROGRAM The name of source program that made the update. &OBJECT The source table name or its alias. &SEQNO The sequence number of this update in the journal. &SYSTEM The hostname of the source system &TIMSTAMP Time of the update or refresh. &USER The user ID which made the update. www. datamirror. com ---------------------------%BEFORE Net change (before image) %CURR Current image %CONCAT Concatenation %REPLACE Character substitution %SUBSTRING Substring %LOWER Lower case character conversion %UPPER Upper case character conversion %PROPER Proper case character conversion %LTRIM Left Trim blank characters %RTRIM Right Trim blank characters %TOCHAR Convert to character %TONUMBER Convert to number %TODATE Convert date format %TOTIME Convert time format %CENTURY Add a 2 digit century to your date %IF Conditional %VAR Initialise a result variable %USER Call user exit program %GETCOL Get a column from another table %STRPRC Call user exit stored procedure 19

Summarization ORDER TABLE Order Number Order Date Customer Product Qty Amount ORD 0001 09/10/04 Smith Bags 100 $10, 000 ORD 0002 09/10/04 Johnson Bags 5 $3500 ORD 0003 09/11/04 Robinson Clothes 12 $5000 DAILY ORDER TABLE Order Date Product Qty Amount 09/10/04 Bags 105 $13500 09/11/04 Clothes 12 $5000 www. datamirror. com 20

Row Consolidation 1: 1 Row Consolidation: Allows users to merge data from multiple source tables into one or more rows in a subscription table. 1: 1 (with a single row change affecting only one row in a target table) EMPLOYEE_PERSONAL TABLE EMP TABLE Employee Number Employee Name Employee Hire date Employee Salary Employee Number Employee Phone Employee Address EMP 001 Elmer Cecilio 11/08/99 30000 EMP 001 (416) 819 -1234 Markham EMPLOYEE TABLE Employee Number Employee Name Employee Hiredate Employee Phone Employee Address EMP 001 Elmer Cecilio 11/08/99 (416) 819 -1234 Markham www. datamirror. com 21

Row Consolidation 1: Many (with a single row change affecting one or many rows in a target table) EMP TABLE DEPT TABLE Employee Number Employee Name Employee Hiredate Department Number EMP 001 Elmer Cecilio 11/08/99 DEPT 001 EMP 002 John Nicholson 12/17/02 DEPT 001 Department Number Department Name Department Location DEPT 001 Finance New York EMPLOYEE TABLE Employee Number Employee Name Employee Hiredate Department Name Department Location EMP 001 Elmer Cecilio 11/08/99 Finance New York EMP 002 John Nicholson 12/17/02 Finance New York www. datamirror. com 22

What Our Customers Are Saying… “Data. Mirror’s solution is a critical component of our business strategy. The solution’s robust, real-time data integration capabilities provide us with high visibility into inventory levels, sales reports, e-Business channels, and other business-critical operations, allowing us to better manage sales cycles and deliver superior customer experiences worldwide. ” Jim Hill, Manager of Database Services, Tiffany & Co. www. datamirror. com 23

What Our Customers Are Saying… “Prior to implementing the Point. Base solution, all local development, testing, tutorials, and sample applications were run on multiple databases. With Point. Base, we have a unified database that minimizes internal research and development time and allows us to make efficient use of our product development and licensing funds. ” Dave Glende, CTO, Unify www. datamirror. com 24

What Our Customers Are Saying… “From the implementation phase through to the production phase, our requirements have been met with a high level of diligence, professionalism, and willingness on the part of Data. Mirror. We can now rest assured that our customers will continue to receive the level of service they deserve and expect from us. ” Licenciado Walter Alperín, i. Series Technology Planning, Lloyds Bank Argentina www. datamirror. com 25

Vorteile Integration Suite § Real-time & minimal latency § Real-time data warehousing § Performance § Increased scalability § Minimal impact on operational system performance § No impact on operational system architecture § Rapid implementation www. datamirror. com 26

§ Danke § Pause § Kundenbeispiel www. datamirror. com 27

Product Support www. datamirror. com/support • 24/7 support • Problem and resolution tracking • Electronic problem entry • Escalation • Online knowledge base • New release & PTF downloads from the web • Discussion forum • Remote dial-in (Web. Ex) www. datamirror. com 28 Slide Menu

Integration Suite 2005: Komponenten

Flexibler Daten Fluss 1 -way 2 -Way Cascade Distribute Consolidate Multi-thread Bi-directional www. datamirror. com 30