From an English question to a governed Power BI report
An LLM pipeline that turns a natural-language question into governed SQL, then into a real semantic model and report.
AI Data Engineer: design and implementation
The problem
Business users asked questions in English; getting an answer meant a ticket, an analyst, and days of waiting. Text-to-SQL tools promised to close that gap but stopped at a result set, which is not what anyone actually wanted. People wanted a report they could keep, in the tool they already used.
What I did
- Designed the pipeline from question to finished Power BI artefact.
- Integrated Databricks Genie so generated SQL runs inside Unity Catalog governance rather than around it.
- Built the result-profiling step that decides which visual a given shape of data deserves.
- Implemented the YAML to TMDL conversion that produces a real, openable Power BI project.
Architecture
- The question goes to Databricks Genie, which generates SQL against Unity Catalog. Governance is inherited, so a user cannot ask their way past a permission.
- The result set is profiled before anything is drawn: cardinality, types, whether there is a time dimension, how many measures against how many dimensions.
- Visual selection is a rules layer over that profile, not a guess by the model. A single scalar becomes a card; a measure over time becomes a line chart; a measure over a category becomes a bar.
- The chosen structure is emitted as YAML, then converted to TMDL and PBIR, the file formats Power BI Desktop actually opens.
- The output is a project, not a screenshot. Someone can open it, change it and own it.
What went wrong first
The model should not choose the chart
Letting the LLM pick a visual produced confident nonsense: pie charts of time series. Moving selection into deterministic rules over a profiled result set made the output predictable, and made failures explainable.
TMDL is unforgiving
Power BI's project format fails closed: a near-miss produces a file that will not open at all. Generating it reliably meant treating the format as a contract to validate against, rather than a template to fill in.
Governance has to be inherited, not reimplemented
It would have been easier to run generated SQL with a service principal. Routing through Genie instead means the answer respects the asker's own permissions, which is the difference between a demo and something you can deploy.
Results
- less manual report creation time
- ~60%less manual report creation time
- query-to-visual accuracy
- 92%query-to-visual accuracyon the internal evaluation set
- governance bypasses
- 0governance bypassesSQL runs under the asker's own permissions
The point was never to remove analysts. It was to stop them spending their week rebuilding the same four charts, so the questions that actually need a human get one.