The company faced significant challenges in utilizing the data directly from the source because of the complexity. In addition to this, the reports developed earlier were not interactive and took a lot of time to load.
A nationwide network of learning centers operates under an organization, generating surplus data stored across five different sources tailored to specific requirements. Utilizing this data, the organization aims to develop comprehensive Power BI Reports that are both robust and user-friendly, ensuring easy comprehension and usability.
There is an FMCG company having a Nationwide presence that supply FMCG products which are usually not long-lasting products and regarded as CPG (Packaged Goods for Consumers). So, to minimize the wastage and maximize the sales it is very crucial to monitor sales and inventory and take necessary action. All this business is managed Using Dynamics 365 CRM and data is Stored in its unified storage location Dataverse. Using this Data, company wants to develop a Power BI Report which helps them to have a bird eye’s view of their business and track the Sales and Inventory of their products in various stores and notify the manager concerned on crossing a prescribed threshold.
Our client faced challenges in consolidating data from diverse sources, including APIs and Azure Excel sheets. To address this, they adopted Microsoft Fabric and implemented a layered data transformation approach—Bronze, Silver, and Gold layers—ensuring structured, efficient, and seamless data management:
Bronze Layer: Raw data was loaded and stored as-is, with notebooks fetching data from the sources.
Silver Layer: Specific columns were selected from the raw data for analysis, and calculations were performed to prepare the data for final use.
Gold Layer: The final layer was designed for housing semantic models and final tables for analysis.
Despite this architecture, they faced significant challenges
A dynamic business environment requires real-time insights from continuously changing datasets. This case study showcases the creation of a real-time CDC dashboard using Microsoft Fabric’s integrated ecosystem. The solution streamlined data ingestion, transformation, and visualization, enabling up-to-the-minute decision-making.
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.
undefined
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.
© All Rights Reserved. Inkey IT Solutions Pvt. Ltd. 2025