Several months ago, I joined a Protiviti team working on a client’s robotic process automation (RPA) project. As the team was getting assembled, we shared a sense of enthusiasm and curiosity to review the requirements and begin devising a solution strategy. With so many ways to apply RPA to business processes, it felt like getting a white elephant gift at the holidays, anticipating what kind of surprise would be awaiting as the contents were revealed. As it turns out, we were not the least bit disappointed.
The team was assigned to automate the user access review process – a process with which Protiviti has considerable experience. This project was an extension of a proof of concept (POC) to use RPA to collect user data from an assortment of online and cloud applications and validate permissions for each system that a user accessed. Armed with Protiviti’s process knowledge and the proof of concept at our fingertips, we were ready to dig in and provide a stable, effective solution for the client. It is important to note that the user access review process is just one of a myriad of client processes that may require data consolidation from multiple systems. RPA is an extremely effective method to effectively manage data and automate these processes.
In general, RPA is an ideal solution for extracting various types of data such as user account information, vendor details, bank details, personnel data, customer information, etc., across different systems and platforms for a variety of purposes. In this case, the client data sources ranged from spreadsheets that would be emailed to a specific account and then consumed by the RPA tool, to websites that RPA would scrape, to actual online databases where the data would be directly extracted via a predefined query. Each data set presented different field formats, data types and naming conventions. From the onset, it was easy to see how this project could rapidly turn into a quagmire, producing a solution that would be difficult to develop, modify and manage on all fronts. Considering this, Protiviti developed an approach to resolve these clear and significant obstacles.
Since we were working with large sets of sensitive data, we soon realized we would need to modify the approach used in the POC and incorporate a SQL server database to more effectively store, manipulate and secure the data in question. What was originally identified as a strict RPA project was quickly becoming a hybrid solution, leveraging Protiviti’s wide-ranging talent pool.
To make this a more manageable endeavor, we had to first standardize the way the data would be extracted, formatted and stored. To achieve this end, it was determined a database was the best option. Also, instead of the original approach — having to build an automation for each application in the access review process — we developed an RPA template to be lightly modified and used to load all application data into a common repository. At this point, we combined our RPA solution with a database solution to provide the most efficient way of reaching our goal. We extracted data from each application in its native format and loaded it into the database, where various transformations took place to present it all in a common format.
To complicate matters further, the project also required several actions that were not available in the out-of-the-box RPA toolset. For example, we did not have the ability to reference specific tabs in an Excel document by name to retrieve the associated user data. So, the RPA built-in activity required that Excel be installed on the machine in which the automation was being run to perform that action. To resolve this, we built a custom activity to fulfill that action. By combining our knowledge of software development with the online documentation for building custom components, we were able to leverage Visual Studio and the .NET framework to construct several activities that did not previously exist but were essential to our strategy. The three main capabilities that we accomplished with this custom approach were:
- Determining the name of an Excel tab by passing in a tab index
- Connecting to a Redshift database to extract user data
- Performing a bulk insert of data into a database instead of having to use the time-consuming record-by-record load.
After very careful consideration regarding how we would design this solution, we determined that RPA was not going to be the sole answer to our problem, but an important piece of the puzzle. We also learned that understanding multiple technologies, and how to best integrate them, provided a greater opportunity for Protiviti to deliver the most effective solution to the client.
By using this multi-technology approach, we were able to provide greater delivery in a shorter amount of time, more secure storage of and easier access to the data, and the ability to easily scale the solution for future engagements with the client.
This project was a perfect example of how companies with a forward-looking vision can leverage their partnership with an experienced partner like Protiviti to benefit from cutting edge technologies such as RPA to identify and creatively pursue maximum efficiency and productivity.
Sometimes the white elephant gift turns out to be much more than you could have hoped for.
To learn more about how Protiviti can help automate your data transformation processes, contact us.
