Past Analytics Excellence Awards

Author: Rana Dalbah, Director - Workforce Intelligence & Processes

Company: BAE Systems

Awards Category: Most Unexpected Insight - Best Use Case for Alteryx in Human Resources

Working in Human Resources, people do not expect us to be technology savvy, let alone become technology leaders and host a "Technology Day" to show HR and other functions the type of technology that we are leveraging and how it has allowed us, as a team, to become more efficient and scalable.

Within the Workforce Intelligence team, a team responsible for HR metrics and analytics, we have been able to leverage Alteryx in a way that has allowed us to become more scalable and not "live in the data", spending the majority of our time formatting, cleansing, and re-indexing. For example, Alteryx replaced both Microsoft T-SQL and coding in R for our HR Dashboard, which allowed us to decrease the pre-processing time of our HR Dashboard from 8-16 hours per month to less than 10 minutes per month, which does not account for the elimination of human intervention and error.

With the time savings due to Alteryx, it has allowed us to create custom metrics in the dashboard at a faster rate to meet customer demands. In addition, it has also given us the opportunity to pursue other aspects of Alteryx forecast modeling, statistical analysis, predictive analytics, etc. The fact that we are able to turn an HR Dashboard around from one week to two days has been a game changer.

The HR dashboard is considered to have relevant data that is constantly being used for our Quarterly Business Reviews and has attracted the attention of our CEO and the Senior Leadership. Another use that we have found for Alteryx is to create a workflow for our Affirmative Action data processing. Our Affirmative Action process has lacked consistency over the years and has changed hands countless times, with no one person owning it for more than a year. After seeing the capabilities for our HR Dashboard, we decided to leverage Alteryx to create a workflow for our Affirmative Action processing that took 40 hours of work down to 7 minutes with an additional hour that allows for source data recognition 

recognition and correction.  We not only have been able to cut down a two or three month process to a few minutes, but we also now have a documented workflow that lists all the rules and exceptions for our process and would only need to be tweaked slightly as requirements change.

For our first foray into predictive analytics, we ran a flight risk model on a certain critical population.  Before Alteryx, the team used SPSS and R for the statistical analysis and created a Microsoft Access database to combine and process at least 30 data files.  The team was able to run the process, with predictive measures, in about 6 months.  After the purchase of Alteryx, the workflow was later created and refined in Alteryx, and we were able to run a small flight risks analysis on another subset of our population that took about a month with better visualizations than what R had to offer.  By reducing the data wrangling time, we are able to create models in a more timely fashion and the results are still relevant.

The biggest benefit of these time-savings is that it has freed up our analytics personnel to focus less on “data chores” and more on developing deeper analytics and making analytics more relevant to our executive leadership and our organization as a whole.  We’ve already become more proactive and more strategic now that we aren’t focusing our time on the data prep.  The combination of Alteryx with Tableau is transformative for our HR, Compensation, EEO-1, and Affirmative Action analytics.  Now that we are no longer spending countless hours prepping data, we’re assisting other areas, including Benefits, Ethics, Safety and Health, Facilities, and even our Production Engineering teams with ad-hoc analytics processing.

Describe the problem you needed to solve 

A few years ago, HR metrics was a somewhat foreign concept for our Senior Leadership. We could barely get consensus on the definition of headcount and attrition.  But in order for HR to bring to the table what Finance and Business Development do: metrics, data, measurements, etc. we needed to find a way to start displaying relevant HR metrics that can steer Senior Leadership in the right direction when making decisions for the workforce.  So, even though we launched with an HR Dashboard in January of 2014, it was simple and met minimum requirements, but it was a start. We used Adobe, Apache code and SharePoint, along with data in excel files, to create simple metrics and visuals. In April 2015, we launched the HR Dashboard using Tableau with the help of a third party that used Microsoft SQL server to read the data and visualize it based on our requirements. However, this was not the best solution for us because we were not able to make dynamic changes to the dashboard in a fast timeframe. The dashboard was being released about two weeks after fiscal month end, which is an eternity in terms of relevance to our Senior Leadership.  

Once we had the talent in-house, we were able to leverage our technological expertise in Tableau and then, with the introduction of Alteryx, create our workflows that cut down a 2 week process into a few days - including data validation and dashboard distribution to the HR Business Partners and Senior Leadership.  But why stop there?  We viewed Alteryx as a way to help refine existing manual processes: marrying multiple excel files using vlookups, pivot tables, etc. that were not necessarily documented by the users and cut back on processing time. If we can build it once and spend minimal time maintaining the workflow, why not build it?  This way, all one has to do in the future is append or replace a file and hit the start button, and the output is created.  Easy peasy! That is when we decided we can leverage this tool for our compliance team and build out the Affirmative Action process, described above, along with the EE0-1 and Vets processing.

What took months and multiple resources now takes minutes and only one resource.

Describe the working solution

The majority of the data we are using comes from our HCM (Human Capital Management Database) in excel based files. In addition to the HCM files, we are also using files from our applicant tracking system (ATS), IMPACT Awards data, Benefit provider, 401K, Safety and Health data, and pension providers.

Anything that does not come out of our HCM system are coming from a third party vendor. These files are used specifically for our HR dashboard, Affirmative Action Plan workflow, Safety & Health Dashboard, and our benefits dashboard.

