3 Secrets To An Awesome ERP System!

Part of being a Microsoft Dynamics Consultant is seeing all the inventive ways clients use the software we deploy and take that knowledge and provide best practices to everyone! Here are just some of the things to enhance your system processes:

  • Best Practices
  • Customization and Modifications
  • Reporting Enhancements

Best Practices

Customization, Modifications and Third Party Add-ins

  • General Ledger, Sales Distribution, and Payables Distribution intercompany Excel paste – James Lyn’s Excel Paste add-in. is great at extending Dynamics GP’s out of the box functionality. While you’re on his site, check out his other add-ins like GP batch attach for Payables.
  • Create Dynamics GP Marcos or use PowerShell scripts to automate tasks – i.e. Macro to log into Dynamics GP and run inventory reconcile process or reboot your web client servers to remove hung processes.
  • Custom workflows and reporting – reporting to provide detail information on current/open and historical/approved workflows. Find out how to do that here.

Reporting Enhancements

Dynamics GP comes with some good reporting capabilities:

  • Management Reporter.
  • Excel refreshable reports.
  • SmartList.
  • Jet Express for Dynamics GP.
  • Solver’s BI360.
  • Power BI.

With Dynamics GP 2018 you can now deploy Power BI GP content pack or embedded Power BI visual inside of Dynamics GP. So what do the Power BI content pack visuals look like and how do we get them installed? As of Microsoft Dynamics GP 2018, the GP OData service was updated to OData version 4. This redesign also brought on paging and filtering of OData requests. This will create a more stable and robust platform for delivering Microsoft Dynamics GP content to authenticated users. The Power BI content pack features sample reports for Financial, Sales, Purchasing and Inventory data. Each report utilizes relationships built between GP tables and various Filters that can be used to display the information that is important to you. You can also review the included Calculated Columns as examples for including calculations on your Power BI reports such as Net Debit/Credit, Profit, and Item Sales amounts.

In order to use the Power BI Content Pack with Dynamics GP, install OData Services. Once this is complete, you will have to publish the following Data Sources inside Dynamics GP. (Administration > System > OData > Data Sources) The following Data Sources will need to be published for the GP content pack.

  • Accounts
  • Account Transactions
  • Customers
  • Inventory Sales Summary Period History
  • Inventory Transactions
  • Item Quantities
  • Purchase Line Items
  • Purchase Requisition Lines
  • Purchase Requisitions
  • Receiving Line Items
  • Sales Line Items
  • Vendors

The Power BI Content Pack will also have to be configured to point to you’re existing Microsoft Dynamics GP OData Service. This can be done by modifying the existing Data source in Power BI, or by creating a new data source and configuring the content to use the new source. The screenshots below show what the GP content pack sample reports look like.

Finance Dashboard

Finance

Sales Dashboard

Sales

Purchasing Dashboard

Purchasing

Inventory Dashboard

Inventory

Enhancing Our Dashboards

These GP content pack reports are a good starting point and can speed up the process of implementing a Power BI solution. With a little work from your Microsoft Dynamics Consultant, we can provide you so much more. In February of 2017, I started a blog series that provided a “how to” on building a Finance, Sales, Customer, Product, and Inventory dashboard. Since my main goal was to show an update of the Excel multi-company dashboard, I choose to first build a small DataMart and integrate the data from Dynamics GP databases before building my visuals. Follow the links below to find out how I built each one of the enhanced dashboards.

Enhanced Finance Dashboard

The finance dashboard, from the blog series, now provides a summary profit & loss statement that you can drill down into line level detail. Additionally, you see total sales by inventory class and customer class and a weekly moving average.

Enhanced Finance Dashboard

Enhanced Sales Dashboard

The sales summary dashboard provides an analysis of sales by inventory item class, customer class and weekly moving averages for total sales dollars and transactions. The report also includes a cumulative sales and cost comparison.

Enhanced Sales

Product Performance and Inventory Reorder Dashboard

In the product performance dashboard, I’ve added to compare total profits and profit margin, cost by warehouse and ABC segmentation analysis. Additionally, there is a weekly moving average cost comparison chart at the bottom of the report.

Enhanced Product Performance

For my inventory reorder analysis report, I’m using some discussed by Belinda Allen in her, Inventory Item Reorder Dashboard. I converted it to Power BI to help your procurement manager evaluate what’s on hand, allocated to open orders and items sold within a given time period.

Enhanced Inventory Dashboard

Future Developments

Returns are generally thought of a loses and return percentages can be dependent both the type of product and the company’s returns policy. While the average industry rate is four percent, consumer durable goods can range from two to 10 percent and apparel can be in excess of 20 percent. There are several reasons for merchandise returns and tracking the costs and reasons associated with them can increase revenues, lower costs, improved profitability and enhanced levels of customer service. Using Dynamics GP’s RMA module can help and analyze that data in Power BI is one of the things that I’m currently working on. Below is a screenshot of that analysis and a subject of a future blog post.

Sales Returns Dashboard

Ready to Do Even More with Dynamics and Power BI?

Stay tuned for more help in lead your organization into becoming data-driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP journey.

Advertisements

SSIS and PowerShell – A Powerful Combination

PowerShell is a powerful task automation tool from Microsoft. Although SSIS does not provide something like Execute PowerShell Script Task out of the box, it does have an Execute Process Task which can be used to run PowerShell scripts just as easily.

