Switzerland’s largest health insurance provider Helsana serves one out of every four insured, which translates into 1.9 million customers and 6.4 billion Swiss francs in sales annually.

In the past, innovation had been prevented by reliance on hand-coding, manual processes, and institutional knowledge held by individuals, said Gallus Messmer, data warehouse architect at Helsana — so they set out to find a way to modernize.  

Helsana’s BI & data warehouse team consists of about 55 people responsible for infrastructure, ETL jobs, BI reports, mathematical models and warehouse management. Three years ago, that team was split into eight groups and distributed across the company, making collaboration a challenge.

“We depended heavily on individuals. If they were on holiday or out sick, the whole process stopped,” said Messmer. In addition, Helsana’s 500 self-service business intelligence users were essentially ungoverned, purchasing tools and systems to meet the needs of their own silos, leaving the BI & data warehouse  team responsible for keeping up.

Problem: Updates Drag On

Scheduled updates and releases for Helsana’s two data warehouses required footing the bill for up to 15 consultants and took 30 to 40 person-days to complete. Locating source code was difficult, if not impossible, because their warehouses were not built in-house, and neither was fully automated.

One of Helsana’s core systems requires monthly releases, and every quarter, Messmer said, there is a larger release requiring changes to data structure and an additional 10 person-days to implement. Development work took 20 to 30 person-days. “We were just making sure our data warehouse kept track of changes in the core system. We couldn’t really do any innovation for our users,” he said.

Considerations

To address these challenges, Helsana identified the following desired outcomes:

• Automate deployment work
• Help the team organize code and deliverables better
• Move development work in-house
• Reduce ETL expenditure
• Reduce the number of tools used

Helsana’s many disparate technologies required the hire of outside experts for each tool. The warehouse team included just three skilled ETL developers, with the rest of the 55-person team skilled in Cognos or SQL without much background in ETL. The CFO was committed to retaining the existing staff, so it was essential to find tools that would work for users who were not skilled in ETL. Previous tools had to be developed by outside consultants, so they wanted a solution that could bring Helsana’s disparate processes together in a user-friendly package, making the most of their in-house team’s skills.

Selection Process: Narrowing the Field

Messmer put together a catalog of about 250 must-have features that they used to evaluate 70 products in the first round. After narrowing the field down to 20, discussions with each vendor led to a short list of three for proof of concept.

Proof of Concept: WhereScape Accelerated Past the Competition

WhereScape sent a solutions architect to show Helsana the effectiveness of WhereScape 3D and WhereScape RED using Helsana’s own data. “WhereScape was with us for three days, and rather quickly they were able to produce some of our code,” he said.

According to its website, WhereScape 3D enables business users to confirm their requirements using visual prototypes, avoiding misunderstandings and shortening development cycles from months to days. WhereScape RED then writes the necessary native code to create the model’s physical form, bypassing potential for human error in the coding process.

Based on the metadata repository, dependencies are visible from the beginning to the end because WhereScape is managing the entire code, Messmer said, “So we can track forward, backwards, and do impact analysis, even throughout quarterly structure changes, which we could never do before.”

Even with different languages in place, everything still resides in the repository, and the logic is in WhereScape, he said. “When we do a deployment, every piece of logic is marked as belonging to this package. We don’t lose any scripts because everything just stays in the repository.” During the proof of concept, they were able to load more than 3000 tables in two days of testing, and now know that, other than some tool-specific template changes, prepping the warehouse and loading data for future migrations will take 20 days at most, he said.

WhereScape

According to the company, unlike most ETL and ELT tools, WhereScape is metadata-driven, automatically producing and maintaining metadata to keep track of upstream and downstream dependencies of all objects in the data infrastructure. This means developers can create, manage, and document dependent objects knowing that they will automatically remain integrated and appropriately updated whenever there are changes to the underlying infrastructure that affects them.

By using metadata to synchronize and automate change management and documentation, WhereScape makes it much easier and faster for teams to respond to change. Whether evolving existing environments or migrating to new ones, metadata-backed automation gives developers a leg up compared to using previous ETL- or ELT-only methods. “When you develop in WhereScape, you don’t really write code in the target systems,” Messmer said. “You write it on the metadata, and then WhereScape translates it.”

• Results: Painless Governance with Templates

Helsana had a history of delays in production due to hand-coding by inexperienced developers, and consistent governance was impossible, so WhereScape created templates, or “design patterns,” with governance baked in. “We don’t have to talk about governance now, because we developed these templates, and the ETL developers just use them.” At first there was some resistance from developers because they felt the template governance controls limited their freedom to develop, but Messmer said the speed with which they were able to get up and running quickly outweighed the restrictions. “Now they just use the templates, and the governance gets applied automatically.”

• Results: Templates Empower Business Users

Templates include technical processes such as proper primary indexing, distributing data across nodes, and doing compression, so users doing the modeling and the ETL, “don’t really have to know about that as long as they have to answer a known type of question,” he said. When they come across a new situation for which there is no existing design pattern, they can rely on their core experts to create one.

• Results: Time-to-Value Improves

Helsana has seen game-changing reductions in time-to-value for scheduled updates, he said. Processes that once took 30 to 40 person-days now take one or two, meaning work is completed much faster, and big teams of external resources are no longer needed:

• Millions saved on external staff and resources
• Core system updates are down from 30 to 40 days to two
• Agile methodologies have been initiated
• Rapid prototyping allows close collaboration with business users
• Number of tools used has been reduced

Messmer said Helsana has been able to reduce the number of employees doing maintenance, bulk fixing, and upgrading, allowing them to focus more on delivering value to their business users. With their last project, they needed to hire 10 to 15 external staff. “With automation, we now have 90 percent of the project staffed internally.” They have also been able to move from using three different front-end tools to one.

• Results: Best Practices Attract Attention

Messmer is finding that the benefits of using Wherescape have engendered his team’s credibility throughout the company. Team members from the business side have asked to be part of the ETL team for a few months so  they can learn to write their own code and develop best practices for working with WhereScape’s features.

“The idea is that our internal people can really become the masters and drive the whole process. That wasn’t possible before WhereScape.”

• Next Steps: Nightly Builds

Messmer said that Helsana wants to build on improved turnaround times, using short iterations to quickly deliver new innovation. The BI team can now use Agile methodologies to add value to the business rather than just keeping up with it. “WhereScape makes us Agile. We have small monthly Scrum teams but the next step is to have nightly builds — that’s the target for our next sprint,” he said.

• Next Steps: Expansion of Data Labs

Helsana has what they call “data labs,” where business users work on datasets with custom coding. Messmer said that some of that code could be quite valuable if it was made available for use by other business people. He wants to increase the number of business users who are developing their own code, so that his team can act more like a data warehouse service provider.

“By giving them access to WhereScape, they can easily integrate their custom-developed code into the data warehouse, making it available for use by other business people.”

In the upper right corner of Helsana’s website is the phrase “Committed to Life.” Before WhereScape, Messmer’s team was barely able to keep up, requiring “life support” in the way of costly outside consultants. Now Helsana’s warehouses are able to stand on their own, keep up with the speed of life, and innovate as they move forward. “How much money do we save? It’s in the millions.”

Image used under license from Shutterstock.com