Azure CosmosDB in Banking Sector

  • Article

Happy New Year 2018 !!!

Other day I was reading about change feed feature of Azure CosmosDB and thought of possibilities can be driven from it. That triggered me to test a scenario which we deal with in our day to day life which is getting notification of any activity happened on Bank account.

Use case

Typically, in a banking sector, Any bank keep account holders information stored in their database and provides various methods to spend (through debit card), withdraw (through ATM), money transfer (Online or mobile app) and deposit (any channel). The balance will get adjusted based on the transaction type. At the end of successful transaction, as a bank customer we get SMS/email regarding the transaction took place in our account. This will make sure customer is updated of any transaction happening on their account and also if there any fraudulent transaction takes place they will act.

With that thought, I am going to write about how to build the solution using Azure and give information about the services.

Technologies Involved

Azure CosmosDB:- This is heart of this solution. Azure CosmosDB is a multi-model geo replicated database. It provides various APIs like SQL, MongoDB, Cassandra, Graph and make sure core features like security, multi-region replication, consistency, partition, scalability, indexing and log monitoring applies to all. Azure CosmosDB doesn’t enforce the learning of new tools but provides an ability to developer to continue using existing tools for example if a developer is working on GrpahDB, he/she canstill use gremlin console to interact with it.

Change Feed: - This feature once enabled, creates a lease collection. This collection keeps track of changes happening. It stores changes the way they happen on the collection. All the inserts and updates get captured however for deletes, it requires workaround like adding additional property to decide delete. Change feed can be accessed by Azure function (we’ll cover in this blog), Azure Cosmos DB SDK and Azure Cosmos DB Change Feed Processor library. Below is the example of document stored in lease collection.

clip_image002

For more information about Change Feed feature please check here.

Azure Functions: - Based on serverless architecture, Azure Functions provide ease of running micro code without worrying about setting up infrastructure, scheduling job, security, multitasking etc. One can quickly build a function and run it on any scale required. This architecture can be used in various domains like IoT, retail, finance etc. For more features and capabilities of Azure functions please refer here. Before production deployment please refer here to make sure the programming language that you are planning to use (.Net, JS, Java etc.) is in GA and not in experimental.

PowerBI: - PowerBI is the representation layer. It gives a a visualization capacity for users to make sense of different datasets and computations done on them. A dashboard shows the story of data it’s presenting. Microsoft PowerBI has evolved a lot since the time I started working with it. It captures all the different verticals and audiences. The ability to develop once and render it on any device makes it more powerful. For more information please refer here.

Architecture

clip_image004

Prerequisites

  • Active Azure Subscription
  • Visual Studio 2017

Setting up Azure CosmosDB account

clip_image006

  • Once page is open, Enter ID
  • Select API as SQL
  • Select Subscription
  • Create/Select Resource Group
  • Select Location
  • Select Pin to Dashboard
  • Click Create

             clip_image008

  • Once the page is open, click Overview
  • Click Add Collection

clip_image009

  • Let’s create Database and a collection to store account master information. Provide Database id
  • Provide Collection Id
  • Select Storage Capacity as Fixed (10 GB)
  • Click OK

             clip_image011

  • Once Collection created successfully, Click New Collection

clip_image013

 

  • Enter Database id

clip_image015

  • Enter Collection Id
  • Select Storage capacity
  • Select Throughput
  • Click OK
  • Once Database and collection created, screen will look like below

clip_image017

 

  • Click Close (X)

clip_image019

 

Adding Account Master Details

  • Go to https://github.com/rawatsudhir1/AzureCosmosDBChangeFeedUseCase and either clone the repo or download it as zip
  • In the repo under SupportingFiles, open CustomerAccountInfo.txt file. This file has some records or json document (contains customer account master information) which we will upload through portal. The other approach is to create a web-app or mobile app (with proper security enabled) to send this data.

clip_image021

*Note :- I am not following any data modeling technique. Generally, when you are storing data in Azure CosmosDB please decide, whether application will perform read or update extensive operation.

  • Copy first record from the file (from line 1 to line 8 as per the above image)
  • Goto Azure CosmosDB account, created in earlier step.
  • Click Data Explorer

