Analysing and serving · Chapter 19

Reporting, Semantics, and Power BI

Almost every organisation we meet has a reporting layer that works and a set of numbers that do not agree, and the two facts are related. This chapter is about the layer where a business concept gets its definition, and about the tool most of our clients already own.

The semantic layer belongs in the platform #

Unity Catalog Business Semantics has two parts. Metric views hold the structured definitions, meaning the measures and dimensions that make up a business concept expressed in SQL. Agent metadata holds the contextual knowledge that AI agents need in order to interpret a question the way a person in that department would interpret it.

The important property of a metric view is where it lives rather than what it does. It sits in Unity Catalog, inherits the same governance as the tables under it, appears in lineage, and can be queried by any engine or tool that speaks SQL. A LookML model can only be read by Looker, and a DAX model can only be read by Power BI. Both are perfectly good pieces of engineering, and both make the definition invisible to everyone standing outside the tool.

That invisibility is the cause of most of the symptoms people ask us to fix. Dashboard sprawl happens because a team cannot find or reuse an existing definition, so they build their own. AI answers are wrong because the model has the tables but not the meaning. Two directors disagree in a meeting because two tools computed the same word differently and neither is wrong within its own boundary.

What you get when the definitions move #

AI/BI Dashboards run in place on the lakehouse rather than over an extract, using Unity Catalog for access control, lineage and metadata. Because they sit on metric views, the definitions stay consistent with everything else that reads them, and common measures often run faster than the equivalent ad hoc SQL because of the materialised structure underneath. Databricks also markets AI/BI without per-user licence limits, which matters more than it first appears and which we return to below.

AI/BI Genie is the conversational side of the same foundation. A Genie space is scoped to one subject area and grounded in Unity Catalog, and metric views give it structured definitions instead of guesses. The Genie knowledge store is where authors curate table and column descriptions, synonyms, sampled values, value dictionaries and structured SQL instructions, and Genie learns from thumbs up and thumbs down feedback so that good answers can be captured as reusable knowledge snippets. In our experience the difference between a Genie space people trust and one they abandon is almost entirely the quality of that curation, not the model behind it.

Dashboard discipline #

A dashboard should read gold tables and metric views and contain no business logic of its own. The moment a widget carries its own SQL, you have created a calculation with no review, no test and no lineage, sitting in a place nobody thinks to look during an audit.

Dashboards also cost money when nobody is watching. A scheduled refresh runs whether or not the audience still exists, and we have found dashboards on client estates refreshing hourly for an owner who left two years ago. Query history is available as system tables, so identifying dashboards with no viewers in the last quarter is a query rather than a project. We run that review quarterly and retire aggressively, because an unopened dashboard costs compute now and costs credibility later when somebody finally opens it.

The Power BI question #

We want to be fair here, because the argument is often made badly. Power BI is an excellent visualisation tool with a mature ecosystem, a large trained user base, and a Databricks connector that works well. We are not going to claim that AI/BI draws better charts, and we would not ask a client to abandon a working reporting estate to prove a point about architecture. The argument is about four specific things.

The first is visibility. Business logic written in DAX inside a Power BI model cannot be read by a SQL analyst, a data scientist or an AI agent, and it cannot be traced in lineage back to the source columns it depends on. Move those definitions into metric views and Power BI continues to work exactly as before, except that it now reads governed definitions rather than owning them privately.

The second is copies. Import mode creates a second copy of the data with its own refresh schedule and its own security model, which is precisely the divergence the platform exists to remove. DirectQuery against a SQL warehouse keeps one copy of the data and one set of numbers.

The third is security. Row-level security defined in the platform applies to every consumer, including the notebook, the API and the agent. Row-level security defined in Power BI applies to Power BI, so anybody who reaches the data another way is outside it.

The fourth is licensing, and it is the one clients find most uncomfortable when they think it through. Per-seat pricing quietly shapes who is allowed to see data, which means a commercial model is making a governance decision that ought to belong to the business.

Read that table honestly and Power BI wins two rows outright. Our position is that the rows it wins are presentation rows and the rows it loses are architecture rows, and presentation is easier to change later than architecture is.

Beyond the basics #

The following matter mainly at the scale of hundreds of reports and multiple business units, where the failure modes are organisational rather than technical.

Certification, without the certification feature. Large Power BI estates use endorsed and certified datasets to tell users which model to trust. The equivalent with metric views is a naming and ownership convention plus catalog tags, where a certified metric view has a named owner, a test suite, and a tag that a search can filter on. We prefer this because the tag lives with the object rather than with the tool, so certification survives a change of BI vendor.

Managing hundreds of dashboards. We organise dashboards by domain rather than by team, give each domain a single owner accountable for retirement as well as creation, and require that a new dashboard names the metric views it reads. That last rule alone slows sprawl considerably, because it makes the reuse question unavoidable at creation time rather than at audit time.

Performance for reporting workloads. Three levers cover most of it. The result cache answers repeated identical queries without touching data, which is why a well shaped dashboard costs almost nothing on its second viewer. Materialised structures behind metric views turn a repeated aggregation into a computed one. Warehouse sizing should follow query shape, so a warehouse serving many small concurrent dashboard queries wants more clusters at a modest size, while one serving a few heavy analytical queries wants a larger size and less scaling. SQL, Dashboards, and Sharing covers those settings in detail.

Migrating a large estate incrementally. A big bang migration of a Power BI estate is the most reliable way we know to lose organisational goodwill. The sequence we use starts with inventory and retirement, because a surprising share of reports have no current audience and should not be migrated at all.

Excel users are real. Finance runs on Excel, this is not going to change, and pretending otherwise simply pushes the work into files nobody governs. Excel connects to a SQL warehouse over ODBC and can query metric views directly, which means a pivot table can be built on the governed definition rather than on a CSV somebody exported last Thursday. We treat a supported Excel path as a governance win rather than a compromise.

The pattern in that table is the same one running through the whole manual. The other clouds each do the job well inside one product, and the gap only appears when a second consumer of the same definition turns up, which today is usually an AI agent.

Row-level security has a second, harder form when the audience is customers rather than employees, where each tenant must see only their own data and the reporting layer becomes part of the product. That is a different design problem with different failure modes, and it has its own chapter in Multi-Tenant and Per-User Reporting.

Table of Contents