Advanced Metric Options Dialog

You can specify a variety of advanced options for a metric on the Advanced Metric Options dialog.

Open the Metric Editor.
Click Metric Options.

General

Use the General section to select the dynamic aggregation function and whether the metric uses smart subtotals.

Dynamic Aggregation function: Select the function used for the rollup of metric values that occurs when an attribute is moved from the report grid to the Report Objects panel (called dynamic aggregation).

Allow smart metric: Determine whether the metric calculates subtotals on the individual elements of the metric, as described below. This option is available for compound metrics.

The formula of a compound metric can be composed of multiple objects joined by arithmetic operators, such as Metric1/Metric2 or (Fact1 + Fact2)/Fact3. The subtotal of a compound metric can be calculated in different ways:

Calculate the sum of all parts of the compound metric, then calculate the compound metric. This formula is represented by Sum(Metric1)/Sum(Metric2).
Calculate the compound metric for each row of the report, and then roll up the data to the correct level. This formula is represented by Sum(Metric1/Metric2).

The first case uses smart subtotals, which calculate subtotals on the individual elements of a metric (also referred to as a smart metric). For example, the Profit Margin metric is calculated as the Profit metric divided by the Revenue metric. The Profit Margin metric can be totaled as follows:

Add all the profit values together. Add all the revenue values together. Divide the two sums. This is a smart metric.
Divide each profit value by each revenue value, then sum up the resulting ratios.

Subtotals

Use the Subtotals section to select the default subtotal function and the functions that are available to subtotal the metric on reports.

Function for default subtotal: Select the default function to use to calculate report subtotals. To prevent a grand total from appearing when the metric is used on a report, select None.

Select the subtotals you want available for this metric: Select the functions that can be used to calculate subtotals for the metric. To do this, click the Expand icon . Select the subtotal types to be available to the user when the report is run, and clear any subtotal types that you do not want to be available. To prevent any subtotals from appearing when the metric is used on a report, clear all subtotal types.

VLDB Properties

Use the VLDB Properties section to specify VLDB properties and take advantage of unique, database-specific optimizations.

Null checking for Analytical Engine: Determines whether to convert null values to zero when the Analytical Engine performs calculations. For more information, see the Advanced Reporting Help.

Subtotal Dimensionality Aware: Enables subtotaling based on the attribute level used to calculate the metric (dimensionality). If this option is set to True, and a report contains a metric that is calculated at a higher level than the report level, the subtotal of the metric is calculated based on the metric's level. For example, a report at the quarter level containing a yearly sales metric shows the yearly sales as the subtotal instead of summing the rows on the report. The default value is Inherit - True.

Metric Validation: Determines whether dynamic sourcing is available for metrics. Dynamic sourcing makes Intelligent Cubes more accessible by allowing regular reports to automatically access published Intelligent Cubes that can satisfy the data requirements of the report. In general, if metrics use outer joins, accurate data can be returned to reports from Intelligent Cubes through dynamic sourcing. However, if metrics use inner joins, which is a more common join type, you should verify that the metric data can be correctly represented through dynamic sourcing. The default value is Inherit - Enable dynamic sourcing.

Integer Constants in Metric: Determines whether to add a .0 after integer values in the metric. The default value is Inherit - Add '.0' to integer.

Metric Join Type: Defines the default method for joining the metric to other metrics.

Inherit (default): The metric uses the report or project level setting to determine how to join the metric to other metrics.
Inner join: Includes a row in the report only if the row contains data for all the metrics in the row.
Outer join: Shows all rows for the metric, regardless of other metrics on the report

Null Check: Determines how to handle arithmetic operations with null values. The Analytical Engine can either check for null values in all queries or in temporary table joins only. The default value is Inherit - Check for NULL in all.

Transformable Aggregation Metric: Defines what metrics should be used to perform transformations on compound metrics that use nested aggregation. See System Administration Guide for a sample scenario of how to use this property. For example, you create two metrics. The first metric, referred to as Metric1, uses an expression of Sum(Fact) {~+, Attribute+}, where Fact is a fact in your project and Attribute is an attribute in your project used to define the level of Metric1. The second metric, referred to as Metric2, uses an expression of Avg(Metric1){~+}. Since both metrics use aggregation functions, Metric2 uses nested aggregation.

Including Metric2 on a report can return incorrect results if a transformation shortcut metric is defined on Metric2, and Metric1 is defined at a lower level than the report level. In this scenario, the transformation is applied to the outer metric, which in this case is Metric2. To perform the transformation correctly, the transformation should be applied for the inner metric, which in this case is Metric1. To apply the transformation to Metric1 in this scenario, you can use the Transformable Aggregation Metric option. The options are:

Inherit (default): The metric uses the report or project level setting to determine which metrics should be used to perform transformations on compound metrics that use nested aggregation.
True: The metric is defined as a metric to perform a transformation when it is included in another metric through nested aggregation. This option should be used only for metrics that are defined for a scenario similar to Metric2 described above.
False: The metric uses default transformation behavior. This option should be used for all metrics except for those metrics that are defined for a scenario similar to Metric2 described above.

Zero Check: Determines how to handle division by zero or when to check for zeroes in the denominator during division calculations. When a zero check is performed, a zero in the denominator of a division calculation is changed to a null value. The options are:

Inherit (default): The metric uses the report or project level setting to determine how to handle division by zero.
Do nothing: The database, rather than the Analytical Engine, handles division by zero.
Check for zero in all queries: Checks for division by zero in all queries.
Check for zero in temp table join only: Checks for division by zero only for temporary table joins.

Count Distinct: Determines how to handle queries performed on multiple partitioned tables which return a distinct count of values. A distinct count of values allows you to return information such as how many distinct types of items were sold on a given day. Selecting the correct option for your project setup can help improve performance. The options are:

Inherit (default): The metric uses the report or project level setting to determine how to handle queries performed on multiple partitioned tables.
Do not select distinct elements from each partition: Returns a distinct count of values from multiple partition tables. The tables are first combined together as one large result table, and then the count distinct calculation is performed. While this returns the proper results, combining multiple tables into one table to perform the count distinct calculation can be a resource-intensive query.
Select distinct elements from each partition: Returns a distinct count of values from multiple partitioned tables. The size of each partition table is first reduced by returning only distinct values. These smaller tables are then combined and a count distinct calculation is performed. This can improve performance by reducing the size of the partition tables before they are combined for the final count distinct calculation.

Column Options

Use the Column Options section to specify an alias for the metric in temporary tables and select the metric's data type.

Column name used in SQL table creation: Type the column alias, which changes the name of the metric in temporary tables. This renaming does not affect the actual name of the metric in the metadata but allows you to identify the metric easily, such as in a long SQL report. Temporary tables are used in data marts and SQL generation.

Data Type: Determine the data type for the metric.

Joins

Metric Join: Defines the default method for joining the metric to other metrics.

Default Inherit Value: The metric uses the report or project level setting to determine how to join the metric to other metrics.
Inner join: Includes a row in the report only if the row contains data for all the metrics in the row.
Outer join: Shows all rows for the metric, regardless of other metrics on the report.

Metric Formula Join: Determine how to combine the result set together within the metric.

Default Inherit Value: A join that is defined in each element.
Inner join: Includes only data that are common across all elements, that is, the intersection of the information.
Outer join: Includes data that apply to every element, or a union of all the information.