Past Analytics Excellence Awards
Team Members: HeatherAnn Bromell, Amar Shabbir, Dusty Evely, Julio Ramirez
Company: Quantum Spatial, Inc.
Business Partner: Slalom Consulting, Matt Ewalt
Awards Category: Best Value Driven with Alteryx
Our ERP is great for a lot of things, but it’s not great for tracking historical data. The Enterprise Systems team was charged with creating a way to track project Estimates-at-Complete (EAC) on a week to week basis so that Finance could understand variances in EAC and know where to look if a project EAC varies greatly from its baseline EAC.
By automating a weekly pull of project data, not only were we able to greatly impact Finance’s ability to track EACs, but we were also able to create a suite of reports for PMs using Tableau so that they can more easily get in front of large variances in EAC and be proactive in reining in project costs. Being able to accurately track the progress of EAC over the life of projects both helps keep EACs in check, and provides a trail of breadcrumbs to follow in case a project does “go off the rails.”
Awards Category: From Zero to Hero
A year ago, QSI had no formal BI system in place and the Enterprise Systems team had never heard of Alteryx. Slalom Consulting was engaged for BI advice and they demonstrated how Alteryx could provide value both as a tool for everyday workflows that can be automated to stakeholders and also as a builder of BI resources itself.
We were able to start delivering value to the Finance team with smaller workflows almost immediately, but our EAC Snapshot database (described below) has been the “game-changer” that everyone hoped it would be when we had thought it up over a year ago.
Additionally, since our ERP and CRM don’t “talk to” each other, we’ve started to bridge gaps in the project life cycle data collection and created the ability to smoothly report on projects from opportunity stage to project closeout.
Describe the problem you needed to solve
For years, QSI has been hampered by the inability of our ERP to hold historical data as a “snapshot” of where projects were in their execution at a particular point in time. Project EACs (Estimates at Complete or EAC = Job to Date or JTD + Estimate to Complete or ETC) were visible, but only as of “right now.” Senior leadership wanted the Enterprise Systems team to figure out a way to show EAC variance over time – that is, did the project perform better or worse as time progressed?
We were contemplating ways to do this when were introduced to Alteryx by our BI consultant from Slalom Consulting. We immediately saw potential for this to be the answer to our historical data problems. Using Alteryx, we could create a workflow that would take a “snapshot” of all project data each week and use that to compare project data week to week.
Since setting up this workflow, stakeholders in Production as well as Finance and Data Acquisition have found immense value in the data being captured each week. In addition to this, pieces of the EAC Snapshot workflow have been used as the basis of a number of reports with completely separate final outputs.
Describe the working solution
For the EAC Snapshot (and most other workflows), QSI pulls data from the ERP SQL tables and transforms those data in a number of ways. Figuring out the SQL tables was half the battle with this workflow. Inputs include:
Baseline (all joined in the workflow down to the task and resource level)
- Consultants (all subcontractors being used on a project)
- Expenses (all expenses from those working on the project)
- Labor (all labor for a project)
- Units (all acquisition assets used on a project – aircraft and sensors)
Job to Date (all joined in the workflow down to the task and resource level)
- Consultants
- Expenses
- Labor
- Units
Estimate to Complete (all joined in the workflow down to the task and resource level)
- Consultants
- Expenses
- Labor
- Units
Along the way, qualitative data such as project manager, account manager, and subvertical are added in addition to data from several custom data keys that are stored in Google Sheets. Date filters are used to show the data “as of the end of the previous week” so that we can easily define fiscal periods and make sure all data are appropriately accounted for.
The workflow is run and the output table is written to our SQL server in early hours of Thursday mornings for two reasons: on Thursdays, we can be sure that all timesheets and units have been submitted for the previous week, and so as not to hit the database too hard during working hours.
Following the scheduled run of the workflow, a second workflow is run to retrieve the data from the EAC Snapshot database and convert it into a .tde so that Tableau can easily digest it. The EAC is then compared to the project baseline for variance in gross margin. There is also a trend version which shows changes week to week. Several other calculations give insight to how a project has progressed over time.
Describe the benefits you have achieved
For the EAC Snapshot (and most other workflows), QSI pulls data from the Deltek Vision SQL tables and transforms those data in a number of ways. Figuring out the SQL tables was half the battle with this workflow.
If the Enterprise Systems team were attempting to collect these data each week without Alteryx, it would take many, many hours per week. Maybe 40? Thank goodness we never had to try it. PMs save time because they no longer have to try to cobble together several reports to get some of the information they need. Also, they no longer have to try to root around in hard-to-read ERP reports to get to their bottom line.
Considering that none of us is a DBA, constructing this database without Alteryx would be scary at best and impossible at worst. The success of this endeavor has Enterprise Systems considering ways that Alteryx could help with client deliverables in our production environment.
