Manager (Institutional Intelligence) & Senior Institutional Research Analyst
The Hong Kong Polytechnic University
Overview of Use Case
Every organisation wants to be smarter with their data, and universities are no exception. Many universities and colleges are missing out on the competitive advantage that their data has the potential to deliver, held back by manual, slow and error-prone processes. Anson Wun and Amanda Li are research analysts working in the Institutional Research and Planning Office (IRPO), The Hong Kong Polytechnic University (HKPolyU). Although none of the team members have a computer science background, they are working with data day in and day out. The team used to be handling data with Access and Excel, and that poses a lot of challenges. But with Alteryx, they can now manage the data better - from data cleansing to performing advanced data analysis that wouldn’t have been possible using manual techniques e.g.: RDBMS or SQL.
Describe the business challenge or problem you needed to solve
IRPO's main tasks include routines like strategic planning, statistical reporting, and KPIs and other more ad-hoc, dynamic analyses such as research analytics and rankings analyses. The ultimate objective, however, is always to provide the right insights to aid decision making.
Most of the higher educational institutions in Hong Kong are publicly funded. Although student enrolment may not be as competitive as institutions in other countries, accurate student number projection is critical for internal planning i.e. space allocation, programmes design, hiring staff, IT infrastructure and budgeting.
Previously, calculations were strictly driven by Access and Excel to perform the student number projections with data points such as; demographic data, exam results, and subject registration records, etc. Every year a staff member will extract main data from over 300 queries in Access, combine them with data from other sources, then import the interim data into Excel. From there, the data will be manipulated strictly by Excel formula. As a result, the data that were exported into Excel from Access were rather laborious to understand; colours, highlights, nested formula and so on. As data size grows bigger over time and computation criteria get complicated, they discovered this method is error-prone and difficult to validate and audit. Mistakes could be from the source data, or from typos in Excel. They had to find and update the queries one by one to fix the errors.
This way of projecting student numbers is very primitive and may not be able to reflect today’s environment. Another challenge is that the knowledge of the whole process resides in one or two of their colleagues, so it’s difficult to validate and improve upon. Moreover, Access Database requires knowledge in SQL and RDBMS which the team does not have the background of. It was also not traceable, so if any of the staff resign, the team will have to start all over.
They used to spend at least 1 month to do the data preparation alone.
Describe your working solution
There are a few motivations that allowed them to change the old way of working:
Seeking for a more robust projection model rather than using only historic-based across all programmes
They need a more accurate projection on the student number
Something that is replicable and traceable is needed
Something that they can develop themselves
From the above concerns, they started to look for a better solution. Then, they found Alteryx.
The Alteryx workflow helps them to map out the entire thought process. It is reliable, replicable and traceable. Alteryx encapsulates operations into comprehensible units: loading data, mapping / transforming & parameterization, doing modeling, and creating the output. It also allows them to do validation anywhere and, in any way. They are also able to apply different statistical models for different data nature / scenarios very easily. This specific Alteryx workflow is very fast and scalable – each run only takes about 2 minutes to complete.
With the new workflow in place, they are now in a much better position to experiment with their projection parameters. They also use the predictive tools in Alteryx to come up with the best statistical model to project their data. Previously with the complexity of the Excel file, they could only do projection at the programme (i.e. study programme) level. With this new workflow, they can take it deeper to the student level and can take in a lot more factors into their model.
Describe the benefits you have achieved
Excel used to be their main tool, but has since then become secondary. The time that they used to change formulae in Excel before can now be much better utilized in more meaningful things – like playing around with the data models to improve their projection. And the workflow spoils them with the luxury of making mistakes. Previously with Excel, they had to be extremely careful because errors are hard to trace (due to the sheer volume of rows, columns, and formulae), and any mistake would mean wasted work. With Alteryx it’s just as easy to detect anomaly as it is to rectify a misconfigured tool. They are now much more confident with their results and can afford to explore the data without worrying about wasting each other’s work. And they can finally go home earlier, thanks to Alteryx