How to use Autonomous REST Connector to access OpenEdge through the ABL business logic?

With Progress OpenEdge ODBC and JDBC drivers, it’s easy to connect a BI tool like Tableau or Power BI directly to the OpenEdge database. So why would you want to get Your OpenEdge data through Advanced Business Logic (ABL) and not straight from the database? There are several very good reasons:

• You may use ABL code to create calculated data that is not stored on the OpenEdge database
• Performance can be improved by joining OpenEdge tables on the OE server
• The OpenEdge schemas are complex and were designed for a specific application…. not for easy, intuitive access by end users
• Quality of service concerns when allowing SQL access directly to an operational database. For example, a user doing business analytics could unintentionally execute a query on unindexed column causing a table scan.
• You may have implemented DIY multi-tenancy before it was available in OpenEdge

If you still want to connect a BI tool directly to the OpenEdge database, here’s a tutorial on connecting Power BI to directly to the OpenEdge database using the ODBC driver.

Going through the ABL business logic takes care of the above concerns and using Autonomous REST Connector allows you to create ODBC and JDBC connectors for any REST data source, without coding. This, in turn, allows BI tools to use SQL and access ABL as if it were a relational database. 

In this example, I have two OpenEdge ABL programs. One returns data from the Customer table on the Sports2000 database. Some of the data is masked by the ABL code, so it’s only returned to users who are entitled to see the data. In my example, the CCNUM data is masked and appears like this: “XXXX XXXX XXXX XXXX”.

I have another ABL program that calculates sum of the extended price (Price x Qty - Discount) for all of the OrderLines in a specific customer’s orders. This information is not stored on the OpenEdge tables and must be derived by an ABL program.

Using Progress Application Server for OpenEdge (available with OpenEdge 11.6 and higher) I’m able to expose these two ABL programs as REST endpoints.

The Autonomous REST Connector allows me to take the two REST endpoints and sample them using the ARC user interface

 

In this case I’m using basic authentication with User and Password, but ARC can also support other authentication scenarios:

• OAuth2
• Bearer Token
• URL parameter
• HttpHeader
• Amazon Web Services credentials
• Custom

 

 

 

 

 

 

After I sample the two REST endpoints, I end up with a single virtual table for each of them. If the REST output had been more complex (i.e., embedded documents or embedded arrays) the output could have been normalized to multiple virtual tables. This would allow for a better user experience with the different BI tools.

 

 

If the REST source supports filtering to limit the output of the REST request, ARC will support that and take advantage of that in order to maximize performance. The ARC UI is able to recognize REST request query parameters as potential filters that can be captured from the incoming SQL and passed through to the REST request:

 

https://test.api.com:8443/v3/invoice?invoicenum=1

 

ARC can also support filters in the URL path and filters passed in a POST body:

 

https://test.api.com:8443/v3/customers/1/orders/16

 

 

When I’ve finished sampling the OpenEdge ABL REST endpoints, I can download the ARC REST configuration file (or “model”). You can see that it is a JSON file that describes the path to the REST endpoints as well as providing a description of the data that will be returned.

 

 

I can then configure an ODBC datasource using the ARC driver and the model I just generated.

 

 

 

 

 

 

 

ARC has a new ODBC configuration manager that makes connecting to OpenEdge ABL REST services and running test SQL statements easy to do.

 

 

 

 

 

 

 

 

The ARC ODBC driver also includes a Power BI DirectQuery custom connector. This enables you to use DirectQuery mode to retrieve data from its original source in real-time, rather than processing static data in Import mode.

 

If you want to use Tableau, ARC provides a Tableau Data source Customization (.tdc) file to customize Tableau-specific settings for the driver.

 

 

 

 

 

 

 

 

 

After installing the ARC Power BI DirectQuery custom connector, it will show up as an option to Get Data:

 

 

I enter the ODBC datasource I just created and then select DirectQuery Data Connectivity mode:

 

 

 

The two OpenEdge ABL REST endpoints appear as relational tables.

 

 

 

Power BI is able to manage the relationships and join data from the two OpenEdge ABL REST sources just as if they were actual relational database tables. In this example, CustNum can be used to link the two tables together.

 

 

 

 

 

 

 

 

From here, I’m able to manipulate the data and create Power BI visualizations like I would with relational database tables. But because I’m using ARC to go against the OpenEdge ABL business logic, I’m able to mask column data and work with calculated data that is not stored on the database.

 

 

 

 

 

And so, using ARC to access your OpenEdge ABL business logic gives you easy access to your OpenEdge data without all the concerns of going directly against the database. Since the business logic is exposed as relational tables, it works well with BI tools that were primarily designed for relational databases. And extra features, like Power BI DirectQuery custom connectors and Tableau Datasource Customization (.tdc) files ensure that ARC and your ABL code function optimally with