Level Metrics – Filtering

In every business model, multiple quantifiable measures are used to monitor the status of each process. These metrics must be analyzed at various levels on business needs to derive deeper insights.

In Microstrategy, the metrics are usually aggregated at the level of the report attributes, which is commonly known as report level. Hence, the Level Metrics comes into play to find the evaluation at many attribute levels, as we can see briefly in this article.

Scenario:

To better understand the concept, consider the following scenario of estimating the total number of goods sold by a company over a period of time. We have created the metric “Quantity Sold” on top of “Units Sold” fact with the default report level.

Metric Definition: Sum([Units Sold]) {~}

When the created metric is used in a report with Year and Month attributes, the report shows the aggregated value for the report’s granular level, which is the month. Only the first quarter of each year has filtered out for better readability.

SQL for Report Level Metric:

select a11.MONTH_ID  MONTH_ID,
 sum(a11.TOT_UNIT_SALES)  'Quantity Sold'

from FACT_SALES a11
where a11.MONTH_ID in (201401, 201402, 201403, 201501, 201502, 201503, 201601, 201602, 201603, 201701, 201702, 201703)
group by a11.MONTH_ID 

Now the challenging tasks for Business Analyst would be,

• If they need to see data at the annual level and compare the contributions of each month to that year?
• If they want to know the relationship between the seasonal changes with each year?
• If they want to find out how much each year contributes to total sales.

As these require the aggregation of metrics at a higher level, we must create Level metrics by altering their filtering properties.

Filtering:

Filtering is the metric property that determines how the report filter should behave with the metric calculation. There are four different ways to control the interactions,

• Standard
• Absolute
• Ignore
• None

Some Level metrics are created similar to the metric definition of “Quantity Sold” with the level at Year attribute.

Standard filtering:

Standard filtering determines that the report filter should interact normally with the metric calculation without any modifications.
As per the scenario, only the months mentioned in the report filter, i.e., months from the first quarter, are included in the metric calculation of Quantity Sold (Standard, Standard).

select a12.YEAR_ID  YEAR_ID,
 sum(a11.TOT_UNIT_SALES)  'Quantity Sold (Standard, Standard)'
from FACT_SALES a11
 join DIM_MONTH a12
   on  (a11.MONTH_ID = a12.MONTH_ID)
where a11.MONTH_ID in (201401, 201402, 201403, 201501, 201502, 201503, 201601, 201602, 201603, 201701, 201702, 201703)
group by a12.YEAR_ID 

Absolute filtering:

Absolute filtering allows the report filter to change its level to the target (attribute level at which the metric calculation grouped).
For the above scenario, all months of each year are included in the metric evaluation irrespective of the month filter; the metric Quantity Sold (Absolute, Standard) does aggregation for all months in the entire year.

select a12.YEAR_ID  YEAR_ID,
 sum(a11.TOT_UNIT_SALES)  'Quantity Sold (Absolute, Standard)'
from FACT_SALES a11
 join DIM_MONTH a12
   on  (a11.MONTH_ID = a12.MONTH_ID)
where ((a12.YEAR_ID)
 in (select s21.YEAR_ID
 from DIM_MONTH s21
 where s21.MONTH_ID in (201401, 201402, 201403, 201501, 201502, 201503, 201601, 201602, 201603, 201701, 201702, 201703)))
group by a12.YEAR_ID

Ignore filtering:

Ignore filtering does not consider the filtering conditions based on any attribute in the target and any other attribute in the same hierarchy.
Hence, Quantity Sold (Ignore, Standard) metric executes as though there is no filter applied to Months for the first quarter and extracts according to the grouping conditions, which in this case is year.

select a12.YEAR_ID  YEAR_ID,
 sum(a11.TOT_UNIT_SALES)  'Quantity Sold (Ignore, Standard)'
from FACT_SALES a11
 join DIM_MONTH a12
   on  (a11.MONTH_ID = a12.MONTH_ID)
group by a12.YEAR_ID

None filtering:

None filtering does not allow filter conditions based on the given report filter. So the filtering part is determined by the target and grouping attributes, such that if any attribute belongs to the same hierarchy as the report filter attribute, it aggregates on that attribute or else does by absolute filtering.
In the scenario, the provided filtering attribute “Year” falls under the same hierarchy, so Quantity Sold (None, Standard) metric aggregates as per the month level.

select a12.YEAR_ID  YEAR_ID,
 sum(a11.TOT_UNIT_SALES)  'Quantity Sold (None, Standard)'
from FACT_SALES a11
 join DIM_MONTH a12
   on  (a11.MONTH_ID = a12.MONTH_ID)
where a11.MONTH_ID in (201401, 201402, 201403, 201501, 201502, 201503, 201601, 201602, 201603, 201701, 201702, 201703)
group by a12.YEAR_ID

If the metrics you are looking at aren’t useful in optimizing your strategy – stop looking at them.

Mark Twain