MicroStrategy developer on retainer: Branch Performance Scorecard generates 12 SQL passes from compound metrics — report scans FACT_TRANSACTIONS 12 times in 47 minutes — MicroStrategy developer on monthly retainer

MicroStrategy 2021.4.0 • SQL Server 2019 • LFG_Analytics • Branch Performance Scorecard • compound metrics • Multi-Pass SQL • FACT_TRANSACTIONS • Diagnostic Viewer • retainer billing

The operations director at Lakeside Financial Group had been running the Branch Performance Scorecard every Monday morning for two years. The report covered twelve financial metrics across 47 branch locations: net new accounts opened, average transaction value, fee revenue, overdraft count, and eight derived comparisons against prior-period and budget targets. The report had always been slow — 20 to 25 minutes in 2024, creeping toward 35 minutes through early 2026 — but the Monday morning run time had recently crossed 47 minutes, which pushed it past the 45-minute Intelligence Server execution timeout on most mornings. The MicroStrategy developer retainer was called in. The report had not been touched in eight months. What had changed was the data warehouse: a warehouse expansion in March 2026 had grown FACT_TRANSACTIONS from 28.4 million rows to 47.3 million rows as three more years of legacy transaction data were loaded from a decommissioned system. The 47-minute runtime was the product of twelve separate full-table scans of a fact table that had grown 67 percent.

Lakeside Financial Group ran MicroStrategy Intelligence Server 2021.4.0 on Windows Server 2019, connected to a SQL Server 2019 data warehouse. The LFG_Analytics MicroStrategy project contained 340 reports used by branch operations, finance, and compliance teams. The Branch Performance Scorecard was a Grid report in the Operations folder containing 12 metrics, 3 attributes (Branch, Region, Branch Manager), and a date range prompt. All 12 metrics had been defined as compound metrics — metrics whose formulas referenced other metric objects rather than direct SQL aggregate expressions against the warehouse tables.

What compound metrics are and why they generate Multi-Pass SQL

In MicroStrategy, a compound metric is a metric defined by a formula that references other metric objects. For example:

• [Net Fee Revenue] = [Gross Fee Revenue] − [Fee Waivers]
• [Avg Transaction Value] = [Total Transaction Amount] / [Transaction Count]
• [Budget Variance %] = ([Net Fee Revenue] − [Budget Net Fee Revenue]) / [Budget Net Fee Revenue]

Each of [Gross Fee Revenue], [Fee Waivers], [Total Transaction Amount], [Transaction Count], and [Budget Net Fee Revenue] is itself a metric object with its own SQL definition. When the MicroStrategy SQL Engine generates SQL for a report that contains compound metrics, it issues a separate SQL pass for each base metric component that cannot be resolved in the same query block. A compound metric that references two base metrics that are themselves compound metrics can cascade into 3, 4, or more SQL passes, each running as a separate SQL statement against the warehouse.

The MicroStrategy SQL Engine’s behavior with compound metrics is documented as Multi-Pass SQL. The engine resolves metric dependencies by computing each base-level metric in its own SQL pass, storing the result in a Temp table, then computing the compound metric in a subsequent pass that joins the Temp tables. For the Branch Performance Scorecard, 12 metrics with an average dependency depth of 2–3 base metrics each produced 12 separate SQL passes:

-- MicroStrategy Diagnostic Viewer: Branch Performance Scorecard (2026-10-06)
Report:    Branch Performance Scorecard
Project:   LFG_Analytics
SQL Passes: 12
Total Time: 47m02s (2,822 seconds)

Pass 1:  SELECT branch_key, SUM(txn_amount) FROM FACT_TRANSACTIONS ... GROUP BY branch_key
         Rows: 47,312,847   Time: 3m54s

Pass 2:  SELECT branch_key, COUNT(txn_id) FROM FACT_TRANSACTIONS ... GROUP BY branch_key
         Rows: 47,312,847   Time: 3m47s

Pass 3:  SELECT branch_key, SUM(fee_amount) FROM FACT_TRANSACTIONS WHERE txn_type='FEE' ...
         Rows: 47,312,847   Time: 3m51s

Pass 4:  SELECT branch_key, SUM(fee_waiver_amount) FROM FACT_TRANSACTIONS WHERE txn_type='WAIVER' ...
         Rows: 47,312,847   Time: 3m49s

Pass 5:  SELECT branch_key, COUNT(account_id) FROM FACT_TRANSACTIONS WHERE txn_type='OPEN_ACCT' ...
         Rows: 47,312,847   Time: 3m52s

Pass 6:  SELECT branch_key, SUM(overdraft_fee) FROM FACT_TRANSACTIONS WHERE txn_type='OD_FEE' ...
         Rows: 47,312,847   Time: 3m56s

[Passes 7-12: similar patterns, 3m44s to 4m02s each, all scanning FACT_TRANSACTIONS 47.3M rows]

