MicroStrategy (and SAP HANA)

Two tools work together here. The MicroStrategy tool (nistor-tool-microstrategy, tool id microstrategy) discovers the objects of a MicroStrategy project, links them to the tables they read, and refreshes cubes and reports from rules. The SAP HANA tool (nistor-tool-sap-hana, tool id sap_hana) has no connection of its own: it recognises SAP tables in the SQL of MicroStrategy cubes so that they appear in lineage.

Installing the MicroStrategy package installs the SAP HANA package. Activate both. The MicroStrategy README calls the tool "Production" and its package carries the Alpha classifier; the SAP HANA README calls it an "identity-only tool" (see the overview and SAP HANA).

What needs more than the public image#

Discovery needs the MicroStrategy SDK, mstrio-py. Its licence is proprietary, so it is the optional extra sdk of the MicroStrategy package, and the public runtime image does not install it. Without it, a compile reports the MicroStrategy connection as failed with "MicroStrategy SDK not installed". Everything else loads and works without it.

To add the SDK, activate the two tools and put the extra in your distribution file, then build the image with nistor dist build --tag:

[activation]
tools = ["gcp", "microstrategy", "sap_hana"]

[packages]
extras = { nistor-tool-microstrategy = ["sdk"] }

The build leaves mstrio-py out of the third-party licence check, because the public policy has no exception for it. The image then contains software under MicroStrategy's licence: keep it private. The sdk extra accepts mstrio-py 11.5 up to, but not including, 11.7. See the overview for locks.

Create the connection#

Create a connection of type microstrategy in the web shell (Connections, /admin/connections) or with POST /api/v1/connections:

The only secret is password.

The test asks the Library for its status (GET /api/status), then logs in and out. The Intelligence Server must be version 11.3 up to, but not including, 11.7; another version is unhealthy, and no session is opened. If the Library does not report its version but the login works, the test answers degraded.

Where each part reads its credentials#

Three parts of the tool log in to MicroStrategy, and they do not all read the connection:

So set the three variables on the scheduler, backend and jobs roles as well as creating the connection. The enable_microstrategy Terraform switch of the reference root (below) sets them on the scheduler, compiler and dq-executor. The provider module also outputs a variable set for the backend and jobs (backend_env_vars), which the reference root does not wire: do that in your deployment root.

Tell the compiler what to scan#

Add a microstrategy entry to the environment's compiler configuration and save it with nistor compiler sync (see CLI):

[
  {
    "environment": "prod",
    "tools": [
      {
        "id": "microstrategy",
        "config": {
          "connection_id": "<connection-uuid>",
          "project_ids": ["<project-id>"],
          "default_bq_project": "acme-data-prod",
          "object_types": { "users": true }
        }
      }
    ]
  }
]

• project_ids lists the MicroStrategy projects to read.
• default_bq_project is the BigQuery project assumed for two-part table names in cube SQL. Without it, Nistor takes the project from three-part names in the same SQL.
• object_types switches a kind of object on or off. On by default: cubes, dashboards, documents, reports, refreshReports, schedules, events and subscriptions. Off by default: datasources, tables, facts and users.
• If the connection fails, the compile keeps the previous MicroStrategy nodes instead of dropping them.

What it collects#

The tool declares 14 node types: MicrostrategyCube, MicrostrategyDashboard, MicrostrategyDocument, MicrostrategyReport, MicrostrategyIncrementalRefreshReport, MicrostrategySchedule, MicrostrategyEvent, MicrostrategySubscription, MicrostrategyDatasource, MicrostrategyTable, MicrostrategyFact, MicrostrategyUser, MicrostrategyAttribute and MicrostrategyMetric. A node id is <NodeType>_<object-id>-<project-id>.

Lineage, with the SDK:

• A cube takes its attributes and metrics as ancestors, and the BigQuery tables and SAP HANA tables that its SQL view reads.
• A report takes the BigQuery tables its SQL reads.
• A dashboard takes the datasets it uses.
• An incremental refresh report takes its target cube. Resolving it needs a MicroStrategy privilege (below).

