Data Modeling & KPIs

When sales and controlling report different revenue for the same month, the data is rarely the cause. “Revenue” was calculated differently in several places: in spreadsheets, in individual reports, or in queries someone wrote at some point.

We build data models in which every metric is defined exactly once. The star schema gives the data its structure, a semantic model holds the calculations, and a KPI catalog records what each metric means and who is responsible for it. Dashboards, Excel analysis and AI assistants then draw on the same definitions. How data is loaded, historized and organized in layers inside the data warehouse is a topic of its own. Here we deal with the model on top, which business teams and tools use directly.

Facts and dimensions

In a star schema, fact tables store events with numeric values, such as sales with quantity and amount. Around them sit dimension tables that describe what you filter and group by: product, customer, region, date. That matches how BI tools work, since every visualization groups by dimensions and aggregates values from facts. Microsoft explicitly recommends the star schema for Power BI models, partly for performance and partly for clarity.

In practice a lot depends on granularity. Each fact table has a fixed level of detail, for example one row per order line. If plan and actual values at different levels are mixed in the same analysis, the totals no longer add up. A shared date dimension makes prior-year comparisons and month-end closes behave the same everywhere, and order, delivery and invoice dates use it in different roles. Relationships run over keys that appear exactly once in the dimension. When a salesperson changes region, their earlier revenue has to stay with the old region, which is usually solved in the data warehouse.

The most common design mistake is taking the ERP’s tables over one to one. A model built around the source system instead of the business teams’ questions stays hard to understand.

Where the semantic layer lives

The semantic layer holds the calculation rules for the metrics so they are not rebuilt in every report. When a definition changes, it is adjusted in one place. Anyone working exclusively with Power BI defines metrics as DAX measures in the semantic model, and reports, Excel and Copilot all use the same model.

Inside the BI tool the layer is quick to set up but applies only there. As soon as several tools, applications or assistants need the same numbers, it belongs better in the data platform or in a separate layer that describes metrics, dimensions and access rules as code. Several vendors now offer dedicated objects for this. It is an architecture decision, and we make it based on the systems you have.

The KPI catalog

A KPI catalog describes each metric so that the business and technical sides mean the same thing. An entry contains the name and the decision the metric supports, a business definition (“Revenue is the invoiced net amount excluding cancellations”), the calculation with formula, filters, time reference and source, the person or role who approves changes, and the version, meaning since when the definition applies and what changed.

Metrics rarely stand alone. Revenue, contribution margin and profit build on each other and form a metric hierarchy. We reflect these relationships in the model so that deviations can be traced to one cause. For every metric it is settled who owns it on the business side. New metrics then do not appear in an individual report but go through a short, fixed route into the catalog.

We start with the metrics that decisions rest on and let the catalog grow from there. Anyone who tries to capture everything first rarely gets to the model.

Treating metrics like code

Definitions that exist only in a tool’s interface or in individual people’s heads cannot be checked. With metrics as code they sit as files in version control such as Git. Changes are reviewed, traceable and reversible. Most dedicated semantic layer tools work this way anyway, and in Power BI, semantic models can be saved and versioned as a Power BI Project with TMDL files. Automated tests add to that, for instance checking that the sum by region equals the grand total.

So that definitions are not tied to one vendor, several vendors are working on an open exchange format. The Open Semantic Interchange (OSI) is now being developed further under the name Apache Ossie at the Apache Software Foundation. Models with tables, relationships, dimensions and metrics are described in YAML. Tools such as dbt support the format, and Microsoft is involved too. The standard is still young and does not map every aspect of a model. It does not replace clean definitions, but it can make a later switch easier.

What an AI assistant expects from the model

Assistants that answer questions about company data read the semantic model: which metrics exist, what they are called and how tables connect. The more precisely this is described, the less they guess. That includes understandable names, descriptions, synonyms (such as “turnover” for “revenue”), consistent formats and hidden helper fields. Several vendors document this explicitly as a prerequisite for good answers.

Without such a model, an assistant would have to derive calculations from raw tables itself and would reach different results depending on the question. How chat features work in detail and how they are tested is covered under Conversational Analytics.