On a recent project, I was tasked with downloading and updating Federal Election Commission, FEC.GOV, Committee and Candidate Election data into Dynamics GP vendor maintenance and eOne Solutions Extender tables. To accomplish this, I created a PowerShell script to download the zip files for the FEC.GOV web site, added this script inside a SSIS package Execute Script task and then added additional data flow tasks to un-zip the files and import into custom SQL tables for use with a SmartConnect integration into Dynamics GP. The final SSIS package looked like the screen shot below.

SSIS Package Control Flow

Here are the detailed steps to create this SSIS package:

1.  Create the two custom tables

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FEC_Candidate](

[Candidate_ID] [varchar](9) NOT NULL,

[Candidate_Name] [varchar](200) NULL,

[Party_Affiliation] [varchar](3) NULL,

[Year_of_Election] [numeric](4, 0) NULL,

[Candidate_State] [varchar](2) NULL,

[Candidate_Office] [varchar](1) NULL,

[Candidate_District] [varchar](2) NULL,

[Incumbent_Challenger_Status] [varchar](1) NULL,

[Candidate_Status] [varchar](1) NULL,

[Principal_Campaign_Committee] [varchar](9) NULL,

[Street1] [varchar](35) NULL,

[Street2] [varchar](35) NULL,

[City] [varchar](35) NULL,

[State] [varchar](2) NULL,

[Zip] [varchar](9) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

CREATE TABLE [dbo].[FEC_Committee](

[Committee_ID] [varchar](50) NOT NULL,

[Committee_Name] [varchar](200) NULL,

[Treasurer_Name] [varchar](100) NULL,

[Street1] [varchar](50) NULL,

[Street2] [varchar](50) NULL,

[City] [varchar](50) NULL,

[State] [varchar](50) NULL,

[Zip] [varchar](50) NULL,

[Committee_Designation] [varchar](50) NULL,

[Committee_Type] [varchar](50) NULL,

[Committee_Party] [varchar](50) NULL,

[Filing_Frequency] [varchar](50) NULL,

[Interest_Grp_Category] [varchar](50) NULL,

[Connected_Org_Name] [varchar](200) NULL,

[Candidate_ID] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

2.  Delete Extracted FEC Files – Delete the previous downloaded zip files

a.  Add Script Task to the Control Flow

b.  Add C# script to delete previous extracted files – Add the Script Task to the Control Flow and give it a name “Delete Extracted FEC Files”. Then edit it by choosing the Script Language (C# or VB.NET). I used C# for my solution.

Script Task Editor

C# delete files script

public void Main()

{

string directoryPath = @”D:\KTL\FEC_Downloads\Extract”;

string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, “*.txt”);

foreach (string currFile in oldFiles)

{

FileInfo currFileInfo = new FileInfo(currFile);

currFileInfo.Delete();

}

// TODO: Add your code here

Dts.TaskResult = (int)ScriptResults.Success;

}

3.  Truncate FEC tables – Truncate the custom tables from the previous download

4.  Download FEC files from Website – Attached the below PowerShell script to this data flow task to get the updated FEC data

a.  Create PowerShell script and save as a .ps1 file

Invoke-WebRequest ftp://ftp.fec.gov/FEC/2018/cm18.zip -OutFile D:\KTL\FEC_Downloads\Archive\Committee.zip

Invoke-WebRequest ftp://ftp.fec.gov/FEC/2018/cn18.zip -OutFile D:\KTL\FEC_Downloads\Archive\Candidate.zip

Invoke-WebRequest ftp://ftp.fec.gov/FEC/2018/ccl18.zip -OutFile D:\KTL\FEC_Downloads\Archive\Link.zip

b.  Add PowerShell.ps1 to SSIS Execute Process task – Add the SSIS Execute Process task to the Control Flow and add PowerShell.exe to the Executable field and –F D:\KTL\FEC_Downloads\FEC_Download.ps1 to the Arguments field. The Arguments will need to change based on your system file location and name.

Execute Process for PowerShell script

5.  Foreach loop to Extract the zip files

a.  Add a Variable to the SSIS package – Add a variable to the package named “ZipFullPath”

SSIS Package variable

b.  Add a Foreach Loop to the Control Flow and then add then the following to the Collection and Variable Mappings within the Foreach Loop Editor

Foreach Loop folder location

Foreach Loop variable

6.   Add a Script Task inside the Foreach Loop

a.  Inside the Foreach Loop add the Script Task and open the Editor. Use C# with the ReadOnlyVariables shown below.

C# Script Editor

b.  Click on the Edit Script and add the following script

public void Main()

{

string zipfullpath=Dts.Variables[“User::ZipFullPath”].Value.ToString();

string inputfolder=Dts.Variables[“$Package::InputFolder”].Value.ToString();

using (ZipArchive arch=ZipFile.OpenRead(zipfullpath))

{

foreach(ZipArchiveEntry entry in arch.Entries)

{

entry.ExtractToFile(Path.Combine(inputfolder,entry.FullName));

}

}

File.Delete(zipfullpath);

// TODO: Add your code here

Dts.TaskResult = (int)ScriptResults.Success;

}

The final script should look like this:

C# zip extract script

7. Add a Data Flow task to the Control Flow – Add a Data flow task and two data flow from the Committee text file and one for the Candidate text file into each of the custom tables created in the first step.

Data Flow screen shot

Conclusion

Hope this helps show you some of the possibilities that you can create by combining PowerShell with SSIS.

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.