Modelling for Power BI (I): data marts, KPIs and a single version of the truth

When a Power BI report does not add up, the problem is almost never the chart. It is further upstream: an indicator each area calculates its own way, a table with more detail than needed, or a formula nobody decided how to handle when the denominator is zero. This series covers how to model properly for Power BI, end to end. This first part focuses on what has to be settled before building any table: the role of the data mart and the definition of indicators. In the second part we move on to dimensional design and the semantic model.

We use insurance (policies, premiums, claims and reserves) as a running example, but the principles apply to any business.

The data mart: the bridge between the data warehouse and the business

A data mart is a subject-specific subset of the corporate data warehouse, aimed at one business area. An insurer may have one for claims, another for new business and another for technical reserves. While the data warehouse integrates information from across the organisation, each data mart offers a clean, optimised view of what one team needs.

The benefits are practical:

• Speed. Because it holds only one domain's data, queries and reports respond much faster than against the full warehouse.
• Simplicity and safe self-service. A star structure with tables such as Claims, Customer, Product and Date makes sense to a business analyst; a corporate model with hundreds of tables does not.
• Fit for the area. It can include its own calculations, such as cumulative provisions or actuarial risk segments, while still starting from the official data.
• Local governance. It inherits the quality of the corporate warehouse and adds the area's rules (for example, working only with the last five years) without touching the global model.

A well-designed data mart strikes the balance between corporate governance and departmental agility.

Dependent or independent

There are two ways to build them:

	Dependent data mart	Independent data mart
Source	The corporate data warehouse	Operational or external sources directly
Advantage	Data already integrated and cleansed: a single source of truth	Agility: you can start without a central warehouse
Risk	Assuming KPI consistency is guaranteed	Different definitions across departments

The dependent approach is generally recommended when a data warehouse exists. The independent one, associated with Kimball's incremental approach, is valid for urgent needs and growing piece by piece. Both share a trap: data coming from the same source does not guarantee that indicators are calculated the same way. Only a global KPI map, a shared glossary and data governance that keeps them alive can ensure that.

KPIs first, tables second

Before designing anything you need to be clear about what the business wants to measure. That means sitting down with the people who use the data (claims, sales and risk managers, actuaries and leadership) and going through three steps: identify the indicators they need, document how each is calculated, and validate the interpretation before implementing.

An industry's indicators look obvious until you ask about the details:

• Policies cancelled this month: does it include non-renewals or only early cancellations?
• Premiums written this quarter: gross or net of commissions?
• Loss ratio by product: (claims paid + reserves) / earned premiums, but which reserves, and as at which date?
• Average claim resolution time: from opening to closing, or to first payment?
• Quote conversion rate and customer lifetime value: over what time window?

Every unanswered question becomes a different figure in two reports.

The metrics map

The answers produce the area's metrics map: one entry per KPI that guides the whole design of the data mart and the model, and that other data marts can reuse when they share indicators.

Field	What it captures
Business definition	What the indicator measures and why it matters, in business language
Calculation formula	Numerator, denominator and filters, with no ambiguity
Required data	Data warehouse tables, fields and transformations
Business rules	Exclusions, special cases, nulls and outliers

For example, the policy cancellation rate would read: percentage of policies cancelled in the period over all policies active at the start; excludes natural non-renewals at expiry; includes only cancellations requested by the customer or due to non-payment.

A single version of the truth

Imagine the finance director asks for the customer retention rate and receives three reports with three figures: 87%, 82% and 91%. Sales counts repeat purchases, Finance looks at retained revenue and Marketing at campaign-driven loyalty. All three are "the retention rate". The outcome is familiar: mistrust in the data, hours lost reconciling numbers and decisions made on the wrong figure.

The fix is for every KPI to have a single agreed definition and for that logic to be implemented once, either in the data mart as a calculated field or in the semantic model as a DAX measure, so every report reuses it. It rests on three pillars:

1. A central metrics repository (the global KPI map), accessible to all, versioned and auditable.
2. A formal definition process, with an owner who approves each metric and its changes.
3. Consistency across data marts: same metric name, same calculation everywhere; centralised validation and automated checks in the loads.

