Sharing the Wealth – Sorry not $$$ just Knowledge

Recently, I created several Power BI visuals on Dynamics GP data and had a request to share the SQL scripts I used to import into my Power BI model’s datasets. The previous Power BI datasets and visuals included:

  • General Ledger financial statement and analysis
  • Inventory reorder point analysis
  • Procurement analysis
  • Sales summary
  • Customer analysis
  • Sales Return (RMA) analysis

Most of the scripts where SQL views and can be used to import into Power BI or in SSIS to import into a Data Mart. Some SQL scripts will need to be changed to meet your needs or fit your environment. The zip file can be found here. Hope this helps with your quest for knowledge.

Advertisements

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

Power BI, R and Marketing Campaign Analysis

For the second post in a row, I’m going to talk about casino performance analysis so I’m feeling a little like Marty McFly from Back to the Future. One of the biggest struggles we had was with our direct mail offers to our players’ club members. A proper direct mail offer campaign would be setup with a control group so we could perform analysis against the control group, but try to tell that to the A tier player that didn’t get an offer or a different amount then the other A tier players.

Without the control group, we would calculate the difference between the average coin in before and after the direct mail offer was received to judge our incremental revenue and evaluate whether the campaign was successful. This method was very simplistic because it assumed all differences between performance before and after the interaction are due strictly to that direct mail offer. Google set out to help digital marketers with this problem of no control group in 2014 and we can use their Causal Impact R package to analyze our casino’s direct mail offers as well.

Putting it All to Use

Using the same daily Coin in (gross sales) data from the last blog on “Use Power BI and R to Quickly Identify Business Insights”, we need to complete the following steps:

  1. Install the following R packages
    1. install.packages(“devtools”)
    2. install.packages(“BoomSpikeSlab”)
    3. install.packages(“dtw”)
    4. install.packages(“chron”)
    5. devtools::install_github(“google/CausalImpact”)
  2. In Power BI Desktop, select the “R” in the Visualizations section and then drag the Denomination, Date and Revenue fields from the Coin In tables into the Values section to build our dataset for are R code.

Power BI Desktop

3. Add the following code in the R Script editor

library(CausalImpact)

library(data.table)

productsM <- melt(dataset, id=1:2)

productsC <- dcast(productsM, Date ~ Denomination + variable, sum)

colnames(productsC) <- make.names(colnames(productsC))

products <- zoo(productsC[,2:ncol(productsC)],as.Date(productsC[,1]))

pre.period <- as.Date(c(“2008-01-01”, “2008-10-03”))

post.period <- as.Date(c(“2008-10-04”, “2008-11-15”))

impact <- CausalImpact(products, pre.period, post.period, model.args=list(nseasons=7))

plot(impact)

#summary(impact)

4. Click the run button to execute the R script

Power BI R Script

Understanding the Results

The Casual Impact R package reduces the below three graphs

Original: Solid, black line is observed data before the intervention and dotted, blue line is the predicted coin in values for what would have occurred without the intervention

Pointwise: The net difference between the observed and predicted response on the original scale, or the difference between the solid, black line and the dotted, blue line on the original graph.

Cumulative: Dotted, blue line is the individual causal effects added up in time, day after day.

For all three graphs, the light blue shaded area represents the results in a 95% confidence level. The farther that the graph extends past the beginning of the intervention, the less certain of the causal effect; hence, the larger the shaded area.Power BI R visualization

Conclusion

Hope this helps you with your marketing campaign analysis in your organization.

Use Power BI and R to Quickly Identify Business Insights

In statistics, the correlation coefficient or Pearson’s correlation is a measure the strength and direction of association that exists between two continuous variables. The value of correlation, r, is always between +1 and –1. To interpret its value, see which of the following values your correlation r is closest to:

  • Exactly 1. A perfect downhill (negative) linear relationship
  • 0.70. A strong downhill (negative) linear relationship
  • 0.50. A moderate downhill (negative) relationship
  • 0.30. A weak downhill (negative) linear relationship
  • 0. No linear relationship
  • +0.30. A weak uphill (positive) linear relationship
  • +0.50. A moderate uphill (positive) relationship
  • +0.70. A strong uphill (positive) linear relationship
  • Exactly +1. A perfect uphill (positive) linear relationship

Don’t make the mistake of thinking that a correlation of –1 is a bad thing, indicating no relationship. Just the opposite is true! A correlation of –1 means the data are lined up in a perfect straight line, the strongest negative linear relationship you can get. The “–” (minus) sign just happens to indicate a negative relationship, a downhill line. Most statisticians like to see correlations beyond at least +0.5 or –0.5 before drawing any conclusions. Additional information on Pearson’s correlation can be found here.

Putting it All to Use

This is all great knowledge, but how can we apply this in a business environment. When I worked for a casino, we kept a daily record of Coin In (gross sales), High Temp, Low Temp and Fuel price in an Excel spreadsheet and tried to find a correlation between these data points. Try find the correlation with this data.

Excel Casino data

With Power BI and R, we can make this association all that much faster and with slicer for interactivity to drill into the data points. I first downloaded the R Correlation Plot custom visual located here and then Excel spreadsheet from above imported into Power BI. I was then able easily identify the correlations in a matter of minutes.

Positive correlations are identified with increasingly dark blue circles, negative correlation is red. The greater the bigger and darker the circle. Selecting “Nov” from the calendar slicer, you will notice that the intersection between “Low Temp” has a positive correlation (blue circle), so the lower the temperature the less you will realize in coin in for the that month.

Power BI and R Correlation Plot

Selecting the “Jul” from the calendar slicer, and notice that the correlation is different.

In this case, the correlation is zero between temperature and coin in.

Power BI and R Correlation Plot image 2

Conclusion

Hope this helps you quickly identify business insights in your organization.

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.