Financial Analytics Solution Smoothly Handling 500+ Concurrent Reports

About Our Client

The Client is a financial regulatory authority responsible for capital markets. The Client’s functions include supervising and issuing licenses to market intermediaries and monitoring their activities.

Challenge

With the aim to oversight market and conduct market research, the Client employed varied data source systems – a risk-based supervision system, a surveillance system for monitoring marketing activities, Excel databases, an upload web portal for registered entities, etc. However, the inability to consolidate these data sources for in-depth analysis hindered advanced supervision and raised difficulties for regulatory policies development and financial analysis. Thus, the Client was looking for a vendor that could help them establish a centralized BI platform with robust analytics capabilities.

Solution

Discovery

ScienceSoft’s business intelligence team analyzed the Client’s business objectives and defined optimal features for the future solution:

• Integration and management of disjoint data sources across the organization, including the web portal used for receiving the documents from registered entities required for financial analysis.
• Multi-source data analysis to support informed decision-making and proactive capital market regulation.
• Comprehensive financial analysis, including financial models implementation, trend analysis, compliance check analysis, multidimensional statement analysis, etc.
• Custom reports and dashboards aligned to different user needs across the company’s departments.
• Scheduled and ad-hoc reporting capabilities.
• Data access based on user roles and permissions.

Solution architecture design

ScienceSoft’s team designed the solution’s architecture, which consisted of three layers:

• Data staging (integration) layer – data extraction from heterogeneous internal and external data sources, its further transformation and loading into the DWH.
• Data warehousing layer – data receiving and storing for further analysis.
• Business intelligence layer – scheduled and ad-hoc analytics and reporting for end users.

BI solution development

The BI team delivered the solution, which consisted of the following components:

• Data integration platform

To consolidate data from the Client’s data sources, ScienceSoft’s team built the staging layer with the Extract, Transform, Load (ETL) processes. The ETL pipeline enabled data retrieval (either in batches or micro-batches), data cleansing, validation, processing, quality testing, refining, filtering, tuning and master data management.

• Data warehouse

The cleaned, formatted, reorganized, summarized and supplemented data was then loaded for storage into the DWH, which became the main source of information for analysis and report generation.

The team also added an analytics sandbox to the DWH. This separate environment supported experimental or development analytics activities, which allowed the Client to create, test and deploy their own analytics models.

• Business intelligence component

To allow comprehensive data analysis, the BI team established OLAP cubes, which summarized complex large amounts of processed data to grant the Client quick access to any data point. That way, the Client could conduct financial market analysis to determine development and capital raising strategies, market participant analysis to discover hidden relationships between entities, trigger fraud alerts, etc.

To provide end users with the necessary information depending on their needs and objectives, ScienceSoft’s team delivered custom reports and dashboards. For example, the executives could conduct quick analysis based on high-level KPIs and general metrics (e.g., comparative market analysis, trend analysis at entity and industry levels, etc.). And regular users could get quick access to the operational data they needed daily with the help of traditional reports.

All solution components were built with the software compatible with the Client’s existing technology environment. The suggested technology stack belonged to the Microsoft family, which enabled improved software interoperability, reliability and supportability. Since the Client had already had a number of Microsoft licenses, it allowed optimizing licensing costs. What is more, the solution could be easily migrated to the cloud.

Sensitive data security

To ensure high data security, ScienceSoft set up elaborate user access control, permission matrix based on row and column level security, which wouldn’t negatively affect the analytics solution performance.

Results

The Client has obtained a fully functional and highly secure BI and analytics solution to monitor and manage their data for in-depth financial analysis, automate data flows, develop custom analytics models. The delivered solution supports 200+ concurrent business users handling over 500 reports simultaneously, which leads to optimized business operations and accelerated decision-making.

Technologies and Tools

Data integration: Microsoft SQL Server Integration Services, Microsoft SQL Server Stored Procedures, Microsoft SQL Server Agent.

Data warehouse: Microsoft SQL Server Enterprise Edition with software assurance.

Analytics: Microsoft Server Analysis Services, Microsoft SQL Server Machine Learning Services, R, Python.

Reporting: Microsoft Power BI Report Server, Microsoft SQL Server Reporting Services, Microsoft Excel, Microsoft Power Pivot.