In addition to dashboards, we have been able to leverage the mentioned files along with survey data and macro-economic factors for our flight risk model. We have also leveraged Google map data to calculate the commute time from an employee's home zip code to their work location zip code. This was a more accurate measurement of time spent on the road to and from work when compared to distance.

The ultimate outputs vary: an HR dashboard that tracks metrics such as demographics, headcount, attrition, employee churn/movement, rewards and exit surveys is published as a Tableau workbook. The Flight Risk analysis that allows us to determine what factors most contribute to certain populations leaving the company; a compensation dashboard that provided executives a quick way to do merit and Incentive Compensation planning includes base pay, pay ratios, etc. is also published as a Tableau Workbook.

This workflow has as its input our employee roster file, which includes the employee’s work location and supervisor identifiers and work locations going up to their fourth level supervisor.  For the first step of processing, we used stacked-joins to establish employee’s supervisor hierarchies up to the 8^th level supervisor.  We then needed to assign initial “starting location” for an employee based on the location type.  That meant “rolling up” the employee’s location until we hit an actual company, not client, site.  We did this because Affirmative Action reporting requires using actual company sites.  The roll-up was accomplished using nested filters, which is easier to see, understand, modify, and track than a large ELSEIF function (important for team sharing). 

Once the initial location rollup was completed, we then needed to rollup employees until every employee was at a site with at least 40 employees.  While simply rolling all employees up at once would be quick, it would also result in fewer locations and many employees being rolled up too far from their current site which would undermine the validity and effectiveness of our Affirmative Action plan.  Instead, we used a slow-rolling aggregate sort technique, where lone employees are rolled up into groups of two, then groups of two are rolled up into larger groups, and so on until sites are determined with a minimum of 40 employees (or whatever number is input).  The goal is to aggregate employees effectively, while minimizing the “distance” of the employee from their initial site.  This sorting was accomplished using custom-built macros with a group size control input that can be quickly changed by anyone using the workflow.

The end result was the roster of employees with the original data, with new fields identifying their roll-up location, and what level of roll-up from their initial location was needed.  A small offshoot of “error” population (usually due to missing or incorrect data) is put into a separate file for later iterative correction.

Previously, this process was done through trial and error via Access, and Excel.  That process, was not only much slower and more painstaking, but it also tended to result in larger “distances” of employees from initial sites then was necessary.  As a result, our new process is quicker, less error-prone, and arguably more defensible than its predecessor.

One of the Macros used in AAP:

Describe the benefits you have achieved

Alteryx has enabled our relatively small analytics shop (3 people) to build a powerful, flexible and scalable analytics infrastructure without working through our IT support.  We are independent and thus can reply to the user's custom requests in a timely fashion.  We are seen as agile and responsive - creating forecasting workflows in a few days to preview to our CEO and CHRO instead of creating Power Point slides to preview for them a concept.  This way, we can show them what we expect it to look like and how it will work and any feedback they give us, we can work at the same time to meet their requirements.  The possibilities of Alteryx, in our eyes, are endless and for a minimal investment, we are constantly "wowing" our customers with the service and products we are providing them.  In the end, we have been successful in showing that HR can leverage the latest technologies to become more responsive to business needs without the need for IT or developer involvement.

Author: Alberto Guisande (@Aguisande), Services Director

Awards Category: Most Unexpected Insight - Proving teachers wrong - Apples & Oranges can be compared! (thanks to Alteryx)

Describe the problem you needed to solve 

Our customer is a Public Transportation company, in charge of buses going around the city of Panama. They transport more than 500K passengers a day (1/6 of the total population of the country). Almost 400 routes, with 1,400 buses going around the city all days, working 24/7, reporting position every a few seconds. The company is supporting its operation with a variety of tools, but at the time to put all data together, they realized there was no "point of contact" in the data. They have to compare apples & oranges! Really? Why does the saying exist? Because you can't! So we started trying to do the impossible!

BTW, the business questions are pretty simple (once you got the data!): What route was every bus in, when every transaction occurred? What is the demand of every route? and for every stop?

Describe the working solution

Working with Alteryx, we were able to analyze data coming from three different sources, where the only common information was some LATITUDE & LONGITUDE (taken with different equipment, so the accuracy was, at least, questionable) at some random points in time. The data was received in several files:

• Routes: Contains the ID & the name of every route. Stop Points: Containing every bus stop, its LAT & LONG, and the stop name
• Pattern Detail: Containing every route, its stops and the sequence of those stops in a route
• Some remarks: A lot of stops are used by different routes, and there are some stops, where the bus pass through, that are not part of the specific route the bus is at

So far, the easy part! We managed very easily to get all this info together. Now the tricky part: There mainly two operational datasets: AVL (Every position of every bus, every n seconds, where n is an arbitrary number between 0 and what the piece of hardware wanted to use). BTW, a huge amount of data every day.

Transactions: transactions registered in time, in a bus. As you may infer, there are no data in common that allow us to match records beside an arbitrary range of latitude and longitude in some random time ranges. Because of how everything is reported, the bus may be passing in front a stop that is part of another route, or stopping far from the designated stop.

Describe the benefits you have achieved

With this solution, the company can start analyzing activity per route, demand per bus, route, stop, etc. Without Alteryx, this customer information still be looking like apples and oranges! We were able to make it sense and allow them to use it to get insights.

Colorful note(and some ego elevator) : 5 other vendors took the challenge. No other one could reach a glimpse of solution (of course, "no Alteryx, no gain").