Pass 12: SELECT TT1.branch_key, (TT3.net_fee / TT5.count_accts) AS acct_fee_ratio
         FROM TT1 JOIN TT3 ON ... JOIN TT5 ON ...   Time: 0m12s

Each of the first 11 passes scanned all 47.3 million rows in FACT_TRANSACTIONS independently. Only Pass 12 was a join of the pre-computed Temp tables. The 47-minute runtime was essentially 11 full-table scans of the same fact table, each retrieving a single aggregate value.

Diagnosis: Diagnostic Viewer SQL pass count and SQL View tab

The MicroStrategy developer retainer opened the Branch Performance Scorecard in MicroStrategy Developer and used the SQL View tab (Data › View SQL) to inspect the generated SQL before executing. The SQL View showed 12 distinct Pass N of 12 blocks, each with a separate SELECT statement scanning FACT_TRANSACTIONS. No pass combined multiple aggregate expressions from the same fact table into a single SELECT.

The developer then opened each of the 12 metric definitions in the Metric Editor. The pattern was consistent: each metric was defined as a formula referencing other metric objects. For example, the [Net Fee Revenue] metric formula was:

-- MicroStrategy Metric Editor: [Net Fee Revenue] (compound metric, before fix)
Formula:   [Gross Fee Revenue] - [Fee Waivers]

-- [Gross Fee Revenue] metric definition:
Formula:   Sum(fee_amount) {~+}
Fact:      FEE_AMOUNT (maps to FACT_TRANSACTIONS.fee_amount)
WHERE:     txn_type = 'FEE'

-- [Fee Waivers] metric definition:
Formula:   Sum(fee_waiver_amount) {~+}
Fact:      FEE_WAIVER_AMOUNT (maps to FACT_TRANSACTIONS.fee_waiver_amount)
WHERE:     txn_type = 'WAIVER'

Because [Net Fee Revenue] referenced two separate metric objects, MicroStrategy computed each base metric in its own SQL pass before computing the subtraction. Both base metrics scanned FACT_TRANSACTIONS independently, even though both aggregates could have been expressed as a single SQL SELECT with two SUM expressions and a shared WHERE clause or CASE branching.

The fix: restructuring compound metrics as direct SQL aggregate functions

The fix required redefining each of the 12 metrics as direct SQL aggregate functions — metric definitions where the formula is a SQL expression computed directly by the database engine, not a formula that references other MicroStrategy metric objects. For [Net Fee Revenue], the restructured definition:

-- MicroStrategy Metric Editor: [Net Fee Revenue] (direct SQL, after fix)
Formula:   Sum((Case When txn_type = 'FEE' Then fee_amount Else 0 End)
              - (Case When txn_type = 'WAIVER' Then fee_waiver_amount Else 0 End)) {~+}

-- No referenced metric objects. Single SQL aggregate expression.
-- MicroStrategy generates: SUM(CASE WHEN txn_type='FEE' THEN fee_amount ELSE 0 END
--                              - CASE WHEN txn_type='WAIVER' THEN fee_waiver_amount ELSE 0 END)

With all 12 metrics defined as direct SQL aggregate functions referencing no other metric objects, the MicroStrategy SQL Engine could compute all 12 values in a single SQL pass with 12 aggregate expressions in the SELECT clause:

-- Generated SQL after restructuring all 12 metrics (Pass 1 of 1):
SELECT
    A1.branch_key,
    SUM(CASE WHEN A1.txn_type='FEE'    THEN A1.fee_amount     ELSE 0 END
      - CASE WHEN A1.txn_type='WAIVER' THEN A1.fee_waiver_amt ELSE 0 END)  AS net_fee_rev,
    SUM(A1.txn_amount)                                                      AS total_txn_amt,
    COUNT(A1.txn_id)                                                        AS txn_count,
    SUM(CASE WHEN A1.txn_type='OPEN_ACCT' THEN 1 ELSE 0 END)               AS new_accounts,
    SUM(CASE WHEN A1.txn_type='OD_FEE'   THEN A1.overdraft_fee ELSE 0 END) AS od_fees,
    -- [7 more aggregate expressions for the remaining metrics]
FROM
    LFG_DW.dbo.FACT_TRANSACTIONS A1
    INNER JOIN LFG_DW.dbo.DIM_BRANCH A2 ON A2.branch_key = A1.branch_key
    INNER JOIN LFG_DW.dbo.DIM_DATE   A3 ON A3.date_key   = A1.txn_date_key
WHERE
    A3.fiscal_year = 2026
    AND A3.fiscal_month BETWEEN ? AND ?   /* date range prompt parameters */
GROUP BY
    A1.branch_key

The SQL View tab in MicroStrategy Developer confirmed 1 SQL Pass after the metric restructuring. The developer updated all 12 metric definitions in the Metric Editor, saved the changes, and triggered an Intelligence Server Schema Update to publish the updated metric definitions. The report cache was cleared to ensure the next run used the updated metric SQL.

The post-fix test run:

