Amazon RDS Service Ready
Achieving simple, consistent data modelling with Amazon RDS

25th February 2020

CDL's insurance retail solution, Strata, is able to handle every aspect of our customers' core business.

Strata supports insurance providers to bring products to market using a multi-channel strategy via a single database. Its components enable the sale and administration of policies, including telematics-based insurance, via the web, price comparison sites, contact center, mobile and voice devices.

Used by some of the UK's largest personal lines providers, the system generates millions of transactions each day and these are spread throughout a highly relational data model.

We needed a way to bring this data together into a less complex and more consistent model that allowed us and our customers to develop powerful analytics capability.

Change Data Capture
News Story Image

With CDC, transactions are read from the source online transaction processing database and are sent via Qlik Replicate into our data platform which we run in AWS. The transactions are applied to an Amazon RDS (Relational Database Service) instance.

From here, we denormalise the transactions within Amazon RDS using materialized views. These are essentially a series of data marts focused around a particular business function, such as policy, call center or accounts.

By turning the transactions into these data marts, we reduce the number of database tables analytics staff need to be familiar with. We're able to reduce down from 700 to 100 tables.

We are also able to keep the data model consistent by hiding changes in the source model. For example, if a column is moved in the source table, we can make that change transparent to the analytics staff in our data marts.

The materialized views use fast refresh technology and we can achieve a latency as low as 1 minute from the original transaction taking place.

Finally, we open up the data to analytics with most customers choosing a visualization tool such as Tableau or PowerBI.

Why Amazon RDS?
We chose Amazon RDS as it gave our engineers more time to focus on the things that mattered to customers. Instead of installing, patching and backing up a fleet of databases, our engineers now spend more time on improving the data marts, adding better indexing and performance tuning SQL.

Architecture
This is an example of typical deployment for customers using Tableau for visual analytics.

News Story Image

Tableau Online is a secure cloud-based solution provided and hosted by Tableau. This provides ease-of-use, speed and security without requiring infrastructure to be managed within CDL.

This online platform provides high availability solutions to share, collaborate, distribute workbooks, dashboards, data sources at anytime, anywhere, and facilitate the live connection to on-premises databases using the Tableau Bridge services. Tableau Bridge communicates with Tableau Online through an encrypted TLS connection to keep published data sources secure and up-to-date.

Source data can remain within the Amazon RDS database with the Tableau Bridge acting as an intermediary between the source data and Tableau Desktop / Tableau Online and maintain a live data source connection.

Case studies: Kingfisher Business Intelligence and Tableau visualization in action
Comfort Insurance
Comfort Insurance required the ability to visualise and derive immediate and accurate business insight. Working with CDL, they sought a solution that would enable them to move away from bespoke spreadsheets, which required time-consuming manual intervention, and where the accuracy of the results of the analysis was in question.

Utilising Tableau with Kingfisher, Comfort are now able to produce automated, repeatable near real-time analytics and visualisation of their business activities to drive business insight and improved decision-making.

By removing the need for manual management and interventions, Comfort are able to deploy their people resources more efficiently and focus on income-generating activities. They have also seen improved response time to market changes.

iGO4 Insurance
iGO4 wanted the ability to consume live business data in near real-time to their business data-lake platform and deliver scheduled feeds to business partners. They aimed to move away from nightly feeds and large volume reporting to use of data visualisation tools to provide data insight. Specific targets were to support data analytics in an automated way, remove manual intervention and deliver data within minutes rather than up to 24 hours after the event.

Utilising Kingfisher with customer-specified enterprise data consumption tools, iGO4 are now able to produce automated and repeatable near real-time analytics to drive business insight and near real-time decision making, whilst supporting business partners in the sharing of data.

Achieving simple, consistent data modelling with Amazon RDS
In summary we have found Amazon RDS to be an excellent way to deliver software to our customers quickly, freeing up our resources to provide higher levels of value within our data and analytics solutions.

Database Engine Versions
Amazon RDS Oracle 19c 19.0.0.0
Amazon RDS Oracle 18c 18.0.0.0
Amazon RDS Oracle 12c 12.2.0.1
Amazon RDS Oracle 12c 12.1.0.2
Amazon RDS Oracle 11g 11.2.0.4
Amazon RDS PostgreSQL 11 11.4
Amazon RDS PostgreSQL 10 10.7
Aurora PostgreSQL 3.0