LAS VEGAS–A credit union that sought to build out its own analytics solution and data warehouse only to scrap it after investing significant dollars and time and then starting over from scratch, shared some valuable insights on the process and lessons it learned in getting to the robust functionality it now has in place.
Tom Wilson, SVP-enterprise data and analytics with the $2-billion One AZ Credit Union in Phoenix, shared with CUNA’s America’s Credit Union’s Conference here his CU’s path—including the potholes—to building its analytics solution.
Quoting the credit union’s CEO, Dave Doss, Wilson said the mission at One AZ is to “ensure the member experience is easy, enjoyable and effective.”
To help members through their journey, the steps of which it defines as Discover, Consider, Purchase, Onboard, Use and Engage, the credit union was looking to create a 360-degree view of the members. To get there wasn’t easy, he said.
The first challenge the credit union faced was that it was running on an account-based core system that didn’t really provide a 360-degree view. One AZ CU needed a complex nightly data transformation in a 360-degree view and needed it uploaded into CRM before 7:30 a.m. “Otherwise, it’s of no use to the front-line,” said Wilson.
To achieve that, One AZ CU’s 2015-2016 initiatives included:
- Implement Microsoft Dynamics CRM to support 360 views
- Implement Electronic Data Warehouse (EDW) and data to support CRM
- Enhance Business Intelligence via EDW
Wilson said the initial plan with the EDW strategy involved reengineering its data delivery model to share across the company. It was also seeking to:
- Create a relational EDW, then integration
- Have a project team driven by IT involvement from all businesses
- To launch two associated projects: Implementation for EDW and CRM
- Build reliance on custom SQL Server Integration Services (SSIS) code to integrate five source systems
- Put in place numerous, complex data rules, data staging and DataMarts
- Outsource implementation with a project launch in August of 2015
That custom coding turned out to be a mistake, shared Wilson. The bad news: By December, the EDW was not working, and in July of 2016 the credit union cancelled the entire project (and eating the cost) after determining it to be unacceptable.
One AZ CU’s experience isn’t unique, said Wilson, noting that 50% of data warehouse projects fail.
“Think about the biggest library you’ve ever been in, where the only way that library is functional is if everything is categorized and you are able to go in and find what you need right away, nicely and neatly organized,” said Wilson. “That’s what you have to do with a relational database. You have to think about everything you want in that thing, and how to organize it. There is a lot that goes into this thing. Part of our mistake was we made it too large to begin with, and we realized we were never going to get to the CRM integration. The other challenge with this is they are difficult to maintain.”
The Myths of Traditional EDW
While some may consider his list “controversial,” Wilson said from his view some of the myths around EDW include:
- Data sources, fields and requirements don’t change
- The stakeholders know what they need today
- Someone in the company understands all EDW requirements (with emphasis on the “one”)
- Various data sources play well together (easy Extract, Transform and Load process)
- Mindset that one size fits all data consumers and analytics
- Nirvana when finished and there is a single version of the truth
Why do traditional EDW projects fail? According to Wilson:
- Overly complex relational database design, hierarchy structure, and connectivity plan
- Export Transform Load (ETL) models and processes have limited agility once developed
- Code-based ETL, such as SSIC, often result in creating a black box
- Inability to internally maintain as a credit union
- Doesn’t adapt well to changing data volume, velocity and variability
- Analyst and data users continue to circumvent EDW and IT controls to meet their needs.
- Insisting on moving forward no matter how bad it’s going!
Another big issue EDW projects all face, said Wilson, is the increasing amounts of data coming from sources that aren’t yet known at the time of design.
Wilson said a good model for credit unions to follow in establishing a data analytics function or EDW is the Gartner Guide for Self Service Data Preparation. That guide, he said, recommends:
- Develop a deployment strategy for self-service data preparation
- Consider creating a new role: Business Data Engineer
- Establish guidelines and processes for business users
- Create a process reusing models developed by business users, including data integration, extraction, transformation, loading solutions for all requirements
- Leverage MPP and Hadoop (created by Yahoo and used by One AZ) wherever possible
Wilson said he created a list of 14 “must-have” requirements he wanted in an Analytical Data Utility that led to a lot of vendors saying they couldn’t do it. “I wanted a system that the business people could use. A lot of these are built for IT,” he said.
One critical factor Wilson said he was seeking was the Analytical Data Utility needed to have intuitive data cataloging, that preferably would be automated.
One AZ Credit Union chose Hadoop because it is agile and agnostic for a wide variety of data sources, types and formats, said Wilson. He noted it collects all data regardless of quality, and then repairs, enriches and joins it. The solution also requires a minimum of custom coding, and does not require a traditional data model, he said, adding that is further offers rapid, ad hoc and scalable data modeling.
In One AZ Credit Union’s case, after scrapping its initial project, it moved to what Wilson called “Plan B.” The steps it when through there included:
- Start fresh and scrap everything. It created a new ADU business unit tasked solely with project success
- Adopt an Enterprise Data Lake and Self-Service data prep strategy
- Procure and rely on Hadoop ETL solution and NO custom code
- Leverage MSFT Azure Hadoop environment (HD Insight)
- Reliance on software solution to feed EDL and integrate five source systems
- Create 360-degree view within ADU and pass CRUM SQL tables
Seven weeks after moving to Plan B, its Enterprise Data Lake was operations, and four months later, said Wilson, the project has been a complete success. One AZ CU now does nightly processing that requires two-to-three hours to complete, and it believes it’s going to get that number under one hour soon.