BI Modernization for Financial Enterprise for 100x Faster Big Data Analysis

The legacy architecture of the analytical software, built years ago, was causing a snowball effect of issues, disrupting the enterprise's daily business operations.

Limits in functionality as a barrier to detecting critical risks and insights

While handling data directly in PowerBI was adequate for basic data analysis tasks - such as extracting data from a single source like an Excel file or a database, performing fundamental data transformations, and creating visualizations for simple analyses – it fell short in more complex scenarios.

Specifically, financial analytics could not develop custom algorithms to perform complex data operations, like gathering data from multiple sources simultaneously, merging and aggregating data from different branches, executing non-standard calculations for fraud detection, and many more.

Slow performance impeding the work of bank employees and analytics

The bank faced performance bottlenecks in its operations and analytics due to using a single database for both routine tasks and data analysis. This database, originally designed for small, real-time transactions, and couldn't handle the large amounts of data and complex calculations needed for tasks like customer segmentation. Frequent performance issues and system crashes resulted from this limitation, which made the bank less efficient and unable to scale up in data-heavy situations.

Challenges in scalability owing to data source constraints

The financial analytics system had a linear setup, taking data straight from the transactional database into Power BI, but it had a 2 GB file size limit. While it was sufficient for standard customer transactional data, it proved inadequate for broader market trends analysis. As the bank's customer database grew, the data size frequently exceeded this limit, causing operational and scaling challenges, especially in fraud detection scenarios.

Limited in-depth reporting due to relying only on one BI visualization tool

The bank heavily relied on Power BI as its sole BI tool for data visualization, which limited its analytical capabilities for various roles within the organization, including top managers and analysts. While Power BI is suitable for high-level reporting, financial analytics might need to perform detailed analysis like Excel transformations. This one-size-fits-all approach reduced flexibility and hindered effective data analysis across different roles within the bank, impacting decision-making and reporting capabilities.

The Сlient aimed to address performance, scalability, and tool limitations in the financial analytics and Business Intelligence solution by modernizing the software architecture in line with industry best practices.

This modernization followed three key pillars:

• developing an advanced ETL agent for data Extracting, Transforming, and Loading
• establishing a Data Warehouse (DWH) for centralized data storage
• migrating the model to SQL Server Analysis Services (SSAS) for improved performance

Customized operations for critical insight and risk detection

The development team designed custom ETL components to improve the efficiency of complex data operations for financial analysts. These components allow simultaneous data extraction from multiple sources, such as branch transaction records, web services, and external files. They also help merge and aggregate data across the network, which is important for tasks like credit risk assessment. This advancement gives analysts precise control over data handling and structure in analytics environment.

100 times faster performance with minimal resources

We introduced a dedicated Data Warehouse to address the challenge of slow performance caused by using a single transactional database for daily operations and data analysis. The DWH is optimized for analytical queries, efficient compiling, cleaning, and structuring data. This allows financial analysts to handle large data volumes without affecting regular operations.

We integrated Apache Spark, a cutting-edge engine for large-scale data processing, which has significantly sped up data analysis and calculations, processing data up to 100 times faster. It processes data in-memory, ensuring rapid performance with minimal resource usage. For example, processing 3 million records, including loading, computation, and uploading to the DWH, now takes about 6 minutes, showcasing increased efficiency improvements.

Scalable architecture aligned with business growth

To accommodate the expanding bank network, we addressed data model size limitations by modernizing the ETL module and incorporating SSAS Tabular Cube. These enhancements enable processing large data volumes without size constraints.

Apache Airflow, a sophisticated platform for managing workflows, efficiently handles and processes large data volumes from various sources. The SSAS Tabular model overcomes file size restrictions in Power BI, allowing for unlimited data model size.

Compatibility with various reporting and visualization tools

The new architecture effectively overcomes the limitation of a single data visualization tool. With integrating SSAS Tabular models, the system acts as a universal adapter, compatible with a wide range of reporting tools such as Excel, Power BI, SSRS, Tableau, and other BI tools as needed.

This empowers bank employees at all levels to choose the most suitable reporting and visualization tools for their specific requirements. With this adaptability, management can access detailed custom reports while analysts can perform in-depth data analysis, fostering a more nuanced understanding and robust historical data analysis across the bank.

Technologies Stack:

• ETL: Apache AirFlow and Spark
• Programming language - Python
• DWH: MS SQL Server
• BI: SQL Server Analysis Services and Microsoft Power BI Report Server

The project was delivered in just 1,5 months within the promised scope. The BI software modernization allowed the financial enterprise to resolve the hindrances to business operations and growth.

The Client was pleased with our professional approach and the positive outcomes from the modernized architecture. They continued collaborating to enhance system performance, functionality, and security.