clip_image023

  • Click SummaryInfo
  • Click Documents
  • Click New Document
  • Paste the record copied from github in earlier step

clip_image025

 

  • Click Save
  • Repeat Steps to add more records (copy from github repo)
  • After adding all six records, here is how screen look like

clip_image027

 

  • Close (X) Data Explorer

Before recording deposit or expense details in DetailInfo collection, let’s first build out Azure Function Logic

Building Azure function logic

  • Click Create a Resource
  • Click Compute
  • Click Function App

clip_image029

 

  • Once blade is open, Enter App name
  • Select Subscription
  • Create new or Use existing Resource Group
  • Select Windows as OS
  • Select Consumption Plan as Hosting Plan
  • Select Location (Select the same region where Azure CosmosDB is created)
  • Let default values for Storage
  • Click Pin to dashboard
  • Click Create

clip_image031  

 

  • Once Function App is created, Click on New ( + )

            clip_image033

 

  • Click Custom Function

clip_image035

 

  • Choose a template, enter cosmos in search

clip_image037

 

  • Select Cosmos DB trigger
  • On New Function, Select C# as language
  • Provide Name of the function
  • On Azure Cosmos DB account connection click new and select the cosmodb account created earlier
  • Provide Collection name
  • Provide Database name

clip_image039

 

  • Let Collection name for leases as it is
  • Click Create
  • Once function is created, Click on function app and then Application Setting

clip_image041

 

  • Under Application settings, add two variables endpointUrl and authorizationKey. These variables hold values to connect CosmosDB to retrieve and update Bank Account Master table. Copy values for both the variable from CosmosDB account under Keys section.
  • Add endpointUrl , authorizationKey with values and Save it. This is how it will look like.

         clip_image043

 

clip_image045

 

Azure function is setup. Let’s move to next step

Post transaction

  • Open Visual Studio 2017, create a console application
  • Copy code from here (Program.cs) and paste it.
  • Include Microsoft.Azure.DocumentDB.Core and Newtonsoft.Json nuget package.
  • Add endpointurl and authorizationKey as shown below.

          clip_image047

 

  • Run/F5 to run console application. This action will post a transaction to Azure CosmosDB

          clip_image049

 

Output at Azure Function

  • Switch to Azure portal
  • Look at Logs window in function

clip_image051

 

Send Notification to Bank user

  • Please make sure to setup twilio test account to send sms notification.
  • Switch to Azure portal and open Azure function
  • Click Integrate
  • On Outputs click New Output

clip_image053

 

  • Select Twilio SMS and click Select

clip_image055

 

  • Keep message as Message parameter name
  • Keep acctsid as Account SID setting. The variable acctsid will define under application setting which hold value of Account SID (got from Twilio dashboard)
  • Keep authtoken as Auth Token setting. The variable authtoken will define under application setting which hold value of Authentication token (got from Twilio dashboard)
  • Provide To number. Make sure this phone number is verified in Twilio
  • Provide From number. This information available in in Twilio dashboard. If first time created Twilio account then it will provide a number to use for some time.

clip_image057

 

  • Let’s add variables in Application settings. Click function and Application settings

clip_image059

 

  • Add acctsid and value (Account SID from Twilio Dashboard)
  • Add authtoken and value (Authorization Token from Twilio Dashboard)

clip_image061

 

  • Click Save
  • Copy code from github and paste in
  • Click Save.Make sure there is no error in log
  • Run console program and post a transaction

clip_image063

 

  • Once it run successful, a SMS will be sent to the mobile number. If not received make sure defined number is not in DND (Do Not Disturb) mode.

Building PowerBI Report

  • Follow here to build a PowerBI dashboard.

Summary

In this blog, we built a use case to showcase how Azure COSMOSDB can be used in Banking Sector. However, there are another scenario which can be build using this feature like create request for vendors once an order is received (Retail domain), Immediate action on bad feedback (Service domain), Alert when a new quotation request is received (Insurance domain) etc.

Thanks for reading and I hope you liked it.

Thanks to my colleague Gandhali for her suggestions.

Eat Healthy, Stay Fit and Keep Learning.