KB18272: How does the metric qualification advanced option to consider other qualifications interact with metric conditionality in MicroStrategy SQL Generation Engine

• Strategy

This article describes how does the metric qualification advanced option to consider other qualifications interact with metric conditionality.

Metric qualifications -- Set of (attribute list) Where (metric condition) -- may choose to import qualifications from the surrounding filter context into the evaluation of the metric on which the filter is defined. This is done by an advanced option, 'When resolving this qualification in a report, other qualifications will be taken into consideration and merged.' This option is described in the following Strategy Knowledge Base technical note:

KB18271 (KB5200-8X-2414): What is the metric qualification advanced option 'When resolving this qualification in a report, other qualifications will be taken into consideration and merged' in Strategy SQL Generation Engine 8.x?

The option is intended to include qualifications from the same filter object of which the metric qualification is a part. In the example in KB18271 (KB5200-8X-2414), the metric qualification and Quarter condition are both part of the report filter; with the option enabled, the Quarter condition becomes part of the metric qualification. The situation is straightforward because no filter exists other than the report filter.

If a metric on the report has a filter embedded in it (under 'Conditionality,') this is a separate filter object from the report filter. Application of other qualifications, then, depends on the location of the metric qualification and the metric conditionality embedding settings.

Hereafter, qualifications in metric conditionality will be highlighted in green and report filter qualifications in blue.

Case 1: Metric qualification in the report filter, unrelated metric conditionality
Consider a report with a report filter defined as follows:

Set of Item Where (Units Sold >= 100)
AND
Quarter in (Q1 2004)
In addition, the template metric has a metric condition Call Center in (Web).

The "Consider other qualifications" setting applies to the "Set of Item" condition in the report filter. Which of the other qualifications are considered depends on the filter embedding method defined as part of the metric's conditionality.

The following table shows the filtering pass generating the set of items for the four possible cases, assuming that the metric qualification is part of the report filter.

This system check can be scheduled on a daily basis. Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Quarter filter is considered in the metric qualification because it is in the report filter along with the metric qualification. The Call Center filter, as part of the template metric's conditionality, is not part of the report filter and is not considered when determining the qualifications to apply to the metric filter pass.	This system check can be scheduled on a daily basis. Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Quarter filter is considered in the metric qualification because it is in the report filter along with the metric qualification. The Call Center filter, as part of the template metric's conditionality, is not part of the report filter and is not considered when determining the qualifications to apply to the metric filter pass.	This system check can be scheduled on a daily basis. Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Quarter filter is considered in the metric qualification because it is in the report filter along with the metric qualification. The Call Center filter, as part of the template metric's conditionality, is not part of the report filter and is not considered when determining the qualifications to apply to the metric filter pass.
This system check can be scheduled on a daily basis. Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Quarter filter is considered in the metric qualification because it is in the report filter along with the metric qualification. The Call Center filter, as part of the template metric's conditionality, is not part of the report filter and is not considered when determining the qualifications to apply to the metric filter pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ00 from ITEM_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where a12.QUARTER_ID in (20061) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Here, the Call Center filter in the template metric's conditionality is brought into the report filter, and then the metric qualification is resolved against the other qualifications. Both attribute conditions are included in the metric filtering pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Consider other qualifications = NOThe metric qualification is instructed to ignore other qualifications. Neither attribute condition appears in the metric filtering pass.
select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Consider other qualifications = NOThe metric qualification is instructed to ignore other qualifications. Neither attribute condition appears in the metric filtering pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ00 from ITEM_MNTH_SLS a11 group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Merging the template metric's condition into the report filter overrides the option to consider other qualifications. Both attribute conditions appear.	select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Call Center filter is merged into the template metric's conditionality, and is thereby available to be considered when determining the qualifications to apply to the metric qualification. Both attribute conditions are included in the metric filtering pass.

Case 2: Metric qualification in the metric conditionality, unrelated report filter
Reversing the situation from case 1, the '(Set of Item) AND Quarter' filter is placed into the conditionality of the metric used on the report template, and the Call Center qualification is placed into the report filter.

Report filter:

Call Center in (Web)
Metric conditionality:

Set of Item Where (Units Sold >= 100)
AND
Quarter in (Q1 2004)
When the "Consider other qualifications" option is enabled, its behavior is reversed with respect to the filter embedding method.

select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Call Center filter is merged into the template metric's conditionality, and is thereby available to be considered when determining the qualifications to apply to the metric qualification. Both attribute conditions are included in the metric filtering pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Call Center filter is merged into the template metric's conditionality, and is thereby available to be considered when determining the qualifications to apply to the metric qualification. Both attribute conditions are included in the metric filtering pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Call Center filter is merged into the template metric's conditionality, and is thereby available to be considered when determining the qualifications to apply to the metric qualification. Both attribute conditions are included in the metric filtering pass.
select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Metric conditionality filter embedding method: Merge report filter into metricMetric conditionality filter embedding method: Merge metric condition into reportConsider other qualifications = YESThe Call Center filter is merged into the template metric's conditionality, and is thereby available to be considered when determining the qualifications to apply to the metric qualification. Both attribute conditions are included in the metric filtering pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ00 from ITEM_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Here, the metric qualification (in the template metric's conditionality) is resolved against the Quarter condition also in the template metric. Subsequently, this completed filter (with the Quarter filter considered in the metric qualification) is merged into the report filter. The Call Center filter is omitted from the metric filtering pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Consider other qualifications = NOThe metric qualification is instructed to ignore other qualifications. Neither attribute condition appears in the metric filtering pass.
select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Consider other qualifications = NOThe metric qualification is instructed to ignore other qualifications. Neither attribute condition appears in the metric filtering pass.	select a11.ITEM_ID ITEM_ID into #ZZMQ00 from ITEM_MNTH_SLS a11 group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0 Merging the template metric's condition into the report filter overrides the option to consider other qualifications. Both attribute conditions appear.	select a11.ITEM_ID ITEM_ID into #ZZMQ01 from ITEM_CCTR_MNTH_SLS a11 join LU_MONTH a12 on (a11.MONTH_ID = a12.MONTH_ID) where (a12.QUARTER_ID in (20061) and a11.CALL_CTR_ID in (18)) group by a11.ITEM_ID having sum(a11.TOT_UNIT_SALES) >= 100.0

The rule of thumb, then, is:

When merging other conditions toward the metric qualification (e.g., if the metric qualification is in the report filter, merging metric conditionality into the report filter), include other qualifications that are merged.
When merging filters away from the metric qualification (e.g., if the metric qualification is in the report filter, merging the report filter into the template metric), include qualifications from the filter object containing the metric qualification, but do not include qualifications from other filter objects.

Comment

0 comments

Details

Knowledge Article

Published:

May 25, 2017

Last Updated:

May 25, 2017