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.

Advertisements

Analyzing Dynamics GP Procurement Transactions with Power BI

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

Cost Analysis
There are several things to look at 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 and the Item’s Current Cost from Dynamics GP Inventory Item Card vs. the Average purchasing cost 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. Staff need to analysis delivery/lead times and plan accordingly to prevent stock outs and limit storage area and cash resource needs.

Putting It All Together
I first created the below SQL view in my Dynamics GP company.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[vw_POPTransactions]
AS
    WITH    CTE
              AS ( SELECT   [PONUMBER] ,
                            [ORD] ,
                            [POLNESTA] ,
                            [POTYPE] ,
                            [ITEMNMBR] ,
                            [ITEMDESC] ,
                            [VENDORID] ,
                            [VNDITNUM] ,
                            [VNDITDSC] ,
                            [NONINVEN] ,
                            [LOCNCODE] ,
                            [UOFM] ,
                            [UMQTYINB] ,
                            [QTYORDER] ,
                            [QTYCANCE] ,
                            [UNITCOST] ,
                            [EXTDCOST] ,
                            [INVINDX] ,
                            [REQDATE] ,
                            [PRMDATE] ,
                            [PRMSHPDTE] ,
                            [REQSTDBY] ,
                            [COMMNTID] ,
                            [DOCTYPE] ,
                            [POLNEARY_1] ,
                            [POLNEARY_2] ,
                            [POLNEARY_3] ,
                            [POLNEARY_4] ,
                            [POLNEARY_5] ,
                            [POLNEARY_6] ,
                            [POLNEARY_7] ,
                            [POLNEARY_8] ,
                            [POLNEARY_9] ,
                            [DECPLCUR] ,
                            [DECPLQTY] ,
                            [ITMTRKOP] ,
                            [VCTNMTHD] ,
                            [BRKFLD1] ,
                            [JOBNUMBR] ,
                            [COSTCODE] ,
                            [COSTTYPE] ,
                            [ORUNTCST] ,
                            [OREXTCST] ,
                            [LINEORIGIN] ,
                            [FREEONBOARD] ,
                            [ODECPLCU] ,
                            [Product_Indicator] ,
                            [Source_Document_Number] ,
                            [Source_Document_Line_Num] ,
                            [RELEASEBYDATE] ,
                            [Released_Date] ,
                            [Purchase_IV_Item_Taxable] ,
                            [Purchase_Item_Tax_Schedu] ,
                            [Purchase_Site_Tax_Schedu] ,
                            [PURCHSITETXSCHSRC] ,
                            [BSIVCTTL] ,
                            [TAXAMNT] ,
                            [ORTAXAMT] ,
                            [BCKTXAMT] ,
                            [OBTAXAMT] ,
                            [Landed_Cost_Group_ID] ,
                            [SHIPMTHD] ,
                            [LineNumber] ,
                            [ORIGPRMDATE] ,
                            [FSTRCPTDT] ,
                            [LSTRCPTDT] ,
                            [RELEASE] ,
                            [ADRSCODE] ,
                            [CMPNYNAM] ,
                            [CONTACT] ,
                            [ADDRESS1] ,
                            [ADDRESS2] ,
                            [ADDRESS3] ,
                            [CITY] ,
                            [STATE] ,
                            [ZIPCODE] ,
                            [CCode] ,
                            [COUNTRY] ,
                            [PHONE1] ,
                            [PHONE2] ,
                            [PHONE3] ,
                            [FAX] ,
                            [ADDRSOURCE]
                   FROM     POP10110
                   UNION ALL
                   SELECT   [PONUMBER] ,
                            [ORD] ,
                            [POLNESTA] ,
                            [POTYPE] ,
                            [ITEMNMBR] ,
                            [ITEMDESC] ,
                            [VENDORID] ,
                            [VNDITNUM] ,
                            [VNDITDSC] ,
                            [NONINVEN] ,
                            [LOCNCODE] ,
                            [UOFM] ,
                            [UMQTYINB] ,
                            [QTYORDER] ,
                            [QTYCANCE] ,
                            [UNITCOST] ,
                            [EXTDCOST] ,
                            [INVINDX] ,
                            [REQDATE] ,
                            [PRMDATE] ,
                            [PRMSHPDTE] ,
                            [REQSTDBY] ,
                            [COMMNTID] ,
                            [DOCTYPE] ,
                            [POLNEARY_1] ,
                            [POLNEARY_2] ,
                            [POLNEARY_3] ,
                            [POLNEARY_4] ,
                            [POLNEARY_5] ,
                            [POLNEARY_6] ,
                            [POLNEARY_7] ,
                            [POLNEARY_8] ,
                            [POLNEARY_9] ,
                            [DECPLCUR] ,
                            [DECPLQTY] ,
                            [ITMTRKOP] ,
                            [VCTNMTHD] ,
                            [BRKFLD1] ,
                            [JOBNUMBR] ,
                            [COSTCODE] ,
                            [COSTTYPE] ,
                            [ORUNTCST] ,
                            [OREXTCST] ,
                            [LINEORIGIN] ,
                            [FREEONBOARD] ,
                            [ODECPLCU] ,
                            [Product_Indicator] ,
                            [Source_Document_Number] ,
                            [Source_Document_Line_Num] ,
                            [RELEASEBYDATE] ,
                            [Released_Date] ,
                            [Purchase_IV_Item_Taxable] ,
                            [Purchase_Item_Tax_Schedu] ,
                            [Purchase_Site_Tax_Schedu] ,
                            [PURCHSITETXSCHSRC] ,
                            [BSIVCTTL] ,
                            [TAXAMNT] ,
                            [ORTAXAMT] ,
                            [BCKTXAMT] ,
                            [OBTAXAMT] ,
                            [Landed_Cost_Group_ID] ,
                            [SHIPMTHD] ,
                            [LineNumber] ,
                            [ORIGPRMDATE] ,
                            [FSTRCPTDT] ,
                            [LSTRCPTDT] ,
                            [RELEASE] ,
                            [ADRSCODE] ,
                            [CMPNYNAM] ,
                            [CONTACT] ,
                            [ADDRESS1] ,
                            [ADDRESS2] ,
                            [ADDRESS3] ,
                            [CITY] ,
                            [STATE] ,
                            [ZIPCODE] ,
                            [CCode] ,
                            [COUNTRY] ,
                            [PHONE1] ,
                            [PHONE2] ,
                            [PHONE3] ,
                            [FAX] ,
                            [ADDRSOURCE]
                   FROM     POP30110
                 )
    SELECT  a.POPIVCNO ,
            a.IVCLINNO ,
            a.POPRCTNM ,
            a.RCPTLNNM ,
            b.VENDORID ,
            b.VENDNAME ,
            b.VNDDOCNM AS [Invoice No] ,
            b.receiptdate AS [Invoice Date] ,
            f.Released_Date ,
            DATEDIFF(day, f.Released_Date, c.receiptdate) AS Lead_Time ,
            f.PONUMBER ,
            c.VNDDOCNM AS [Bill of Lading] ,
            c.receiptdate AS [Date Received] ,
            d.ITEMNMBR ,
            d.ITEMDESC ,
            d.VNDITNUM ,
            d.VNDITDSC ,
            a.QTYINVCD ,
            e.QTYSHPPD ,
            e.QTYREJ ,
            f.UNITCOST
    FROM    POP10600 a
            INNER JOIN POP30300 b ON a.POPIVCNO = b.POPRCTNM
            INNER JOIN POP30300 c ON a.POPRCTNM = c.POPRCTNM
            INNER JOIN POP30310 d ON a.POPRCTNM = d.POPRCTNM
                                     AND a.RCPTLNNM = d.RCPTLNNM
            INNER JOIN CTE f ON d.PONUMBER = f.PONUMBER
                                AND d.RCPTLNNM = f.ORD
            INNER JOIN POP10500 e ON a.POPRCTNM = e.POPRCTNM
                                     AND a.RCPTLNNM = e.RCPTLNNM

