CASE STUDY. |
DATA ECOSYSTEM AUDIT AND BEST PRACTICE RECOMMENDATION
PARKINSON’S UK
A UK charity that focuses on research and support for Parkinson’s disease. Funded by different constituents including individuals and organisations, their primary purpose is to support those affected by Parkinson’s. Their main goal is to find a cure for Parkinson’s, and they intend to do this by supporting research.
THE CHALLENGE. |
The core aim was to align a data strategy and recommend best practices around improvements in tool usage while upskilling the in house team in these technologies.
To combat rising costs with their current data solution, the client needed an audit of their data ecosystem. The critical area of focus was Snowflake, Matillion, Raiser’s Edge and Tableau. We also recommended the creation of a single version of truth for constituents who have multiple points of interaction with the organisation.
The technologies being evaluated did not have defined success criteria or a holistic view, and there was a need to define how the technology would be measured as a true fit for the business.
There was also a lack of expertise in these technologies within the business, as well as a lack of governance around the usage of tools. Particular areas of inefficiency were highlighted resulting in rising costs for the data estate.
THE SOLUTION. |
Over 14 days, BI:PROCSI undertook a rapid, complete data discovery and engineering engagement to provide the client with a set of recommendations to enable them to alleviate the issues by fully understanding them and effectively decrease the data usage costs.
The first step in the solution process was to identify the main offending queries and use these as benchmarks in a BI:PROCSI led project with a focus on tooling evaluation and data migration.
Parallel to this, BI:PROCSI ran a full and detailed Tableau audit at every level from users to content and security to capture the inefficiencies in platform usage.
The BI & reporting estate was scrutinised and evaluated to identify offending design flaws and improvement opportunities.
Additionally, the BI:PROCSI developed a core data migration plan to move data from Raiser’s Edge, a legacy platform into the client’s newly purchased Snowflake instance.
This approach was to remove duplication of efforts, ensure a single version of the truth and increase ROI on the technology stack. BI:PROCSI developed a single version of truth for constituents across multiple touchpoints post-migration of the Raiser’s Edge data feeds.
The final element for the BI:PROCSI engagement was a full GDPR and PII audit to identify gaps, ensure compliance and if necessary, suggest the best way forward.
THE RESULT. |
BI:PROCSI demonstrated that Matillion usage was not optimised in the most efficient way and the Matillion database had the highest unnecessary cost by a large margin.
Matillion was generating a high volume of queries in Snowflake hitting an average of 100k queries per month and costing an unnecessarily high portion of Snowflake credits, Snowflake’s cost metric. Certain jobs within the Matillion platform had been set up incorrectly and performing menial tasks with no business benefit while impacting resources and cost.
BI:PROCSI highlighted that an API component was deprecated and recommended audit, documentation and recreation of relevant components. Additionally, BI:PROCSI also discovered that status log components were being used inefficiently creating a large volume of interactions and unnecessarily using resources and dramatically increasing technical costs.
Raiser’s Edge, a legacy system, was being used for storing constituent details and their interactions with the organisation and the BIPROCSI-led Data migration plan was implemented removing the need for legacy systems.
Matillion orchestration and transformation jobs were created and the core datasets were migrated from Raiser’s Edge to Snowflake. This was a huge win in terms of showcasing best practice when using Matillion and also the migration of key data sources which enabled movement from a legacy platform, reducing technical debt and cognitive overhead.
VALUE ADD. |
The primary value-add in this engagement was the creation of a single view of truth for constituents. Prior to its implementation, the organisation had to look up various sources of data in order to identify whether a constituent was active in the last 24 months. BI:PROCSI developed and implemented a transformation job in Snowflake, which combined and aggregated data from multiple sources – to create a single view.
This highlighted the active constituents across various touchpoints and helped understand the first point of initiation of a customer’s interaction with the organisation. This was a primary goal for the client in house teams for a substantial amount of time, BI:PROCSI was able to plan, develop and deliver this solution alongside the other workstreams in a matter of weeks.
Alongside this, it was highlighted that internal teams required training and enablement in the new technologies to understand best practices and how to create valuable solutions from these newly purchased technologies. BI:PROCSI planned and led several hands-on training workshops with the client in Tableau, Matillion and Snowflake to upskill the existing team with a focus on using their data in a practical setting. Q and A sessions were held to allow the team to ask questions and highlight problems they had been trying to solve in their day-to-day.
BI:PROCSI has always had a strong focus on training and enablement of our client’s teams and believe this is the foundation for success for any business. By helping the clients team understand the tips, tricks and common pitfalls when using these technologies and processes, we can ensure continued success for future in house projects.
NEXT STEPS. |
One of the leading factors in unnecessary technical costs was incorrectly configured/redundant Matillion jobs.
With this in mind, the first recommendation was to create a monthly task audit dashboard to monitor jobs created and their impact on the Snowflake warehouse using task history data. Integration of Matillion with GIT for user and version control was the next step. Additionally, best practices for standardisation of jobs, scheduling and debugging was also detailed out alongside full training via a hands-on workshop for Matillion.
The next focus area was Snowflake. Recommendations included best practices for suspension, concurrency, micro-partitioning and role usages. A snowflake usage dashboard was built in Tableau to monitor usage and credits and forecast future requirements and flag any problem queries.
The next focus area, Tableau, included suggestions and recommendations for password embedding in connection, usage of extracts instead of live feeds and the usage of published data sources to increase performance and reduce costs.
The recommendations also included the best way forward on the usages of the single view of constituents built by the BI:PROCSI team. Regular refreshes of the single view were set up and we recommended best practices provided along with usage in conjunction with additional data sources to build Tableau dashboards to gain insights on constituent interaction.
GDPR recommendations included best practices on passwords, cleanup of user data on employees leaving the organisation and more where documented and presented to the senior data team. Best practices on how best to handle PII data were provided to the leadership and data teams.
BI:PROCSI continues to support Parkinson’s UK and work with them to achieve their data goals and get the greatest value from their data and technology stack.
Our aim has always been to be a strategic partner to our customers, to support and guide along every step of their data journey, and we are proud to be supporting the Parkinson’s UK team.
