With the rapid development of cloud data technology, businesses are increasingly enhancing the value of their data by taking advantage of the latest features and tools available in the market. This is why Microsoft Fabric stands out as a compelling platform for companies looking to migrate to the cloud.

In our journey to leverage the power of Microsoft Fabric, we encountered a versatile and user-friendly platform that simplified our data analytics efforts. Microsoft Fabric, being a Software as a Service (SaaS) solution, removes the need for provisioning underlying resources, making it incredibly easy to set up and use. With a straightforward pricing model focused on storage allocation for OneLake and project compute capacity, we embarked on our project with high hopes and clear objectives.

Use Cases of Microsoft Fabric
Setting Up the Workspace and Capacity Allocation

Our first step was creating a workspace and allocating F8 capacity* to it. This capacity assignment became the backbone of our data analytics infrastructure. Within this workspace, we established separate Lakehouses for each layer of our Medalion architecture, comprising bronze, silver, and gold. This strategic organization ensured that data flowed seamlessly from one layer to another, undergoing transformations and enhancements along the way.

*In Microsoft Fabric, 'F8 capacity' refers to the computing power allocated to the workspace for processing data.

Data Ingestion and Transformation

The client's requirements presented a clear focus on performance, prompting us to delve into various approaches for smooth data transfer from on-premises database to Microsoft Fabric. Along this journey, we examined different options, each presenting unique challenges and hurdles. Particularly notable were the limitations of this new software.

In pursuit of optimal performance using the available tools at the time, we devised several alternatives to connect an on-premises database to Microsoft Fabric. These included Dataflow Gen2, notebooks with PySpark, and Data Factory within Fabric, each with its own advantages and disadvantages, they were rigorously tested and monitored with real loads of data to verify that the performance met the client’s expectations and after an evaluation choose the option that had a better fit to the requirements.

Additionally, for cost-effectiveness and time efficiency we opted for a change data capture (CDC) approach instead of performing full data loads every time. This ensured that only the most recent changes in the data were transferred from the on-premises database to Microsoft Fabric.

Transitioning Data Between Layers (Bronze to Silver, Silver to Gold)

Transitioning data from the bronze to the silver layer involved a well-orchestrated process. Using pipelines and a range of activities, including lookup activities, script activities, stored procedures, and copy activities, we seamlessly brought data from the bronze layer to the silver layer. Here, we created Fact and Dimension tables that formed the foundation of our data architecture.

The goal of our data journey was to create final tables in the gold layer. These tables were instrumental in generating reports and dashboards using PowerBI and other applications. The synergy between Fact and Dimension tables in the silver layer, combined with the capabilities of Microsoft Fabric, enabled us to generate insights that were both actionable and valuable to our organization.

In the search for real-time data analytics, we scheduled pipelines to run at regular intervals, ensuring that the latest changes were seamlessly integrated into the platform. This approach allowed us to maintain data currency and deliver timely insights to our stakeholders.

Monitoring and Performance Tuning

A standout feature of Microsoft Fabric is its comprehensive monitoring capabilities. Through the monitoring hub menu, we had a bird's-eye view of all running activities. Additionally, the Fabric Metric app allowed us to monitor the consumed capacity of each process, facilitating performance tuning and debugging when necessary.

Microsoft Fabric, as an all-in-one data engineering and analytics platform, offers a holistic solution that eliminates the need to piece together various technologies and tools. In our project, it proved to be the cornerstone of our data analytics success, empowering us to efficiently manage, transform, and utilize data to its full potential.

Challenges

Throughout the journey, we encountered various challenges. Leveraging the features provided by Microsoft Fabric, we successfully overcame these obstacles, ultimately delivering a robust solution to our customer.

• Connecting to on-premises databases from Microsoft Fabric offers limited options, some of which raise security concerns. Therefore, alternative tools such as Power BI Gateway are utilized to establish secure connections.
• Performance can often be the most challenging non-functional requirement to fulfill, influenced by factors such as infrastructure, budget constraints, and other limitations. In our case, we had a specific performance target to achieve. However, due to constraints imposed by connection limitations, we encountered unexpected performance issues. Overcoming these challenges required the knowledge and expertise of our team
• At the outset of the project, not all available objects were supported by Subversion (Git), preventing the establishment of distinct branches for development and production.
• Some SQL Server features, such as the ability to create identity columns, were not available in Microsoft Fabric. Despite these limitations, the team successfully devised alternative approaches to achieve our objectives.

Despite the challenges encountered, the team successfully reworked the original solution to address the intricacies and limitations of Fabric in its current state, ultimately delivering a successful outcome for the customer.

About the Authors

Amir Golzadeh, a seasoned software developer, began his career in 2012. Currently, he serves as a consultant at Online Business Systems, where he has made significant contributions to multiple projects across various fields. His expertise spans data management, web applications, ERPs, and solution architectures. Amir's critical thinking skills shine as he consistently seeks out areas for improvement and delivers effective solutions. His ability to identify and address complex challenges has proven invaluable in driving innovation and enhancing business processes within diverse industries.

Natalia Borbon is a software developer who is very passionate about data. She began her journey in development in 2011 and has since been involved in various projects across diverse industries. Working closely with business stakeholders, she understands the importance of data and is always pursuing ways to enhance its use to add extra value to businesses.

Ready to unlock the full potential of Microsoft Fabric for your organization?

Contact our Data Services team to explore how Fabric can transform your data management and analytics processes.

Be sure to read Part 1 of our Microsoft Fabric blog series, where we unpacked the many exciting features and benefits of this powerful platform! 

 Whether you need a comprehensive Data Strategy & Roadmap, robust Data Solutions Implementation, or insightful Analytics & Reporting, we're here to empower your organization with innovative and strategic data approaches.