MicroStrategy Analyst cheat sheet

The semantic layer is a metadata model (project schema objects) that maps physical warehouse tables to business entities: attributes, facts, metrics, hierarchies, and relationships, so SQL is generated at query time rather than hand-written.

Schema objects live in the project schema; the three-tier model is project (business model) → warehouse (physical tables/views) → MDX/SQL engine that translates report requests into warehouse-specific SQL.

An attribute is a business concept (Customer, Day, Product) with forms: ID form (key used for joins and grouping) and DESC form (display label). Grouping always occurs on the ID form, never on the description.

Attribute relationships: a child attribute has a 1-to-many lookup to its parent (City → State → Country). The system infers drilling paths from these relationships; missing relationships force manual drill maps.

Facts are numeric, additive-at-a-grain data stored at the attribute level (e.g., Revenue at Day × Item). Fact extensions (conditional, logical, derived) add calculations without new warehouse columns; implicit facts are auto-created from table columns.

A metric = fact (or expression) + aggregation function + dimensionality + filter. Level metrics (a metric with a target attribute level) are evaluated at that level before being combined with report rows.

Compound metrics combine other metrics with arithmetic and inherit the dimensionality of their inputs; derived metrics are defined at the report level and are not stored in the schema.

Hierarchies define drill paths and aggregation rollups; user hierarchies are explicit, system hierarchies (e.g., date) come from attribute relationships. Drilling changes the attribute set in the SELECT/GROUP BY, not the metric definition.

Dimensionality is the set of attributes at which a metric is calculated; the engine adds relationship-table joins to allocate metrics to report levels, which is the main driver of generated SQL complexity.

Governed metric = single certified definition (formula, filter, dimensionality, format) stored once in the schema and reused by every report, dossier, and dataset; changes propagate to all consumers.

Certification workflow: define in schema → validate against source totals → mark as certified/approved by data steward → publish to shared folders; uncertified metrics stay in personal or sandbox folders.

A metric filter is part of the metric definition, not the report: WHERE-style filters on fact tables restrict the metric's calculation and must not be confused with report filters (which restrict rows returned).

Non-additive metrics (distinct counts, ratios, averages of averages) must be defined at the correct level; summing pre-aggregated ratios across rows produces mathematically wrong totals - use level metrics or weighted formulas.

Smart metrics let the warehouse compute the metric at its native level (e.g., SUM at Day) and the engine re-aggregates; use only when the aggregation is decomposable, otherwise totals will differ from detail.

Metric format, currency, and threshold settings belong to the metric object so every consumer sees consistent presentation; overriding format at report level creates definition drift.

Definition lineage: metric → fact/column → table → source. Document numerator/denominator, filter conditions, and grain in the metric description; a metric without a stated grain is not governable.

Change control: version or duplicate before editing a certified metric in production; renaming a metric breaks dependent reports and datasets, so use aliases or deprecation rather than deletion.

Database instances (connection objects) store driver, server, port, credentials, and connection-mapping settings; multiple instances can point to the same warehouse for different workloads or impersonation modes.

Connection mapping assigns different database instances per user/group (e.g., read-only replica for analysts, full warehouse for ETL), evaluated at query time based on login.

A project can have multiple source (lookup) tables and one or more fact tables; the engine joins them using attribute ID columns, so keys must match data types and be indexed.

Intelligent Cubes are pre-computed, multi-dimensional in-memory data sets stored in the Intelligence Server; queries are answered from the cube instead of the warehouse, trading freshness for speed.

Cube refresh options: full, incremental (append new data), or update-with-changes; schedule refreshes and set a partition/aging policy so cubes do not grow unbounded.

Report and dataset caches store prior result sets keyed by the SQL and security profile; cache invalidation happens on schema change, cube refresh, or manual cache clear.

In-memory dataset (MTDI) caching in dossiers keeps imported data on the server; scheduled refresh and row limits control staleness and memory footprint.

Query governance: set row limits, timeouts, and warehouse-specific optimization flags (e.g., intermediate table creation) at the VLDB/DB-instance level to prevent runaway SQL.

Free SQL and direct data access bypass the semantic layer; they are fast to build but lose governed metric definitions, so restrict them to exploration and promote validated logic into the schema.

Security model layers: project-level ACLs (Browse, Use, Create, Configure), object-level ACLs on folders/reports/dossiers, and security filters that append row-level WHERE clauses per user or group.

Security filters are schema-level objects assigned through user/group ACLs; they must reference attributes present in the report or the engine adds joins to enforce them - verify SQL includes the filter predicate.

Data-level security alternatives: security filters (row), connection mapping (database), and attribute-based access (deny elements of an attribute, e.g., hide cost centers).

AI/NL query features (auto-generated insights, natural-language search, auto-dashboards) inherit the requesting user's security profile; an AI answer must never use a service account with broader access than the user.

Governance for AI: restrict which datasets and metrics are exposed to AI assistants, certify the metric set the AI may cite, and log prompts/responses for audit.

Validate AI-generated SQL and narratives against governed metric definitions before publishing; treat AI output as draft analysis, not certified reporting.

Least privilege: grant group-based roles rather than per-user object ACLs; audit inherited permissions and remove default 'Everyone' grants on sensitive folders.

Audit and monitoring: use enterprise/audit logs to track logins, object access, and query history; alerts on failed logins and unusual export volume support access-control review.

Prompt/data leakage risk: users can paste sensitive data into external AI tools, so policy must define approved AI endpoints and prohibit exporting governed data to unapproved services.