Case study

One governed model across a multi-brand retail group

Multi-brand retail

A retail group of several brands, each with its own systems and its own answer to what a margin is. One governed Fabric model now spans them, with one definition of revenue, margin and inventory.

Read the engagement brief(opens in a new tab)
Period
10 months
Company
500–1,000 people
A schematic: the separate reports this engagement started from, the one consolidated reporting layer that replaced them, and the technologies underneath.

3 answers to what a margin is, replaced by one

Every brand could report on itself, but the group could not compare them without a week of manual work. We replaced an 8-year-old SQL Server warehouse and a nightly Excel chain with Microsoft Fabric, and one definition per metric made the comparison a filter selection.

definition of revenue, margin and inventory
1
sales channels: stores, online shops, wholesale
3
semantic models on one lakehouse
4
months, as lead architect with a delivery team
10

Anonymised by sector and size, because the confidentiality that protects this client would protect you too. Sector and outcomes are real; identifying detail is not.

Before and after

Before and after the engagement

3 definitions

  • Brand A marginIts own definition, in its own reporting
  • Brand B marginIts own definition, in its own reporting
  • Brand C marginIts own definition, in its own reporting

1 definition

  • One margin definitionDecided once, upstream, in the governed model
  • Genuine: sales channelStores, online shops and wholesale, modelled as a dimension
  • Genuine: brand differenceCarried in the model and labelled where it genuinely exists
  • Habit onlyRemoved: one definition wins
3 brands answering what a margin is 3 ways, resolved into one definition. The differences that are real are modelled and labelled; the ones that were habit are removed.

The engagement in full

Client
A multi-brand retail group of 500 to 1,000 people, with around 50 stores, 3 online shops, a wholesale business and around 200 BI consumers.
Situation
Every brand could report on itself and the group could not report on any of them together without a week of manual work. That made group-level questions expensive enough that people stopped asking them, which is the quiet cost nobody puts in a business case.
Mandate
Introduce Microsoft Fabric from scratch in place of an 8-year-old SQL Server warehouse and a nightly Excel chain, and give the group one answer per metric.
What we built
  • FoundationA review of each brand’s reporting that showed where the brands’ figures could not be compared, margin first, and ended in a fix-first list.
  • ActivationOne governed semantic model spanning the brands, with a single definition per metric and the brand differences that genuinely exist modelled rather than argued about.

Comparison as a filter selection

Once revenue, margin and inventory were defined independently of the channel, consolidating the group stopped being a piece of work. The group, one brand or one channel is now a filter on the same model. Before, a week of manual consolidation; now, a filter selection.

A customer master as its own data product

Customer identity resolution was built as its own data product, so one customer is one record whichever channel they buy through: store customers from the tills, online shop customers from 3 shops and wholesale accounts from the wholesale business, resolved into a persistent customer master.

Store revenue in near real time

Till data streamed into a KQL database replaced a 2-day reporting delay on store revenue.

  1. Store tillsAround 50 stores
  2. EventstreamEvents carried as they happen
  3. KQL databaseReal-Time Intelligence
  4. Store revenueWithin the trading day

How it was delivered

Delivered as lead architect with a specialist delivery team, and handed over with runbooks and Excel enablement.

  1. .pbip under GitFrom day one
  2. Optimise firstBefore any capacity increase
  3. RunbooksFor the client’s team
  4. Analyze in ExcelEnablement at handover

The architecture

Sources: Store tills · Online shops · Wholesale · SAP and Salesforce. Processing: Databricks · Mirrored catalog · Eventstream. OneLake: 4-layer medallion · KQL database · Customer identity. Semantic models: Finance · Commercial · Executive · Marketing and Customer. What reads it: Power BI · Excel
Databricks stays the processing engine and is mirrored into OneLake rather than copied. The semantic models read the lakehouse over Direct Lake, with no nightly import.

Architecture decisions

DecisionRejected alternativeWhy
One model spanning the brandsOne model per brandFaster to build, but it keeps the reconciliation problem. The comparison is the deliverable.
Differences modelled, not flattenedOne margin forced on every brandA genuine difference is modelled and labelled; one that was habit is removed.
Databricks kept and mirroredRebuilding processing in FabricMirrored cross-tenant into OneLake, so Power BI reads it over Direct Lake with no nightly import.
Group truth owned apart from consumer viewsOne layer serving every useA consumer view can change without touching the group figures.

What the client has today

  • One governed semantic layer across the brands, with the real differences modelled.
  • Group-level comparison as a filter selection, without the week of manual consolidation.
  • Store revenue within the trading day instead of 2 days later.
  • A persistent cross-channel customer master, built as its own data product.
Where it left them
The group is compared by brand or by channel with one click, on figures that mean the same thing in every brand.
Stack
  • Microsoft Fabric
  • Direct Lake
  • Databricks
  • Eventstream
  • KQL Database
  • Power BI
  • DAX
  • SAP
  • Salesforce
  • Azure DevOps
All case studies

The other engagements

  • Retail

    Foundation then Activation then Acceleration alongside Stewardship

    Store revenue, executive questions and forecasting on one set of definitions

    A retailer with Microsoft Fabric already in production, semantic models over Direct Lake and store revenue streaming in, who wanted forecasting, Copilot questions and anomaly detection on top of it.

    • Microsoft Fabric
    • Power BI semantic models
    • Direct Lake
    • Copilot in Power BI
    • Fabric AI Functions
    • Activator

  • Industrial manufacturing

    Foundation then Activation then Acceleration

    Governed reporting across a production network

    An industrial manufacturer reporting across a production network of sites, each with its own view of what was running.

    • Microsoft Fabric
    • OneLake
    • OneLake Security
    • Direct Lake
    • Spark
    • MES
    • OPC UA
    • Power BI
    • DAX
    • Azure Key Vault
    • Purview
    • Azure DevOps

  • Hospitality and real estate

    Foundation then Activation

    A CFO cockpit over a group that closed its books separately

    A holding group across hospitality and real estate, with entities that each closed their own books and a CFO who had to answer for all of them.

    • Microsoft Fabric
    • OneLake
    • Direct Lake
    • Databricks Mirrored Catalog
    • Fabric Data Factory
    • Power BI
    • DAX
    • Calculation groups
    • Row-level security
    • Azure DevOps
    • Azure Key Vault
    • Purview

  • Omnichannel e-commerce

    Foundation then Activation

    Finance reporting that keeps up with the day

    An omnichannel retailer whose finance leadership was steering on yesterday’s numbers in a business that changes within the day.

    • Microsoft Fabric
    • OneLake
    • Direct Lake
    • Fabric Data Factory
    • Eventstream
    • Power BI
    • DAX
    • REST APIs
    • ERP integration
All case studies

Free consultation

Know where your data stands in an hour.

A free 60-minute consultation. No pitch, no invoice. You’ll leave knowing whether your data is ready for AI, and whether we’re a good fit.

Book your free consultation(opens in a new tab)