Having the right data on how your company is performing is crucial to making business decisions. But dredging through long reports every day is a time consuming process that takes away from important productive activities.
Dashboards are a great alternative to reports as they provide you with an at-a-glance update on your company’s operating and financial status. These quick updates can help you make business decisions faster.
Power BI gives you the ability to create dashboards from Dynamics GP data that can be published and added to GP 2016 and 2018.
What is Power BI?
So, what exactly is Power BI, and what does it do? Here is how Microsoft describes it…
- Power BI is a suite of business-oriented analytics tools.
- Connects to hundreds of different data sources.
- Includes the tools to produce dashboards and reports on web and mobile devices.
- Works with PowerApps and Flow.
The ability of Power BI to interact with many sources makes it very powerful. You may be wondering if you can have dashboards in the GP that use data outside GP. The answer is yes, you can.
Connecting Power BI to Dynamics GP
There are two ways Power BI can connect to Dynamics GP. The first is by creating a direct SQL connection. This is generally easier to do quickly but has a direct load impact on the server. This means that doing expensive queries could impact performance.
The second method is via the OData service which is available on GP 2016 and later. This service is designed for creating report views and supports data caching to reduce SQL server load, but can be slower then direct SQL. The OData service can be used by any application that supports OData sources, such as Microsoft Excel. It can also be exposed to allow users in the organization to create reports using Power BI desktop without giving them direct access to the SQL server.
One additional thing I’d like to mention…if you’re running GP 2018, there is a free content pack available with pre-made OData report views that may help you get started faster.
Installing and Configuring the OData Service
The OData service must be installed and configured on your server by an individual with administrative rights before it can be used. It is available in the GP installation main menu under Additional Production as shown below:
The first installation screen requires the Dynamics SQL server name and the credentials for connecting to the SQL server.
The second installation screen has settings for TCP/IP port, which is used to connect to the OData service. The default is the standard HTTPS port of 433 which requires a certificate. While you can switch this port 80 and not use a certificate, this is not recommended as the transmitted data will not be secure. The lower half of the dialog contains fields to specify the domain, user, and name under which the service itself will run. This should be a separate account solely for services to use with password expiration disabled.
Once the installation is finished, the next steps are to configure the reporting in GP. You can do this by going to Tools -> Setup -> System -> Reporting Tools Setup which shows the screen below. Here you can set the URL to server running the OData. This must be entered as http:// or https:// followed by the full DNS name of the server.
If you are using the web client, then you must also check that box on the Power BI tab of this form.
The next steps are to set GP views, which will be published to OData. This is done in setup in Tools-> Setup -> System -> OData -> Data Source which shows the dialogue below. Here you can mark the views that are made available for OData use.
Once this is done, go to Tools -> Setup -> System -> OData -> Publish OData which brings up the Publish OData dialog shown below. Here you mark off the which of the previously marked views are published to OData. This dialog will also show the full OData URL to each source.
Next you must associate each user’s Windows domain account to their GP account in the User Setup Windows Account tab.
Finally, to test that everything is setup correctly, open a web browser and enter one of the URLs from the Publish OData setup. It should resolve without errors. Switch the view to Raw View and you’ll see the fields and views of raw report data.
Creating the Dashboard
Power BI Desktop can create the dashboard you want to be visible. Launch Power BI Desktop and you should be presented with an empty document. If you get a popup window on launch you can close it.
The first thing you need to do is retrieve data by clicking the bottom of the Get Data button on the ribbon bar and selecting either SQL Server to go direct or OData to use the OData service. For this example, we’ll use SQL Server.
You’ll then be prompted to enter the server and database. Remember that the GP companies are in separate databases and so you you’ll need to enter the company database name if you want to create the report again. After filling out this form, you may be prompted to enter the credential to connect.
Once connected, the Navigator dialog will appear which lets you browse and select which SQL objects to use for obtaining data. You can filter the list using the text box. For example, if we type in “Sales” the list will filter to like below. Now, let’s go ahead and select and check the box for SalesTransactions. On the right you’ll now see a preview of the data fields that will be queried.
Now click the Load button at the bottom and Power BI will pull the model into your dashboard. On the right-hand side will be a Fields list with all the fields that were pulled. You can see an example of this below:
If you look over this list, you’ll notice that many fields have a summation symbol to the right of the check box. Power BI does this automatically for numeric values as dashboards generally aren’t a single record but rather the total of many.
Now go to the Sub Total field and check the box. When you do this Power BI will automatically add the default bar chart control to the dashboard.
Just this value by itself isn’t useful, so let’s change this to show the total for a particular year. To do that scroll down to the Document Date field. Only this time instead of checking the box, we drag the field over the Page level filters area to the left and drop it there. A new filter is then added as seen below:
Since we are using the GP test company, Fabrikam, for this example the sales are all for 2014, so let’s use that year for the example. Change the Filter Type drop down to Advanced filtering and set a range between January 1st 2014 and January 1st 2015 and click Apply Filter.
The bar chart should now update to the range selected. However, it still says “Subtotal” on it which isn’t very helpful. We can change the label by moving the scroll bar in the Visualizations to the area above the filtering. At the top will be a Value box:
Right click on that and select Rename. Then type in “Total Sales for 2014” and hit enter. The bar chart will now update.
Keep in mind that this simple example is just touching the iceberg in terms of the many ways you can customize your dashboards. There are over two dozen controls available in Power BI, each with settings to customize the appearance. You can also add labels with text based off the data.
Now let’s publish this by clicking on the File drop down and selecting Publish followed by Publish to BI. You will be prompted to save the dashboard profile to a file. After saving, select “My Workspace” as the location to publish.
Linking GP to Power BI
To allow GP access to Power BI the application must first be registered. This is done by going to https://dev.powerbi.com/apps to create a client ID. For this test we’ll using GP2018. The application type is “Native app” and redirect page must be set to https://login.live.com/oauth20_desktop.srf.
In the Step 3 section check all the boxes like below:
Now click the Register app button and you will receive a client ID that you can use in the GP Reporting Tools Setup under the Power BI tab to make the dashboard available in GP.
What to Learn More?
Do you have a Dynamics Partner? A good Dynamics Partner like KTL Solutions has the knowledge and experience to help with training your staff on Dynamics GP. KTL Solutions understands that every organization is unique with its challenges and needs. Our experience working with hundreds of clients means that we can provide you with best practices, and software customizations you need to help your business succeed. We can even recommend implementations and improvements.
Don’t let your technology hold your business back. Let’s talk.
