MicroStrategy Developer cheat sheet

The semantic layer is a set of reusable objects (attributes, facts, metrics, filters, prompts) stored in the metadata repository; reports and dossiers reference objects by ID, so a definition change propagates to every consumer without editing documents.

Attribute = business concept used for grouping (Customer, Day, Region); it contains forms (ID, DESC, etc.) and a lookup table. Fact = column-level numeric data mapped to a table. Metric = formula combining facts, attributes, and functions.

Metric levels are determined by the attributes placed on the report, not by the metric definition; a metric with no fixed level aggregates at whatever grouping the report supplies. Use level metrics or a relationship filter to pin a level.

Compound metrics reference other metrics and inherit their dimensionality; derived metrics apply arithmetic to metrics. Nesting depth is limited, so promote frequently reused nested expressions into standalone metrics.

Conditionality is applied with a filter on the metric (WHERE clause on the source) or with a level metric plus relationship filter (HAVING-style); the two produce different SQL and different results when the filter attribute is not in the report.

Attribute relationships (1-to-many, many-to-many, 1-to-1) drive automatic join paths between tables; a missing or misdeclared relationship produces Cartesian joins or fan-out that inflates metric totals.

Attribute forms: ID form drives joins and sorting, DESC form drives display. Setting the DESC form as the browse/template display form changes labels only, not aggregation.

Governed definitions require object-level security and change control: modifications are captured in metadata change logs, and promoted objects should move through a project lifecycle (development to test to production) rather than direct edits in production.

Database instances define connectivity (driver, server, database, authentication). A database connection maps a metadata project to one or more instances; a project can contain multiple connections for multi-source reporting.

MicroStrategy generates SQL from the semantic layer; the warehouse's own optimizer determines execution. Use VLDB properties to control join type, index hints, temporary table creation, and pre/post statements per metric, template, or report.

Intermediate table handling: reports can build temporary tables in the warehouse for multi-pass SQL. VLDB settings choose whether to use derived tables, temporary tables, or permanent tables, which affects concurrency and cleanup.

Intelligent Cubes are multi-dimensional in-memory structures built from a report; they answer subsequent queries without returning to the warehouse. They are refreshed on a schedule or on demand, and their data ages independently of the warehouse.

Report and document caches store result sets keyed by report definition plus user security filters; cache invalidation happens on schema/object changes, scheduled expiry, or manual purge. A cache hit avoids warehouse execution but can serve stale data.

In-memory dataset (MTDI) and cube caches are separate from the report cache; a dossier using an in-memory dataset reads the dataset, not the warehouse, until the dataset is refreshed.

Query governance: set row limits, timeouts, and connection pooling per instance. Long-running queries should be traced in the warehouse and correlated with the SQL MicroStrategy logged.

Multi-source options: data can be federated across connections in one report when the warehouse supports it, or brought together through in-memory datasets; cross-warehouse joins are not performed by MicroStrategy itself.

Security model layers: authentication (standard, LDAP, Windows, SAML, Kerberos), then project access, then object-level ACLs, then data-level security filters. A user must pass every layer to see data.

Security filters append a WHERE predicate to every query for the user or group; they are the mechanism for row-level security. A user in multiple groups receives the union of the filters unless the project is set to intersect.

Object ACLs use the ACL list: view, execute, edit, delete, control, and grant. Deny takes precedence over allow; inherited privileges come from parent folders and from the user's groups.

Privileges are separate from object ACLs: a privilege (for example, creating a dossier or using the Developer tool) is granted to users or groups at the project or server level and gates the action regardless of object rights.

Project-level security roles include Project Administrator, Analyst, and Viewer; these are bundles of privileges, not ACLs. Custom roles should be cloned rather than edited when the built-in bundle is still needed.

Audit logging records user connections, logins, report executions, and object changes. Enterprise Manager and the platform's audit tables provide usage and performance history; retention depends on configuration.

Data-level governance also covers caching: cached results respect the security filter of the requesting user, so caches are not shared across users with different filters.

