/
Optimize Reporting Using Real-Time Replication Optimize Reporting Using Real-Time Replication

Optimize Reporting Using Real-Time Replication - PowerPoint Presentation

jane-oiler
jane-oiler . @jane-oiler
Follow
365 views
Uploaded On 2019-03-15

Optimize Reporting Using Real-Time Replication - PPT Presentation

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

replication data qad target data replication target qad rep salesrep sales real time pro2 record mssql business single reporting

Share:

Link:

Embed:

Download Presentation from below link

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.


Presentation Transcript

Slide1

Optimize Reporting Using Real-Time Replication

October, 2018

Phill Norrey

QAD Professional Services Manager

Progress SoftwareSlide2
Slide3

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