Microsoft Fabric Part 2 — Data Transformation, Real-Time Reporting and AI for D365 Summary With D365 data landed in Bronze (covered in Part 1), this blog covers the next three stages — transformation through a Medallion architecture, Direct Lake reporting in Power BI, and conversational AI via the Fabric Data Agent. Two generic PySpark notebooks — bronze_to_silver and silver_to_gold — transform raw Bronze data into cleansed Silver and business-ready Gold tables without entity-specific code. A Direct Lake semantic model built on the Gold layer gives Power BI real-time reporting without an import step or scheduled refresh cycle. The built-in Fabric Data Agent, grounded on the Gold layer, gives business users natural-language access to governed data — no separate AI platform, no additional licensing. The result is a complete data and AI layer — ingestion, transformation, reporting, and conversational AI — built on a single platform, driven by configuration, and designed to grow. Table of Contents 01 Quick Recap — Part 1 02 Technical Deep-Dive 03 Business Impact 04 Frequently Asked Questions 05 Conclusion Quick Recap — Part 1 In Part 1, we covered how a config-driven ingestion framework on Microsoft Fabric pulls data from any D365 Finance & Operations entity and lands it into the Bronze layer of a Fabric Lakehouse — using just two generic pipelines and a master CSV config file. No entity-specific code, no new pipeline per entity, and reliable incremental upsert loading driven entirely by configuration. Bronze is the raw layer — data arrives exactly as it comes from D365, unmodified. That is intentional. The Bronze layer is not for reporting. It is the foundation — a reliable, auditable record of everything that came in. What happens next is where the data becomes useful. Where Part 2 picks up Bronze Layer (raw — covered in Part 1)  →  Silver Layer (cleansed)  →  Gold Layer (business-ready)  →  Power BI (Direct Lake reporting)  +  Fabric Data Agent (conversational AI) This part walks through how two generic PySpark notebooks transform Bronze data into clean, business-ready Gold tables, how a Direct Lake semantic model exposes that Gold layer to Power BI without a refresh cycle, and how the built-in Fabric Data Agent gives business users conversational access to the same governed data. Technical Deep-Dive Bronze → Silver — Cleansing and Column Mapping The bronze_to_silver notebook is a parameterised PySpark notebook that promotes raw Bronze data into cleansed Silver tables. For each entity it: 1Reads the Bronze table for the configured entity 2Strips technical column prefixes added by D365 3Consults the b2s_columnconfig table to determine which columns are active and how they should be aliased 4Applies type casting and standardisation 5Writes the result to the Silver schema The same notebook promotes any Bronze table to Silver — changing the entity parameter is all it takes to onboard a new one. bronze_to_silver — strips prefixes, reads column config, applies aliasing. One notebook handles every Bronze-to-Silver promotion Silver → Gold — Business Logic and Joins The silver_to_gold notebook builds business-ready Gold tables by joining multiple Silver entities and applying business logic. For the Resource Time Tracking Gold table it: 1Joins six Silver entities — bookableresources, msdyn_timeentries, msdyn_projecttasks, msdyn_projects, accounts, and msdyn_transactioncategories 2Derives fields including Category, EntryDate, Weekday, TimeSheetStatus, and TimeSpent 3Translates D365 status codes into readable values 4Writes the result as a Delta table to the Gold schema The Gold table is the single, trusted, business-ready version of the data — the only layer exposed to reporting and AI consumers. silver_to_gold — joins Silver entities, applies business logic, and writes a clean Gold table ready for reporting and AI querying Direct Lake Semantic Model — Creation Once the Gold layer is ready, a Direct Lake semantic model is created directly from the Lakehouse home screen. The setup follows three simple steps: 1Click New semantic model from the Lakehouse toolbar 2Select only the Gold table — Bronze and Silver remain hidden from report authors 3Confirm — the model is created in Direct Lake mode, reading Delta Parquet files directly from OneLake No import step. No scheduled refresh. No data duplication. Only Gold is selected for the semantic model — reporting consumers never see raw or intermediate data Direct Lake Semantic Model — Power BI The finished Resource Time Tracking semantic model exposes Gold-layer fields ready for report authoring in Power BI: Category, Customer, EntryDate, ProjectName, ProjectTask ProjectType, ResourceName, TimeSheetStatus, TimeSpent Weekday, Week Number, Year Because it runs in Direct Lake mode, reports always reflect the latest Gold data without anyone needing to trigger a refresh. The finished semantic model in Power BI — Gold fields available for report authoring immediately, with no refresh cycle required Fabric Data Agent Beyond traditional BI, the built-in Fabric Data Agent extends the framework into conversational AI. Key characteristics of the agent: Grounded exclusively on the Gold layer — only curated, business-ready data is exposed Supports natural-language questions — trend analysis, outlier detection, resource summaries, and more Runs within the same Fabric workspace — no separate AI platform, no additional licensing, no integration work Business users and data teams access the same governed data whether they use Power BI or the agent The Fabric Data Agent grounded on the Gold layer — business users ask questions in plain English on the same data that powers Power BI Business Impact 1Consistent transformation governance — two notebooks enforce the same cleansing and business logic across every entity, from Bronze through Gold, with no per-entity exceptions or inconsistencies 2Near real-time reporting — Direct Lake semantic models read Gold Delta tables directly from OneLake, eliminating the import and refresh cycle that traditional Power BI datasets require 3Single trusted layer for all consumers — both Power BI reports and the Data Agent draw from the same governed Gold layer, ensuring consistent numbers across structured reporting and conversational queries 4Built-in AI access at no extra infrastructure cost — the Fabric Data Agent gives business users conversational access to governed data without a separate AI platform, additional licensing, or integration work 5End-to-end traceability — every … Continue reading Microsoft Fabric Part 2: Where Raw Data Becomes Business Intelligence and Conversational AI →

