Role-Playing Customer Attributes Inflate Revenue by 3.4x
A retail project on MicroStrategy 2021 has one CUSTOMER lookup table (1.2 million rows) feeding two role-playing attributes: Bill-To Customer and Ship-To Customer. Both use the same ID and DESC forms. An analyst builds a grid containing both attributes and the certified metric Revenue = Sum(fact_sales.amount). The grid returns $48.6M; finance's control report returns $14.3M. A second metric, Customer Count = Count(Customer ID), returns 3.9M instead of 1.2M. FACT_SALES has one row per order and carries both customer keys. The project has no relationship between the two customer roles, and the Customer hierarchy has no compound key. ETL recently added 18 duplicate rows to the lookup table for a reorganization. The analyst wants to fix the report with a filter; the modeling team wants a governed semantic-layer fix. The dashboard serves 250 users and must be certified next week.
1. Diagnose why Revenue and Customer Count inflate. Explain the join and aggregation behavior inside MicroStrategy, including the role of duplicate lookup rows.
Read the worked answer
Revenue is being evaluated at the combination of Bill-To Customer and Ship-To Customer, not at the order grain. FACT_SALES has one row per order and two independent customer keys. Because there is no relationship path that resolves the two roles to a common parent, MicroStrategy produces a result set at the cross-product of the two attributes. Each fact row is matched to its bill-to customer and its ship-to customer, and the same order amount is then repeated across the combined customer combinations. Summing that result multiplies revenue.
The duplicate lookup rows make the fan-out worse. The ID form is the attribute key; if 18 customer IDs now appear more than once, each join to the lookup returns multiple rows for those customers. MicroStrategy cannot assume uniqueness, so the fact row is duplicated again. Customer Count is not immune: Count(Customer ID) counts the inflated rows, not distinct customers. The 3.9M result is consistent with a multiplied grain and non-unique keys, not with a data-volume change in the fact table.
The key point is that the metric has no declared dimensionality that pins it to a single customer role. The report asks for two attributes that the semantic layer cannot reconcile, so the engine does the only thing it can: aggregate at the report grain after the joins. That is why the symptom appears only when both roles are in the same grid.
2. Recommend a governed semantic-layer fix, including metric dimensionality/level, attribute relationships, and lookup-key design. Explain why a report filter is not sufficient.
Read the worked answer
Fix the model in three layers. First, clean the lookup so the ID form is unique; if a customer can legitimately have multiple records, introduce a compound key or a relationship table rather than leaving duplicate IDs. Second, model the two roles explicitly. Bill-To Customer and Ship-To Customer should each have their own relationship to FACT_SALES and should not share a hierarchy path that implies they are the same entity. If a relationship between the roles is needed, create a governed relationship table at the order grain, not an implicit many-to-many.
Third, define metrics with explicit levels. Create Revenue (Bill-To) with level Bill-To Customer and Revenue (Ship-To) with level Ship-To Customer. Use Count(Distinct Customer ID) with the appropriate level for customer counts. If a single report must show both roles, use separate metrics and separate sections, or aggregate first at Order ID and then allocate deliberately; do not expect one Revenue metric to be simultaneously valid at two unrelated customer grains.
A report filter is not sufficient because it changes which rows are returned, not the grain at which the metric is evaluated. Filtering to one region or one customer still leaves two customer roles in the report and still permits the cross-product. The semantic layer must declare the grain; otherwise each new report re-creates the defect.
3. What governance controls would prevent this from recurring across the 250-user dashboard and future role-playing attributes?
Read the worked answer
Treat the attribute and metric as governed schema objects. Lock the certified Customer attributes and Revenue metrics so report authors cannot add a second role without review. Add a modeling standard for role-playing dimensions: each role gets its own attribute object, its own relationship, and metrics with explicit levels. Require a uniqueness test on every attribute ID form in the ETL pipeline and fail the load if duplicates appear.
Use the change journal and object migration to show who changed the lookup, the attribute forms, and the metric definitions, and require dependent-object review before promotion. Add an integrity check that compares dashboard totals to finance control totals at the order, customer, and region levels. Publish a test report that includes both customer roles and alerts on row-count or revenue multipliers. Monitor audit row counts for the dashboard after each schema release.
Finally, certification should include a negative test: add both roles to a grid and confirm the result equals the order-level control total. Without that test, the dashboard can be certified while the underlying grain defect remains available to the next author.