Refreshes that Nistor did not start are observed. Every 60 seconds (MSTR_EXTERNAL_RUN_POLL_INTERVAL_SECONDS) a poller reads the Intelligence Server job monitor and records running jobs, then resolves their outcome on a later tick: the monitor drops a job once it finishes. By default it watches only the objects that an active rule manages. Set MSTR_OBSERVE_ALL_EXECUTIONS=true to watch the whole catalogue and to record reads as well.

Usage comes from MicroStrategy Platform Analytics. An hourly poll records read events, and the shell can load the history of one object on demand. The object ids of the Platform Analytics project and cubes are settings (MSTR_PA_*) with defaults.

Freshness of a cube comes from its last update time.

SAP HANA#

The SAP HANA tool reaches no SAP system. It has no connection, no executor and no settings, and its health probe always answers healthy ("no connection: identity-only tool"). Nistor never logs in to SAP HANA, and no SAP credential is needed.

What it does is read SQL. For each cube SQL view that the MicroStrategy tool reads, it finds FROM and JOIN references whose schema looks like SAP: _SYS_BIC, _SYS_BI0 and other _SYS_ schemas (HANA calculation views and BW InfoCubes), SAP followed by letters or digits such as SAPABAP1 and SAPSR3 (ECC and S/4HANA tables), or any schema name containing HANA or BW. Each reference becomes a SapHanaTable node:

SapHanaTable_PRD_<schema>_<object>

/ and . in the object path become -. The system part is PRD for every table that the MicroStrategy tool finds. Only cube SQL views are read, not report SQL. Without the SAP HANA tool active, cubes compile without their SAP sources.

Refresh from rules#

Two executors ship with the tool. Both need the Nistor permission actions:run_microstrategy.

Set project_id in the parameters. Without it the executor uses a project id fixed in the code, which will not be yours.

- name: Refresh the acme sales cube when its tables are loaded
  priority: 5
  status: active
  code_id: bi/acme-sales-cube-refresh
  applies_to:
    node_ids:
      - MicrostrategyCube_<cube-id>-<project-id>
  when:
    events:
      - origin: ancestors
        types: data_updated
        ancestor_depth: direct
    wait_for_all_ancestors: true
  if:
    quiet_period_seconds: 300
  then:
    target:
      same_as_applies_to: true
      scope: node
    do:
      kind: single
      action:
        type: microstrategy_refresh
        parameters:
          project_id: <project-id>

Check it with nistor rules validate --files <file>. See Rules and scheduling.

How a refresh behaves:

• If MicroStrategy answers a transient error or a lock conflict before it returns a job id, Nistor retries with a backoff for up to 180 seconds (MSTR_TRIGGER_PATIENCE_SECONDS).
• A cube refresh is not finished at the first update of the cube: Nistor waits for the publish step to settle. When the job monitor cannot say, it trusts the cube's last update time once that is older than MSTR_CUBE_PUBLISH_SETTLE_SECONDS (120 seconds).
• After three failures in a row the circuit breaker opens for 120 seconds, so that a MicroStrategy server at its session limit is not hammered.
• An interrupted dispatch is failed, not started again.
• The cost of a refresh is reconstructed from the BigQuery work it caused, in a time window around the run. There is no pre-flight cost estimate.

A poller whose login MicroStrategy rejects (401 or 403) opens a configuration incident, skips its tick, and waits 15 minutes between login attempts until the login works again (MSTR_AUTH_BACKOFF_MINUTES).

Permissions#

Give the MicroStrategy account these rights. Nistor checks the first two before a rule runs.

A cube whose SQL view the account cannot read, or whose privilege it lacks, yields no sources. The compile goes on and logs the gap as one for a MicroStrategy administrator to close.

Settings#

These environment variables tune the tool. Defaults are in brackets.

Terraform#

The reference root has a switch, enable_microstrategy (off by default). With it on, the provider module creates the secret for the password and sets MSTR_* on the scheduler, compiler and dq-executor. Its inputs are mstr_server_url, mstr_username, mstr_password (pass it as TF_VAR_mstr_password, never in a file), mstr_project_name, mstr_project_id, max_concurrent_mstr_workers (3), max_concurrent_mstr_operations (2), mstr_max_retries (5), mstr_retry_initial_delay (10 seconds) and mstr_retry_max_delay (300 seconds). See Install on GCP.