Enhancing our Inventory Reorder Point Analysis in Power BI

In my original Inventory Reorder Analysis report, I’m using some the technics discussed by Belinda Allen in her Inventory Item Reorder Dashboard. I converted it to Power BI and added a little extra by also analyzing SOP product sales data and computing “Inventory Logic” based on whether the “Inventory Sold in Time Period” columns total is three times the current “Qty Available” producing a report that looked like the screen shot below.

Original Inventory Re-Order Analysis

Enhancing the Inventory Reorder Analysis

A good Inventory Reorder analysis includes the recalculations to forecast future demand and safety stock 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. After following time series instructions my sales forecast results looked like this.

Sales Quantity Usage Forecast

With the Sales forecast quantity usage in place, I then needed to calculate our Safety Stock based on the below formula.

Safety Stock Formula

Using the data from the Procurement Analysis and the Sales Analysis reports, I calculated the four data points with the below DAX formulas.

Max Days Consumption =

MAXX (

VALUES ( Dates[Date] ),

MAXX(

VALUES( ‘Products'[ItemNumber] ),

‘Sales Measures'[Units Sold]

))

Max Days Inventory Lead Time =

MAXX (

VALUES ( Dates[Date] ),

MAXX(

VALUES( ‘Products'[ItemNumber] ),

[Lead Time]

))

Average Days Consumption =

AVERAGEX (

VALUES ( Dates[Date] ),

AVERAGEX(

VALUES( ‘Products'[ItemNumber] ),

‘Sales Measures'[Units Sold]

))

Average Days Inventory Lead Time =

AVERAGEX (

VALUES ( Dates[Date] ),

AVERAGEX(

VALUES( ‘Products'[ItemNumber] ),

[Lead Time]

))

You put it all together to your Reorder Point – (Lead time demand) + (safety stock) = Reorder Point. The end result is the updated Inventory Reorder Analysis report shown below.

Update Inventory Re-Order Point Analysis

Updated Template

You can download the updated template here. In the template, I also updated the Inventory Reorder Point report using the New Matrix functionality.

Stay tuned for the future developments of the reports and an overview of their functionality and for help with building reports like this.

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

2 more Inventory Power BI Dashboards to Supercharge Your Dynamics GP Reporting

As I continue to build out my Dynamics GP Power BI dashboards, we look at product performance and inventory reorder analysis.

Product Performance

In the Product Performance dashboard, I’ve add ABC segmentation, moving averages and time comparisons using DAX measures. You can find additional information on the technics I’m using within my dashboards and reports at www.daxpatterns.com.

In the Product Performance report below, I used a scatter chart to analysis the Product classes profit margin and totals profits, the Sales ABC by ABC Class stacked bar chart segments the products using ABC segmentation and allows you to drill down into each segment to see the under-lining detail. Additionally, there is a total cost by warehouse location and weekly moving average cost comparison chart at the bottom of the report.

Power BI GP Product Performance

Inventory Reorder Analysis

For my Inventory Reorder Analysis report I’m using some the technics discussed by Belinda Allen in her YouTube video on Inventory Item Reorder Dashboard. I converted it to Power BI and added a little extra by also analyzing SOP product sales data and computing “Inventory Logic” based on whether the “Inventory Sold in Time Period” columns total is three times the current “Qty Available”

Power BI Inventory Reorder

Updated Template

You can download the updated template here. In the template, I also updated the Finance Summary report to use the New Matrix visual that was released in March. This adds the drill down functionality to the Profit and Loss Matrix Visual.

Future Dashboard developments

In the coming months, I plan additional Power BI reports associated with my Dynamics GP data. These reports will include:

  • Vendor Analysis
  • Field Service – Contract and Service Call Analysis

Stay tuned for the future developments of the reports and an overview of their functionality.

Supercharge Your Dynamics GP Dashboards & Reports with Power BI

During my last blog post, I walked you through creating a financial dashboard using Power BI with Dynamics GP data. I have created additional analysis and measures now.

clip_image002

If you look closely at the bottom left, you will see two additional reports for Sales Summary and Customer Analysis.

Sales Summary

The data used from Dynamics GP for this report are the Sales Order Processing (SOP) tables, Inventory, (IV) and Customer (RM) tables. The Sales Summary contains the same report filters, Year, Month and Company as the Financial Summary report. The top left visual shows the total sales by product class ranked in descending order. The bottom left visual shows the total sales by customer class/sales channel. The top two visuals in the middle and right show the total sales and transactions based on the filters selected with a seven-day moving average behind them. The bottom right visual shows a comparative between cumulative sales and cumulative costs.

clip_image004

Customer Analysis

The Customer Analysis report allows you to dig into Dynamics GP sales data by Customer, Year, Month and Company. By selecting a customer on the left you get to see three years of comparative sales, sales detail, last sales date and amount, total sales, sales last year, totals profits and percent sales growth for that customer.

clip_image006

Future Dashboard developments

In the coming months, I plan additional Power BI reports associated with my Dynamics GP data. These reports will include:

  • Vendor Analysis
  • Inventory Analysis
  • Field Service – Contract and Service Call Analysis

Stay tuned for the future developments of the reports and an overview of their functionality.

Power BI Visuals in On Premise SQL Server Reporting Services

Yesterday, January 17, 2017, Microsoft released the Power BI reports in SQL Server Reporting Services (SSRS) technical preview. You can read all about it here. I downloaded the technical preview and installed it on my system and deployed a Power BI Visual to my SSRS web site. Here are the steps to get it installed on your system.

· Download the SSRS Technical Preview and Power BI Designer for the preview

Power BI SSRS

· The Technical Preview has the following system requirements:

    • Your own VM or server (not one in production use)
    • Windows Server 2012 or later (or Windows 8 or later)
    • .NET Framework 4.5.2 or later
    • SQL Server Database Engine (2008 or later), to store the report server database
    • SQL Server Analysis Services (2012 SP1 CU4 or later), to store your data models

· I first installed the SQLServerReportingServices.exe on my system and accepted the licensing terms and agreement and then selected the “Install” button.

POwer BI SSRS

· After the installation completed I needed to configure the technical preview of SSRS by selecting the “Configure Report Server” button.

Power BI SSRS

· I provided my SQL Server name to Connect the Reporting Services Configuration Manager.

Power BI SSRS

**NOTE – While the configuration manager let, me configure the SSRS technical preview web site with my original VM’s computer name of ‘BARRYCROWEL38F0’ I could not publish by Power BI report with that computer name. I shortened the name to 7 characters and removed the numbers from the name to get the Power BI Designer Technical Preview to function with the SSRS web site.

· Design a Power BI visual with the Power BI Designer Technical Preview. In my visual, I connected to an SQL Server Analysis Services (SSAS) database. To publish you report to SSRS, select the ‘Save As’ menu option.

Power BI Technical Preview SSRS

· Following the screen prompts and supply the Reporting Server.

Power BI Technical Preview SSRS

· Then enter the report name.

Power BI technical preview SSRS

The final product is a Power BI visual with slicer functionality in an on premise SSRS web site.

**NOTE – the current SSRS technical preview does not work with custom visuals or R visuals.

Power BI On Premise

Conclusion

I’m loving the first on premise technical preview and can’t wait for what next.

May the Force of PowerShell Module dbatools be with you

Dbatools is a great PowerShell module that has 106 ways to help ease the administrative burden faced by SQL Server DBA’s and developers. As a IT Consultant that handles Microsoft Dynamics products that install within SQL Server, I would suggest that this is a great module for us to know and use too.

For those that may not know what PowerShell is, it was developed by Microsoft for task automation and configuration management. You can learn more about PowerShell at Microsoft Virtual Academy

How to Install

There are four ways to install dbatools module into PowerShell. The PowerShell command that I used was:

Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in)

The complete instructions on how to install the module can be found on dbatool’s download page: https://dbatools.io/download/

Getting Started

There are several scenarios to use dbatools. The developer’s goal with every command is to support:

  • SQL Server 2000 – 2016
  • Express – Datacenter Edition
  • Clustered and stand-alone instances
  • Windows and SQL authentication
  • Default and named instances
  • Multiple instances on one server

You can learn more about dbatools and the Functions associated with the PowerShell module here: https://dbatools.io/getting-started/

Functions to Know for Dynamics

As IT Consultant in the Microsoft Dynamics space, we are sometimes tasked with setting up SQL Server and/or migrating the Dynamics databases to the client’s new SQL Server. Some of the key functions I have used in the past to do this are:

clip_image001 Databases

clip_image002 Security

clip_image003 Server Objects

clip_image004 SSIS

clip_image005 Management

clip_image006 SQL Server Agent

Best Practice Commands

General Administration Commands

A complete list of the 106 Functions contained in the current version, 0.8.693. Can be viewed here, https://dbatools.io/functions/, and just follow the link for each Function on that page to get the details on that Functions use.

Conclusion

Until next time, I hope this helps you with your SQL Server Administration and database migrations.

Dynamic Security – a look at the final product

Introduction

Recently I posted a blog titled “How to implement Analysis Services Dynamic Security based on Microsoft CRM user access rights”. That post talked about how to implement the back-end structure needed to put it in place but didn’t show the result of all that hard work.

The dynamic row level security (RLS) that we setup inside Analysis Services will work within reports and dashboards built inside Reporting Services, Power BI and Excel. Power BI has other ways of deploying RLS when not using an Analysis Services connection. You can learn more about that here:

· http://sqlkover.com/dynamic-security-in-power-bi/

· http://radacad.com/row-level-security-configuration-in-power-bi-desktop

The End Result

We built are Dashboard in Power BI Designer and published the Dashboard to our end users. One of the end users “Tom” has access to 558 CRM Customer account within the US. When he accesses the Dashboard, his can view results that he has privileges to. (See Figure 1 below)

Power BI dashboard image 1

Figure 1 – Tom’s access view

If “Tom” tries to access an area that he doesn’t have privileges to, say Canada, the dashboard allows him to select the filter and changes the chart(s) results to show no data. (See Figure 2 below)

Power BI dashboard image 2

Figure 2 – Tom’s view with Canada selected

Additionally, the chart(s) change based on the end user that’s accessing the dashboard. In Figure 3 below, I show an administrative user accessing the dashboard and the result shown to them when selecting the “Canada” filter.

Power BI Dashboard image 3

Figure 3 – Administrative user view of Canadian Sales

Conclusion

I hope this helps you with your future Analysis Services development and Dashboard projects.