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?
Do you have a business system written in ABL?
What BI reporting tools do you use? Is it high volume reporting (10s of millions of rows, or 10s GB)?
Everyone's definition of BI is different so it is probably important to clarify the types of reports and tools that you would consider creating.
If you haven't yet started using appserver (PASOE), then that is certainly the most obvious place to start accessing your data, but involves programming. Are you a developer? The nice thing about appserver is that your reporting can make re-use of any available ABL logic that is used for interpreting your data. Many BI tools would be able to call your appserver routines directly or indirectly (eg. via REST or other types of interfaces)
If you aren't ready to do any ABL programming, then Pro2 may be your best bet. I haven't tried it yet, and haven't heard much discussion about it in these forums either. But I am told by our account representatives that Pro2 is extremely popular (despite the lack of discussion). There are some things that may be problematic for you, however. I think Pro2 will probably give you the same number of databases on the SQL side. It also uses ROWID's for unique record identification by default. This means that If your databases are ever dumped and loaded you need to reinitialize replication. Also, I think CDC is the best way to do Pro2 - and for that you will need to be on a much more recent version of OE.
We have a number of customers happily running Pro2 for exactly this purpose.
Another option is to deploy OpenEdge Replication and to plug your BI on one of the replication targets. We also have a number of customers setup this way.
Yes, the business system is written in ABL. BI Tool is QlikView... Data excess of 10Gb.