Microsoft Fabric Part 2 – Where Raw Data Becomes Business Intelligence and Conversational AI

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.

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.

The bronze_to_silver notebook is a parameterised PySpark notebook that promotes raw Bronze data into cleansed Silver tables. For each entity it:

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

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:

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

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:

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

The finished Resource Time Tracking semantic model exposes Gold-layer fields ready for report authoring in Power BI:

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

Beyond traditional BI, the built-in Fabric Data Agent extends the framework into conversational AI. Key characteristics of 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

Transforming raw data into business intelligence has traditionally required multiple tools, multiple teams, and a significant gap between when data arrives and when it becomes useful. For organizations running Dynamics 365, that gap has often meant delayed reporting, inconsistent numbers across teams, and business users waiting on data specialists for answers to straightforward questions.

The transformation and reporting layer built on Microsoft Fabric closes that gap end to end. Two PySpark notebooks handle every entity’s journey from Bronze to Gold — consistently, without duplication of logic, and without rewriting code for each new entity. A Direct Lake semantic model means Power BI reports reflect the latest data the moment the Gold layer is updated. And the built-in Fabric Data Agent means business users can ask questions of that same governed data in plain English, without leaving the platform or raising a request.

The result is a complete data and AI layer — built on a single platform, driven by configuration, and designed to grow as the organization’s data needs grow.