Microsoft Fabric Part 1 — Config-Driven D365 Ingestion into the Lakehouse Summary Building a new pipeline for every new data entity is one of the most common and quietly expensive habits in enterprise data engineering — this blog shows how to eliminate it entirely. A config-driven ingestion framework on Microsoft Fabric pulls data from any D365 Finance & Operations entity into the Bronze layer of a Fabric Lakehouse using just two generic pipelines and one master CSV config file. Adding a new entity requires no new pipeline, no new code, and no deployment — just a single row added to a configuration file. The framework handles OAuth authentication, OData pagination at 5,000 records per page, incremental watermark filtering, and key-based upsert loading — all driven by configuration. This is Part 1 of a two-part series. Part 2 covers transformation through Bronze → Silver → Gold, Direct Lake reporting in Power BI, and conversational AI via the Fabric Data Agent. Table of Contents 01 Let’s Start Here 02 The Challenge 03 The Solution 04 Technical Deep-Dive 05 Business Impact 06 Conclusion Let’s Start Here Every data engineering team eventually hits the same wall. You build a pipeline for one entity — opportunities, invoices, time entries. It works well. Then another entity gets added, and another. Before long, you have a collection of pipelines that each do roughly the same thing but are written differently, maintained separately, and break in different ways. The question is not whether this happens — it always does. The question is whether your framework is designed to prevent it from the start. This is Part 1 of a two-part series on building an end-to-end data engineering framework on Microsoft Fabric connected to Dynamics 365. Before we get into the detail, here is how the full architecture fits together: The Full Architecture — D365 to AI D365 / F&O  →  Bronze Layer (raw ingestion — this blog)  →  Silver Layer (cleansed)  →  Gold Layer (business-ready)  →  Power BI Reports + Fabric Data Agent Part 1 covers the ingestion step — pulling data from D365 into the Bronze layer of the Fabric Lakehouse using a config-driven pipeline framework. Part 2 covers everything after Bronze — transformation through Silver and Gold, Direct Lake reporting in Power BI, and conversational AI through the Fabric Data Agent. The Challenge Traditional data engineering approaches treat each entity as a unique problem. A pipeline is built for accounts, another for contacts, another for time entries. Each has its own authentication logic, its own pagination handling, its own incremental-load approach. This creates a set of problems that compound over time: 1Onboarding a new entity requires a new pipeline build, test cycle, and deployment — work that can take days 2Incremental load logic is duplicated across pipelines, often inconsistently, leading to missed records or duplicates 3When upstream systems change — authentication, API structure, column names — the blast radius is wide 4There is no single place to look to understand what is being ingested and how The answer is not better pipelines. It is a framework where the pipeline is generic and the entity-specific details live in configuration. The Solution — Config-Driven Ingestion on Microsoft Fabric The ingestion layer of this framework runs entirely from a single Fabric workspace containing one Lakehouse, two pipelines, and one master configuration file. Every entity, its key column, its watermark column, and its source details live in a CSV — not in pipeline code. The Lakehouse is structured using a Medallion architecture — three table layers: Bronze (raw data exactly as it arrives from D365), Silver (cleansed and standardised), and Gold (business-ready, joined, and logic-applied). The ingestion framework is responsible for the first step — getting data from D365 into the Bronze layer reliably, incrementally, and without entity-specific code. The Fabric workspace — one Lakehouse, two pipelines, and a config file that together handle ingestion for any number of D365 entities Adding a new entity to the framework means adding one row to a CSV. No new pipeline. No new deployment. No code change. Technical Deep-Dive The Lakehouse and Config Files Inside CRM_Lakehouse, the Tables area is organised by Medallion layer. The Files area holds the configuration CSVs that drive every pipeline and notebook. Three files do all the work: ingestion.csv — controls which entities are ingested, how they are filtered, and where they land b2s_columnconfig.csv — controls Bronze-to-Silver column mapping, aliasing, and type casting gold_fixed.csv — controls Gold-layer business logic and join definitions The Lakehouse holds both the data layers and the config files that drive every pipeline and notebook — everything in one place The Master Config — ingestion.csv Every entity is described in a single row of ingestion.csv. Each row contains: Entity name and OData logical name — what to call and where to find it in D365 Primary key column — used for upsert to prevent duplicates Incremental filter column — the watermark field used to fetch only changed records Partition key — supports multi-source ingestion Checkpoint — stores the last watermark value applied so each run picks up exactly where the last one left off The pipeline reads this file, builds its incremental filter dynamically, and upserts records using the configured key. No entity-specific code exists anywhere. ingestion.csv — every entity is a single self-describing row. Adding a new D365 entity is a config change, not a code change The Trigger Pipeline The Data Ingestion Trigger pipeline is the orchestrator. It works in three steps: 1Lookup — reads ingestion.csv and returns the full list of configured entities 2ForEach — loops through every entity row in the config 3Invoke Pipeline — calls the Entity pipeline once per row, passing the full config object as a parameter One lightweight orchestrator pipeline controls an unlimited number of entities — no changes needed when a new entity is added. The Trigger pipeline — Lookup config, loop through entities, invoke the Entity pipeline once per row The Entity Pipeline The Data Ingestion Entity pipeline is the reusable worker. It … Continue reading Microsoft Fabric Part 1: Building a Config-Driven Data Ingestion Framework for Dynamics 365 →