Prior to the release of GP2016, I was super excited about the new OData feature. I was so looking forward to a live method of sharing data in SQL Server (from Microsoft Dynamics GP) to Microsoft Power BI and Excel, regardless of whether the data is on Premise or in the Cloud. Big sentence, big request.
So what is OData anyways?
OData is a set of rules and procedures that allow users to access data via an API, even allowing users to create API’s. OData stands for Open Data Protocol. OData will provide you a consistent experience accessing data from anywhere on any device.
So what is an API then?
API’s are a set of programming instructions that allow one piece of software to access data from another piece of software. API stands for Application Programming Interface. A good example of this is when a game you play on your iPad prompts you to use Facebook credentials for the game log in. The game is using an API to get your log in information from Facebook instead of creating a log in system of their own. The API will create a secure URL called an “Endpoint” to expose only the data you want exposed, and nothing else.
Putting it together
Obviously it is much more technical than I spell it out here, but if you were not sure what it all does, this will give you a bit of a foundation. To put the pieces together for our world, let’s say we are using Excel locally and our Dynamics GP is in the cloud and we want to have data from Dynamics GP in the Excel sheet that would update by using the simple Excel Command Data | Refresh, or just refresh on its own.
1. You would tell Excel to use its OData connector to get your Dynamics GP data.
2. The OData Connector talks to your secure Dynamics GP OData Endpoint (in the API) to get the data from Dynamics GP and put it in Excel.
3. Your data is in Excel.
Every time you refresh, steps 2 and 3 are repeated. Of course you can only get access to the data if you are enabled via security in Dynamics GP and SQL Server. Sounds great, right?
Using OData with Microsoft Power BI
I used the previous example of Excel because everyone uses Excel and is comfortable with how it works. In the case of Power BI, more often than not, the data is consumed using the Power BI Service (web version.) In this case, you may or may not be on the same network as your Dynamics GP SQL Server, even if your Dynamics GP does not live in the cloud. This makes refreshing with a secure OData Endpoint much more important and valuable.
So why is Dynamics GP2016’s OData not quite ready for Prime Time?
Let me preface everything I’m about to say with the following statements:
* Microsoft is aware of all the issues/concerns I’m about to share and they are actively working on improving, repairing and/or adding features to handle these concerns.
* It would be wise to begin thinking about OData as part of a strategy for reporting and business intelligence. So I encourage you to start testing OData, so when it is ready for you, you’ll be ready to move forward.
Now let’s talk about why it’s not ready for production use as of today. The single biggest reason I would not suggest using it in production right now is due to inconsistency and time outs. As we have performed pretty full testing, we have discovered the following:
* Using Dynamics GP OData with Excel seems to work almost all the time if you specify a particular view or table in the URL. However, occasionally (not normally), you will time out if you let the URL provide you with a list of views/tables. I believe this has to do with high volumes of data. (See Microsoft’s Chris Bolson’s blog to support our findings. http://community.dynamics.com/gp/b/dynamicsgp/archive/2016/07/13/microsoft-dynamics-gp-2016-odata-service )
* Using Dynamics GP OData with Power BI seems to fail much more frequently than it works, even if you specify a particular view or table in the URL. I’m not sure the reason for this. Maybe volumes of data as well?
* OData services only exist in Dynamics GP2016. This means you may have to upgrade to use it.
* Cloud based issues
o At this point, Dynamics GP OData is not set to authenticate against Azure Active Directory. This is on Microsoft’s radar, but as Microsoft is pushing more and more for products like Dynamics GP to be used in Azure, Azure AD will be more prevalent.
o The current version of OData for GP2016 does not support the Microsoft Dynamics GP Web Client.
o The OData Service can only be used in a single tenant environment. This is more of a partner/hosting issue than an end user issue.
So how can I use Microsoft Power BI today, without OData?
I have a few options for you listed below. These options do require some manual intervention, but they are still relatively easy for you to have current data in the Power BI Service (web) or Power BI Mobile, regardless of whether your data is installed in your office or in the cloud.
Use Jet Express for GP (free Excel Add-on) to upload Excel Worksheet to Power BI Service
Using the free Jet Express for GP (Table Builder) tool, you can create an Excel File with a refreshable table. The table can be created using any tool that works for you. I like the Jet Table Builder tool because you can enter parameters, meaning if I only want 2016 and 2017 data I can bring only that data into the spreadsheet. Why is this important? Most people are still using a 32-Bit version of Excel which has RAM limitations. If your Excel ever freezes working on a big spreadsheet, you are likely hitting the RAM limitations. If you bring in only the data you need, you are using less RAM and are less likely to freeze.
Let’s Walk through this process now.
1. I created an Excel Table using the Jet Express for GP Table Builder for General Ledger Transaction data. This is the window that appears when I refresh. I can enter a single Period (or range) and either a Single Year (or range, so long as all the years are either Open or History.)
2. After entering the Open Year range of 2016..2017, the Excel Table is created. Note: There are more columns than the image shows. Save the file.
3. In Power BI Service (www.PowerBI.com) click on the + to add a Dataset on the pane on the left.
4. The Get Data window will open. Select to Import a File by selecting Get for Files.
5. Choose Local File.
6. Locate your file and then choose Import.
7. Your dataset exists, so you can build your reports, then add visualizations to a dashboard.
8. To refresh, someone will need to refresh the Excel Data file, then walk through the Import steps above. After you click on the Import button on step 6 you’ll receive the following message: