Definition of simple metrics - Microstrategy

Metrics are constructed of components that differentiate one metric from all others and also serve as criteria for defining the calculations and data included in each metric.

Simple metrics include these components:

  • The formula defines the data to be used and the calculations to be performed on the data. The outermost formula must be a group function.
  • The level, or dimensionality, determines the level at which to perform the metric calculation. For example, you can choose to calculate at the month level or year level.
  • Conditionality associates a filter to the metric calculation. This is an optional component.
  • The transformation applies offset values, such as “four months ago,” to the selected attributes. This is also an optional component.

Recall from Distinguishing between simple and compound metrics that a metric defined similar to the following is a simple metric:

Avg(Sum(Revenue) {Month+}){Year+}

This metric calculates the yearly average of monthly sales, and is actually two metrics. In the metric definition pane of the Metric Editor, the inner metric is contained within the outer metric. To view the definition of the inner metric, you must expand the formula of the outer metric. It is a simple metric because it can contain a level, condition, and transformation at its highest level, as illustrated by this screen shot from the Metric Editor:

Definition of simple metrics

The inner metric is Sum([Revenue]). Recall that this was previously defined as a simple metric. The code {Month} within the same set of parentheses indicates that this data is tallied at the month level, regardless of what appears on the report. Once this information is calculated, the second metric is performed, that is, the result from the first metric is averaged at the year level. The following diagram represents this process.

Definition of simple metrics

This type of metric provides a convenient method for multistep calculations when fact tables in the warehouse do not provide data at the appropriate higher level for subsequent calculations. You can therefore use the result of a metric calculation as an intermediate result set for calculating another metric. For example, your existing fact tables provide revenue figures for each day, but you need monthly sales information. Using this kind of metric allows you to obtain the monthly figures.

Formula

This is the essential part of the metric definition. The formula of a simple metric is a mathematical expression consisting of group functions, such as sum, average, minimum, maximum, and so on. It also includes the data to be used in the calculations. This can include facts, attributes, constants, and other metrics. The formula can also contain a non-group function or arithmetic operator, in addition to the required group function.

In SQL, the formula becomes part of the SELECT clause of the SQL command.

Defining custom plug-in functions

The MicroStrategy Function Plug-In Wizard can be used for defining custom functions relevant to your business case ascenarios. The Intelligence Server makes no distinction between these custom functions and the ones provided by default. These custom plug-in functions are indistinguishable from all other functions or operators, such as Sum, Average, Min, Max, Count, -, +, /, or *.

Defining custom plug-in functions involves the following steps:

  • In the design stage, you determine how to implement the analytical procedures into a computer system.
  • Creation builds the Microsoft Visual C++ project, which is used to produce a library containing your algorithms.
  • Implementation involves creating the code for the algorithms and compiling this code into a library that will be used by MicroStrategy.
  • Importing adds the library to a MicroStrategy project so that its algorithms are available for use in the project.
  • Deployment distributes your library to the MicroStrategy Intelligence Servers, which will execute it.
  • The final step is execution, which is creating new metrics that use the algorithms and using those metric in a MicroStrategy report.

The Function Plug-In Wizard guides you through the creation and implementation steps. It helps you create a Microsoft Visual C++ project with placeholders where you can add custom analytic code. After adding your function-specific C++ code and building your project, you can launch MicroStrategy Desktop to import your new function plug-in to be used for all the reports. Deployment occurs on each Intelligence Server system that will use it. The execution step is also performed in MicroStrategy Desktop, when you create metrics and reports using the new function. For detailed information on each step, see the Function Plug-In Wizard online help. The MicroStrategy online help also provides instructions on importing the functions.

The Function Plug-In Wizard must be installed and activated before you can create and implement custom plug-in functions. The self-extracting installation file, named FPWizard.exe, is located in the ...MicroStrategyDesktop directory.

Base formulas

