Example 1: Using level metrics - Microstrategy

Report requirements

Your company has recently kicked off a new ad campaign targeted at certain areas that present high growth opportunities. In your regions, this consists of the Boston, New York, and the Washington DC call centers. You need to perform an analysis from different perspectives and are looking for answers to the following:

  1. How do the sales of each call center compare to the total sales of the targeted call centers in a given region?
  2. How do the sales of each call center compare to the total sales of all the call centers in a given region?
  3. How do the sales of each call center compare to the total sales of all the call centers in a given region for a given year?

Solution 1

Grouping set to Standard and Filtering set to Standard

In this case, the Regional Sales is equal to the sum of the revenues of the call centers in a given region. This sum takes into account only those call centers that are included in the report filter. For example, the Mid-Atlantic Regional Sales only includes the Washington DC call center sales as this is the only call center from that region that has been included in the report filter. The metric groups at the target level of Region because grouping is standard.

With standard filtering, all of the report filter elements are included in the calculation of the metric. This occurs by placing the report filter in the WHERE clause of the SQL pass for this metric that is shown in the following example:


The report is displayed as follows:

report is displayed

The Revenue subtotals match up with the values of the total Regional Sales.

Solution 2

Grouping set to Standard and Filtering set to Absolute

In this case, the Regional Sales is equal to the sum of revenues of all call centers included in a given region. Grouping continues to occur at the target attribute level of Region.

With absolute filtering, the report filter is present in the subquery of the WHERE clause in the SQL pass for this metric as shown in the following example:


The report is shown in the following figure:

Grouping set to Standard and Filtering set to Absolute

Note the following:

  • With absolute filtering, the report filter is placed in the sub query of the WHERE clause only if it is of a lower level than the target. If the report filter is of a higher level than the target, there is no need for a subquery and so the engine does not use one.
  • The VLDB properties of the report may be changed to use two passes of SQL rather than a subquery.

Solution 3

Grouping set to Standard and Filtering set to Ignore

In this case, the engine ignores the report filter and the report displays the Regional Sales as the sum of revenues of all the call centers in that region.

With no filtering, the report filter elements that are directly related to the target attributes are not placed in the WHERE clause of the SQL pass for the metric as shown in the following example:


If the report filter contains attribute elements such as Year, these attributes are not ignored because they are not directly related to the target attribute Region.

In the following example, since call centers are directly related to the target attribute Region, the entire report filter is ignored. The report displays Revenue as the sum of revenues for the years 2002 and 2003 and Regional Sales as sum of revenues for the years 2002 and 2003.

sum of revenues for the years 2002 and 2003 and Regional Sales as sum of revenues

In the example that follows, Year 2003 is included in the report filter. As a result, the engine ignores the report filter but calculates Revenue and Regional Sales for the year 2003 only.

Solution

Security filters are included in the WHERE clause of the level metric’s SQL statement even with absolute or ignore filtering. The engine includes the security filter to ensure that there is no breach in security for any level metric. With filtering ignored, the security filter is unioned with the report filter and is applied to the metric also. With absolute filtering, the security filter is applied in the subquery with the report filter.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics