Optimize Reporting Using RealTime Replication October 2018 Phill

Optimize Reporting Using Real-Time Replication October, 2018 Phill Norrey QAD Professional Services Manager Progress Software 1 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

2 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

How to Find Data § The Big Picture § Consolidation of data from different data sources • Data Warehouses • Data Lakes § Single Source data, while still important, is no longer the norm 3 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

QAD and Data § § Browses Cyber Query Crystal Reports QAD Reporting Framework § ODBC § JDBC § Etc What they have in common § Working on live QAD data 4 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Reporting Problems § § Unindexed Queries Overstuffed Fields Server Configurations Etc 5 © 2016

Unindexed Queries § Demo Database Example § Indexes defined on Cust-num, Name, Zip DEFINE VARIABLE i AS INTEGER. FOR EACH salesrep, EACH customer WHERE salesrep. sales-rep = customer. sales-rep BREAK BY salesrep. sales-rep. i = i + 1. IF LAST-OF(salesrep. sales-rep) THEN DO: DISPLAY salesrep. sales-rep i. i = 0. END. 6 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Unindexed Queries § Demo Database Example § Indexes defined on Cust-num, Name, Zip N A C S DEFINE VARIABLE i AS INTEGER. FOR EACH salesrep, EACH customer WHERE salesrep. sales-rep = customer. sales-rep BREAK BY salesrep. sales-rep. i = i + 1. IF LAST-OF(salesrep. sales-rep) THEN DO: DISPLAY salesrep. sales-rep i. i = 0. END. E L B A T 7 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Unindexed Queries § QAD Example § TR_HIST – 17, 774, 303 Rows in the table Date 09/05/18 (Wed) 09/04/18 (Tue) 09/03/18 (Mon) 09/02/18 (Sun) 09/01/18 (Sat) 08/31/18 (Fri) 08/30/18 (Thu) 08/29/18 (Wed) Record Reads Record Creates Record Updates Record Deletes 2, 865, 133, 974 4, 094 5, 057 0 5, 121, 913, 422 7, 486 9, 426 0 5, 293, 323, 626 6, 918 8, 307 0 4, 776, 906, 034 2, 067 2, 405 0 4, 844, 352, 184 3, 603 4, 710 0 5, 018, 478, 752 8, 063 10, 006 0 5, 053, 397, 070 8, 181 10, 066 0 5, 068, 773, 334 7, 430 9, 209 0 8 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Applications are not designed with the index structure for reporting The indexes are mainly used for validation during data entry 9 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Overstuffed Fields NAME field is defined as CHARACTER FORMAT “X(20)”. LENGTH(customer. name) returns 50. ODBC access causes: Column Name at rowid 2113 in table PUB. customer has value exceeding its max length or precision. 10 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Server Configuration There is a shared server pool for both 4 GL and SQL connections and all the entries are taken by the 4 GL connections. OE Broker fails to find a server available for connecting. . (8933) 11 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

These are just some of the issues you may encounter when accessing data directly from the QAD system There must be a better way 12 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Evolution § Exporting data is a common practice • Daily exports • Typically loaded into MSSQL § Business decisions are made on the data • More often exports are required § Unacceptable to make business decisions on stale data • Need Real-time Replication 13 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Data Replication § Application-level replication is difficult • • Maintaining connectivity Managing different data types and data structures Complicated Hurts performance § Your replicated data is only as fresh as the last batch • Disruption of normal business operations • Poor performance • Stale data 14 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Pro 2 SQL Provides Real-Time Replication § § Real-time data replication No connectivity limitations No application changes No disruption to normal business operations or risk to your system of record. § Create a channel for transferring Progress Open. Edge data into a target databases • Typically MSSQL 15 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Pro 2 provides live real-time replication from QAD to MSSQL or Oracle 16 ©

On Premise - LAN SOURCE SIDE MACHINE TARGET SIDE MACHINE(S) mfg Target DB admin MSSQL ODBC Oracle Client 4 GL Client custom etc … Pro 2 Software 17 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

QAD Cloud - WAN TARGET SIDE MACHINE(S) App. Server mfg admin Target DB WAN MSSQL ODBC Oracle Client 4 GL Client custom etc … Pro 2 Software 18 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Pro 2 Components § Change Data Capture • Replication Trigger Based • Keeps track of all updates • Adds to a Replication Queue § Replication Process • Manages the Replication Queue • Pushes data to the Target Database § Provides Real-time Replication 19 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Pro 2 Benefits § Low-impact and scalable § Records ABL and SQL changes § Administration § Alerting § Customizable § § Flexible Configuration Overcome Target Limitations Target schemas do NOT need to match Constantly sends “net change” 20 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Target Schema § Make as many additional indexes as you want • Must be non-unique indexes § Transform the SCHEMA • By default hyphens “-” are converted to underscore “_” – This allows SQL queries to run without quotes • EXTENT fields in QAD are changed to field##1, field##2, … § Transform the DATA • All ABL code, with a facility to do custom data transformation 21 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Target Schema § Includes additional fields • Source ROWID • Created_Date – Datetime • Modified_Date – Datetime § Allows you to see what has changed when • Useful for doing net change into BI tooling 22 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Common Configurations § MFG and Custom tables mapped to a single MSSQL database • Typical for a single plant QAD install § Single Source – multiple targets • Segmented by domains • Useful to expose the data back to the plant § Multiple QAD instances – single target • Unique index contains the source site identifier • Useful for multiple plants rolled up into a single view 23 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

With Pro 2 You Can § Provide accurate data in near real-time to support better business decisions § Leverage the value of the data in your system(s)-of-record § Tailor the data to match your specific needs § Overcome technology issues 24 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Maturity!!! § Supports Progress 8. 3 – Open. Edge 11. x § Mature Product Version 1 2006 Version 3 2007 2008 2009 Version 4 2010 2011 2012 Version 5 Version 4. 6 2013 2014 2015 2016 2018 § Large install base 650+ 250+ implementations customers worldwide 25 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved. 2018

Who uses Pro 2

SPECIAL EVENT DISCOUNT! SAVE 20% *Order must be placed by November 15, 2018 • Fast replication from Open. Edge into a separate Open. Edge, SQL Server or Oracle DB • Easy access to mission-critical data from your Open. Edge system • No disruption of normal business operations or risking transactional database stability 27 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.

Summary § Real-Time Replication for Reporting • Your data, Your way § § § Multi-threaded Leverage ABL for transformations. Plugs Into any Existing Reporting Solution Low Overhead and Easy Administration Mature Product Great Discount 28 © 2016 Progress Software Corporation and/or its subsidiaries or affiliates. All rights reserved.