Commissions Process Automation with SQL Server, .NET, and Tableau

CHALLENGE

American Marketing & Publishing is a marketing and advertising agency based out of Illinois. The mid-sized business was looking to create an automated way to calculate its monthly and bi-weekly commission process according to a custom pay plan and bonuses administered to the AMP sales team. This legacy process was being done manually via Excel.

SOLUTION

HGS delivered a custom .NET based data management web application, a Tableau based reporting solution, new SQL Server customizations in a new reporting database, Tableau administration, including scheduling, row level based security, and AD group based security, and post-implementation on going customizations and support. The project was conducted in three phases, first, the SQL Server customizations, second, the Tableau development and administration, and third, the development of the custom web application to manage the data.

TECHNOLOGY

The Tableau based reporting solution consisted of the AMP hosted Azure SQL databases that feeds in data from the AMP SugarCRM. A separate reporting database instance was deployed on a server dedicated to the custom commissions process implemented by HGS.

The manual commissions process was re-created with SQL server stored procedures and scheduled to populate according to the AMP sales calendar when the commissions process needed to be run. The complicated process pulled different contract types, rep types, and reporting periods and aggregated and sorted the data accordingly for Tableau reporting.