October 2018 Phill Norrey QAD Professional Services Manager Progress Software 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 ID: 756683
Download Presentation The PPT/PDF document "Optimize Reporting Using Real-Time Repli..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Optimize Reporting Using Real-Time Replication
October, 2018
Phill Norrey
QAD Professional Services Manager
Progress SoftwareSlide2Slide3
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 normSlide4
QAD and Data
Browses
Cyber Query
Crystal Reports
QAD Reporting Framework
ODBC
JDBCEtc
What they have in common
Working on live QAD dataSlide5
Reporting Problems
Unindexed Queries
Overstuffed Fields
Server Configurations
EtcSlide6
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.
END.Slide7
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.END.
TABLE SCANSlide8
Unindexed Queries
QAD Example
TR_HIST – 17,774,303 Rows in the table
Date
Record Reads
Record Creates
Record Updates
Record Deletes
09/05/18 (Wed)
2,865,133,974
4,094
5,057
0
09/04/18 (Tue)
5,121,913,422
7,486
9,426
0
09/03/18 (Mon)
5,293,323,626
6,918
8,307
0
09/02/18 (Sun)
4,776,906,034
2,067
2,405
0
09/01/18 (Sat)
4,844,352,184
3,603
4,710
0
08/31/18 (Fri)
5,018,478,752
8,063
10,006
0
08/30/18 (Thu)
5,053,397,070
8,181
10,066
0
08/29/18 (Wed)
5,068,773,334
7,430
9,209
0Slide9
Applications are not designed
with the index structure
for reporting
The indexes are mainly
used for validation during
data entrySlide10
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.Slide11
Server Configuration
There is a shared server pool for both 4GL and SQL connections and all the entries are taken by the 4GL connections.
OE Broker fails to find a server available for connecting .. (8933)Slide12
These are just some of the issues
you may encounter when accessing data
directly from the QAD system
There must be a better waySlide13
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 dataNeed Real-time ReplicationSlide14
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 dataSlide15
Pro2SQL 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 OpenEdge data into a target databases
Typically MSSQLSlide16
Pro2 provides live real-time replication from QAD to MSSQL or OracleSlide17
On Premise - LAN
admin
SOURCE SIDE MACHINE
Target DB
TARGET SIDE MACHINE(S)
Pro2 Software
MSSQL ODBC
Oracle Client
4GL Client
custom
mfg
e
tc
…Slide18
QAD Cloud - WAN
Target DB
TARGET SIDE MACHINE(S)
MSSQL ODBC
Oracle Client
4GL Client
WAN
AppServer
Pro2 Software
custom
mfg
e
tc
…
adminSlide19
Pro2 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 ReplicationSlide20
Pro2 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”Slide21
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 transformationSlide22
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 toolingSlide23
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 viewSlide24
With Pro2 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 issuesSlide25
Maturity!!!
Supports Progress 8.3 –
OpenEdge
11.x
Mature Product
Large install base
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2018
2018
Version 1
Version 3
Version 4
Version 4.6
250+
customers worldwide
650+
implementations
Version 5Slide26
Who uses Pro2Slide27
SPECIAL EVENT DISCOUNT!
SAVE
20%
*Order must be placed by November 15, 2018
Fast
replication from
OpenEdge
into a separate
OpenEdge
, SQL Server or Oracle DB
Easy
access to mission-critical data from your
OpenEdge
system
No disruption
of normal business operations or risking transactional database stabilitySlide28
Summary
Real-Time Replication for Reporting
Your data, Your way
Multi-threaded
Leverage ABL
for transformations.
Plugs Into any Existing Reporting SolutionLow Overhead and Easy Administration
Mature Product
Great DiscountSlide29