Data Management in Microsoft Fabric

Client Region – Germany
Company Size – 100 – 200 people
Domain – Telecommunications

Challenges

Managing diverse data systems posed a challenge for our client. This study highlights two approaches to leveraging Microsoft Fabric’s medallion architecture for integrating data from Oracle and MySQL sources, performing transformations, and delivering actionable insights through Power BI reports. Both methods demonstrate robust pipeline creation while showcasing the advantages of structured data flows.

Previous Implementation: The Previous implementation utilized the medallion architecture—a structured framework involving bronze, silver, and gold layers. Both the databases were hosted in AWS, and using PBI Data Gateway, they fetched the data from there. Their methodology was as follows:

• Raw Data Ingestion: Instead of ingesting raw data into the bronze layer, they directly loaded the data into the silver layer using Dataflow Gen2.
• Silver Layer Transformations: The silver layer served as the central repository for unifying and structuring the data. However, the raw data from both Oracle and MySQL was stored here without prior standardization in the bronze layer.
• Gold Layer Semantic Model: Transformations and semantic modeling were executed entirely in the gold layer, preparing the data for reporting.
• Reporting: The gold layer was used as the foundation for Power BI reports, offering insights to stakeholders.

While this approach successfully delivered reports, bypassing the bronze layer introduced certain limitations. The lack of a dedicated raw data repository and initial standardization in the bronze layer could impact traceability, lineage, and overall efficiency.

Standardized Medallion Approach: we implemented an alternative approach that aligns with the medallion architecture principles, ensuring optimal efficiency, traceability, and maintainability within Microsoft Fabric:

Bronze Layer – Raw Data Repository:

• Raw data from Oracle and MySQL was ingested into the bronze layer Lakehouse.
• This layer served as a single source of truth, preserving the original state of the data for traceability and auditability.

Silver Layer – Data Standardization:

• Data structures from both sources were standardized to ensure consistent data types and schemas.
• Selective columns were extracted based on analytical requirements, reducing storage overhead and improving performance.
• The dataset is saved in a silver layer Lakehouse.

Gold Layer – Semantic Modeling and Transformations:

• Advanced transformations were performed in the gold layer, focusing on creating a clean and meaningful dataset.
• The data was optimized for business intelligence use cases, ensuring readiness for reporting and analytics.
• The dataset is saved in a gold layer Lakehouse. 

Power BI Reporting:

Reports were built on the semantic model created on a dedicated reporting Lakehouse, which references the dataset saved in the gold layer Lakehouse. This ensured actionable insights with high accuracy and reliability.

Key Features of the Solution

Our methodology provided several advantages:

• Enhanced Traceability: The bronze layer preserved raw data, allowing for better lineage tracking and simplified debugging.
• Improved Data Consistency: Standardizing schemas and data types in the silver layer ensured uniformity across sources, reducing discrepancies.
• Efficient Resource Utilization: Selective column extraction minimized unnecessary data processing and storage requirements.
• Streamlined Transformations: Segregating transformations across layers improved pipeline clarity and reduced complexity.
• Scalability and Maintenance: The layered approach allowed for easier scalability and adaptability to new data sources or requirements.

Qualitative Impact:

• Traceability & Auditability: Enhanced data lineage tracking and simplified debugging with raw data in the bronze layer.
• Governance: Improved data governance through a structured, well-defined approach.
• Collaboration & Maintainability: Clear layer separation made the pipeline easier to maintain and adapt.
• Reduced Reporting Complexity: Clean gold layer dataset reduced complexity in Power BI reports.
• Business Decision-Making: Timely, accurate insights supported better organizational decision-making.

Quantitative Impact:

• Data Quality: Reduced data discrepancies by 15%, improving the reliability of Power BI reports.
• Performance: Faster data processing with a 30% improvement in processing time and reduced query time for reports by 25%.
• Scalability: Enabled scalable pipelines, supporting a 50% year-over-year growth in data volume.

Conclusion

Both approaches demonstrate a commitment to utilizing Microsoft Fabric Lakehouse for effective data management and analytics. However, the Standardized Medallion approach implemented by our team adheres more closely to the medallion architecture’s principles, enabling improved efficiency, scalability, and governance. By leveraging the bronze layer for raw data ingestion, the silver layer for standardization, and the gold layer for advanced transformations, this methodology ensures a robust foundation for delivering impactful insights through Power BI.