Transform Dynamics GP Data into Actionable Intelligence with Power BI

By Rob Wagner | April 23, 2024

Actionable intelligence blank Power BI

Creating actionable intelligence from your Dynamics GP data can be accomplished with the robust features and functionalities in Power BI.

In business, data is the lifeblood of any organization that fuels informed decision-making and propels growth. However, harnessing its power requires more than acquiring some numbers and putting them into a spreadsheet. Organizations must take a strategic approach to capture, model, and analyze the data effectively to plan two steps ahead when planning for the future.

In this blog, we will dive into how you can use your Dynamics GP data to create actionable intelligence through the data visualization capabilities in Power BI.

First, let’s answer a key question:

What is Actionable Intelligence?

Actionable intelligence is information or data available at your fingertips you can use to guide your decision-making during an evolving situation or even forecast the future. A simple but effective example is something we see at the grocery store every year when the seasons change. Based on buyer behaviors and data compiled about shopping habits, stores know to put items like corn, BBQ materials, sunscreen, and cold beverages in high-traffic areas during the summer months. Conversely, they might put warm clothing, hot beverages, and holiday-themed items in those areas during the winter months.

By turning your data into actionable intelligence, you can capture meaningful insights into how your business is performing, areas of strength, and places where you can improve. All of this combines to guide your decision-making now and in the future.

An easy way to do this is to take your ERP data – from Dynamics GP, as an example – and combine it with the strength of Power BI.

Establishing Actionable Intelligence: How to Get Your Dynamics GP Data into Power BI

While this might sound like a complicated process, it’s as simple as following these steps:

1. Open Power BI

Start with a clean slate and open a blank Power BI report.

2. Pull in your Dynamics GP data from the SQL Server

Choose Get Data and then import data from SQL Server. Make sure you enter the SQL Server name and the database containing the data you want to pull in, then press OK.

Actionable intelligence import sql server data

3. Find the data tables you want to use

Once you connect to the database, you will see a list of views and tables available. Select the ones you want to include in the Power BI report and then click “Transform data”. This will bring the data into the Power Query editor where you can rename or amend your datasets to make them more user-friendly.

Select data tables

During this step, you can also create Append queries, also known as views between two tables inside the Power Query editor. Doing this creates a friendly-named combination of tables for the model user to draw from. You can then hide base tables from the user to avoid the unfriendly named components. This process ensures that users creating a Power BI report only see the easily identifiable and relevant information they need for their jobs.

Append Tables

4. Click “Close and Apply” to bring the data into Power BI

After the system completes the upload, you can move on to the next step.

Actionable intelligence click close and apply

The Three Ms of Creating Actionable Intelligence with Power BI: Modelling, Munging, and Making

Now that your Dynamics GP data has been uploaded into Power BI, it’s time to transform the shape of your model. You can do this by following the three Ms, modeling and munging. Let’s dive into how each one functions in helping you create your report.

Modeling with Munging Your Data

Actionable intelligence munging

The first step is to munge your data together. This is simply the process of creating relationships between the tables and datasets. You can do this in Power BI by matching table names through a user-friendly drag-and-drop feature. Once you have munged the data, you have a simple ready-to-use model. Note: Further transformation is done with Excel-like functions inside Power Query.

A new window will open when you create a relationship. You can use the defaults and click “OK.”

Actionable intelligence relationships

Once you’ve created your relationships and munged the tables to produce your model, it’s time to make your visuals and presentations. You can do this by co-mingling the data with charts and dropping the field into the “well.” Note that “well” is a term applied to whatever visual you selected. Just as you’d lower a bucket into a well to retrieve water, you “lower” your fields into the well to retrieve insights from your data.

Actionable intelligence making

Making Your Power BI Dashboard

If you go back into your report, you can select visuals from the panel on the right-hand side of your screen. For this example, we’ve chosen a column chart. If you go further right, you will find your data tables and add them to the visual.

You’re not limited to a single visual per report. You can add other visuals that display different data and then drag and drop or size them to fit your needs. You can also apply your branding elements like colors logos, and more. When you make your report, you will pull from the Model that has tables munged during the transformation process.

Actionable intelligence making

You can also customize the reports to make them interactive. In the example below you can click on the geographical regions in the map and see different sales numbers for each region in the same view. You can do the same with months, weeks, or years if you are trying to show revenue growth.

There are many ways you can customize the report to make it interactive.

Here is your finished product:

dynamics gp data power bi report

Want to Learn More About What Power BI Can Do for You?

Reach out to Stoneridge Software! Whether you are operating a legacy system like Dynamics GP or something cloud-based in Dynamics 365, Power BI can help you easily compile and display your data so you can use it to create actionable intelligence to drive informed decision-making.

Related Posts

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!