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

+1 Likes (4)

    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 relevent for reporting, in most tables and the complexities of complex joins.



    Length:  9 Minutes