You can recycle a formula to use it in multiple metric definitions. This is called a base formula, which can contain arithmetic operators, attributes, facts, group functions, metrics, and non-group functions. Using a base formula allows you to

  • update multiple metrics by modifying the base formula only once, as the change is automatically propagated to all the metrics that use the base formula
  • find or categorize all the metrics that use a common base formula
  • use a simple expression as a base formula to facilitate the creation of more complex metrics
  • use it as a formula in simple or compound metrics

Level

The level of a metric, also referred to as dimensionality, allows you to determine the attribute level at which the metric is calculated. In addition, you can specify the grouping and filtering involved in a metric.

The concepts underlying the term level in the context of MicroStrategy metrics are interchangeable with those of dimensionality. The term level is used throughout this manual.

All metrics, by default, calculate at the report level. This means that the attributes on the report template dictate how the metric is aggregated. For example, if the report shows revenue by year and month, the numbers are calculated to reflect monthly sales data. However, you can specify any set of attributes as the calculation level of a metric.The engine determines the set that is at the lowest level; for example, Region, Month, and Year resolves to Region and Month.

By default, the report level is part of the metric level. This allows for more flexibility in the use of the metric.

The elements needed to specify a level for a particular metric are:

  • target
  • grouping
  • filtering

A target, grouping, and filtering combination composes one level unit. Grouping and filtering are independent of each other. That is, the target and grouping work together to determine the level, and the target and filtering also work together to establish the level.

Clicking Reset on the Level pane of the Metric Editor changes the level unit back to the default of report level for the target and standard for filtering and grouping.

Target

The target is the attribute level at which the metric calculation groups. It determines the table to use to calculate the metric. Any set of attributes or a hierarchy can be the target. A special case is the default target, which is report level.

Grouping

Grouping determines how the metric aggregates. The result of this setting is reflected in the GROUP BY clause of the SQL command. The grouping options for levels include

  • Standard groups by the attribute level of the target. That is, the metric calculates at the level of the target, if possible.
  • None excludes the attribute in the target from the GROUP BY clause. It also excludes any of the target attribute’s children.

    None is not an option if the target is set to the report level.

The remaining options are only used for nonaggregatable metrics. A nonaggregatable metric is one that should not be aggregated across an attribute. An example is an inventory metric. While the data warehouse may record the inventory every month, these monthly numbers are not added together to calculate the yearly inventory. Instead, you may want to use the End-On-Hand and Beginning-On-Hand inventory numbers to see how the total inventory changed over the year. These grouping options, described below, are used in such cases:

  • Beginning lookup uses the first value of the lookup table.
  • Ending lookup uses the last value of the lookup table.
  • Beginning fact accesses the first value of the fact table.
  • Ending fact accesses the last value contained in the fact table.

Setting a metric level to one of the options listed above defines the metric as nonaggregatable. Whether you select a fact or lookup table largely depends on how the necessary information is stored. For example, to find the Beginning-on- Hand inventory for a particular item, you need to know how the inventory information is stored. If the inventory count isnot taken on the first day of the week, as the lookup table requires, the inventory count should be taken from the fact table for the first recorded entry.

There is another important difference between accessing a fact table and a lookup table. If a value, such as April sales, is missing from a fact table, the row still exists in the table and is reported as null or zero. If that same value is missing in a lookup table, the April row does not exist. The previous or next value (March or May) is reported, depending on whether the level is set to beginning or ending value.

Level grouping examples

A revenue metric is defined as: Sum(Revenue){Quarter} The level target is set to quarter, with standard grouping. When this metric is placed on a report with quarter, the results are shown below.

Level grouping examples

Notice that the sales are calculated for each quarter, because the metric is grouping at the quarter level, as shown in the SQL:

Using the same metric on a report with month, however, yields the following results.

Level grouping examples

This is only a subset of the report.

Although each month is listed, the value for each month in a quarter is the same. The metric is calculating quarterly revenue, based on the grouping level set on the metric. The SQL for this report is, in essence, the same as the previous example:

