Metric-specific VLDB properties - Microstrategy

There are other VLDB properties besides joins that affect metrics. VLDB properties allow MicroStrategy products to exploit the unique optimizations offered by different databases. These settings affect how MicroStrategy Intelligence Server manages joins, metric calculations, and query optimizations, among others. VLDB properties are available at multiple levels, so that SQL generated for one report can be manipulated separately from the SQL generated for a different report. The hierarchy, or order of precedence, for VLDB properties is outlined in the following figure.

Metric-specific VLDB properties

The arrows depict the override authority of the levels, with the report level having the greatest authority. For example, if a VLDB property is set one way for a report, and the same property is set differently for a metric included on the report, the report property takes precedence.

Although there are ten VLDB properties that pertain specifically to metrics, there are only six metric VLDB properties available at the individual metric level. You can set additional metric VLDB properties at other levels, such as the report and project levels. Refer to the MicroStrategy System Administration Guide for a detailed description of these properties.

Metric-specific VLDB properties that can be set at the metric level include

  • Integer Constant in Metric
  • Metric Join Type
  • Null Check
  • Zero Check
  • Null Checking for Analytical Engine
  • Subtotal Dimensionality Aware

To set these properties, select Advanced Settings from the Tools menu in the Metric Editor. Then choose VLDB Properties to access the VLDB Properties (Metric) dialog box. The last two settings in the list above are contained under the Analytical Engine folder, while the others are found in the Metrics folder.

Metric VLDB properties

Integer Constant in Metric

This setting determines whether to add a “.0” after the integer. The options for this property are as follows:

  • Add “.0” to integer constants in metric expressions.
  • Do not add “.0” to integer constants in metric expressions.
  • Use the default inherited value.

Metric Join Type

This property sets the type of join used in the metric. The options are

  • Inner join, which includes only data that is common across all elements
  • Outer join, which includes data that apply to every metric in a report
  • The default inherited value

Null Check

The Null Check property indicates how to handle arithmetic operations with null values. The options for this property are as follows:

  • Do nothing, which means that the database rather than the Analytical Engine handles division by a null value.
  • Check in all queries.
  • Check in temporary table join only.
  • Use the default inherited value, from the DBMS level.

Zero Check

The Zero Check property indicates how to handle division by zero or when to check for zeros in the denominator during division operations. The options for this property are as follows:

  • Do nothing, which means that the database rather than the Analytical Engine handles division by a zero.
  • Check in all queries.
  • Check in temporary table join only.
  • Use the default inherited value, from the DBMS level.

Analytical Engine VLDB properties for metrics

Null Checking for Analytical Engine

This setting determines whether a null value is interpreted as zero when the Analytical Engine performs calculations. The options for this property are as follows:

  • False, which means that null values are not altered
  • True, which means the Analytical Engine converts null values to zeros
  • The default inherited value

You can also set replacement text for nulls at the report level.

Subtotal Dimensionality Aware

The Subtotal Dimensionality Aware setting enables subtotaling based on the dimensionality of a metric. How it works depends on another VLDB property, Query Population Dimensionality Aware, which handles backwards compatibility with MicroStrategy 7.1. These settings work together as illustrated in the following table.

Subtotal Dimensionality Aware

The default setting for the Subtotal Dimensionality Aware property is TRUE, so subtotals depend on the metric’s dimensionality. If you must subtotal without using the dimensionality of the metric, set this property to FALSE. The options for this property are:

  • FALSE, which means that subtotals do not take into account the dimensionality of the metric
  • TRUE which means that subtotaling is aware of metric dimensionality
  • The default inherited value

For example, the Quarterly Revenue metric is defined as Sum(Revenue) Dimensionality = Quarter, and the Yearly Revenue metric is defined as Sum(Revenue) Dimensionality = Year.

Subtotal Dimensionality Aware

If Subtotal Dimensionality Aware is set to FALSE, the quarterly subtotal is calculated as 600, that is, a total of the Quarterly Revenue values. The yearly subtotal is calculated as 2400, the total of the Yearly Revenue values. This is the way MicroStrategy 7.1 calculated the subtotal.

If Subtotal Dimensionality Aware is set to TRUE, the quarterly subtotal is still 600. MicroStrategy is aware of the dimensionality of the Yearly Revenue, so rather than simply adding the column values, it calculates the total as 600.


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

Microstrategy Topics