Challenge: Flexible, Efficient Sharing of Application Data
A building is assembled brick by brick, but from the perspective of the builder, a construction project might more accurately be described as a massive collection of data points. How many bricks will be needed? How many bricklayer hours will be required, at what wages? What are the differences between the planned and actual costs of laying the bricks? Even a small structure might generate hundreds of such questions, all of which need to be answered with high-integrity data.
Construction Industry Solutions (COINS), the market leader in enterprise software solutions for the construction industry, has made managing construction data its core business for more than 30 years. The global company has over 62,000 users worldwide. COINS has evolved along with the construction industry, introducing product modules for contracting, home building, engineering, equipment/plant management and services and facilities management as its client base has grown and added new lines of business. COINS also founded and supports the COINS Foundation, which funds humanitarian construction projects for inclusive communities in the developing world. A long-term Progress customer, COINS has been able to build and update its software portfolio easily using a combination of the OpenEdge developer platform and database.
Profitability and agility in the construction business are dependent on mastery of information. As a result, COINS has used Progress DataDirect OpenAccess to create ODBC and JDBC drivers that expose the data inside the COINS application. OpenAccess is a software development kit (SDK) that enables simple development of custom drivers for any data source or API. OpenAccess is especially suited for integration with OpenEdge business logic hosted on an OpenEdge Application Server and was the obvious choice of technology for COINS due to their Open Architecture, based on the OpenEdge Reference Architecture.
Today, COINS’ clients are embracing business intelligence (BI) platforms so they can conduct more individualized, in-depth analysis of their data. For example, a construction company might want to use a BI tool such as Cognos or Tableau to examine budget vs. actual cost data from COINS concerning multiple projects. Connecting with third party BI tools however presents a challenge to any application and COINS needed a way to share data outside its own application stack in an efficient structure more suited for BI analytics and reporting.
The data sharing challenge had several components. It was not a problem to give BI tools access to the OpenEdge database inside COINS. The difficulty was that, following recommended industry norms, COINS stored and managed the raw data in a form that was optimized for transaction processing and not necessarily easily usable by BI software. In the budget vs. actual cost example, the COINS database would hold the budget and actual costs in separate tables. A BI tool with access to these tables would have to create the business logic that said, in effect, “For a given project, what were the budgeted costs and what where the actuals?” COINS needed a way to grant access to the data with the business logic included, not just a raw view of database tables, in a way that was economical for the client to manage with minimal coding. In the standard COINS configuration, it would require a great deal of custom SQL coding to use OBDC to access and prepare the data along with the COINS data security model. Each client would likely require its own custom SQL code.
Solution: Building a New Semantic Layer with DataDirect OpenAccess
COINS is now taking advantage of the latest version of OpenAccess to build a semantic layer on top of the COINS application. The semantic layer makes data available for BI tools or other applications that need it. It offers users flexible queries of data from COINS through a Web 2.0 style interface. COINS users are no longer limited to report screens inside COINS.
The semantic layer also provides secure, direct access to COINS application data. With OpenEdge, COINS was able to create virtual fields in the database tables that allow users to see data in views that include inherent business logic, rather than the way it is natively stored. The virtual field, combined with a dynamic query builder created with OpenEdge, does the data “joins” that extracts data out of separate database tables in a way that reflects the business logic that the user wants to see. For example, if a user wants to see costs per project, the query builder automatically joins the project and cost tables and presents a result that matches exactly what the application would see. This approach offers several advantages, including:
- No recoding of business logic in the BI query – The semantic layer does not require any Java or ABL coding to deliver access to data that includes business logic.
- Built-in security – OpenAccess implements data security according to the COINS security parameters. User authentication and authorization, as well as table and row-level access controls are in effect. There is no need to replicate the security in the BI integration.
- Additional Logic – Because the BI layer passes through the OpenEdge application layer it is possible to execute additional business logic beyond simply retrieving and presenting data. This is especially useful for key application functionality such as language translation and auditing.
The semantic layer confers a number of business and technological advantages on COINS. From a business perspective, OpenAccess makes COINS a continually appealing partner for its construction industry clients. “By combining Progress OpenEdge with DataDirect OpenAccess, we have unlocked the potential with regard to BI tools.” said Tim Armitage, Technical Director, Construction Industry Solutions. “This is a huge advantage for us strategically. Not only are we making in-depth, flexible BI possible at a low cost, we are enabling the client to integrate BI into their platforms, using any toolset they prefer, and that’s the power of the Progress OpenAccess application.”
Technologically, the layer makes it possible for COINS’ clients to engage in BI while avoiding a great deal of the IT overhead generally associated with the process. There’s virtually no code writing. The layer obviates the needs for the creation of a separate data warehouse for COINS data. Any data the client needs can be accessed directly in COINS. And, the COINS data queries can be reused, making it possible for the client to switch BI tools without having to migrate and regression test complex SQL queries from one BI tool to another. These benefits are drivers of major savings in effort and expenditure.