ERP Systems

Goal

Easily create reports and analyze data from ERP system with tools such as Crystal Reports, Brio, Excel, and others, while isolating those reports from changes in the underlying ERP system due to version upgrades, customization, or data relocation.

Issues and Alternatives

ERP systems store their data in complex schema that consists of hundreds of tables and thousands of columns. Each site in turn can customize their ERP system through changes such as how the data is stored and the addition of fields. This makes it difficult to supply general purpose reporting tools. And writing applications that go directly to the database schema has limitations, such as data stored in physically different databases, cryptic table/column names, security issues, and more.

Resolution

Use OpenAccess SDK to expose ERP data to end-user applications. OpenAccess components provide the ODBC, JDBC, or OLE DB APIs, SQL parsing, distributed query processing aggregation, and a client/server protocol (if required). These components interact with the ERP system’s Interface Provider code, which implements the schema management, query translation, security, and execution of the query against the data source(s).

Implementation

See Exposing Data from ERP Systems through ODBC, OLE DB or JDBC (.pdf)

Benefits

Quickly opens access to ERP data from off-the-shelf commercial tools like Microsoft Access, Crystal Reports, Brio, Excel and others Works with the site-specific implementation of the ERP system, displaying tables and columns based on current meta-data and using meaningful names to make it easier for users to work with the data Supports distribution of data and meta-data in two or more databases

Real World Example

Software vendor Unity Enterprise Solutions used OpenAccess SDK to implement an ODBC driver that supports the use of Object Configuration Manager tables (meta data) for exposing the schema and for accessing the data in Oracle’s JDEdwards EnterpriseOne ERP system. For more details, see Exposing Data from ERP Systems through ODBC, OLE DB or JDBC (.pdf)