The Client
The client is a global leader in providing industrial, weather, and environmental monitoring solutions.
The Business Need
Our client’s customers needed a solution to query the data stored on our client’s systems and combine it with data from our client’s enterprise systems. However, the only way to access this data was through the use of our client’s API. This would require writing scripts using the API, retrieving the data, storing it locally, and then combining it with other data.
Our client wanted to provide their customers with a SQL like interface to the data without providing direct access to the data.
The Optimus Solution
The first step was to determine a suitable approach to meet the business requirement. Two options were explored – create a local database with 10 years synchronized data for querying or implement a SQL to ODBC connector. After careful consideration, we chose to implement a SQL to ODBC connector.
The next choice was to either develop the SQL to ODBC connector from scratch or start with an existing solution and customize it to our client’s needs. After looking at available solutions, Optimus proposed using a ready-made Open Database Connectivity (ODBC) interface that would convert SQL queries into API calls and avoid developing custom software.
Optimus used its expertise in the field to source potential ODBC based SQL-REST connectors, shortlist 3 candidates, and select the best-fit provider for the job. In this case, it was Progress, a software company focused on digital experiences.
Yet, Progress’s product didn’t perfectly work with our client’s APIs. Changes had to be made both to Progress’s product and our client’s APIs.
In order to make the connector and our client’s APIs work together, Optimus needed to configure six API end-points and create a customized REST configuration file. This file allows us to specify multiple endpoints, define POST requests, and configure paging. The following type of API end-points were configured and provided SQL scripts to trigger them:
- Parameterized and non-parameterized API end-points,
- Paginated and non-paginated API end-points, and
- APIs with default parameter values.
Although the REST connector automatically created the default tables and columns to map them with the API response, Optimus configured the table and column names in the REST file to make them more user friendly. Data types were also configured to account for the maximum attributes size that the API can return.
Optimus also suggested using a data caching mechanism to increase the speed of data access and provided SQL scripts to cache the data into a local database. Along with SQL queries, Optimus documented the database schema for the end-users reference.
The Optimus team also:
- installed, configured, and tested the ODBC driver.
- configured, tested, and documented the SQL scripts.
- documented the process, ODBC configuration settings, and SQL scripts for an administrator to configure.
- acted as a liaison between our client and Progress to facilitate the process.
The Result
The ODBC connector reduced entry-barriers for our client’s customers. Now, instead of writing custom scripts, clients can now run queries simply through SQL that will extract the required data, which can be imported into a database, excel spreadsheet, or a reporting tool.
Optimus documented the process, configured requirements, and enabled our client to have an implementation procedure for their customers. Any changes to the system can be tested from end-to-end with a repeatable process. The solution Optimus provided is also:
- installable and maintainable.
- compatible with the security requirements of our client’s APIs.
Technologies used:
- Progress ODBC REST Connector
- SQL Server scripts and SQL Server Linked Server scripts
- Postman for API
