With the release of the Content Pack for Dynamics GP 2018, Power BI is quickly becoming a fantastic way to visualize data straight from your company databases. If you haven’t had an opportunity to try it out just yet, you can use the information gathered here to seize the opportunity to do so!
Before you get started, I wanted to call out a couple of items on versions and setup.
For the reasons above, we recommend utilizing the Power BI Content Pack on Dynamics GP 2018 or later.
Moving forward, I have gathered up some of our previous blogs on setting up Power BI to work with Dynamics GP and also put together some information to help you get started with pulling the information that you would like to see from your Dynamics GP system.
First, you will first need to set up OData feeds from Dynamics GP for use with Power BI using the blog linked below. https://community.dynamics.com/gp/b/dynamicsgp/archive/2017/09/26/using-power-bi-desktop-with-dynamics-gp-odata-feeds
Once you have set up the OData service and have the Power BI Desktop application up and running, we can work on getting the Power BI Content Pack deployed for Dynamics GP using the blog article linked below.https://community.dynamics.com/gp/b/dynamicsgp/archive/2018/02/15/dynamics-gp-power-bi-content-pack
As an extra note on the Dynamics GP Content Pack for Power BI, you will need to be sure to Publish the following views from the Dynamics GP Publish OData window for the Content Pack to work properly.
To access the window, please go to Microsoft Dynamics GP | Tools | Setup | System | OData | Publish OData.
Views for use with Dynamics GP Content Pack for Power BI:
Once you have added the views to run the reports built into the Dynamics GP Content Pack for Power BI, one of the first things that you will notice is that there are not very many views that have been pulled into Power BI versus what there is available to users within the Dynamics GP Company Database. To utilize more views in Power BI, you will need to add them to your Dynamics GP Content Pack Power BI file.
To add views from Dynamics GP, you will need to publish the views and tables you would like to utilize through OData. In Dynamics GP, you can go to Microsoft Dynamics GP | Tools | Setup | System | OData | Publish OData. From the Publish OData window, you can use the dropdowns and Publish checkboxes to select the items that you would like to be able to utilize in Power BI. You can also pull them all in by selecting the Mark All button on the window.
Back in Power BI, 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.
After entering in your OData URL (https://servername.domain.com:443/GPOData/TWO/), you can then select the items that you would like to load into Power BI from the Navigator window.
To use as an example of information we do not have loaded into the Dynamics GP Content Pack, I have selected the FixedAssets and FixedAssetsBooks views from the list. This will enable me to create some various charts and reports that are not immediately available when the Content Pack is loaded.
Once you have the items marked that you would like to load, you will want to click the Load button and allow Power BI to pull everything in.
Please Note: Some of the views available for Dynamics GP are quite large and may run into issues loading into Power BI if you have too many items marked at one time. Start out by pulling in just what you would like to work with and move forward from there.
Once you have the items loaded into Power BI, the next thing that you will want to do will be to Save this information to a Power BI file. This will ensure that same list of views will be available to you when you load the file next time.
To save the file, go to File | Save As. I like to set up a new file, just in case you have other users that would like to use the Content Pack as it is. Once in the Save As window, provide a location, name, and ensure the ‘Save As Type’ is set to Power BI File, and then click Save.
Now that you have set up your views, you can begin working with the assorted items already included in the Content Pack or create your own reports on a new page in Power BI Desktop. If you are unsure where to start with creating or modifying the information, there is an extremely helpful guided tutorial available to you on Microsoft.com titled, ‘Guided Learning for Power BI’.https://docs.microsoft.com/en-us/power-bi/guided-learning/
The Guided Learning will really get you on your way for modifying and creating reports in Power BI. As a quick display of what you can do with some of the information in Guided Learning, I was able to put together the reports in the screenshot below using the FixedAssetsBooks view that I imported using the Get Data menu option.
One of the best things about Power BI is its ability to highlight data on an entire page across multiple carts and reports with ease. All it takes is clicking on one Asset ID in my ‘YTD_Depreciation_Amount by Asset_ID’ chart, and I can quickly highlight the rest of that asset’s data in the other reports on the page, as shown below when I click on Asset ID 00004 from the Fabrikam company data.
From the screenshot, you can see how Power BI highlights the data in the charts by representing how much of the whole is specific to that Asset. You’ll also notice how my Asset Depreciation report in the bottom right is filtered down to just Asset ID 00004 as well. This is an exceptional way to switch between different sections of large sets of data and to give you a quick representation of the specific data behind your totals.
Another excellent feature of Power BI are the easy ways that you can give contrasting colors to values in various Visualization types. An example of that would be to add conditional formatting to my YTD_Depreciation_Amount in the Asset Depreciation Matrix that I have set up from earlier. You can right click on the column name under the Values section, and select Conditional Formatting | Font Color Scales, as shown below. This will open the Font Color Scales window, also shown below.
Setting up this type of formatting can give you a sliding color representation of your information, which can highlight low/high values and easily show items that may need to be evaluated.
These examples of Visualizations and Formatting are just a small sample of what you can do within Power BI. As you start to work with the program more and more, you will quickly find that you can do some awesome and complex things with Power BI and also make them available for your users to consume with ease.
Keep an eye on the Dynamics GP Support and Services Blog for more Power BI blogs coming soon!