Modelling for Power BI (II): from the data warehouse to the semantic model

In the first part of this series we settled the hardest part: what the business wants to measure and how each indicator is defined, without ambiguity. With that KPI map we know what data we need. Now it is time to build: derive the data mart from the data warehouse, design its structure and turn it into a Power BI semantic model that anyone can explore with confidence.

We keep the insurance example and, at the end, apply everything to a complete motor claims case.

Deriving the data mart from the data warehouse

When the company already has a corporate data warehouse (on Exadata, Cloudera, Azure Synapse or Microsoft Fabric), the recommended strategy is top-down: build the data mart from that central store. There are five steps:

1. Select and filter. Only the tables and fields the area needs: for claims, claim records, related policies and policyholders, not HR data. And only the useful rows: the last ten years and the relevant lines of business.
2. Transform for the context. Area-specific cleansing and fields that make analysis easier, such as a claim severity band (minor, moderate, severe) or the number of claims per policy. The balance lies in doing calculations upfront without losing flexibility.
3. Aggregate where it pays off. If almost every report works monthly by product, an aggregate table or materialised view speeds queries up enormously.
4. Load incrementally. An initial load with the full history, then periodic loads that bring only what is new: yesterday's claims, new or recently cancelled policies.
5. Isolate the environment. The data mart lives in its own schema or database, with its own permissions and indexes, usually on the same platform as the warehouse to avoid moving large volumes.

The benefit is clear in a regulated industry like insurance, where premiums, reserves and claims are audited: the data mart starts from data that is already validated and reconciled, every report comes from the same base, and lineage can be traced from source to dashboard. If the data warehouse is the whole house's pantry, the data mart is the tray with just the ingredients for one recipe, in the right portions and with some already pre-cooked.

Dimensional design: facts, dimensions and granularity

Most analytical data marts follow Kimball's dimensional modelling, which organises data into two kinds of table:

• Fact table: the centre of the model. Each row is a business event and holds numeric measures (amounts, counts) and keys to the dimensions.
• Dimensions: descriptive tables that provide context (who, what, when, where, why) and allow filtering and grouping.

They relate one-to-many: each dimension row describes many facts. Design happens in three steps.

Step 1: decide the granularity

Granularity is the level of detail of each fact table row, and it is the first decision. In a claims data mart:

• Is each row a claim? Probably yes, if we want to count claims, see each one in detail and look for patterns.
• Do we need more detail? If a claim affects several covers, the unit could be "cover within a claim", although the consolidated claim is the usual choice.
• Could we aggregate further? One row per month and product makes the model lighter, but removes daily and case-by-case analysis.

The general rule: store the lowest level that will be analysed or that is needed to recalculate the KPIs. You can always aggregate later in queries; you cannot recover lost detail. In our example, the granularity is the individual claim, identified by its number.

Step 2: define the fact table

The Fact_Claims table would hold:

Field type	Examples
Identifiers and keys	ClaimID; keys to policy, customer, product, cause, garage and region
Numeric measures	AmountPaid (indemnity), ReserveSet (technical provision), ClaimCount
Dates	OccurrenceDate, OpenDate and CloseDate (empty while open)

Step 3: choose the dimensions

Every "by what" in the business questions ("how does cost vary by region?") is a dimension:

• Date: the calendar with year, quarter, month and day. Practically mandatory.
• Product: lines and policy types with their hierarchy (Motor within General insurance).
• Customer: type, sales channel, age, tenure, risk profile.
• Geography: country, region and town of the claim or policyholder.
• Cause: collision, theft, fire; in health, the type of treatment.
• Provider: garages or hospitals, to analyse spend and renegotiate.

The result is a star schema: the fact table at the centre and the dimensions connected directly, in a single layer. Hierarchies are stored as columns of the dimension itself rather than spread across several tables; the snowflake is kept for extreme cases.

Design best practices

• Numeric (surrogate) keys. Relationships use integer identifiers, not text: they take less space and joins are faster.
• An "unknown" row in every dimension. For example, ProductID = −1. Facts without a match point to it, nulls are avoided and integrity is preserved.
• Each attribute in its own dimension. If the line of business depends on the product, it belongs in Product, not repeated in Customer.
• Document derivations. A calculated field such as a severe-claim flag has its logic documented.

