A single reporting model for a multi-entity retail group

Client context

A retail group operates several entities acquired at different times, each with its own point-of-sale and finance systems. Group reporting is assembled monthly by consolidating extracts, and management meetings begin by reconciling differences between entity figures.

The challenge

Each entity defines core measures slightly differently, and the differences are legitimate in local context. A single definition imposed centrally would be inaccurate for local operations. The group needs comparable figures without invalidating local reporting, and needs to trace any figure back to source.

Objectives

• Comparable group figures without invalidating local reporting
• Named owners and written definitions for core measures
• Lineage from any published figure back to source
• Reduced manual effort in monthly pack preparation
• Row-level security aligned to entity and role

The solution

Core measures are defined at group level with named owners and written definitions, and entity-specific variants are modelled explicitly rather than hidden. Source data lands in Microsoft Fabric through a medallion structure, with conformed dimensions built at the silver layer. One shared semantic model serves group and entity reporting, with row-level security aligned to entity and role. Lineage is published through Microsoft Purview so any figure can be traced. Definition changes follow a change process with dependent report identification.

Architecture

• Microsoft Fabric lakehouse with bronze, silver and gold layers
• Azure Data Factory pipelines for entity source ingestion on agreed schedules
• Conformed dimensions for product, location, calendar and customer at the silver layer
• Shared Power BI semantic model with row-level security by entity and role
• Microsoft Purview for catalogue, lineage and definition documentation
• Change process for measure definitions with dependency analysis before release