How to Use Power BI to Analyze Microsoft Dynamics Data

Over the past several months, I had the pleasure of presenting at Power Platform World Tour in Reston at the Microsoft Center and teaching Dashboard in a Day at the Bethesda Microsoft Center. If you haven’t done either I highly recommend them.  The Power Platform World Tour’s lineup of great content; showcasing modern data visualization, app customization and innovation at its core. Our event series brought to you by your Power Platform User Groups, gives you the opportunity to come and meet both Microsoft and industry experts, explore the latest business challenges and solutions across various industries and see the innovative world of technology in action. Join us on this exploration around the world, gaining peer-to-peer networking opportunities and building a local support system sure to provide value 365 days a year.

Innovate for Greatness

The theme of this year’s Power Platform World Tour is pursuing greatness through innovation. We’ll be focusing on how the power platform applications can push the envelope on your business needs. Industry experts will be showing you the power and agility of Power BI, PowerApps and Microsoft Flow through robust learning sessions, demos and Q&A opportunities.

The Power Platform World Tour approach is one that focuses on you, the end user, pushing the boundaries of current business practice to becoming focused on empowerment, customization and innovation through a community approach. We believe these applications, along with this incredible event, are the recipe for success in 2019. We invite you to join us as we take a trip around the world.

Power BI – make sense of your data, produce beautiful reports, and uncover hidden insights for your business.

PowerApps – listen in as peers talk through how they have created apps that extend and mobilize their business using the no code PowerApps option. 

Microsoft Flow – find out how to automate processes, making your business more efficient.

 My Power Platform Presentation

During the Power Platform World Tour, I was able to present a Power BI solution that I created for one of KTL Solutions clients.  The below screenshots aren’t the full solutions provided to the client.  The Power BI PBIX file has been updated to embed the SQL queries in the Power Query code and SQL Server and database parameters so it can easily be added to your environment.  This Power BI Desktop works with Dynamics GP and has the Fabrikam demo data loaded into the reports.  You will need to modify the PBIX file to work with other ERP systems. So, let’s go over some of the reports within the file to get a general understanding for them.

Sales Overview

The first report is a Sales Overview that as the name implies provides an overview of the current years sales.  Across the top is the break out of sales by location and the ability to filter the overview by ABC classification.  The report also includes performance trends, sales growth by segment, sales growth by salesperson and sales by inventory item category that can be drilled down to the inventory item level.

Sales Overview

Customer Analysis Report

The Customer Analysis Report allows you to dig into Dynamics GP sales data by Customer, Year and Month.  By selecting a customer on the right-hand filter pane, you get to see three years of comparative sales by month, sales by item category, open sales orders, weekly moving average. Across the top of the report, you last sales amount, last sales date, and total sales amount, total Qty sold CY (current year), Total sales LY (last year), and percent sales growth for that customer.

Customer Analysis

Inventory Order Analysis

Inventory Analysis refers to a set of metrics used to optimize inventory levels — minimizing stock outs without overstocking. To remain liquid, you must turn inventory into cash so you can pay your bills, including employee wages. The more frequently you can turn over inventory, the higher your gross profits. Stock outs and backorders cut into gross profits, but so do overstocked items, known as safety stock. These items take up valuable space and require extra operations that drain cash from the business. In order to help you better analyze and manage your inventory levels, we’ve broken inventory analysis into a set of three standard equations you can easily compute. A good Inventory Reorder Analysis includes the recalculations to forecast future demand, safety stock, and find your reorder point.  For the Sales Quantity Forecasts for my lead time demand, I’m going to use the DAX time series discussed here by Fountain Analytics.

Inventory Analysis

Product Performance

Procurement is an important part of any business.  Regularly analyzing this data is easier with the use of Power BI. It ensures that you are making the best use of your company’s resources. Here are a couple of things to look at when analyzing your purchasing department – Cost and Delivery Time Analysis.

Cost Analysis

There are several things to consider when analyzing Inventory Costs. Obviously, the unit cost of the item, but we also have to look at delivery, handling, storage, deposits, and item returns from customers.  In the Power BI analysis below, I’m simply looking at the Item’s Current Cost from Dynamics GP Inventory Item Card vs. the Average Purchasing and Per Order Unit Costs.

Delivery/Lead Time Analysis

Delivery/Lead times can be important to companies, particularly when perishable and large goods are concerned.  Delivery/Lead times that start to creep higher will hamper your Item Resource Planning Process. Your staff needs to analysis delivery/lead times and plan accordingly to prevent stock outs, limit storage area, and cash resource requirements.

Product Performance

Updated Power BI Desktop File

The updated Power BI Desktop file is no longer a template but pre-loaded with Dynamics GP Fabrikam demo data.  The data is limited since this is the demo company within Dynamics GP but at least you can see how the reports work.  You can download it here.  Additionally, all of the SQL queries are embedded in the Power Queries that pull the data.  To add these reports to your system, you will need your Active Directory/window login ID added to SQL server.  Once that is done follow these steps to add your own data:

  1. Click on the “Edit Queries” button to open Power Query EditorEdit Queries
  2. Navigate to either the SQLServer or Database parameters and then click on the Manage Parameters button.Manage Parameters
  3. Enter in your SQL Server and Dynamics GP company database information.Server Settings
  4. Close & Apply to save the changes and load your Dynamics GP data.

Ready to Do Even More with Your Data?

Start organizing, knowing and executing on your data today with dataflows and Power BI to provide a self-service data lake in the future.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s