# Analytics and BI Solution Design: [Solution name]

> Solution design for an analytics and business intelligence (BI)
> solution: consumers and decisions supported, the KPI catalogue, source
> mapping and bus matrix, the dimensional model, the semantic layer,
> refresh and latency, row-level security, self-service governance,
> performance and capacity, report design standards, distribution,
> testing and reconciliation, adoption and operations. Dimensional
> modelling follows the concepts popularised by the Kimball Group
> (business process, grain, conformed dimensions, slowly changing
> dimensions), described here in original wording. Replace every
> `[bracketed]` value and every row marked *Example*. Worked examples
> follow a retail sales and margin solution for finance and
> merchandising.

<!--
Guidance: How to use this template
- Write one design per BI solution (a subject area such as sales and
  margin, or a programme with several marts). The platform it runs on is
  described in Data Platform Architecture
  (`architecture/data-platform-architecture`); do not repeat platform
  choices here.
- Start from decisions and KPIs (sections 1 and 2), not from source
  tables. A model that answers no named question is waste.
- Every KPI needs one definition, written in Metric Definition
  (`ai-data/metric-definition`), and one implementation, in the semantic
  layer (section 5).
- Fill the bus matrix (section 3.2) before modelling: it shows which
  dimensions must be conformed across business processes.
- Related: Data Dictionary (`architecture/data-dictionary`), Data
  Pipeline Specification (`ai-data/data-pipeline-spec`), Master Data
  Management Design (`architecture/master-data-management-design`).
-->

## Document control

<!-- Guidance: Identify the solution, its consumers and owners. A change of grain of a core fact table or of the semantic layer technology is a new major version. -->

| Field | Value |
|---|---|
| Document ID | [BI-001] |
| Solution name | [Name] |
| Subject areas | [Sales and margin / Inventory / Customer / Finance] |
| BI and semantic tooling | [BI tool, semantic layer product or build] |
| Data platform | [Platform name and document ID] |
| Version | [0.1] |
| Status | [Draft / In review / Approved / Superseded] |
| Owner | [@bi-architect] |
| Business owner | [@business-owner] |
| Approvers/reviewers | [@data-owner], [@security-architect], [@data-platform-architect] |
| Last updated | YYYY-MM-DD |
| Classification | [Internal / Confidential] |

### Approvals

| Role | Name | Decision | Date |
|---|---|---|---|
| Business owner | [@name] | [Approve / Approve with conditions / Reject] | YYYY-MM-DD |
| Data owner | [@name] | [Decision] | YYYY-MM-DD |
| Security | [@name] | [Decision] | YYYY-MM-DD |
| Data platform | [@name] | [Decision] | YYYY-MM-DD |

### Revision history

| Version | Date | Author | Change summary |
|---|---|---|---|
| 0.1 | YYYY-MM-DD | [@name] | First draft |
| 1.0 | YYYY-MM-DD | [@name] | *Example:* Sales and margin mart approved |
| 1.1 | YYYY-MM-DD | [@name] | *Example:* Added inventory periodic snapshot |

## Contents

<!-- Guidance: Keep this list aligned with the headings. -->