Naming KPIs consistently

A naming convention lets the technical name explain what it represents. A scheme that works well combines four parts: FORMULA_CONCEPT_CATEGORY_PERIOD.

• Formula: Num (count), Sum, Avg, PctDiff (percentage change), Delta (relative change).
• Concept: what is measured (Cancellations, Claims, Premiums, Reserves, Balance).
• Category: product or segment (MOTOR, HOME, LIFE, HEALTH, BUSINESS).
• Period: M0 (current month), L3, L12, 0v5 (current month versus five months ago).

Technical name	Meaning
Num_Cancellations_MOTOR_M0	Motor policies cancelled in the current month
Sum_Claims_HOME_L12	Home claims amount over the last 12 months
Avg_Balance_LIFE_L3	Average Life reserve balance over the last 3 months
Delta_Reserves_BUSINESS_0v12	Change in Business reserves versus a year ago

It is not the only possible convention, and if your organisation already has one, start from it. This example assumes monthly periods; if a model mixes scales, add the unit (M3, D7).

Edge cases: the percentage change example

Change formulas look trivial:

Delta = (current value − previous value) / previous value

If reserves fall from 300 to 100 million, the change is −67%. So far, so clear. But in practice situations arise that the formula cannot interpret:

Before	Now	Formula result	Rule to agree
−200M	+100M	−150%	Moving from deficit to surplus is not a fall: "recovery" or "∞"
+200M	−100M	−150%	Report it as a total loss (−100%)
0	+100M	Division by zero	"New" or undefined growth
+100M	0	−100%	Total loss, clear interpretation

None of these decisions is technical: the business makes them and they are documented in the KPI map. They are then implemented with conditional logic in SQL (CASE WHEN) or DAX:

Delta Reserves 0v2 =
VAR Current = [Reserve Balance]
VAR Previous = CALCULATE ( [Reserve Balance], DATEADD ( 'Date'[Date], -2, MONTH ) )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( Previous ) || Previous = 0, BLANK (),   -- from nothing to something: shown as "new"
        Previous < 0 && Current >= 0, BLANK (),           -- sign change: agreed rule
        Previous > 0 && Current < 0, -1,                  -- total loss
        DIVIDE ( Current - Previous, Previous )
    )

And if several data marts calculate changes (claims, premiums, reserves), they must all treat these cases the same way.

Other tricky indicators

Percentage change is just one example. Almost every common metric has cases to decide:

• Conversion rate: what if there are no visits or quotes? Does the same customer count twice? Over what window?
• Year-on-year growth: new products with no prior year, discontinued products, seasonal businesses with zero months.
• Average resolution time: open cases, outliers, business or calendar hours, reopened files.
• Margin: negative revenue from refunds, periods with no revenue, which costs are allocated.

A template to document every metric

For the map to be useful, every entry follows the same template:

• Identification: technical name, business name, category, owner, approval date and version.
• Business definition: what it measures, why it matters, who uses it and which metrics it relates to.
• Technical specification: exact formula, sources, filters, aggregation level, refresh frequency and available history.
• Special cases: nulls, zero denominators, negatives, outliers, sign changes and suspicious data.
• Validation rules: expected range, cross-checks (retention + churn ≈ 100%), trends and reconciliation with another source.
• Implementation notes: where it is calculated (SQL or DAX), performance considerations, known limitations and change history.

How to start

1. Pick the most critical metrics, around twenty.
2. Bring together every area that uses them and agree their definitions.
3. Document them with the template before implementing anything.
4. Add automated validations to the loads.
5. Train teams to use the metrics repository.
6. Set up a data governance committee to approve additions and changes.

With the indicators defined, we know what data is needed and how it must behave. In the second part we look at how to derive the data mart from the data warehouse, design the star schema and turn it into a Power BI semantic model. To go further on modelling, see our guide to data modelling best practices in Power BI. And if you need help putting your indicators in order, at Bidatia we build dashboards with Power BI and Microsoft Fabric starting from exactly this point.