Mr Price (MRP) is a publicly-traded retail company based in South Africa focused on apparel, homeware and sportswear. They operate in 11 countries, mainly in Africa, with over 13,000 stores and 19,000 employees. MRP’s analytics team is using Alteryx to move and manage data to their Snowflake database and to model it in the cloud to build self-service reporting using Tableau. This use case shows the steps to migrate billions of records into their cloud environment, demonstrates how a team with no coding experience formed a new enterprise merchant analytics reporting process, and helped accelerate the overall scale of MRP’s analytics. With Alteryx, the MRP analytics team was able to get back 500 hours per week in time spent trying to accomplish the data related tasks without Alteryx.
Describe the business challenge or problem you needed to solve
MRP has been rapidly growing in recent years and their reporting needs have become overwhelming for its analytics and reporting team to keep up with. For example, in a single day, MRP has opened 18 stores across the globe, thus significantly changing reporting criteria and creating more sources of data. The need for business insight became greater over time and the existing company infrastructure around analytics couldn’t keep pace.
Originally all MRP’s work was done in Excel, with varying data sources aggregated through differing data hierarchies and segmentations thus making analyses much harder to accurately accomplish. MRP was struggling to grasp a deeper understanding of the real reasons behind their merchandise performance. They had plenty of clean data in their Snowflake cloud, but access had been limited to IT and small analytics group. So, they decided to acquire Alteryx to better comprehend the drivers behind sales, product, and store related performance.
Describe your working solution
Using Alteryx, MRP implemented a data migration layer to connect all of their different data sources. That included all of MRP’s transaction, promotional, warehouse management, logistics, social media and weather data. Alteryx orchestrated multiple functions, including replicating their databases, moving and extracting data through XML or JSON, and even using object storage to do event streaming through Apache Kafka. All of this data eventually lands in Snowflake and goes back to Alteryx for the team to build out enterprise models and reports, and it is then published through Alteryx Gallery and deployed to Tableau.
All of their source architecture is on-premise and with Alteryx, they created jobs to read data off each of those sources. They query the most recent data set, or replicate the entire database and then push it into the Microsoft Azure cloud with Alteryx. They model and stage that data and create what MRP calls a ‘Power-User Schema’ which is a curated layer that their analysts can consume either via Tableau or Alteryx Server. In order to carry out the cloud migration, data is read off an existing table, packaged into a flattened file, best practices are applied, and each file is then indexed; at which point that data then becomes a part of a data lake. The data lake holds every load of data that's ever been stored which allows for audibility.
The analytics team then uses Alteryx to script out and segment information from the data lake. The data is then compressed and the team uses an Azure copy command line to develop the parameters needed with a few dynamic variables. This pushes the file up to the cloud, at which point they’d spot check the data once more to verify it’s integrity. The command line utility allows them to execute jobs in the cloud, to load data and move it between the different schemas, remove duplicates, etc. With Alteryx they are able to package up all these complexities into one macro and as an MRP user, they can take any data they desire, use this macro, specify the desired table, enter your credentials and run the workflow. This allows users at MRP to push whatever data they desire up to the cloud with little effort.
Once the team at MRP had found the root cause of their data inconsistencies in comparison to the subsidiary organizations data, they began to format similar data points, de-dedupe records and align their data hierarchy. Once the data was vetted and they standardized their reporting process, Alteryx Server became available. With Server, the analytics team was able to run workflows within several different directories at any given time, and they were able to still prioritize the on-premise jobs to finish first. Server would pick up the data and push it into Snowflake and the analytics team could then run the rest of the modeling within Snowflake. Once data is loaded to Snowflake, workflows are then executed against Snowflake from Alteryx server to create the data models which are consumed by business via Tableau. Once the workflows, tables and views are created, they are then pushed to MRP’s Tableau server as a hyper extract file ease of use.
Describe the benefits you have achieved
Alteryx instilled a greater sense of confidence in MRP’s users and their ability to solve analytic capabilities. Alteryx has empowered them even without having had any prior training with BI tools or coding knowledge. On average, each user at MRP saves a total of 16 hours per week, essentially two full workdays they can dedicate to other deliverables. With roughly 35 users that equates to 500 working hours saved per week.
ALTERYX | SNOWFLAKE
Historical Size Curve Analysis
5 Hours. Need SQL Knowledge
Forward Orders at SKU level
Analyse Item performance by Store
Total Time Saved Per Week
Before Alteryx, the team used to be up every morning hours before leadership got to the office, to ensure they ran all the company’s workflows and completed all the modeling ahead of time. With Alteryx Designer, they no longer have to sacrifice that time manually prepping reports and models. Their process turned into simply sending an email or receiving one on their phones confirming the assigned task is complete. They can now navigate MRP’s different subsidiaries’ data much faster and effectively within the cloud. They can now move data directly to the cloud faster, and ultimately move data anywhere they desire with little to no manual intervention.
Some of the other workflow features the MRP users found most helpful included the In-Database tools. For example, their sale-line table contains every single transaction, in every record, roughly 1.6 billion records, a very large table by their standards. They're able to take that large table and transform that data into anything else they need and can now be used for analysis all within their Snowflake cloud. And with the In-DB tools, MRP no longer needs to download and try merging data multiple times, they even referred to them as “life-changing”.
The entire PowerPoint presentation can be found here.