Semantic Models, Relationships, Drill Paths, Row-Level Security, and Tool Independence

Learning outcomes

1. A metric needs a navigation model

A consumer rarely asks only for a scalar. It asks revenue by product category, date, or geography, then drills to a lower level. The semantic model therefore declares how facts relate to dimensions and which drill paths are meaningful. These relationships do not replace the dimensional model; they expose its intended analytical joins and cardinalities to consumers.

{  "semantic_model": "atlasmart_sales",  "primary_fact": "fact_sales",  "fact_grain": "one current sales order line",  "relationships": [    {"from":"fact_sales.customer_id","to":"dim_customer_current.customer_id","cardinality":"many_to_one"},    {"from":"fact_sales.order_date","to":"dim_date.date_key","cardinality":"many_to_one"},    {"from":"fact_sales.product_id","to":"dim_product.product_id","cardinality":"many_to_one"}  ],  "drill_paths": {    "date": ["year","month","date_key"],    "product": ["category","product_name"],    "customer": ["geography_code","customer_id"]  }}

The many-to-one declarations are important evidence. If a supposed dimension key is not unique, joining can multiply facts. The semantic layer must fail relationship tests rather than hiding fan-out with DISTINCT.

2. Drill paths preserve business hierarchy, not arbitrary columns

The date path year → month → date and product path category → product reflect governed hierarchies. Customer drill can expose geography first and only then customer identity if the principal is allowed to see row-level identity. A drill path is a consumer contract: it should specify ordering, null/unknown members, raggedness where applicable, and security boundaries.

AtlasMart’s inferred C005 has __UNKNOWN__ geography. The model keeps that explicit rather than coercing it into East/West to make a chart prettier.

3. Row-level security must filter the population before aggregation

The local lab gives analyst_east access only to G-EAST. The governed revenue compiler injects the entitlement join before summation. East-visible gross revenue is 395 USD, while unrestricted revenue is 820 USD. East-period retention is 100% because the visible prior set is only C002 and that same customer is active in the current period.

SELECT SUM(f.amount_cents)/100.0 AS gross_revenue_usdFROM fact_sales AS fJOIN dim_customer_current AS dc  ON dc.customer_id = f.customer_idJOIN principal_geography AS pg  ON pg.geography_code = dc.geography_code AND pg.principal = :principalWHERE f.status='paid'  AND f.currency_code='USD'  AND f.order_date BETWEEN :start_date AND :end_date;

SELECT SUM(f.amount_cents)/100.0 AS gross_revenue_usd FROM fact_sales f JOIN dim_customer_current dc ON dc.customer_id=f.customer_id JOIN principal_geography pg ON pg.geography_code=dc.geography_code AND pg.principal=:principal WHERE f.status='paid' AND f.currency_code='USD' AND f.order_date BETWEEN :start_date AND :end_date

4. Security is not only a BI filter

A dashboard filter is user-controlled analytical context. RLS is authorization. Treating the two as the same creates bypass paths: notebooks, exports, direct SQL, or acceleration tables may omit the visual filter. Production systems should enforce access at a layer consumers cannot bypass, or constrain all access through a governed service whose security equivalence is tested.

The local join-based entitlement pattern is portable teaching logic, not a claim that SQLite has native row-security policies. PostgreSQL and cloud warehouses implement security differently; exact syntax and evaluation behavior must be verified on the target engine.

5. Tool independence means one meaning, multiple adapters

Tool independence does not mean every BI tool uses identical syntax. It means the authoritative metric contract is outside any one dashboard workbook, and each adapter compiles or maps it without changing meaning. The lab compiler emits SQL from the same specification. Two simulated consumers that ask for gross_revenue_usd.v1 with the same parameters receive the same SQL semantics and golden result.

A product-native semantic layer may provide caching, query planning, APIs, and BI integrations. Those are useful implementation features, but the course contract remains vendor-neutral: metric name/version, grain, filters, time, units, owner, security, lineage, and tests must survive a tool migration.

6. Relationship failures and bridge awareness

Chapter 9 showed that many-to-many bridges can multiply facts unless allocation semantics are explicit. A semantic model must therefore mark many-to-many relationships and require a governed bridge/weighting rule instead of pretending everything is many-to-one. Similarly, distinct metrics must not be declared additive merely because an aggregate table stores subtotals.

Relationship tests should check dimension-key uniqueness, orphan rates, allowed unknown members, bridge weight totals, effective dates, and row counts before/after joins on representative queries.

7. Acceleration and security equivalence

Chapter 19’s daily/product aggregate can answer unrestricted revenue by date/product because it contains compatible dimensions and reconciles at sequence 207. It cannot answer customer-geography RLS because customer identity/geography is absent. The semantic router must therefore fall back to a secure base path for analyst_east instead of using a faster but unauthorized accelerator.

This is a central production judgment: an accelerator may be fresh and numerically correct yet still be ineligible for a query because its dimensional or security scope is insufficient.

8. Operations, testing, and bridge

• Correctness: test relationship cardinality and metric golden results.
• Security: run positive and negative entitlement tests for each route.
• Observability: log principal, metric version/hash, route, allowed dimensions, and policy version without leaking sensitive row contents.
• Performance: RLS joins may change plans; benchmark target-engine behavior rather than weakening policy.
• Migration: dual-run tool adapters and compare results/security scope before switching consumers.
• Rollback: keep the prior certified adapter/semantic version available.

Lesson 4 turns these semantics into a lifecycle: versioning, deprecation, tests, documentation, and consumer migration.

Knowledge check

Summary and next step

This lesson established the mechanism and production boundaries for Semantic Models, Relationships, Drill Paths, Row-Level Security, and Tool Independence while preserving AtlasMart’s declared grain, governed metrics, history, and reconciliation evidence. Continue to Metric Versioning, Deprecation, Tests, Documentation, and Preventing Dashboard-Specific Business Logic with those contracts unchanged unless an explicit, tested migration says otherwise.

Authoritative references

• Kimball Group — Conformed DimensionsBackground for shared dimensional meaning across analytical consumers and processes.
• Kimball Group — Fact TablesGrain-first fact-table guidance underlying safe metric aggregation.
• dbt — MetricFlow overviewCurrent official example of a semantic metric engine. It is a non-prerequisite product reference, not the course's semantic source of truth.
• dbt — Semantic LayerCurrent official example of centrally defined metrics consumed across tools; exact capabilities and syntax are product-specific.
• PostgreSQL — Row Security PoliciesOfficial engine-specific reference for row-level security. The mandatory local lab simulates entitlement filtering with joins instead of claiming SQLite has equivalent native RLS.
• SQLite — SELECTOfficial semantics for the deterministic local SQL examples.
• Python — sqlite3Standard-library interface used by the no-cost local lab.

9. Lab cleanup/reset

The mandatory examples use only the deterministic local Chapter 20 fixture. Delete atlasmart_ch20_lab (or your configured local output directory) and rerun python ch20_lab.py to recreate a clean state. The script recreates the SQLite database and metric/report files; it does not create cloud resources or modify the academy repository.