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.)

1

 

 

 

 

 

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.

2

 

 

 

 

 

 

3. In Power BI Service (www.PowerBI.com) click on the + to add a Dataset on the pane on the left.

3

 

 

 

 

 

 

 

4. The Get Data window will open.  Select to Import a File by selecting Get for Files.

4

 

 

 

 

 

 

 

 

5. Choose Local File.

5

 

 

 

 

 

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:

8

 

 

 

 

 

 

9. Choose Replace It.  The data will be completely re-uploaded with changes.  The data on the web service is now updated.

Note:  If you want or need to model the data in Power BI, you may opt to:

* Use the Power BI Desktop App to access and model this data.  In this case you only need to Publish the Power BI Desktop file to the service.   First refresh the Excel File, then the Power BI Desktop file, then upload.

* You also may opt to use Excel 2016 Get & Transform (or Excel 2013 Power Query) to model the data prior to uploading to the Power BI Service.

Use Dynamics GP SmartList, exported to an Excel Worksheet, then uploaded to Power BI Service

If you use Dynamics GP, you probably already use SmartList on a daily basis, which means you are comfortable creating a new SmartList object (query) and exporting it to Excel.  It’s a super easy method of obtaining just the data you need and/or want.  SmartList is another method of creating a query exactly as you want, export to Excel and save the file.  At that point, you can use the same steps as above to import the data from Excel into Power BI.  Easy Peasy.

One Drive for Business and Office 365

This is my favorite option, but then again, it only works if you have Office 365.  Create your Excel File, either with Jet, SmartList or any method of your choosing.  Save the file to a folder in One Drive for Business.  Import the file using the same steps as above.

Since your Power BI login and your Office 365 login are the same, approximately once an hour Power BI will re-upload whatever data is in your Excel File.  This means someone would need to open the Excel File and refresh and save.  You’ll need to refresh twice if using Get & Transform/Power Query (once for the query and once for the spreadsheet.)  If you share the folder with others and they import the Excel file, they’ll be updated as well.

This is my favorite because if one person in the company updates the spreadsheet and saves it to One Drive for Business, everyone who uses that file will be automatically updated.

Making it automated without user intervention

Microsoft is actively working on making OData work for us and we, at Njevity, are actively working on making the automated flow of data from Dynamics GP to Power BI work as well.

There is too much to be gained using Power BI to wait for automated updates.  Go ahead and start, it’s worth the temporary manual workarounds.

With only a few days left before our next election, we see new poll numbers being released daily.  We also see candidates changing strategies based on these polls.  For the candidates, polls are their “Business Intelligence” as they are in the business of getting elected.  Like the candidates, you should be using your numbers (data) to help you improve your strategies for increasing your Success!

More on automated updates for Dynamics GP to Microsoft Power BI to come.