The use of Power BI with Dynamics GP is uncharted territory for many GP users. This is unfortunate as the ease of use and low cost of Power BI can bring tremendous value to your existing Dynamics GP ERP solution. The intent of this post is to provide an example of Power BI functionality, while conveying the robust features and solution structure to support your own Power BI model creation effort. Following the procedures outlined below will give you a solid understanding of how to:
- Connect Power BI to Dynamics GP
- Build custom views in Dynamics GP for consumption in Power BI
- Install and configure a “content pack” for Power BI which you can use as a jumpstart for building your own data models
- Create accurate, eye-catching reports in Power BI that will help you make better decisions more quickly
For our example scenario, we will build a Power BI report to support a very common request of allowing department managers to analyze their department spending vs allocated budget. This analysis could be ad-hoc throughout the year, along with supporting the build out of budget allocations for the next year. The power BI model attempts to accommodate this request by assessing the purchase requisition and purchase order data against budget data. Once deployed to your environment, users will be able to filter on data such as GL segment, year, month, and budget to obtain the information most relevant to their needs. Here’s what the end result looks like:
Power BI report example
A couple notes:
- This post assumes you have Dynamics GP 2016 or later.
- With the department spending vs. budget example, your purchasing “spend” data may include transaction type outside of purchase requisitions and purchase orders. If this is the case, then the SQL view can be updated to include the additional transaction data required (i.e. Payables Transactions, Journal Entries, etc.). We’ll cover where to set this up later in this post.
Ready to dive in? Here’s an overview of the process:
- Download and install the Power BI application
- Configure ODATA service in Dynamics GP
- Create and publish a new SQL view with the data required for Power BI analysis (Click here to download the SQL script required)
- Connect Power BI to the new data source (Click here to download the Power BI template file)
- Start exploring your data and creating reports in Power BI
If you already have the ODATA service configured in GP, you can start with Power BI for Dynamics GP Setup – Installing Power BI. If you don’t have the ODATA service configured, or you’re not sure, start with our blog series on Configuring the ODATA Service for Dynamics GP.
Installing Power BI Desktop on your Dynamics GP Server
In this section of our blog post we’ll cover how to download and install the Power BI application on your GP server. Let’s get started:
- Log in to your GP Server
- Open a web browser and navigate to https://powerbi.microsoft.com/en-us/desktop/
- Click Download Free to download the file
- Run the install file to install Power BI
Next up, we will deploy our SQL view that we’ll eventually connect Power BI to as a data source.
How to Create and Publish a SQL View for Analyzing Dynamics GP Data in Power BI
In this section, we’ll cover how to deploy a new view in SQL that will be used as a data source for our Power BI reporting. Creating a new view is a quick and effective way to organize your GP data in a fashion that’s easy to report on in Power BI. In theory, you could connect Power BI to the base tables in GP, but it would require many hours of data modeling to structure the data in the manner that’s suitable for reporting. In this example, we’re creating a view that will enable department managers to analyze their department spending vs allocated budget in Power BI. We are assuming that your purchasing “spend” data is contained within purchase requisitions and purchase orders. If your spend is contained outside of these entities (i.e. payables transactions, journal entries, etc.) you can add them to the SQL query in step 3 below. It’s important to note that this same procedure can be used to create views for other types of reporting in Power BI. By following the basic principles outlined below and in the next posts in this series, you’ll be able to create reports on any data in Dynamics GP. Now, let’s dive in to deploying the SQL view for our example: 1. Launch SQL Server Management Studio and connect to your GP SQL instance using credentials which have permission to create views within the company database.
Connect to server
2. Open a new Query Editor window by expanding Databases, right clicking on your company database then clicking New Query.
Create a new query
3. Open the text file which contains the SQL script to create the SQL view and copy all the content. Paste the content into the query editor window then strike the F5 key or click Execute to create the new SQL view. (Click here to download the SQL script required)
SQL script
Executing the SQL query
4. Update the permissions for the newly created SQL view by expanding the company database and Views folder in the object explorer pane.
SQL views folder
5. Locate the newly created view within the list then right click the view and select properties to open the view properties window.
SQL Explorer Properties
Adding permissions
6. Select Permissions at the left side, then search for and add the DYNGRP database role to the Users or roles section. Click to select the checkbox to grant Select permissions. 7. Click OK then close SQL Server Management Studio.
Update OData Service to Publish New Object
1. Launch GP and login using either SA or another GP user with proper rights to configure the ODATA features.
Login to GP
2. Open Data Sources by navigating to Tools–>Setup–>System–>OData–>Data Sources, then click Add Objects to open the Custom Data Sources window. Browse to the newly created object within the list and click to select the checkbox to the left. Click OK to save your changes and create the data source.
Adding new OData data sources
3. Open Publish OData by navigating to Tools–>Setup–>System–>OData–>Publish OData, then click select the publish checkbox next to the newly created data source. Click OK to complete this procedure.
Publishing OData servces
Grant Access to New OData Object via GP Security
1. Launch GP and login using an account with permissions to create and assign user security objects. 2. Browse to Tools–>Setup–>System–>Security Tasks to open the Security Tasks Setup window. Enter the ID, Name, and Description for the new security task then select the category most applicable to the SQL object being selected. Select the Product, Type, and Series as shown in the following screenshot then click to select the checkbox next to the SQL object. Click Save to save your changes.
Setting up security tasks
3. Browse to Tools–>Setup–>System–>Security Roles to open the Security Role Setup window. Enter the ID and Name for the new security role to be created, then select the recently created security task from the available list. Click Save to complete the setup.
Selecting new security role
4. Browse to Tools–>Setup–>System–>User Security Setup to open the User Security Setup window. Select the GP user to be granted access to the newly created OData object, then select the recently created security role from the available list. Click OK to save your changes.
Adding the new role to a user
Now that our SQL view has been created and published to the OData service, we’re ready for the next step: Connecting to the Dynamics GP data from Power BI.
How to Connect Power BI to Dynamics GP
In this section, we’ll cover how to open and connect to your SQL view from Power BI. It’s important to note that the same procedures outlined here can be used to create reports in Power BI on any data from Dynamics GP. Here’s how to create the Power BI report for our example: 1. Double click the Power BI template file to open the Power BI desktop application and initialize the data model. Enter your OData feed URL into the provide field then click Load to refresh the OData objects with data from your GP instance. (Click here to download the Power BI template file) *Note* Please be sure to NOT include the “/” at the end of the URL
Connect to the GP OData feed in Power BI
2. When prompted for credentials, select Basic from the left then enter the Active Directory credentials associated with the GP user which has been granted permission to the OData objects. You must enter the username in “domain\username” format
Entering credentials in Power BI
*Note* After clicking Connect in the previous setup, the application will begin to compile the data. You will see content similar to the Refresh screenshots below if the processing is functioning correctly.
Loading data from GP OData endpoint
Once the model has been successfully refreshed, you will be presented with two different Power BI visuals as shown in the following screenshot. *Note* The content will vary significantly from customer to customer as the underlying GL and budget configuration data is often unique to each customers GP environment.
Resulting Power BI report
*Tip* Click to select the Stacked Bar Chart visual to the left to expose the visual filters within the Visualizations control pane. In the Visualizations control pane click to expand the BUDGETID Visual level filter and select the budget ID most relevant to the data you intend to display. The default budget ID “BUDGET 2027” is part of the demo and must be changed to accurately produce data results relevant to your GP environment. At this point, you should have a nice department spending vs allocated budget report that you can refresh at any time, and hopefully a solid understanding of how you can use the same techniques in this series to start creating your own custom reports. If you’re new to Power BI, check out the Power BI Training site (https://powerbi.microsoft.com/en-us/learning/) and the Power BI YouTube channel (https://www.youtube.com/user/mspowerbi) for training and tips. Need help setting this up, or creating a custom report that you have in mind? Contact Us today for a free consultation! And please leave us a comment below to let us know your thoughts on this series. We’d love to hear if there is anything we can do to improve the posts, and/or what you’d like to see next!