Having access to accurate, real-time data can drive better decisions and provide competitive advantages, especially in today's dynamic business environment. Microsoft Fabric, with its Real-Time Intelligence service, offers a solution that processes, analyzes, and acts on data as it flows. This service delivers actionable insights instantly. It is transforming how businesses manage data by simplifying architectures, improving scalability, and greatly reducing costs.
In this case study, we explore how Real-Time Intelligence within Microsoft Fabric transformed a client’s data ecosystem in the construction industry. It overcame challenges related to performance, cost, and scalability. By transitioning to Microsoft Fabric’s unified platform, the client achieved faster decision-making and greater efficiency. They now have a scalable solution for future growth, demonstrating the power of Microsoft Fabric’s Real-Time Intelligence.
About the client
The client, an independent software vendor (ISV), provides solutions for every stage of the construction project lifecycle. This includes everything from the design stage to post-construction analysis. Known for bridging the gap between physical and digital workflows, the client relies on on-premises and cloud-based technologies. For their customers, having immediate, accurate data is critical to ensuring that projects remain on schedule and within budget. However, their existing system was fragmented, costly, and lacked scalability.
Overview of existing data model
The client’s existing system was a multi-tenant solution comprising of:
· A SQL Server that captures all updates in real time.
· Debezium (Change Data Capture) and Kafka for streaming real-time changes from the SQL Server database.
· Snowflake, which ingests data from Kafka, used for implementing business logic and processing data.
· Power BI, configured with DirectQuery on the reporting view layer in Snowflake, enabling data reporting capabilities.
While functional, this setup created inefficiencies that impacted the client’s ability to deliver fast and cost-effective solutions to customers.
|
| Figure 1: Existing architecture |
The challenges
The existing system faced several operational, scalability, and performance challenges:
·
High total cost of ownership (TCO)
o Platform and server maintenance: The multi-tenant SQL Server +
Debezium + Kafka setup was replicated for each customer. This resulted in high
operational and ownership costs for platform maintenance, updating, and
managing these separate services.
o License expenses: Separate licenses for SQL Server,
Debezium (virtual machine hosting), and Kafka were also required for each
customer. Power BI Pro licenses were required for every user, increasing costs
further.
·
Slow performance and data latency
o Delayed insights: Processing an average of 180K–200K
records per minute caused increasing latency in real-time updates. This delayed
access to critical data for leadership and decision-makers. Peak record volumes
worsened this issue, increasing delays and costs.
o Report latency: The complexity of business
logic and growing data volumes degraded the performance of DirectQuery (DQ)
reporting.
·
Data processing
o Delta merging challenges: Integrating the latest streamed
records (delta) with historical data for analytical purposes was not performing
as expected.
o Managing DML operations:
While updating records in Snowflake, certain INSERTs and DELETEs failed to
execute, potentially due to the high data volume.
·
Need for a unified data hub
o Isolated real-time streaming: The real-time streaming
implementation operated in silos and was not available as a unified data hub
accessible to all teams.
o Dependency complexities: The system consists of numerous
scripts and tables for data orchestration and business logic implementation.
This made debugging difficult due to interdependence across levels.
Key business requirements
To address current limitations and support future growth, the client needed the solution to meet the following key requirements:
· High throughput requirement: The proposed solution should be scalable enough to handle
around 5,000 records per second.
· Report page load times: Page load time for reports should be minimized to 5-8
seconds, with up to 10 seconds for drill-down views.
· Resilient processing: An error-handling mechanism is needed to simplify debugging and allow
processes to resume smoothly after failures.
· Role-based access control: Row-level security (RLS) needs to be applied to Power BI
reports at the enterprise level to ensure secure data access.
· Cost-efficient solution: The proposed system should incur lower costs compared to
the existing Snowflake setup.
The core of our solution: Microsoft Fabric Real-Time Intelligence
To address these challenges, the client migrated to Microsoft Fabric. They used its Real-Time Intelligence service to unify and optimize their data pipeline. With Microsoft Fabric’s SQL Server Change Data Capture (CDC) connector and Eventhouse, the client achieved seamless real-time data processing.
This integration greatly increased data processing speeds, reduced operational complexity, and lowered costs. The Real-Time Intelligence service within Microsoft Fabric provided unparalleled real-time insights. It empowered the client to make proactive decisions while ensuring scalability for future growth.
Diving into the details
|
| Figure 2: Solution architecture |
The solution was designed with key components to optimize performance:
· High throughput management: Real-time ingestion with the SQL Server CDC connector
eliminated the need for Debezium and Kafka. This significantly reduced TCO. (Step #1)
· Real-time data streaming: Changes are streamed in real-time through event stream,
ensuring continuous data flow and timely updates. (Step #2)
· Real-time analysis with Eventhouse (Kusto Query Language): Eventhouse used
Kusto Query Language (KQL) for fast and reliable real-time querying and
business transformation. This provides end users with fast and reliable
insights. (Step #3)
· Real-time alerting and notifications: Reflex provided alerts and notifications based on
specified conditions in real-time data. (Step #4)
· Scalability and ease of analytics: One-click data availability in OneLake supported analytical
workloads. Direct Lake integration with Power BI enabled near-instant
visualizations. (Step #5)
· Unified platform: All data is now stored/processed in
a unified environment, eliminating the need for separate Snowflake and Power BI
licenses. (Step #6/7)
Measurable business impact
 |
| Figure 3: Solution benefits |
The migration to Microsoft Fabric delivered measurable improvements:
· Data transformations
o
Real-time data transformations via Eventhouse: Processing time is reduced to ~1
minute after applying required business transformations.
o
Near real-time scenarios via OneLake
shortcuts: Transformation
and merging of real-time data with years of historical records are completed in
as little as ~4 minutes.
· Page load time in Power BI: Optimized to 7-9
seconds with Direct Lake access for summary and drill-down views, providing a
faster user experience.
In conclusion
By consolidating systems into a single platform with Microsoft Fabric Real-Time Intelligence, the client achieved a transformative solution. They reduced costs, eliminated scalability limitations, and minimized data latency. Microsoft Fabric’s Real-Time Intelligence service empowered them to harness real-time processing and provide faster insights. This unified platform allows effortless customer onboarding and scaling of their business. All in all, this solution is well suited for ISVs who have multi-tenant architecture with customer-specific app databases.
Interested in learning more?
As a Microsoft Fabric Featured Partner, MAQ Software brings deep expertise in helping organizations unlock the full potential of Microsoft Fabric. Whether you're looking for guidance on implementing data solutions or optimizing your existing platform, we’re here to support you every step of the way. Contact us at Sales@MAQSoftware.com or explore our apps or consulting services on Microsoft Azure Marketplace:
· Microsoft Fabric: 2-Hour Briefing – Microsoft Azure Marketplace
· Microsoft Fabric: 4-Week Assessment – Microsoft Azure Marketplace
· Microsoft Fabric: Accelerated 8-Week Pilot Implementation – Microsoft Azure Marketplace
· Real-time Intelligence using Microsoft Fabric: 1-Day Workshop – Microsoft Azure Marketplace