Metric values are repeated across rows when a report is executed in MicroStrategy

Metric values are repeated across rows when a report is executed in MicroStrategy

When comparing report results between DB Query Tool and MicroStrategy, some reports show repeated metric values in MicroStrategy where there were none in DB Query Tool.

To illustrate the issue, a fact table CAT_ITEM_SLS has been added into the MicroStrategy Tutorial project and populated with a small set of three rows.

CAT_ID	ITEM_ID	REVENUE
1	1	10
1	2	20
2	2	30

Report results in DB Query Tool:

Report results in MicroStrategy:

In MicroStrategy, the row for "Art As Experience" in the Spring 2007 catalog repeats the $20 value from the Winter 2007 catalog, where DB Query Tool shows the $30 value from the fact table.

CAUSE

The discrepancy occurs because the attribute elements for Catalog and Item are in a many-to-many relationship, but the attribute relationship in the MicroStrategy schema is defined incorrectly with a one-to-many relationship.

Note: MicroStrategy Tutorial ships with a many-to-many relationship between Catalog and Item. The relationship was altered in the above example to illustrate the issue.

The MicroStrategy Analytical Engine prepares data for display in the cross-tabbing step by extracting, from the result table, several normalized tables for each attribute and metric. (This supports dimensionality-aware subtotals and dynamic aggregation, among other features.)

When attributes in a metric's dimensionality are related one-to-many according to the schema, the lowest-level child attribute is sufficient to identify each metric row uniquely. Users may observe this behavior in the MicroStrategy SQL Generation Engine, in that intermediate tables may omit one-to-many parent attributes. Thus, in the above example, MicroStrategy normalizes the Revenue metric results as follows:

ITEM_ID	REVENUE
1	$10
2	$20

If the attribute elements truly had a one-to-many relationship, this normalized table would be valid because each Item ID would map onto exactly one Catalog ID. Item ID 2 maps onto two Catalog IDs, and its normalized metric value is repeated as a result.

ACTION

The report returns valid results if the attribute relationship is modified to be many-to-many. With a many-to-many relationship, the Analytical Engine normalizes the Revenue results based on both attributes and all three values are preserved in the normalized table.

In some scenarios, the warehouse data should have been in a one-to-many relationship but invalid data may have been introduced into the warehouse. Correcting the attribute ID values to maintain a true one-to-many data relationship will also resolve the issue.

Note: Changing the Analytical Engine VLDB property "Metric Level Determination" to the option "Include higher-level related attributes in metric level (deprecated)" bypasses the Analytical Engine normalization logic and also produces the expected report results. However, this could produce inflated subtotal or dynamic aggregation results for dimensional metrics. It is generally not recommended to change this setting except for temporary scenarios while fixing the incorrectly mapped data model.

IMPORTANT

According to KB6831 ("Known data modeling restrictions and solutions in MicroStrategy SQL Generation Engine"), MicroStrategy SQL Generation Engine does not support chains of many-to-many relationships. For example, the following hierarchy would not be valid, because of multiple counting and the removal of some filtering conditions. It may also cause join paths between attributes to be evaluated differently.

Not recommended:

Therefore, it is not a correct solution to change a large number of attribute relationships to be many-to-many.

An alternate approach to many-to-many relationships is to make the many-to-many attributes independent parents of a surrogate key attribute. The many-to-many attributes are not directly related to each other, but have separate one-to-many relationships to the surrogate key. The surrogate key can have as many parents as needed without violating the restriction against in-line many-to-many relationships. The surrogate key should be unique for every distinct combination of its parents. If the attributes exist in a denormalized dimension table, the table's primary key would suffice as the common child.