The blog post is Written by Tim Rodman – CPA turned ERP professional, now blogging at AcumaticaReports.com

What is Acumatica

ERP systems have long functioned like a data version of Fort Knox. They contain a wealth of useful information, but accessing it is almost impossible.

The IT department has access, but the business users are left out in the cold.

Traditionally, business users have had to manually export data from screens into formats like CSV and then manually combine the data using reporting tools like Microsoft Excel. Every time they want current data, they have to go to each screen individually and manually export the data, then copy/paste their Excel VLOOKUP formulas and other calculation formulas, carefully ensuring that they didn’t mess up any of the cell references.

Things have changed with Acumatica ERP.

Acumatica is a 100% browser-based ERP solution, built from the ground up in 2008 using .NET C#, and it is turning the traditional notion of ERP software on its head.

Beginning in Acumatica 5.1, users can connect directly to their Acumatica ERP and CRM data using OData, without needing to manually export from screens.

How does it work?

The OData connections are tied to Generic Inquiry screens in Acumatica.

Building a Generic Inquiry screen is a job for a power user, not a programmer. The Acumatica toolset allows you to simply click a button to display the database table/field names behind any field on any screen in the system like this:

Acumatica Schema Browser

Then, the Generic Inquiry creation screen allows you to graphically combine those tables/fields by joining/filtering/sorting/grouping like this:

Acumatica Generic Inquiry Screen

The end user now has an easy to use inquiry screen that allows them to reorder columns, show/hide columns, or filter on any column like this:

Acumatica Generic Inquiry Screen Filtering

No programming skills needed

To enable OData, all the power user has to do is check the Expose via OData box in the Generic Inquiry creation screen and the data instantly becomes available via OData.

Expose via OData in Acumatica ERP

There are no firewalls to configure, no 3rd party applications to install, and no configuration needed within Acumatica. As long as you are using version 5.1, it works out of the box.

Consuming the data

In order to consume the data from an OData-enabled Generic Inquiry screen, an end user simply needs to connect to a URL. Since the Acumatica application is browser-based, users are already accustomed to browsing to a URL in their web browser like this:

http://MyCompany.com/AcumaticaERP

In order to access OData, a user simply adds /OData to the end of the regular URL like this:

http://MyCompany.com/AcumaticaERP/OData

If they browse to this URL from an application like Microsoft Excel, they are prompted for their username and password, then presented with the OData connections that are available to them like this:

Acumatica and Microsoft Excel

Filtering and Sorting

You can pass parameters to the OData URL if you want to apply filters or restrict how many records are returned. For example, if you only want open orders and you only want the first 25 records, you can do this:

http://MyCompany.com/AcumaticaERP/OData/Sales Orders and Quotes?$top=25&$filter=Status eq 'Open'

You can also specify the sort order of the data when it’s returned to you. If you want to sort by the order amount, for example, you can do this:

http://MyCompany.com/AcumaticaERP/OData/Sales Orders and Quotes?$orderby=OrderTotal

Security

One of the beautiful things about this setup is that the Generic Inquiry screens are already built-in to the Acumatica security setup. If a user has access to the Generic Inquiry screen from the regular Acumatica menu, then they also have access to the OData connection.

Traditional ERP systems often rely on expensive Data Warehouse solutions to present their data to end users. These Data Warehouse solutions have a separate security setup and reconciling which users have access to certain data in the ERP application vs the Data Warehouse can be a real nightmare.

An Affordable Data Warehouse

Since OData can be easily consumed by Microsoft BI tools like Power Query and Power Pivot, end users can create their own multidimensional models. Then they can present the information as a fancy dashboard in Power BI. This replaces the need for a data warehouse in the case of many small and medium-sized businesses.

Acumatica, combined with OData and Microsoft BI, opens up the world of ERP data analysis to the business users, not just the IT department.

No longer are the business users left out in the cold.

See it in action

Here are two interesting videos that demonstrate the power of OData in Acumatica.
Using Excel to Display OData from Acumatica Cloud ERP
Acumatica and Microsoft Power BI integration in #Build2015 Keynote