BTD (Bank Transactions Dashboard)

Inspiration

The Azure platform aims to help businesses manage challenges and meet their organizational goals and Power BI is one of the largest and fastest-growing business intelligence tool. This Idea will help client to see their bank transaction details which includes(no of new customers, daily transaction count, turnover etc.) in proper visualized manner.

What it does

Every bank have large number of transactions happens day by day. They were getting those transactions details in the format of excel,csv,raw data, database,xml, etc. What we are doing this BTD project is like, we have created a web portal and the bank authority members can login that portal with their own credentials. They can upload those transaction details files using the web portal and those raw files are initially loaded in a Blob storage(using storage account). From their we have done some transformation and loaded in azure sql db. By using Power BI automation process, we can create store procedures, views to fetch the corresponding information from Azure db and do the visualization . This can help client to understand their daily/weekly/monthly banking transaction details in a understandable manner.

How we built it

• Need to create a Web portal with proper login authentication.
• Each Bank or region(EMA,ASPAC) have their own azure storage accounts and azure data factory which can be done during the installation process and those accounts keys are stored in a web config.
• Bank authority can able to upload their transaction files (any format) using self-hosted integration run time.
• while uploading the file, the code will check the config and find out the storage account and ADF credentials details , then upload the files in to a blob container.
• once the upload process is completed , we already created a event based trigger in ADF, it will automatically fetch those files from blob storage and done some transformation using data flow activity.
• Data flow task is help us to perform unstructured files in a structured format and we created a copy activity in the pipeline which help us to copy the structured files(after dataflow task) in to azure sql database.
• From their we have created some store procedures and views which help to fetch the information from sql db.
• Once data stored in the database, we can connect the azure sql database to the power BI desktop and we can visualize those information in a understandable format like pie chart, slicer, Bar chart.

Challenges we ran into

Complicated Pricing. Azure Data Factory can tackle these challenges faced while moving data to or from the cloud, by the following methods: Job scheduling and orchestration: There is a shortage of services that trigger data integration on the cloud. Although there are some services like Azure Scheduler, Azure Automation, SQL VM, etc.

What we learned

We learned new technologies like Adf, pipelines, Azure sql, Power BI

What's next for BTD (Bank Transactions Dashboard)

We need to implement the same process in Phoenix instead of uploading those transaction files, those information's are already exist in phoenix DB. We just need to copy information to azure sql using ADF then follow the same above steps.

Built With

• adf
• azure
• azuresql
• powerbi