Data Nirvana: Power Pivot, OData, and Acumatica ERP

Posted on Updated on

below taken from powerpivotpro.com

Guest Post By: Tim Rodman

There is finally an ERP product that gets it, that embraces Power Pivot, Power BI, and the more than 800 million users of Microsoft Excel.

“What Power Pivot did to Excel, Acumatica is doing to the world of ERP”.

Acumatica recently announced the ability to securely connect to Acumatica ERP data through OData

This is huge. It’s as if two worlds are colliding, creating a good kind of explosion.

2015-05-31_02-38-40

You: “So, wait a second, what is ERP?”

Me: “Great question, I should probably back up for a minute.”

What is ERP

ERP stands for Enterprise Resource Planning and it is the computer system that ties all of the departments in your organization together.

If you work with Power Pivot, there is a very good chance that you also work with ERP data.
SAP, Oracle, Microsoft Dynamics, Infor, Epicor, Sage, and NetSuite are all examples of ERP systems.

Many of the ERP systems in companies today are very old and very ugly. The problem is that they are expensive to replace so they continue to exist through a patchwork of duct tape fixes that have been cobbled together over the years.

However, ERP systems contain a virtual Fort Knox of data that can lead to incredible insights if analyzed correctly (with Power Pivot of course).

The ERP Problem

However, like Fort Knox, the data in ERP systems can be very difficult to access.

ERP moves like molasses. It’s like the banking industry in that ERP is the last to adopt current trends in technology.

Accessing ERP data can require navigating through layers and layers of antiquated technology.

The CSV Solution

CSV stands for Comma-Separated Values.

I have always found it interesting how often Rob works with CSV files or some other exported data format in his Power Pivot models.

I often wonder if CSV files are the most frequently used data source in Power Pivot models.

ERP is a big reason for this. If you’re lucky enough to get access to the data without having to learn programming, you often have to manually export the data to CSV if you want to be able to do anything meaningful with it.

Updating CSV data is manual. When you want to update your Power Pivot model, you have to re-export the data from the ERP system manually. And this can’t be automated to take place every night while you’re sleeping.

Now, maybe you don’t sleep at night, preferring instead to scour the internet for the near infinite amount of Excel knowledge that it contains. But still, wouldn’t it be nice to eliminate the manual CSV refresh process from your nightly routine?

Of course, Power Query makes the TL part of ETL (Extract/Transform/Load) MUCH easier, but it still seems so archaic that you have to do the Extract part manually.

The Expensive Solution

If you happen to work for a larger company then you probably have a Data Warehouse. And you can point Power Pivot to it without having to manually export data.

But Data Warehouse solutions are expensive.

Even if you are lucky enough to have one, getting new data sources added often requires heavy IT involvement and can take a long time.

If changing a Power Pivot model is like working with clay, changing a Data Warehouse is like working with concrete. Jackhammer anyone?

The Agile Solution

Wouldn’t it be nice to bypass CSV and the Data Warehouse entirely to securely connect directly to your live ERP data?

If this were possible, then you could use Power Update to automatically refresh your data at night while you sleep (or while participating in Excel forum discussions) and your Power Pivot models would effectively become an agile Data Warehouse.

What if IT could make the ERP data easier to access without sacrificing the Fort Knox security that is required for ERP systems?

What if IT could do this just by checking a box on an ERP inquiry screen, without having to buy 3rd-party software or make any risky network firewall modifications?

What if a power user (no geeky developer necessary) could create new data sources using a nice graphical screen within the ERP application?

What if that data source could pass through the existing ERP security layer without the need to overlay an additional security matrix?

If this were possible, IT people and business people would spontaneously head for the nearest campsite, build a fire, and sing kumbaya together.

Seriously, I think it would look something like this.

I mean, wouldn’t this be great? S’mores anyone?

Enter OData

Well, I’m here to tell you that this is possible! And it’s possible today, like right now.

The foundation of the whole thing is OData.

OData is a protocol that Power Query and Power Pivot already understand; it’s already available on the Power Query and Power Pivot menus.

OData is like a United Nations translator that takes whatever foreign language the ERP data is speaking and translates it into a language that Power Pivot can understand.

OData is a secure, reliable, and increasingly popular way to deliver data. OData is already being used as the protocol behind the Salesforce CRM menu items in Power Query and the Microsoft SharePoint data connection in Excel.

When you see new data sources get added to the Power Query menu, the chances are that OData is the enabler behind-the-scenes.

Enter Acumatica ERP – Power Pivot’s new best friend

Acumatica is the world’s fastest growing Cloud ERP product.

Acumatica has stormed onto the SMB (Small and Medium-Sized Business) scene in the last few years. You can buy Acumatica and install it anywhere you like (on-premise, 3rd party hosting, Azure, Amazon, etc.) or you can license the SaaS version which is hosted on Acumatica’s servers. The cool thing is that you get the same product (running the exact same code/bits) regardless of which option you choose. This is rare in ERP these days because most ERP “cloud” products are completely different than the on-premise versions that bear their name.

Acumatica, on the other hand, is a 100% web-based application that was built from the ground up on modern Microsoft technology (C#.NET) starting in 2008 and it is turning the traditionally stodgy world of ERP on its head. You can even make Acumatica screens available in their mobile app without needing a developer.

Last month, Acumatica announced that they are exposing their data securely via the OData protocol.

Just imagine, you can now connect a fire hose from your ERP system (if you have Acumatica) to Power Pivot and flood your model with live data that can be refreshed with the click of a button! Additionally, you can run it through Power Query to better shape the data since Power Query also supports OData connections.

The Power Pivot / Acumatica Details

If you want to see more details, you can watch this 6 minute video where I describe how a power user can build a data source graphically within Acumatica:

You can then connect to the OData data source from Excel (or Power Pivot). This 5 minute video shows you how:

I think this is a big deal. If you want, you can read more about why I think the Excel / Power Pivot / Acumatica combination is so powerful.

Microsoft also thinks this is a big deal. They even listed it on the front page of their recent worldwide Build 2015 developer conference held in San Francisco.

Microsoft Build 2015

Acumatica OData at Microsoft Build 2015 occupied the stage for 5 minutes during the keynote as you can see in this 5 minute video:

Bottom line, Acumatica just became the most Power Pivot friendly ERP product on the planet.

And we are one step closer to Data Nirvana.


Tim Rodman is a “recovering CPA” who loves Excel (especially Power Pivot) and ERP software. You can find his blog posts over at www.AcumaticaReports.com.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s