Change the grouping to none on that same revenue metric and place it on a report with year. Because year is a parent of quarter, the metric can roll up to the year level. The report and its SQL are illustrated below.

Level grouping examples

A total year sales fact table exists in the project, which would be more efficient. Instead of adding all the quarters together, the yearly total could have been pulled directly from that table. However, having quarter in the level of the metric forces the report to use the quarter sales table.

If the same revenue metric, with the grouping set to none, is used on a report with month, the results are shown below.

Level grouping examples

The metric calculates the same number for each month—the total for all the months included on the report. Because month is a child of quarter, month is excluded from the group by clause:


Inventory is one example of a nonaggregatable metric. The following metric definition reports the inventory on hand at the end of the month. The level is set at the report level and at month, with a grouping of ending fact, so that the last entry in the fact table is used.

Sum([End on hand]) {~, Month}

A report contains this metric and the month attribute. The last entry for each month in the fact table is placed on the report. No calculation is performed.

Level grouping examples

This is only a sample of the report, not the entire report.

When the same metric is used on a report with quarter, the value for each quarter is the value for the last month in the quarter. The monthly values for each quarter are not added together. For example, the on-hand inventory for March 2002 is 33,740. Since that is the last month in Q1, that value is reported on the quarterly report.

value is reported on the quarterly report.

Filtering

The filtering setting governs the relationship between the report filter and the calculation of the metric. The filtering options are:

  • Standard filtering allows the report filter to interact as usual in the metric calculation. The metric calculates only for the elements found in the filter definition. The filter criteria for the report is found in the WHERE clause of the SQL statement which calculates the metric in question.
  • Absolute filtering changes the filter on descendents of the target. It raises it to the level of the target, if possible.
    • If the attribute in the metric filter is a parent of the attribute in the report filter, calculations are performed only on elements to which the report filter applies.
    • If the attribute in the metric filter is of the same level or a child of the level in the report filter, calculations occur as specified by the report filter. Absolute filtering influences what is displayed on the report, not its calculations. It includes the report criteria in a subquery rather than in the WHERE clause itself.
  • Ignore filtering omits filtering criteria based on the attribute in the target and its related attributes (parents and children). The report filter does not appear anywhere in the SQL for a metric with this setting.
  • None can be summarized as unspecified—the filtering behavior for the target is not determined by this component. Instead, the target and group components of this level unit define the filter.
    • If the report includes an attribute in the same hierarchy as that indicated by the metric filter, aggregation takes place at the level of that attribute.
    • If the report does not include other attributes in the same hierarchy as that indicated by the metric filter, aggregation defaults to the “Absolute” option.

Level filtering examples

Consider the following report as a baseline to show the revenue for each month and quarter.

Level filtering examples

All of the metrics in these examples have grouping set to none. None of the reports are presented in full; they are only subsets of the complete report.

A revenue metric is defined with quarter as the target and standard filtering. A report is created with month, quarter, this new revenue metric, and a filter for January 2002. When the report is executed, the revenue is the same for every row, as shown below. All months are included on the report, even though the report filter is January 2002. This is an effect of setting the grouping to none. Since quarter in the target is a parent of month in the filter, all months are included on the report. The metric value is the grand total of the filter, in this case, January 2002 only.

Level filtering examples

The same report is created with a metric set to absolute filtering. When the report is executed, the revenue is the same for every row, as shown below. Because of the absolute setting, the report filter rolls up to the level of the metric—that is, month is elevated to quarter. Because the report is filtered for January 2002, the value is revenue for the first quarter of 2002.

Level filtering examples

The same report is run, but this time with a metric that has level filtering set to ignore. Again the metric value is the same for the whole report, but now it is the grand total of all sales in the project. Since month is related to quarter, the filter is also ignored.

Level filtering examples

Advanced options for metric levels

