Analytics and BI Solution Design Template (Dimensional Model, Semantic Layer)

Decisions, KPI catalogue, bus matrix, facts and dimensions, SCD types, semantic layer, refresh, row-level security, capacity, testing, adoption

ArchitekturVollständigKimball dimensional modelling concepts (structure only); DAMA-DMBOK2 Revised (2024) Data Warehousing and BIanalytics solution designAktualisiert 22.10.2026

Im Editor öffnen.md herunterladen

Anleitung: Use it when you design a BI subject area such as sales and margin on top of a data platform. Fill the consumer decisions, KPI catalogue and bus matrix first, then declare the grain of each fact table. The main pitfall is metric logic copied into each dashboard, so certified numbers disagree between reports.

Vorschau

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.

Document control

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

Scope, consumers and decisions
KPI catalogue
Sources and bus matrix
Dimensional model
Semantic layer
Refresh and latency
Security and row-level access
Self-service governance
Performance and capacity
Report and dashboard standards
Distribution and embedding
Testing and reconciliation
Adoption and support
Operations, cost, roadmap and risks
Glossary and references

1. Scope, consumers and decisions

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

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

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

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.

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

5. Semantic layer

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

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

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

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

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

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

11. Distribution and embedding

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

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

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

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

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.