Articles
MicroStrategy Interview Questions: Level Metrics, Filters, and Double Counting
Practice MicroStrategy level metrics with store and region examples, filter traces, repeated totals, distinct counts, and evidence for a safe report repair.
A regional revenue value appears twice in a MicroStrategy report. Is that a defect, a useful denominator, or a subtotal waiting to go wrong? You cannot answer from the repeated number alone. You need the metric's calculation grain, its filter population, and the rule used to combine results.
These MicroStrategy interview questions focus on BI development with the tool. They do not describe interviewing at the MicroStrategy company. Official facts are attributed to current Strategy documentation. Original practice and inference are labeled below; no candidate reports establish an employer's questions, scoring, or process here.
The central skill is to explain why a value exists before changing how it totals. A smaller number is not evidence of a correct repair. For a related SQL exercise, Aggregate User Events and Check Join Uniqueness practices the same habit of checking output keys before trusting a total.
1. What are target, grouping, and filtering?
Official fact: Strategy defines a metric level through its target, grouping, and filtering. Target specifies the calculation context; grouping controls aggregation at that context; filtering controls interaction with the report filter. Standard grouping groups by the target, while grouping None excludes the target and its children from grouping. These are separate controls. Elements of a Metric Level.
Original practice: Suppose the business asks, “What share of each region's monthly sales came from each store?” Define the numerator at Store and Month, and the denominator at Region and Month. Assume Store belongs to exactly one Region and the calendar hierarchy is separate.
| Month | Region | Store | Sales |
|---|---|---|---|
| January | West | W1 | 60 |
| January | West | W2 | 40 |
| January | East | E1 | 80 |
| January | East | E2 | 20 |
| February | West | W1 | 30 |
| February | West | W2 | 20 |
| February | East | E1 | 10 |
| February | East | E2 | 40 |
The fixture has exactly one sales row per Month–Store key. January has regional totals of 100 and 100; February has 50 and 50. Keeping two months with different totals makes an accidentally dropped calendar filter observable.
A useful answer names both the value and its key: “January West revenue is 100 at Month–Region grain.” Saying “the denominator is 100” leaves its meaning unresolved. If that denominator later appears beside W1 and W2, two display rows can refer to one regional calculation.
2. Why does a regional denominator still behave like a store metric?
Official fact: The report level makes a metric adapt to report attributes. Keeping it can add those attributes to the grouping; removing it leaves the explicitly defined level. Workstation level metrics.
Original practice: Put Month, Region, and Store on the report. A denominator intended to group only by Month and Region can accidentally retain Store through the report level. In this fixture, a calculation grouped by all three keys returns 60 for W1. Dividing W1's 60 by that 60 yields 100%, which looks plausible until W2 also claims 100%.
Describe the intended configuration before proposing a repair: explicit Region and Month units, Standard grouping, with report-level inheritance removed from this denominator. Retain Month deliberately; “regional total” should not silently become an all-time total. The numerator remains a store calculation.
Now trace expected results: W1 is 60/100, W2 is 40/100, E1 is 80/100, and E2 is 20/100. Within each January region, store shares total 100%. Across both regions those four percentages total 200%, because they have different denominators. A company-wide share needs company-wide sales as its denominator.
The interview follow-up is often a report change. If someone adds Product, ask whether the denominator must stay regional or adapt to Product. Neither choice is automatically correct. Write the business definition, inspect the effective units, and test the added attribute rather than removing inheritance from every metric indiscriminately.
3. How do Standard, Absolute, and Ignore change the calculation?
Official fact: Standard uses the report qualification. Absolute raises a qualification to the target when possible. Ignore removes qualifications on that target and its related parent or child attributes; it does not remove an unrelated calendar qualification. Filtering None leaves the relationship unspecified and is different from Ignore. Workstation filtering options.
Original practice and inference: Filter to January and Store W1. Trace the regional denominator with explicit Region and Month units. Apply the mode below to the Region unit, keep Month at Standard, and hold other settings fixed.
| Region filtering mode | Sales rows entering this calculation | Regional result before display joins |
|---|---|---|
| Standard | January W1 | West: 60 |
| Absolute | January W1 and W2, because W1 selects its parent West | West: 100 |
| Ignore | January W1, W2, E1, and E2 | West: 100; East: 100 |
This is a calculation-population matrix, not a promise that East appears in the finished grid. Another metric, its joins, and the report's other settings can affect the final rows. Separate “this metric can calculate East” from “the report displays East.”
Ask the business what selecting W1 means. If the denominator should remain its whole region, Absolute fits this fixture. If it should mean selected stores only, Standard gives 60. Ignore has a broader geographic calculation scope; it is not a shortcut for preserving one selected parent region.
Change only January to February. Expected Standard is West 30; Absolute is West 50; Ignore calculates West 50 and East 50. Returning January's 100 would expose a lost period boundary. Also select W1 and E1 together: Absolute should include both parent regions, so a single-store test alone is insufficient.
4. When is repetition valid, and when is it double counting?
Original practice: Display the complete January report with the correctly calculated regional denominator:
| Store | Store sales | Region sales | Store share of region |
|---|---|---|---|
| W1 | 60 | 100 | 60% |
| W2 | 40 | 100 | 40% |
| E1 | 80 | 100 | 80% |
| E2 | 20 | 100 | 20% |
Three failure paths require different evidence:
- Expected display repetition: West's regional value appears beside both West stores. Its Month–Region key remains meaningful. Repetition supports the denominator.
- Wrong subtotal grain: Adding the displayed Region sales column gives 400. Summing one value per January region gives 200.
- Physical join fanout: A regional budget row joined to multiple store sales rows can multiply the budget before aggregation. A subtotal setting cannot repair an already incorrect input population.
Do not fix the second problem with SUM(DISTINCT region_sales). Both regions legitimately have sales of 100, so distinct numeric values would total only 100. Deduplicate by the business key when appropriate, not by the amount. For the third problem, compare row counts and distinct fact keys before and after the join, then inspect the relationship cardinality.
Official fact: Strategy's Subtotal Dimensionality Aware property allows subtotaling to respect metric level; its effect also depends on Query Population Dimensionality Aware. Subtotal Dimensionality Aware.
Recommendation: Inspect the effective inherited properties and the actual subtotal result. A configured checkbox is weaker evidence than a reconciled Month–Region subtotal. Keep an exported-grid sum separate from the report engine's subtotal: an export consumer may simply add repeated cells.
5. How would you verify the numbers independently?
Original practice: Use a small SQL oracle to establish the expected denominator grain. This is conceptual SQL against the fixture, not a claim about the exact SQL Strategy generates:
WITH region_month AS (
SELECT month, region, SUM(sales) AS region_sales
FROM sales
WHERE month = 'January'
GROUP BY month, region
)
SELECT s.region, s.store, s.sales, r.region_sales,
1.0 * s.sales / NULLIF(r.region_sales, 0) AS share
FROM sales AS s
JOIN region_month AS r
ON s.month = r.month AND s.region = r.region
WHERE s.month = 'January'
ORDER BY s.region, s.store;
The join must include Month and Region. Joining on Region alone would attach multiple monthly totals once the CTE covers more than one month. Do not mistake a passing single-month test for proof that the production join is complete.
For the filter matrix, change the denominator's input population separately from the numerator's W1 selection. Standard aggregates W1; Absolute aggregates the West parent population; Ignore aggregates all regions for the retained month. Then inspect how those result sets combine.
The accompanying local fixture checks arithmetic with SQLite, including the 400 naive total and the 100 distinct-amount failure. It does not execute a Strategy report. In an interview or project, the final evidence still needs the metric definition, generated query passes, intermediate grain, and rendered subtotal from the actual environment.
6. Why are distinct counts and ratio subtotals different?
Original practice: W1 serves customers A and B; W2 serves B and C. Each store has two distinct customers, but West has three. Adding store counts produces four because customer B belongs to both populations. No rearrangement of the two counts can recover the overlap information reliably.
Recompute at the desired customer population or use a supported base-data aggregation path. First define whether a customer is unique per month, region, or the whole company. The correct deduplication key changes with the question.
Official fact: Current Subtotal from Base documentation requires Strategy Analytical Engine 14 or higher. It specifies metric options and excludes scenarios including working set reports and nesting in other smart/Subtotal from Base metrics; unsupported cases can return NULL. Enable Subtotal from Base.
Do not present that feature as a universal switch for every installed version. Check the engine, configuration, and report scenario, then test customer overlap explicitly.
Official fact: Strategy distinguishes totaling the parts of a compound metric before division from totaling row-level ratios. Smart subtotals use the former approach. Applying Metric Options.
Original practice: A store with profit 12 on revenue 60 and another with profit 2 on revenue 40 have regional margin 14/100, or 14%. Adding their 20% and 5% margins gives 25%; averaging them gives 12.5%. Explain which underlying amounts must survive aggregation. Also specify the zero-revenue rule rather than letting display formatting conceal undefined division.
7. What should a safe repair handoff contain?
Recommended practice: Capture one failing report and the smallest fixture that reproduces it. Record the business definition, target units, report-level inheritance, grouping, filtering, effective subtotal properties, and input fact keys. Change one semantic choice at a time.
| Check | Evidence to retain |
|---|---|
| Add or remove Store | Regional denominator remains at the intended grain |
| Select one store, then stores from two regions | Calculation population matches the agreed denominator |
| Switch January to February | Values change from 100 to 50 per region |
| Inspect detail and subtotal | Repeated cells are not treated as new regional facts |
| Add overlapping customers | Regional distinct count is recomputed correctly |
| Test empty and zero-sales populations | NULL, zero, and missing rows follow an explicit rule |
If a shared metric is reused elsewhere, inspect those dependent reports before saving. A repair that fixes this report by changing the meaning of another report is incomplete. Preserve a rollback definition and expected results, not merely a screenshot of a nicer total.
A concise interview explanation might be: “I found Store in the denominator's effective grain. I would remove that inherited unit for this metric, retain Month and Region, choose filtering from the business selection rule, and reconcile both detail shares and the subtotal. I would then test two regions and another month before updating dependent reports.” This states a diagnosis, a bounded change, and observable checks.
Practice the SQL boundaries behind the metric
| Question | Connection to this article |
|---|---|
| Aggregate User Events and Check Join Uniqueness | Check aggregation keys and whether a join introduces extra rows. |
| Explore Employee and Session Audit Tables Before Analysis | Establish table grain before defining a metric. |
| Measure Customers Ordering from Bottom-Quartile Restaurants | Separate restaurant selection from the customer population being counted. |
| Count Users Active After Signup | Keep user identity and time qualifications explicit. |
| Find Values Owned Only by the Selected User | Distinguish repeated rows from distinct ownership. |
Start with Aggregate User Events and Check Join Uniqueness. Explain the output key and expected row count before running your query, then use that habit when inspecting a metric's generated SQL.