One semantic layer instead of 30 competing reports

The situation

Sales, Marketing, Finance and Compliance at Axi each kept their own reports and spreadsheets, more than 30 of them, and many overlapped. The same metric name could carry different logic in different places, so the same question could get different answers depending on which report you opened.

The goal was one definition per metric, agreed with the team that owns it, served the same way to every dashboard.

How it came together

1. Agree definitions with owners. For each metric, write down the definition, grain, filters and owner, and have the owning team sign it off.
2. Model at the right grain. Build dbt models at the grain the business thinks in. For client metrics, that meant resolving MT4 and MT5 trading accounts to one Salesforce client ID.
3. Define each metric once. Express every metric in dbt MetricFlow: 45+ metrics across 8 semantic models.
4. Point the dashboards at it. Rebuild 20+ Power BI and Databricks dashboards on the semantic layer, and retire the 30+ overlapping reports.
5. Make it self-serve. Mentor 15+ analysts, run weekly office hours, and set dbt style, testing and pull-request standards so the layer stays trustworthy as it grows.

Key decisions

• Every metric has an owner

  A named business owner signs off each definition, so disagreements about a number have a place to end.

• Client grain, not account grain

  Counting people instead of trading accounts changed how marketing measured acquisition and cut cost per first-funded client by 18%.

• Tests before trust

  300+ dbt tests and slim CI in Azure DevOps check every change before it reaches a dashboard.

• Enable, don’t gatekeep

  Teaching analysts to build on the layer cut ad-hoc requests to the BI team by 35%.

What it looks like in code

semantic_models:
  - name: client_funding
    description: One row per client and their first cleared deposit.
    model: ref('fct_client_first_funding')
    defaults:
      agg_time_dimension: first_funded_at
    entities:
      - name: client
        type: primary
        expr: client_id   # MT4 and MT5 accounts already resolved to one ID
    dimensions:
      - name: first_funded_at
        type: time
        type_params:
          time_granularity: day
      - name: acquisition_channel
        type: categorical
      - name: region
        type: categorical
    measures:
      - name: first_funded_client_count
        agg: count_distinct
        expr: client_id
      - name: first_deposit_usd
        agg: sum
        expr: first_deposit_amount_usd

metrics:
  - name: first_funded_clients
    label: First-funded clients
    description: Clients whose first deposit cleared in the period, counted per person.
    type: simple
    type_params:
      measure: first_funded_client_count

  - name: cost_per_first_funded_client
    label: Cost per first-funded client
    type: ratio
    type_params:
      numerator: paid_media_spend_usd   # defined in marketing_spend.yml
      denominator: first_funded_clients

Illustrative sample written for this page, with made-up names. It shows the pattern, not client code.

If you’re building one

• Start with the contested metrics. The numbers people argue about are where a semantic layer pays off first.
• Write definitions in plain language first. If the owner can’t agree to a sentence, they won’t agree to the YAML.
• Retire the old reports. A semantic layer running next to the old reports is just a 31st version of the truth.