Question Real Time Data Replication for BI Reporting

[rzr]

Need inputs / suggestions.

We have 15-20 production databases (10.2B). Schema is always in sync across these databases. Currently for BI reporting, we either dump data from specific tables into flat files or a ODBC connections into DB.
How have other companies managed BI reporting from Progress Db(s).

I'm in process of evaluating Pro2 for (near) real time sync from Progress Db to SQL Db. Other option is to capture changes (db triggers) and sync through Sonic to target.

Any other thoughts or solutions?

 

[Cringer]

I would be prioritising a migration to 11.7.5 or 12.1 in the very near future. There are a lot of changes that will make this a lot easier to achieve.
Pro2 can then use CDC - much quicker and robust.
You could roll your own CDC solution (probably too much effort).
Openedge Replication Plus gives you a read only replica of the data you can report against to move load from production. You also get a DR solution in the process.

 

[rzr]

I would be prioritising a migration to 11.7.5 or 12.1 in the very near future. There are a lot of changes that will make this a lot easier to achieve.
Pro2 can then use CDC - much quicker and robust.
You could roll your own CDC solution (probably too much effort).
Openedge Replication Plus gives you a read only replica of the data you can report against to move load from production. You also get a DR solution in the process.

Thanks for your inputs. Would love to upgrade but is unfortunately not an option / priority for stakeholders . The need will be to sync specific tables from 15-20 Progress databases into one target SQL database. So, not sure if replication will work given then many-to-1 situation.

 

[TomBascom]

Would love to upgrade but is unfortunately not an option / priority for stakeholders

If that is true then they are irresponsible fools. If they have a fiduciary duty they may be criminally irresponsible.

Or perhaps they have simply not been made familiar with the facts.

OE 10 is ancient, obsolete and unsupported. Deliberately continuing to run a business on it is willfully ignoring significant risks with regards to reliability, availability and security. Are they still running Windows XP? Or Windows Server 2003? The arguments are the same but an OpenEdge upgrade is vastly simpler to accomplish than rolling out a new desktop OS or upgrading all of your Windows servers.

With regards to this specific project -- as James mentioned 11.7.5 or OE 12.1 allow you to use change-data-capture based replication. (With or without Pro2.) This is vastly superior to trigger based replication or hand coded ETL routines. CDC is flexible, performant and implemented in "the engine". Pro2 has already built the infrastructure to accomplish your goal - it would be silly to reinvent that wheel.

 

[rfutil]

One of the companies in our group is using auditing to do this, they parse the audit table for updated records and then read the changed records in to SQL server.