Linking Excel to Epicor® ERP using OData and the REST API.

+1 Likes (5)

Access your Epicor® ERP data in real time from Excel using Odata

Before Epicor® 10.1, the only way to get data into Excel was to either copy & paste, or to directly connect Excel to your Microsoft SQL server that housed your Epicor® database.

The problem with copy & paste, is as soon as you paste, your data can be getting obsolete.

A direct SQL connection has many problems:

  1. The workstation you are using and the SQL Server usually need to be on the same network or at least working through a VPN
  2. The user generally has to have complete access to all the Epicor® tables and fields making it very unsecure
  3. The user has to write complex queries and browse through thousands of tables and fields to get at the data they need.

The Advantage of using OData with BAQ’s:

  1. It can work within your network or across the internet and into the cloud
  2. All the Epicor® table and field security is inherited to the BAQ.  Thus a user who can’t access a particular table, field or company, has the same restriction through Odata
  3. The Epicor® BAQ Designer is much easier to write queries with than pure SQL.  It handles tasks such as company filtering, automatically joining tables, a full data dictionary with detailed field descriptions, guided help designing subqueries
  4. The end-user of the Odata feed, can work with a small ‘flattened’ subset of data they need to analyze versus the hundreds of fields, mostly not relevant for reporting, in most tables and the complexities of complex joins.

In this high-level overview we’ll show how to pull a query into excel using OData

We have an enhanced version of this presentation that also includes Power BI.

You can see it HERE.

Length:  9 Minutes