Change control: object changes are versioned in metadata; use project duplication and object migration (or the platform's migration utilities) to move governed content between environments, and review audit history after promotion.

Model Context Protocol (MCP) is an open protocol that exposes tools and resources to AI clients; a MicroStrategy MCP server publishes governed semantic objects as tools an LLM can call instead of writing SQL.

REST API is the primary programmatic interface: authentication (token or OAuth), then endpoints for projects, datasets, reports, dossiers, and object metadata. Responses are JSON; long-running operations return a job ID to poll.

Library and dossier APIs allow embedding: create a session, obtain a token, and render content in an iframe or via the embedding SDK. Embedding respects the authenticated user's security filters.

AI assistants that answer questions over data should resolve questions through the semantic layer so that metric definitions, security filters, and attribute relationships apply; raw SQL generation bypasses governance.

Prompt objects and search-based interfaces let users type natural-language questions that map to governed objects; the quality of the mapping depends on object names, descriptions, and synonyms in the metadata.

Integration patterns: push data out via API for downstream tools, pull data in via datasets and gateways, or federate at query time. Each pattern has different latency and freshness characteristics.

API rate limits, pagination, and token expiry are the usual failure points; design retries with backoff and refresh tokens before expiry.

When exposing semantic objects to an AI client, restrict the tool surface to the objects the client needs; broader exposure increases the chance of the model selecting an unintended metric or attribute.

Attribute = business concept used for grouping (Customer, Day, Region); it contains forms (ID, DESC, etc.) and a lookup table. Fact = column-level numeric data mapped to a table. Metric = formula combining facts, attributes, and functions.

Metric levels are determined by the attributes placed on the report, not by the metric definition; a metric with no fixed level aggregates at whatever grouping the report supplies. Use level metrics or a relationship filter to pin a level.

Compound metrics reference other metrics and inherit their dimensionality; derived metrics apply arithmetic to metrics. Nesting depth is limited, so promote frequently reused nested expressions into standalone metrics.

Attribute relationships (1-to-many, many-to-many, 1-to-1) drive automatic join paths between tables; a missing or misdeclared relationship produces Cartesian joins or fan-out that inflates metric totals.

Attribute forms: ID form drives joins and sorting, DESC form drives display. Setting the DESC form as the browse/template display form changes labels only, not aggregation.

Governed definitions require object-level security and change control: modifications are captured in metadata change logs, and promoted objects should move through a project lifecycle (development to test to production) rather than direct edits in production.

Database instances define connectivity (driver, server, database, authentication). A database connection maps a metadata project to one or more instances; a project can contain multiple connections for multi-source reporting.

MicroStrategy generates SQL from the semantic layer; the warehouse's own optimizer determines execution. Use VLDB properties to control join type, index hints, temporary table creation, and pre/post statements per metric, template, or report.

Intelligent Cubes are multi-dimensional in-memory structures built from a report; they answer subsequent queries without returning to the warehouse. They are refreshed on a schedule or on demand, and their data ages independently of the warehouse.

Report and document caches store result sets keyed by report definition plus user security filters; cache invalidation happens on schema/object changes, scheduled expiry, or manual purge. A cache hit avoids warehouse execution but can serve stale data.

In-memory dataset (MTDI) and cube caches are separate from the report cache; a dossier using an in-memory dataset reads the dataset, not the warehouse, until the dataset is refreshed.

Query governance: set row limits, timeouts, and connection pooling per instance. Long-running queries should be traced in the warehouse and correlated with the SQL MicroStrategy logged.

Security model layers: authentication (standard, LDAP, Windows, SAML, Kerberos), then project access, then object-level ACLs, then data-level security filters. A user must pass every layer to see data.

Security filters append a WHERE predicate to every query for the user or group; they are the mechanism for row-level security. A user in multiple groups receives the union of the filters unless the project is set to intersect.

Object ACLs use the ACL list: view, execute, edit, delete, control, and grant. Deny takes precedence over allow; inherited privileges come from parent folders and from the user's groups.

Privileges are separate from object ACLs: a privilege (for example, creating a dossier or using the Developer tool) is granted to users or groups at the project or server level and gates the action regardless of object rights.

Audit logging records user connections, logins, report executions, and object changes. Enterprise Manager and the platform's audit tables provide usage and performance history; retention depends on configuration.

Change control: object changes are versioned in metadata; use project duplication and object migration (or the platform's migration utilities) to move governed content between environments, and review audit history after promotion.

Model Context Protocol (MCP) is an open protocol that exposes tools and resources to AI clients; a MicroStrategy MCP server publishes governed semantic objects as tools an LLM can call instead of writing SQL.

REST API is the primary programmatic interface: authentication (token or OAuth), then endpoints for projects, datasets, reports, dossiers, and object metadata. Responses are JSON; long-running operations return a job ID to poll.

Library and dossier APIs allow embedding: create a session, obtain a token, and render content in an iframe or via the embedding SDK. Embedding respects the authenticated user's security filters.

AI assistants that answer questions over data should resolve questions through the semantic layer so that metric definitions, security filters, and attribute relationships apply; raw SQL generation bypasses governance.

Prompt objects and search-based interfaces let users type natural-language questions that map to governed objects; the quality of the mapping depends on object names, descriptions, and synonyms in the metadata.

API rate limits, pagination, and token expiry are the usual failure points; design retries with backoff and refresh tokens before expiry.