The fast-paced financial industry keeps an ascending trend of the different portfolio managers and traders rushing to consume more data. Similar to our Client, a high-quality and controlled data warehouse is then critical for any fast-growing data driven company.

As the data volumes and the number of systems keep increasing, our Client was looking for a better way to get insights from every area of their business quickly. With a target to source over 100 Power BI dashboards and reports, they needed to proficiently transform all raw data into relevant and normalized data.

Being able to bring rich and diverse data into a meaningful data warehouse ensures critical and deeper insights, helps better quality decision-making and satisfies their hunger for data and extracting value from it.

Like any data driven company, the Client subscribes to disparate sources to receive the data needed for their operations, investment, portfolio manager teams and stakeholders. Supplementing more raw data into the Data Warehouse does bolster the Power BI decisional dashboards and other reporting. However, it does add complexity to the ETLs. Examples of the challenges are:

▢ Identifying similar business objects (ex: Security, Funds, portfolio) from diverse sources and mapping them to master tables and database IDs

▢ Dynamically loading the same content from the different source formats. Ex: import the Blotter files from the different fund managers that provide different formatted files and columns

▢ Building an overall scalable import process, which adjusts to the continuous onboarding and retiring of the raw data sources

▢ Tracking the status of the scheduled loads and alert when expected data (all or partial) is missing

▢ Ensure high quality and data integrity

Constant stream of live updates from across the business

As the Client’s IT team had their initial core data warehouse up and running, it led to a better interaction between the wider business and IT. Thus, their manual data integration and operation required more support to keep up with their success. They were also looking for a better way to harness their data to continue building better business insights.

To resolve that growing need, OmniVista provided analysts that were capable of integrating their tech-savvy team. In addition, we provided an expanded support coverage by both US and UAE teams for their business workflows from 11pm to 8pm ET. Together, these played a crucial role in the successful growth of the data warehouse.

Automation that supports seamless data flows

From the beginning we had the scope of transitioning to scalable ETLs and automation. We started with a large business analysis effort which resulted in scoping the different data silos and reverse engineering their MS SQL imports to data sources. This built a clear roadmap for the required data load automations and data integrity check points. We built translations, mappers and program configurations that allowed dynamic ETLs from various sources to be accessible without extra development work. We also added strong error logging and audit tables that facilitated capturing data discrepancies with clear articulation of the source cause. Using a strong agile implementation, we converged to a stable and reliable data warehouse streamlined pipelines and quick turn arounds to fix any data breaks.

The power of hybrid data sources

Enriching the data warehouse from hybrid data sources refined data driven decision making and enhanced data governance. On one hand, we ensured that all business users and data analysts could efficiently work with the required data. On the other hand, we ensured that the different users had access to a constant stream of live updates with seamless data availability and synchronization across the multi data storage.

We were able to provide reliable and comprehensive business intelligence and insights in one place, by retrieving and analyzing data across all sources and combining metrics from databases and data warehouses. For this case study, our Client was using MS SQL and opted for the MS Fabric and Power BI platforms to perform more detailed analysis for the company to: optimize business processes, analyze financial dashboards and develop investment and growth strategies. In addition, accessible diverse data allowed for an intensive financial market understanding and sharp decision-making for an increased revenue.

Making full use of your data despite retrieval and structure complexity required the right approach and expertise. Operating on a platform built for hybrid data driven strategies was key to unlocking those benefits. We are continuously aiming for an architecture that enables you to drill down within different data granularity levels, better analyze portfolios performance and see different diagnostics across your entire enterprise data flows.