VERSION: ACCOUNTS PAYABLE

FREE POWER BI REPORT AVAILABLE FOR DOWNLOAD

Do you ever wish that your ERP solution could tell you when your sub-ledger is out of sync with associated GL control accounts? Leveraging Dynamics GP with Power BI provides the ability to easily project crucial health data to ensure issues are dealt with in a timely and efficient manner. This post is the first in the “ERP Health Check” series and is focused on using Power BI to analyze your Dynamics GP Accounts Payables sub-ledger activity against the activity in the corresponding GL control account. Future posts in this series will address similar issues in other modules such as Bank Reconciliation, Sales, Fixed Assets, etc. Here’s what the end result might look like:

  • The top visual illustrates the point-in-time balance of your AP data vs GP data.
  • The bottom visual illustrates the point-in-time difference between your AP data vs GL data

A couple notes:

  1. This post assumes you have Dynamics GP 2016 or later
  2. With the Accounts Payable example, your AP configuration may include multiple GL AP control accounts corresponding to vendor / AP activity grouped by a vendor class. If this is the case, then you must update the include SQL logic to accommodate the multiple control accounts as the current configuration assumes one AP control account is used for all Accounts Payable activity.

Ready to dive in? Here’s an overview of the process:

  1. Download and install the Power BI application
  2. Configure ODATA service in Dynamics GP
  3. Create and publish a new SQL view with the data required for Power BI analysis. Click here to download the SQL script(s) required to complete this process.
  4. Connect Power BI to the new data source. Click here to download the Power BI template file.
  5. 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:

  1. Log in to your GP Server
  2. Open a web browser and navigate to https://powerbi.microsoft.com/en-us/desktop/
  3. Click Download Free to download the file
  4. 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 SQL OBJECTS FOR ANALYZING DYNAMICS GP DATA IN POWER BI

In this section, we’ll cover how to deploy a new stored procedure and view in SQL that will be used as a data source for our Power BI reporting. In addition to creating the stored procedure and view, we will also be creating and scheduling a SQL job to run the stored procedure on a daily frequency. 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 stored procedure and SQL view that will enable controllers and financial officers to analyze data synchronization between AP sub-ledger and corresponding general ledger control account activity. We are assuming that your accounts payable transaction post to a single AP control account. If your accounts payable configuration includes multiple AP control accounts, 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 into deploying the SQL objects 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. SQL Server Connection2. Open a new Query Editor window by expanding Databases, right clicking on your company database then click New Query. SQL Explorer Query 3. Open the text file which contains the SQL script to create the stored procedure 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 stored procedure. Click here to download the SQL script(s). SQL Execute 4. Update the permissions for the newly created SQL stored procedure by expanding the company database, Programmability, and Stored Procedures folder in the object. 5. Locate the newly created stored procedure within the list then right click the stored procedure and select properties to open the properties window. 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 Execute permissions. 7. Created the SQL job to automate the stored procedure execution on a daily frequency. Click to expand SQL Server Agent, then right click on the Jobs folder and select New Job. Server Agent Job 8. Enter a name and description for the job. Job General Properties 9. Click to select steps from the left side then click New at the bottom to create a new step. 10. Enter a name for the step and ensure T-SQL has been selected as the Type. Enter the following SQL script into the Command text box: USE “Enter Company Database” GO EXEC PBI_BuildAPTBData ‘Enter Start Date’, ‘Enter Account Sting’ GO Be sure to update the script values with data that correlates to your GP environment. 11. Click to select Schedules for the left and enter a schedule name. Select Daily as the Occurs value then specify the number of times to run per day. Specify the time of day the job is to run along with the date on which the job is to start executing. Confirm the schedule details in the summary box then click OK to save. Job Schedules Job Schedule Properties 12. Confirm your new job appears as shown in the following screenshot then click OK to complete the job creation. Job Schedules Confirm 13. Enter the following SQL script into a new query editor window then strike the F5 key or click Execute to create the new SQL view:

CREATE VIEW v_PBIAPRecData AS Select * from PBI_APAgingData GO

14. Update the permissions for the newly created SQL view by expanding the company database, and Views folder in the object. 15. Locate the newly created view within the list then right-click the view and select properties to open the properties window. Data table properties 16. 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

UPDATE ODATA SERVICE TO PUBLISH NEW OBJECT

1. Launch GP and log in using either SA or another GP user with proper rights to configure the ODATA features. Connect Dynamics GP to Power BI2. 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. Data Sources Data Sources Selections 3. Open Publish OData by navigating to Tools–>Setup–>System–>OData–>Publish OData, then click to select the publish checkbox next to the newly created data source. Click OK to complete this procedure. Publish Odata

GRANT ACCESS TO NEW ODATA OBJECT VIA GP SECURITY

1. Launch GP and log in 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. 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. 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. User Security Setup 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 provided 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 GP AP to GL Reconcile 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 Basic Properties *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 Evaluating 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 accounts payable configuration is often unique to each customer’s GP environment. In the following screenshot, the values are $0.00 because transactional data for the provided date range (01/01/2010 – 01/31/2019) does not exist within the TWO company database. *Tip* In the Visualizations control pane click to expand the Year Page level filter and select the current year. This will display the AP to GL reconciliation data through the current month of the current year. If your ledgers are in alignment, then you should see a $0.00 difference. In the situation that a difference exists, you can click to select the prior years to investigate when the difference occurred along with whether the difference has been consistent over time. At this point, you should have a clear representation of your Accounts Payable sub-ledger vs. general ledger AP control account(s) activity 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!

Tagged With

GPPower BI