And one rule sums it all up: the design serves the KPIs. If the loss ratio is claims paid over premiums, the model needs both, usually with a second premiums fact table that shares dimensions with the claims one.

From the data mart to the Power BI semantic model

With the tables created and loaded, we build the semantic model: the layer that translates the data mart into the language of report builders.

1. Connect and import. Import mode if the volume is manageable; if the data mart is in Microsoft Fabric, Direct Lake gives similar performance without copying data.
2. Rename for people. Fact_Claims becomes "Claims" and Dim_Product becomes "Product", with spaces and proper words.
3. Relate. Dimensions to facts, one-to-many with single-direction filtering.
4. Create the DAX measures. One for each indicator in the KPI map.
5. Tidy up. Hierarchies (year, quarter, month, day), technical fields hidden, currency and percentage formats.
6. Verify and publish. Check that figures match the source and publish the model as a central dataset for the whole organisation.

DAX measures: the heart of the model

Measures express the agreed formulas, so every visual shows the same value:

Claim Count = COUNTROWS ( Claims )
Total Paid = SUM ( Claims[Amount Paid] )
Average Cost = DIVIDE ( [Total Paid], [Claim Count] )

Loss Ratio % = DIVIDE ( [Total Paid], [Total Earned Premiums] )

Closure Rate =
DIVIDE (
    CALCULATE ( [Claim Count], Claims[Status] = "Closed" ),
    [Claim Count]
)

Total Paid YoY =
VAR Current = [Total Paid]
VAR Previous = CALCULATE ( [Total Paid], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN DIVIDE ( Current - Previous, Previous )

Notice that complex measures are built on the basic ones: if the definition of "Total Paid" changes tomorrow, all the others inherit it.

Where should each KPI be calculated?

It is one of the decisions with the greatest impact on performance and governance:

	In the data mart (SQL)	In the model (DAX)
When	Heavy calculations on large volumes, logic joining several sources, indicators used by other tools, long-period aggregates that rarely change	Combinations of measures that must recalculate with every filter, for maximum analytical flexibility
Example	Regulatory indicators; a monthly table by line with the loss ratio precalculated	A loss ratio that responds to filters by product, year or region without precalculating every combination

The practical guide: if the KPI is an aggregation of the fact table or a simple combination of measures, use DAX. If it needs to combine sources or scan large histories, use the data mart. Keeping the data mart simple and leaving customisation to the model gives you the best of both worlds.

Case study: motor claims

The Motor Claims area wants to understand trends in claims and costs to manage indemnities better. The claims director, the indemnities manager, the fraud team and financial control are involved. Five years of history are analysed with a daily incremental load.

There are six business questions: how many claims there are each month and how the amount paid evolves; how they split by type and severity; what the average cost per region is; which garages account for the most spend; what percentage has detected fraud; and how long each type of claim takes to resolve.

1. KPIs defined in the map: claim count, total paid, average cost, fraud % and average closure time.
2. Source data: claims and policies from the corporate data warehouse, with their date, cause, garage, geography, product and customer dimensions.
3. Data mart: its own schema with the Motor claims table (filtered by product) and its dimensions. Days to close is calculated in SQL, because it is a fixed attribute of each claim.
4. Dimensional model: a star with individual-claim granularity and six dimensions.
5. Power BI model: relationships, DAX measures and hierarchies, and a report with the monthly trend, analysis by cause, a regional map and KPI cards.

Checklist

• Is every KPI in the report in the metrics map, with its formula and special cases?
• Does the granularity answer every business question?
• Is the model a star, with numeric keys and an "unknown" row in every dimension?
• Is each measure defined once, with the others built on top of it?
• Do the figures match the source before publishing?

Modelling well is what separates a dashboard people use from one nobody trusts. To dig deeper into model performance, see our data modelling best practices in Power BI. And if you are preparing a data mart or reviewing a model that does not add up, at Bidatia we build dashboards with Power BI and Microsoft Fabric from the metrics map through to the final report.