Next I imported the data into my Power BI Desktop pbix file, and created DAX measures for Total Lead Time, Total purchasing Unit Cost, Average Lead Time, Average Purchasing Unit Cost and Lead Time Weekly Moving Average. The end result is the Power BI report below that compares:

  • Item Current Cost vs. Average Purchasing Unit Cost and order detailed costs
  • Average Lead Time vs. per order lead times

clip_image002

Future Dashboard developments

In the coming months, I plan additional Power BI reports associated with my Dynamics GP data. The report will include an updated inventory procurement analysis using the lead time analysis from this post and sales forecasts

What happened to all of my GP OLE Notes?

During the migration of OLE Notes to the GP 2013 and above to the document attach functionality not all OLE Notes will get migrated through the Migration tool. After the GP OLE Notes migration tool has completed you will see a screen detailing the process and the number of unsuccessfully extracted notes. Through research from other GP OLE Notes blog, I was able to piece together a manual process to identify, extract and then attach the document within GP to the appropriate record.

GP OLE Notes 002

The Manual Process

Part One – Identifying the Record the OLE Note is associated with

All OLE Notes that weren’t successfully extracted by Microsoft’s migration utility will remain in the original extraction path.

1. To create a list of the files within the folder follow the directions in this Microsoft KB https://support.microsoft.com/en-us/kb/196158. After that I created an Admin database in SSMS on the SQL server and then imported the results within the txt file to a table called OLENotes_Missing.