-- MicroStrategy Diagnostic Viewer: Branch Performance Scorecard (post-fix test run)
Report:    Branch Performance Scorecard
Project:   LFG_Analytics
SQL Passes: 1
Total Time: 4m38s (278 seconds)

Pass 1:  SELECT branch_key, [12 aggregate expressions] FROM FACT_TRANSACTIONS ...
         Rows processed: 47,312,847   Time: 4m22s

Pass 1 join: 16 seconds (joining DIM_BRANCH and DIM_DATE, formatting results)

The runtime dropped from 47 minutes and 2 seconds to 4 minutes and 38 seconds. The SQL Server execution plan for the single-pass query showed a Columnstore Index Scan with all 12 aggregate expressions computed in one pass — the SQL Server Columnstore index on FACT_TRANSACTIONS could compute all 12 SUM and COUNT expressions in a single scan of the compressed column data, which was significantly more efficient than 11 separate scans even with index optimization.

The operations director validated all 12 metric values for September 2026 against an independent pivot-table extraction from the data warehouse for five branches. All 12 values matched. The Monday morning report had been running for 6 hours by the time the retainer engagement concluded on Tuesday afternoon — the following Monday it completed in under 5 minutes.

Why compound metrics are a recurring MicroStrategy retainer problem

MicroStrategy’s metric object model is designed for reuse: a developer defines [Gross Fee Revenue] once, and any report or metric that needs gross fee revenue references that object. The compound metric pattern follows naturally from this model — [Net Fee Revenue] = [Gross Fee Revenue] − [Fee Waivers] is the most readable and maintainable metric definition for a business analyst building metrics in the Metric Editor.

The Multi-Pass SQL consequence is not visible at development time. At development time, the data warehouse is typically small and the 4-second vs. 40-second difference between 1 pass and 10 passes on a 2-million-row test table seems negligible. The problem becomes operationally significant only when the production warehouse grows to 10–50 million rows, at which point each additional SQL pass adds 3–5 minutes to the report runtime. A report that ran in 3 minutes on a test database can run in 45 minutes on production data.

The diagnostic is always the MicroStrategy Diagnostic Viewer or the SQL View tab. Any report with more than 2 SQL Passes is a candidate for metric restructuring. A pass count above 5 on a report used in daily operations is an operational risk. The fix — replacing compound metric references with direct SQL aggregate expressions using CASE WHEN patterns — requires 15–30 minutes per metric in the Metric Editor and preserves all report output values while reducing the SQL pass count to 1 or 2.

A MicroStrategy developer retainer who includes a quarterly metric definition audit — specifically reviewing all metrics in frequently-used reports for compound metric patterns that would generate Multi-Pass SQL as the data warehouse grows — catches these before they cause timeout failures in production.

How HourTab helps MicroStrategy developer retainers

The Lakeside Financial Group engagement totaled 5.5 hours: diagnosis (1.5h — 0.5h running the Branch Performance Scorecard in MicroStrategy Developer with Diagnostic Viewer active and capturing the 12-pass SQL output and per-pass timings, 0.5h reviewing all 12 metric definitions in the Metric Editor to confirm the compound metric formula pattern across each, 0.5h confirming the warehouse growth timeline from the DBA — FACT_TRANSACTIONS 28.4M rows pre-March to 47.3M rows post-March legacy load — and verifying that the per-pass runtime scaled linearly with row count) and fix (4.0h — 2.0h restructuring all 12 metric definitions in the Metric Editor from compound metric formulas to direct SQL aggregate functions using CASE WHEN patterns, validating that each restructured metric’s SQL View showed the correct aggregate expression, 0.5h Intelligence Server Schema Update and report cache clear, 1.0h QA test run and Diagnostic Viewer confirmation (1 SQL pass, 4m38s runtime), 0.5h production validation with operations director: all 12 metric values for September 2026 confirmed against pivot-table cross-check for 5 branches).

In a HourTab retainer log, the incident session reads: “Branch Performance Scorecard runtime 47min02s → 4min38s; Diagnostic Viewer: SQL passes 12 → 1; root cause: all 12 metrics defined as compound metrics referencing other metric objects → MicroStrategy SQL Engine generates 1 SQL pass per base metric; FACT_TRANSACTIONS 47.3M rows scanned 12 times independently; fix: all 12 metrics restructured as direct SQL aggregate functions using CASE WHEN patterns — no metric-to-metric references; Metric Editor edits + Intelligence Server Schema Update + cache clear; QA: SQL View 1 pass, 4m38s; SQL Server execution plan: Columnstore Index Scan single pass; production: operations director validated all 12 metric values for Sep 2026 vs. manual pivot cross-check on 5 branches — all match; retainer metric definition SOP updated: quarterly compound metric audit for top-20 reports by run frequency; 5.5h.” The operations director at Lakeside Financial sees exactly which metrics were the problem, why the runtime was proportional to warehouse growth, and what structural change prevents recurrence — all in one log entry instead of a sequence of email threads.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: MicroStrategy developer retainers

See also: Hyperion developer retainer · Cognos developer retainer · Teradata developer retainer