Semantic Layer Modeling and Governed Definitions
Semantic Layer Modeling and Governed Definitions
This domain tests whether you can build a reusable, governed semantic layer in MicroStrategy: attributes, facts, metrics, hierarchies, and the relationships that let the SQL engine generate correct aggregates at any level.
Core rules
- Attributes are business dimensions. Every attribute has forms (ID, DESC, and custom forms). Grouping uses the attribute ID; forms only display. A non-unique form (two customers sharing a DESC) explains many apparent duplicate rows.
- Attribute relationships encode grain (1:1, 1:M, M:1). They drive join paths and determine whether metrics fan out and double-count.
- Facts store numbers at a declared level; metrics are formulas over facts with dimensionality, conditionality, and transformations.
- Governed definitions mean one certified metric is reused instead of recreated in every dossier; certification, descriptions, and Change Journal entries make the definition auditable.
Worked example
Revenue is stored at order-line grain; Order ID is at order grain. A developer sets Order to Order Line as 1:1 to simplify the join and builds Revenue as a SUM. A report by Order now joins each order to one line and undercounts revenue, while a report by Customer still looks plausible. Correcting the relationship to 1:M restores the fan-out the engine needs. Reasoning: cardinality is a property of the data, not a convenience; the engine trusts it to choose join paths and to decide whether an aggregate needs a derived table.
Traps that cost marks
- Confusing attribute forms with the attribute ID.
- Building a row-level ratio and expecting it to re-aggregate correctly; use aggregate expressions and metric dimensionality.
- Forgetting Update Schema after a warehouse column change.
- Treating a compound metric as a nested metric, or ignoring level (dimensionality) metrics.