CASE STUDY

Tesco Bank

Deedit uses CData ODBC Drivers to bring live data from Amazon DynamoDB into MicroStrategy for data visualization.





"Small actions, big impact."

That's the mantra for Deedit, a startup-like social good collaboration between Tesco Bank and Edinburgh University running in Edinburgh, Scotland in August 2018. Their goal? To see if encouraging many people to do small good deeds creates a big positive impact.

Deedit uses an interactive, mobile-friendly site to help "Deeders" select and do good deeds, connecting them with actions and organizations to:

  • Help Edinburgh's homeless
  • Help Edinburgh go green
  • Help Edinburgh's community spirit

Specifically, Deedit encourages small acts of kindness, ranging from buying a sandwich for a stranger or giving directions to Swedish Plogging. Deeders can record their activity on Deedit, which then uses the power of data visualization in MicroStrategy to show how these deeds contribute to bigger, positive social change. Deedit's data is part of an exhibition called Data Pipe Dreams: Glimpse of a Near Future, the annual interactive pavilion of the Centre for Design Informatics at the University of Edinburgh. The pavillion showcase is part of the world-famous Edinburgh Festival Fringe.

To help with data visualization, Deedit runs a large display screen at their Festival pavilion and utilizes MicroStrategy Desktop.

But custom-building that visualization from their underlying database was no easy task — especially for their fast-track project.

The Challenge: Data Visualization from DynamoDB

Simon Caruana is a Lead Engineer for Tesco Bank and developed the data layer and data visualization for Deedit. Working with a fast-track timeline, he built Deedit on top of Amazon Web Services (AWS) DynamoDB (database).

Usually, when it comes to analyzing the data or displaying it, he takes data out of a data operation store, such as AWS DynamoDB, places it in relational databases, such as MySQL, then connects it to data visualization tools like MicroStrategy. But for this project, that process wouldn't work.

"It was not straightforward to convert all that DynamoDB data to a relational data format, and we didn't have time for transferring all that data," Caruana said.

Instead, Tesco and Caruana needed a way to instantly move data from DynamoDB directly into the Deedit platform; they needed SQL access to his NoSQL data.

"It was much faster to build a serverless data layer underneath to directly service the app," Caruana said.

But Amazon had no built-in mechanism to connect DynamoDB data with external applications like MicroStrategy.

"Usually, Amazon will try to do the shortcut work for you, such as through Athena. But in this case, Amazon doesn't have a quick system and can't load data directly," Caruana said.

The suggested route from AWS was to move data from DynamoDB to an external database and then to Amazon Athena, where MicroStrategy and Deedit could access the replicated data using SQL. But that would be a bulky solution that would take too much time to set up.

"It's a more robust setup. If you're just creating something to monitor an app or need to build a quick and dirty visualization, that's a hole," Caruana said.

The Solution: SQL Access to DynamoDB through ODBC

After turning online, Caruana found the CData ODBC Driver for DynamoDB, which provides direct access to live DynamoDB data through the ODBC standard.

The Amazon DynamoDB ODBC Driver is a powerful tool that allows users to connect with live data from their Amazon DynamoDB NoSQL databases, directly from any applications that support ODBC connectivity. It enables users to work with DynamoDB data through SQL, exactly as they would with a traditional, relational database.

The driver makes it easy to:

  • Read, write and update NoSQL tables through a standard ODBC Driver interface
  • Gain SQL access to live data with full read/write access
  • Access, analyze and report on data with nearly any SQL-based tool
  • Quickly import, export, backup, analyze, report, transform, integrate and more

The CData ODBC Driver ultimately helped Caruana fill the hole in Amazon's ecosystem, rapidly integrating data from DynamoDB with Deedit.

"Even if you're building all in AWS, CData makes it so you can manage without the pain and expense of ETL," Caruana said.

Caruana's work put the driver through new paces in edge-case scenarios and pushed the limits of live connectivity. So he turned to the CData support team, which assisted Caruana with deployment of the driver and implemented new functionalities as needed. Now, CData drivers play a critical role in data visualization for the Deedit app.

"The CData ODBC Driver worked brilliantly," Caruana said.

The Results: Live Data Visualization

CData is powering the live load layer from AWS DynamoDB to Deedit's dashboard (as built in MicroStrategy), as illustrated below:

With the CData ODBC driver, Tesco and the University of Edinburgh were able to drastically reduce the time to develop Deedit and bring it to market.

"The CData connector is definitely a contender for anyone looking to build dashboards from DynamoDB, as the other routes (like using data pipelines to re-land the data to a RDS, etc.) would just have taken too long and carried too much overhead. Sticking CData in there cut the deploy time considerably, for a better outcome."

— Simon Caruana, Lead Engineer, Tesco Bank

More about Deedit

Deedit has been developed as part of Project Mercury, a unique collaboration between the Centre of Design Informatics and Tesco Bank designed to explore Fintech, develop talent and foster innovation and creative thinking. "One of the challenges we face is helping people understand the impact new data technologies will have on their lives. The partnership with Tesco Bank has been a fantastic help." said Chris Speed, Director of the Centre for Design Informatics at the University of Edinburgh.

It is hoped this research will demonstrate how similar types of digital technology can find use in corporate social responsibility programs to drive engagement, transparency and awareness.







Connectivity is our Core: Any source, Any technology, Any platform.

CData Software (www.cdata.com) is a leading provider of data access and connectivity solutions. We offer a straightforward approach to integration, with easy-to-use, data providers, drivers, and tools available from any technology capable of interacting with major data standards.

Learn More





* All product names, trademarks and registered trademarks are property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names,trademarks and brands does not imply endorsement.