1. [Scope, consumers and decisions](#1-scope-consumers-and-decisions)
2. [KPI catalogue](#2-kpi-catalogue)
3. [Sources and bus matrix](#3-sources-and-bus-matrix)
4. [Dimensional model](#4-dimensional-model)
5. [Semantic layer](#5-semantic-layer)
6. [Refresh and latency](#6-refresh-and-latency)
7. [Security and row-level access](#7-security-and-row-level-access)
8. [Self-service governance](#8-self-service-governance)
9. [Performance and capacity](#9-performance-and-capacity)
10. [Report and dashboard standards](#10-report-and-dashboard-standards)
11. [Distribution and embedding](#11-distribution-and-embedding)
12. [Testing and reconciliation](#12-testing-and-reconciliation)
13. [Adoption and support](#13-adoption-and-support)
14. [Operations, cost, roadmap and risks](#14-operations-cost-roadmap-and-risks)
15. [Glossary and references](#15-glossary-and-references)

## 1. Scope, consumers and decisions

<!-- Guidance: Name the people, the decisions they make and how often. Questions: which decision changes if the number changes, what happens today without the solution? Pitfall: "a dashboard for management" with no decision attached. -->

| ID | Consumer group | Decision supported | Frequency | Current method | Users |
|---|---|---|---|---|---|
| BIC-001 *(Example)* | Finance controllers | Approve monthly margin per country | Monthly, daily during close | Spreadsheet from ERP extracts | 150 |
| BIC-002 *(Example)* | Category managers | Adjust promotions and markdowns | Weekly | Legacy reports, 2-day lag | 300 |
| BIC-003 *(Example)* | Store managers | Re-order and staff planning | Daily | Printed store report | 1,800 |
| BIC-004 *(Example)* | Executive board | Track company KPIs against plan | Weekly | Slide deck | 15 |
| [BIC-NNN] | [Group] | [Decision] | [Frequency] | [Current] | [Count] |

*Example* named users: 150 + 300 + 1,800 + 15 = 2,265. Section 9 sizes
capacity from this figure.

Out of scope: [operational reports inside source systems], [planning and
forecasting write-back], [machine learning features].

## 2. KPI catalogue

<!-- Guidance: List each KPI with its owner, definition reference, grain and target. Keep the full definition in a Metric Definition document; this table is the index. Pitfall: two KPIs with the same name and different formulas in different departments. -->

| ID | KPI | Owner | Definition document | Lowest grain | Certified | Target *(Example)* |
|---|---|---|---|---|---|---|
| KPI-001 *(Example)* | Net sales | Head of finance | MET-001 | Sales line by day | Yes | Plan per country |
| KPI-002 *(Example)* | Gross margin % | Head of finance | MET-002 | Sales line by day | Yes | 38 % |
| KPI-003 *(Example)* | Like-for-like sales growth | Head of finance | MET-003 | Store by week | Yes | 3 % year on year |
| KPI-004 *(Example)* | Promotion uplift | Category director | MET-004 | Product by store by week | Yes | Positive for 70 % of promotions |
| KPI-005 *(Example)* | Stock cover days | Supply chain director | MET-005 | Product by store by day | In progress | 21 days |
| [KPI-NNN] | [Name] | [@owner] | [MET-NNN] | [Grain] | [Yes / No] | [Target] |

Write additive measures (net sales, units) separately from ratios (margin
%) and semi-additive measures (stock on hand, which can be summed across
stores but not across days). The semantic layer must aggregate ratios
from their components, never by averaging stored percentages.

## 3. Sources and bus matrix

<!-- Guidance: Map each business process to its source and to the dimensions it uses. Questions: which system is authoritative for each process, which dimensions are shared? Pitfall: building a mart per department with private copies of customer and product. -->

### 3.1 Source mapping

| Business process | Source system | Source objects | Platform layer used | Owner |
|---|---|---|---|---|
| Retail sales *(Example)* | POS and e-commerce | Sales transactions, returns | Silver sales lines | @retail-ops |
| Cost of goods *(Example)* | ERP | Standard cost, purchase price | Silver cost | @erp-owner |
| Inventory *(Example)* | Warehouse and store systems | Stock positions | Silver stock | @supply-chain |
| Promotions *(Example)* | Promotion planning tool | Promotion headers and lines | Silver promotions | @category-ops |
| [Process] | [System] | [Objects] | [Layer] | [@owner] |

### 3.2 Bus matrix

| Business process (fact) | Date | Store | Product | Customer | Promotion | Supplier |
|---|---|---|---|---|---|---|
| Retail sales *(Example)* | X | X | X | X | X | |
| Inventory snapshot *(Example)* | X | X | X | | | X |
| Purchase orders *(Example)* | X | X | X | | | X |
| Promotion plan *(Example)* | X | X | X | | X | |
| [Process] | [X] | [X] | [X] | [X] | [X] | [X] |

A column with several marks is a conformed dimension: build it once,
with one key and one set of attributes, and reuse it in every mart.
Customer and product come from the master data hub when one exists.

## 4. Dimensional model

<!-- Guidance: For each fact, apply the four design steps: choose the business process, declare the grain, identify the dimensions, identify the facts. Declare the grain in one sentence before listing columns. Pitfall: mixing grains in one fact table (lines and order headers together). -->

### 4.1 Fact tables

| ID | Fact table | Type | Grain (one row per) | Measures | Additivity |
|---|---|---|---|---|---|
| FCT-001 *(Example)* | fact_sales_line | Transaction | Sales line on a receipt or order | Units, gross sales, discount, net sales, cost | Fully additive |
| FCT-002 *(Example)* | fact_stock_daily | Periodic snapshot | Product per store per day | Stock on hand, stock value | Semi-additive (not over time) |
| FCT-003 *(Example)* | fact_order_fulfilment | Accumulating snapshot | Online order, updated at each milestone | Lag days between milestones | Additive lags |
| FCT-004 *(Example)* | fact_promotion_coverage | Factless | Product per store per promotion day | None (row presence) | Count only |
| [FCT-NNN] | [Name] | [Type] | [Grain] | [Measures] | [Additivity] |

### 4.2 Dimensions and change handling

| ID | Dimension | Key | Source | Change handling | Notes |
|---|---|---|---|---|---|
| DIM-001 *(Example)* | dim_date | Date key (YYYYMMDD) | Generated | Static | Fiscal calendar, holidays per country |
| DIM-002 *(Example)* | dim_store | Surrogate key | Store master | Type 2 for region and format, Type 1 for phone | Role: selling store and fulfilling store |
| DIM-003 *(Example)* | dim_product | Surrogate key | Product master | Type 2 for category, Type 1 for description | Category hierarchy of fixed depth |
| DIM-004 *(Example)* | dim_customer | Surrogate key | MDM golden record | Type 2 for segment, Type 1 for contact data | Personal attributes masked (section 7) |
| DIM-005 *(Example)* | dim_promotion | Surrogate key | Promotion tool | Type 1 | Includes "no promotion" row |
| [DIM-NNN] | [Name] | [Key] | [Source] | [Type] | [Notes] |

Use surrogate keys for dimensions so that history (Type 2 versions) and
late-arriving rows can be handled; keep the natural key as an attribute.
Add an "unknown" member to every dimension so facts with a missing
reference still load and are visible in quality reports.

### 4.3 Model diagram

The *Example* sales star joins one transaction fact to five dimensions.

```mermaid
erDiagram
  FACT_SALES_LINE }o--|| DIM_DATE : "sale date"
  FACT_SALES_LINE }o--|| DIM_STORE : "selling store"
  FACT_SALES_LINE }o--|| DIM_PRODUCT : product
  FACT_SALES_LINE }o--|| DIM_CUSTOMER : customer
  FACT_SALES_LINE }o--|| DIM_PROMOTION : promotion
  FACT_SALES_LINE {
    int date_key
    int store_key
    int product_key
    int customer_key
    int promotion_key
    string receipt_id
    decimal units
    decimal net_sales
    decimal cost
  }
```

`receipt_id` is a degenerate dimension: an identifier kept on the fact
with no dimension table of its own.

## 5. Semantic layer

<!-- Guidance: Describe where metrics, joins, hierarchies and security are defined for consumers. Questions: one shared layer or one per tool, how are changes versioned and tested? Pitfall: business logic duplicated in each dashboard. -->

| Element | Design *(Example)* |
|---|---|
| Location | One semantic model per subject area, defined as code in version control and deployed through CI |
| Metrics | KPI-001 to KPI-005 implemented once; ratios built from additive components |
| Hierarchies | Date (year, quarter, month, week, day), product (department, category, subcategory, item), store (country, region, store) |
| Time intelligence | Year to date, same period last year, like-for-like store flag |
| Currency | Facts held in transaction and group currency; conversion at monthly average rate from finance |
| Security | Row-level rules from section 7 applied in the semantic model, not in reports |
| Consumers | BI tool, spreadsheet connector, SQL access for analysts through the same definitions where the tool supports it |
| Interchange | Export of the model in an open format where available; open semantic interchange specifications are emerging, check their maturity |

Version metric logic: a change to a certified KPI formula needs the KPI
owner's approval, a new version of its Metric Definition and a
communication to consumers before deployment.

## 6. Refresh and latency

<!-- Guidance: State freshness per fact and the refresh method. Questions: what is the latest acceptable time for each consumer, what happens when a source is late? Pitfall: full reloads of large facts every night that stop fitting in the window. -->

| Fact | Freshness objective *(Example)* | Method | Window | Late source behaviour |
|---|---|---|---|---|
| fact_sales_line | Daily by 06:00 local; hourly intraday view for e-commerce | Incremental by load date, late returns reprocessed for 35 days | 02:00 to 05:30 | Publish with banner "store X missing", reload when it arrives |
| fact_stock_daily | Daily by 07:00 | Append one snapshot per day | 04:00 to 06:30 | Carry forward previous day, flag as estimated |
| fact_order_fulfilment | Hourly | Upsert on order key | Continuous | Milestones fill when events arrive |
| [Fact] | [Objective] | [Method] | [Window] | [Behaviour] |

*Example* increment: 6.5 million sales lines per day against 2.4 billion
rows in the table, so an incremental load touches 6.5 / 2,400 = 0.27 % of
rows plus the 35-day returns window. A full reload is avoided. Record
freshness objectives in the SLO document (`dev/slo-doc`).

## 7. Security and row-level access

<!-- Guidance: Define who sees which rows and columns, where rules are enforced and how they are tested. Pitfall: security implemented in report filters that users can remove. -->

| ID | Rule | Applies to | Enforcement | Test |
|---|---|---|---|---|
| SEC-001 *(Example)* | Country managers see only their country | All sales and stock facts | Row-level rule on dim_store.country from user attribute | Test user per country |
| SEC-002 *(Example)* | Store managers see their store and regional averages | Store reports | Row-level rule plus pre-aggregated regional table | Test user per role |
| SEC-003 *(Example)* | Customer personal attributes hidden except for the CRM team | dim_customer | Column masking policy | Masked value check |
| SEC-004 *(Example)* | Cost and margin hidden from store roles | Cost measures | Object-level security in the semantic model | Measure not visible |
| [SEC-NNN] | [Rule] | [Scope] | [Enforcement] | [Test] |

Map roles to identity provider groups, not to named users, and review
membership with the Access Review (`ops/access-review`). Exports to files
inherit row rules; restrict bulk export of personal data.

## 8. Self-service governance

<!-- Guidance: Decide what users may build themselves and how content is promoted. Pitfall: thousands of unowned reports, none of them trusted. -->

| Content tier | Who builds | Data allowed | Review | Label | Retention |
|---|---|---|---|---|---|
| Certified *(Example)* | BI team or approved domain developers | Certified semantic models only | Peer review, data owner sign-off, tests in section 12 | Certified badge | Reviewed every 12 months |
| Shared *(Example)* | Trained power users | Certified models plus approved sources | Workspace owner | Shared, not certified | Archived after 180 days without views |
| Personal *(Example)* | Any licensed user | Certified models | None | Personal | Deleted after 90 days without use |
| [Tier] | [Builders] | [Data] | [Review] | [Label] | [Retention] |

Promote content from shared to certified through a request with the
reviewer checklist from section 12. Measure the share of views that land
on certified content (section 13).

## 9. Performance and capacity

<!-- Guidance: Estimate concurrency and query load, set response time targets and choose acceleration (aggregates, caching, import versus direct query). Show the arithmetic and the consequence. Pitfall: sizing for average load when month-end close is the peak. -->

### 9.1 Load estimate *(Example)*

| Step | Calculation | Result |
|---|---|---|
| Named users | Section 1 | 2,265 |
| Daily active users (25 %) | 2,265 x 0.25 | about 566 |
| Peak concurrent sessions (10 % of daily active) | 566 x 0.10 | about 57 |
| Queries per session per minute | Dashboard with 8 visuals, refreshed about every 1.5 minutes | about 5 |
| Peak queries per minute | 57 x 5 | 285 |
| Peak queries per second | 285 / 60 | about 4.75 |
| Queries reaching the warehouse (70 % cache hit) | 4.75 x 0.30 | about 1.4 per second |

Month-end close doubles finance sessions in this *Example*; apply a
peak factor of [2] to the finance share when sizing.

Consequence: about 1.4 warehouse queries per second at peak fits one
small always-on SQL cluster only if p95 query time stays under 2 seconds;
otherwise queries queue. Pre-aggregate fact_sales_line to product
category by store by day, which the *Example* top 10 dashboards use, and
keep the line-level table for drill-through.

### 9.2 Targets

| ID | Target *(Example)* | Measured by |
|---|---|---|
| PRF-001 | Certified dashboard page loads in under 3 seconds (p90) | BI tool usage logs |
| PRF-002 | Drill-through to line level under 10 seconds (p90) | Synthetic test |
| PRF-003 | Semantic model refresh completes by 05:30 | Orchestrator logs |
| [PRF-NNN] | [Target] | [Method] |

Plan performance tests in the Performance Test Plan
(`quality/performance-test-plan`).

## 10. Report and dashboard standards

<!-- Guidance: Agree standards so certified content looks and behaves consistently and is accessible. Pitfall: colour as the only carrier of meaning (red and green), which fails users with colour vision deficiency. -->

1. Start each dashboard with the question it answers and its audience.
2. Show at most [6 to 8] visuals per page; put detail in drill-through.
3. Label every KPI with its definition link, freshness time and filters
   applied.
4. Use consistent number formats and units; show currency codes.
5. Do not rely on colour alone; add icons, labels or patterns. Check
   contrast and keyboard navigation with the Accessibility Checklist
   (`design/accessibility-checklist`).
6. Prefer bar and line charts for comparison and trend; avoid 3D charts.
7. Show the plan or prior period next to actuals so variance is visible.
8. Name pages and visuals so screen readers announce them meaningfully.

## 11. Distribution and embedding

<!-- Guidance: Describe how content reaches users: portal, subscriptions, alerts, embedding in applications, exports. Pitfall: scheduled e-mail exports that bypass row-level security. -->

| ID | Channel | Audience | Content | Security |
|---|---|---|---|---|
| DIS-001 *(Example)* | BI portal | All roles | Certified dashboards | Single sign-on, row rules |
| DIS-002 *(Example)* | Subscription by e-mail (image and link) | Executives | Weekly KPI page | Row rules applied per recipient |
| DIS-003 *(Example)* | Data alerts | Category managers | Promotion uplift below zero | Recipient-level rules |
| DIS-004 *(Example)* | Embedded in store app | Store managers | Store daily page | Application identity with per-user rules |
| [DIS-NNN] | [Channel] | [Audience] | [Content] | [Security] |

## 12. Testing and reconciliation

<!-- Guidance: Define how numbers are proven correct before release and after each change. Pitfall: testing that a report renders, not that the number is right. -->

| ID | Test | Method | Pass criterion |
|---|---|---|---|
| TST-001 *(Example)* | Net sales reconciles to ERP ledger | Compare daily totals per country | Within 0.1 % |
| TST-002 *(Example)* | Row counts per load | Source lines versus fact rows | Equal after known exclusions |
| TST-003 *(Example)* | Unknown member rate | Facts pointing to unknown dimension rows | Below 0.5 % |
| TST-004 *(Example)* | Row-level security | Test users per role | Each sees only permitted rows |
| TST-005 *(Example)* | KPI regression | Known period values before and after change | Unchanged unless the change is approved |
| TST-006 *(Example)* | Performance | PRF-001 to PRF-003 | Targets met |
| [TST-NNN] | [Test] | [Method] | [Criterion] |

Keep test cases in the Test Case Specification
(`quality/test-case-specification`) and run them in CI for every change
to the model or semantic layer.

## 13. Adoption and support

<!-- Guidance: A solution that nobody uses has failed. Plan training, champions, support and usage measurement. Pitfall: launching without retiring the old spreadsheets, so both live on. -->

| Measure | Target *(Example)* | Source |
|---|---|---|
| Monthly active users of certified content | 60 % of named users within 6 months | Usage logs |
| Share of views on certified content | Above 70 % | Usage logs |
| Legacy reports retired | 100 % of mapped reports within 9 months | Report inventory |
| Support tickets per 100 users per month | Below 3 after month 3 | Service desk |

Support model: first line through the service desk, second line through
the BI team, data questions routed to the KPI owner. Name one champion
per consumer group in section 1 and run monthly office hours.

## 14. Operations, cost, roadmap and risks

<!-- Guidance: Cover running cost, licences, operational ownership, delivery increments and risks. Pitfall: forgetting viewer licences for 1,800 store managers in the cost model. -->

### 14.1 Operations and cost

| Item | Design or estimate *(Example)* |
|---|---|
| Ownership | BI team owns semantic models and certified content; platform team owns compute |
| Licences | [Number] creator licences and [number] viewer licences, per the vendor's current price list |
| Compute | One small SQL cluster in business hours plus refresh jobs; cost allocated in `ops/finops-cost-review` |
| Monitoring | Refresh failures, freshness, slow queries; signals in `ops/observability-spec` |
| Runbooks | Failed refresh, late source, KPI dispute (`dev/runbook`) |

### 14.2 Roadmap

| Release | Scope | Consumers | Exit criterion | Date |
|---|---|---|---|---|
| R1 *(Example)* | Sales star, KPI-001 to KPI-003 | Finance | TST-001 passes for 3 closes | YYYY-MM-DD |
| R2 *(Example)* | Promotions and KPI-004 | Category managers | Adoption target for the group | YYYY-MM-DD |
| R3 *(Example)* | Stock snapshot, store app embedding | Store managers | PRF-001 at 1,800 users | YYYY-MM-DD |

### 14.3 Risks

| ID | Risk | Likelihood | Impact | Response | Owner |
|---|---|---|---|---|---|
| RSK-001 *(Example)* | Finance does not accept numbers that differ from legacy reports | High | High | Reconciliation pack per close, joint sign-off | @bi-architect |
| RSK-002 *(Example)* | Self-service sprawl erodes trust | Medium | Medium | Content tiers and archiving (section 8) | @bi-lead |
| RSK-003 *(Example)* | Month-end peak exceeds capacity | Medium | Medium | Aggregates, scale-out rule, load test | @data-platform-architect |
| [RSK-NNN] | [Risk] | [L / M / H] | [L / M / H] | [Response] | [@owner] |

## 15. Glossary and references

<!-- Guidance: Define modelling terms for business readers and list sources with versions. -->

| Term | Meaning |
|---|---|
| Grain | The exact meaning of one row in a fact table |
| Fact | A numeric measurement of a business process event |
| Dimension | Descriptive context used to filter and group facts |
| Conformed dimension | A dimension shared with the same keys and meaning across facts |
| Bus matrix | Grid of business processes against the dimensions they use |
| Slowly changing dimension | Technique for handling changes to dimension attributes (Type 1 overwrites, Type 2 adds a new row version) |
| Semi-additive measure | A measure that can be summed over some dimensions but not over time |
| Semantic layer | Shared, governed definitions of metrics, relationships and security queried by BI tools |

References: Kimball Group, Dimensional Modeling Techniques
(kimballgroup.com, technique names only, checked 2026-10-23); DAMA-DMBOK
2nd Edition Revised (DAMA International, 2024), Data Warehousing and
Business Intelligence knowledge area (name only); Apache Ossie
(incubating), formerly Open Semantic Interchange, github.com/open-semantic-interchange/OSI
(checked 2026-10-23); [BI tool documentation, version].

## Standard and licence note

Structure aligned with dimensional modelling concepts published by the
Kimball Group and with the Data Warehousing and Business Intelligence
knowledge area of DAMA-DMBOK 2nd Edition Revised (2024). Only concept and
technique names are used; no text, diagrams or examples from those
publications are reproduced, and the worked examples are original. This
template is original text written for this collection. Check the current
documentation of your BI and semantic layer tools before use. DAMA-DMBOK
is a trademark of DAMA International and Kimball Group names belong to
their owners; this template is not affiliated with or endorsed by them. Template licence: MIT.