We proposed a new architecture for the bank's data analytics system. Previously, data modeling, data processing, data storage, and report visualization components were tightly coupled together in the cloud service they used. Our solution involved separating the various parts that make up the data reporting system into distinct pieces.
Here's the different components of the solution:
ETL Agent (Apache AirFlow and Spark)
We built a custom software program called an 'ETL agent.' It collects data from the listed sources, then changes this data as needed—transforming it into the required form—and loads it into a central storage, a data warehouse. This ETL agent works independently of Power BI. It means that the process of preparing and storing data in the data warehouse happens regardless of which reporting tool is later used.
Data Warehouse (Microsoft SQL Server)
The centralized data warehouse acts as a repository for historical and aggregated data, separate from the transactional databases. This addresses performance and scalability issues faced when using Power BI directly on transactional data.
SSAS Tabular Cube
Using Microsoft's SSAS analytical modeling tool, we have built and deployed a 'Tabular Cube,' an advanced, optimized analytical data model in a tabular/relational format designed specifically for complex data analysis queries and processing. With regular Power BI, there is a limit to how much data can be analyzed at once. Moreover, the Tabular Cube allows our client to work with larger amounts of data that wouldn't fit in regular Power BI alone. The Tabular Cube can connect to and share its data with many different reporting and visualization tools besides just Power BI Report Server. It is not restricted to only one tool.
Power BI Report Server
While the bank migrated from the cloud Power BI service to the on-premises Power BI Report Server, the solution allows them to continue using Power BI for report distribution and visualization by connecting it to the SSAS Tabular Cube.
We have been working for over 10 years and they have become our long-term technology partner. Any software development, programming, or design needs we have had, Belitsoft company has always been able to handle this for us.
Founder from ZensAI (Microsoft)/ formerly Elearningforce