The advanced options for metric levels include the following settings:

  • Allow other users to add extra units to this definition, which is used to emulate MicroStrategy 6.x behavior, affects only those projects that have been upgraded from 6.x. The option indicates whether the metric accepts dimensionality units, for metrics used at the template level and metrics used in the filter for a metric qualification. This continuation dimensionality is merged with the original units to complete the metric level.
  • Add attributes in the filter to the level (dimensionality) determines whether the metric filter is applied to the metric calculation. By default, the setting is selected. If it is cleared, filter attributes that are not on the template or the level are not included in the metric calculation.

Add filter attributes to the level example

The best way to explain the Add attributes in the filter to the level setting is with an example. The following reports all contain first quarter revenue for books sold in California stores, but depending on the Add attributes setting, that revenue amount changes.

  1. Create a filter with the following conditions and name it CA Books Q1 2002:
    • Call Center = San Diego and San Francisco
    • Category = Books
    • Month = January 2002, February 2002, and March 2002
  2. Create a revenue metric and use the CA Books Q1 2002 filter as the condition. By default, the Add attributes setting is selected. Name it Revenue (Attributes On).
  3. Copy the Revenue (Attributes On) metric, renaming it Revenue (Attributes Off). Edit the metric to clear the Add attributes setting, by following the steps outlined below:
    • Select Level (Dimensionality) in the breakdown window (under the heading Metric is defined as). The Definition window changes to display level options.
    • Click Advanced in the Definition window. The Level (Dimensionality) advanced options dialog box opens.
    • Clear the Add attributes in the filter to the level (dimensionality) check box.
    • Click OK to return to the Metric Editor.
    • Click Save and Close to return to the Desktop.
  4. Create a report with the Region and Call Center attributes on the rows and the Revenue (Attributes On) metric on the columns. Execute the report. The results are displayed below:
  5. Add filter attributes to the level example
  6. Change to SQL view and notice the Where clause, as shown below:

    The complete metric filter (Call Center, Category, and Month, as shown in bold font) is included in the metric calculation.

  7. Save the report as CA Revenue (Attributes On).
  8. Return to Design view. Delete the Revenue (Attributes On) metric and replace it with the Revenue (Attributes Off) metric. Execute the report. The results are displayed below:
  9. Add filter attributes to the level example
  10. Why has the revenue increased substantially? Change to SQL view to check the Where clause:
    where a11.[CALL_CTR_ID] = a12.[CALL_CTR_ID] and a12.[REGION_ID] = a13.[REGION_ID] and a11.[CALL_CTR_ID] in (2, 4)
  11. With the Add attributes setting turned off, only those attributes in the metric filter which are on the template or in the metric level are included in the metric calculation. In this report, only Call Center, as shown in bold above, meets those requirements, since it is on the template. Because the metric conditions of Category = Book and Month = January - March ‘00 are excluded, the revenue is calculated for all categories and all time, increasing the revenue amount dramatically.

    In the previous examples, the metric level has not changed from the default of report level, so it does not really affect the Add attributes setting. The next example adds a metric level.

  12. Save the report as CA Revenue (Attributes Off).
  13. Copy the Revenue (Attributes Off) metric, renaming it Yearly Revenue (Attributes Off). Edit the metric to add Year to the metric level.
  14. Copy the CA Revenue (Attributes Off) report, renaming it Yearly CA Revenue (Attributes Off).
  15. Edit the new report. Delete the Revenue (Attributes Off) metric and replace it with the Yearly Revenue (Attributes Off) metric. Execute the report. The results are displayed below:
  16. results are displayed
  17. The revenue amount has changed again. Check the Where clause in the SQL view to discover why:

  18. Now the metric calculation includes Call Center because it is defined on the template and Month because it is in the same hierarchy as Year, which is the metric level. Category is not included, since it is neither on the template or in the metric level. The metric calculates revenue in all categories for the California stores for the first quarter of 2002.

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

Microstrategy Topics