2. I ran the following SQL script in SSMS to find all missing OLE Note names and associated Note Indexes and convert the HEX Note name.

SELECT NoteINDEX, DATE1, HEX_REMOVE as [OLENote Name]

FROM [ADMIN].[dbo].[OLENotes_Missing]

Order By DATE1 DESC

3. Take the Note Index from first SQL Script and run below SQL script in SSMS to find associated tables. Replace Note Index searching for into yellow highlighted area.

USE [<Production Database>]

DECLARE @noteindx nvarchar(20)

DECLARE @tablename nvarchar(20)

DECLARE @sqlstring nvarchar(MAX)

DECLARE @param nvarchar(100)

DECLARE @print nvarchar(100)

SET @noteindx = ‘9708539’ –Change to note index that you would like to find

DECLARE notecursor CURSOR FOR

SELECT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME

WHERE c.COLUMN_NAME = ‘NOTEINDX’ AND t.TABLE_TYPE = ‘BASE TABLE’

OPEN notecursor

FETCH NEXT FROM notecursor INTO @tablename

SET @sqlstring = N’SELECT ”’ + @tablename + ”’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ‘ + @tablename + ‘ WHERE NOTEINDX = ‘ + @noteindx + CHAR(13);

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlstring += N’ UNION SELECT ”’ + @tablename + ”’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ‘ + @tablename + ‘ WHERE NOTEINDX = ‘ + @noteindx + CHAR(13);

FETCH NEXT FROM notecursor INTO @tablename

END

CLOSE notecursor

DEALLOCATE notecursor

EXEC(@sqlstring)

4. Output from above script will look like below.

GP OLE Notes 004

5. Disregard TableName SY03900 and substitute the other TableName and DEX_ROW_ID into SQL script below and execute in SSMS. This will give you the record that the OLE Notes document will need to be attached to.

SELECT * from SVC00600 WHERE DEX_ROW_ID = 51259

GP OLE Notes 006

PART 2 – Document Extraction and Attachment

Using 7Zip you can manually extract the contains of the file.

1. Open 7Zip and navigate to original OLE Note extraction location.

2. Find Note Name you wish to open and then drill down into Embedding folder(s)

GP OLE Notes 008

3. Open CONTENTS file with Internet Explorer

GP OLE Notes 010

4. Print output to PDF

GP OLE Notes 012

5. Log into GP and find the record from SQL script from step 5 of Part 1 and then attached record using document attach functionality. Follow Steps 1 through 3.

GP OLE Notes

A very manual process, but worth the time if you need the documents within your GP system.