Latest News

Posted on May 3, 2018 By Admin

Moving forward with Power BI for Dynamics GP

In today’s post, we would like to talk about what comes of Power BI with the release of the Content Pack for Dynamics GP 2018. Quickly it’s becoming a unique way to visualize the data straight from the company databases.

Before we start explaining this release, there are a couple of things you need to know. First, this Content Pack for Power Bi is not supported on Dynamics GP 2016 or earlier versions, and second The OData Service has some performance issues on Dynamics GP 2016. That’s why we recommend using Dynamics GP 2018 or later.

To help you get started with pulling the information that you would like to see from your Dynamics GP system we need you to do a couple of things. First, you need to set up OData feeds from Dynamics GP for use with Power BI, if you need help doing this please visit this link. Now, with the OData service configured and the Power BI Desktop application running, it’s time to implement the Power BI Content Pack for Dynamics GP. If you need help implementing this pack visit this link.

Ok, let’s get started, first for the Content Pack to work properly, you will need to publish the following views from the Dynamics GP Publish OData window:

 

  • Account Transactions
  • Account Transactions
  • Customers
  • Customers
  • Inventory Sales Summary – Period – History
  • Inventory Sales Summary – Period – History
  • Inventory Transactions
  • Inventory Transactions
  • Item Quantities
  • Item Quantities
To access the window, please go to Microsoft Dynamics GP > Tools > Setup > System > OData > Publish OData.

Once you have added the views you will notice that there are not many views that have been pulled into Power BI versus what it is available for users within the Dynamics GP Company Database. To use more views in Power BI, you will need to add them to your Dynamics GP Content Pack Power BI file. You can pull these views into the program by clicking on the Get Data dropdown selection on the Home tab of the Power BI Desktop application.  You will want to select the OData Feed option to bring up the window below.

You can select the items that you would like to load into Power BI from the Navigator window once you enter your OData URL.

As an example, you can see that the FixedAssets and FixedAssetsBooks views are selected in the Dynamics GP Content Pack. This will allow us to create several charts and reports that are not immediately available when the content package is loaded. With the items marked, all that remains is to click on the Load button and allow Power BI to extract everything.

Take into account not to have many items marked at one time, some of the views available for
Dynamics GP are too large and may run into issues loading into Power BI.

Once the items are loaded into Power BI, the next thing to do is to save this information to a Power BI file making sure next time you load it, the same list of views will be available.

Now that the views are setup, you can start working with the assorted items in the Content Pack or create your own report on a new Power Bi Desktop page. If you would like to learn more about how to start creating or modifying the information visit this site to read a complete guide about it.

With a quick display of what you can do with some of the information obtained, here is a screenshot below using the FixedAssetsBooks view that was imported using the Get Data menu option.

Power BI has the ability to highlight data on an entire page across multiple charts and reports.  It only takes to click on one Asset ID in my ‘YTD_Depreciation_Amount by Asset_ID’ chart, and now you can quickly highlight the asset’s data in the other reports on the page. You can see an example below on the clicked Asset ID 00004 from the Fabrikam company data.

Above, we can see that Power BI highlights the data in the graphs to represent the most specific of that asset. We can also see in the lower right part how the asset depreciation report is filtered down to just Asset ID 00004 as well. This is a good way to switch between different sections of large data sets, to give you a quick representation of specific data behind your totals.

Power BI offers a great feature that allows you easily give contrasting colors to values in various Visualization types.  For example, we can add conditional formatting to the YTD_Depreciation_Amount in the Asset Depreciation Matrix that was set up earlier.  You can click on the column name under the Values section, and select Conditional Formatting and then Font Color Scales, this will open the Font Color Scales window, as shown in the pictures below.

This format typesetting can provide a color degraded representation of your information, which helps highlight low/high values to easily display the elements that might need to be evaluated.

These are just a small example of what you can now achieve with Visualizations and Formatting in Power BI.  As you keep working on the program constantly, you will quickly get along doing some awesome and complex things with Power BI, making them available and easy for your users to consume.

If you would like to learn more about Power BI or need any kind of assistance, please feel free to contact us at support@bluepathconsulting.com and we will gladly help you with your requirements.


To find out more about Visualizations and Formatting in Power BI, please visit Lucas Reuss blog.