Strategy One

Applying Metric Options

Use the Options tab of the Metric Editor to specify a variety of advanced options for a metric. Examples of these options include the dynamic aggregation function, available subtotal functions, the metric join type, and VLDB properties.

  1. Create a new metric or edit an existing metric.
    • To edit an existing metric instead, find the metric and double-click it.
  2. In the Metric Editor, click Options.
  3. The available options are described below:

    • Alias: Type the alternate name used during SQL generation, providing a way to identify the metric more easily, especially in long SQL reports. The metric's name is not changed in the metadata.

    • 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.

    • Set as HTML content: Enable this setting to allow the metric to render HTML elements such as images, links, or formatted text in reports and dashboards.

      This is useful for creating dynamic visualizations or interactive elements within your reports. Check that HTML and JavaScript content is enabled in reports and dashboards where the metric will be used, ensuring that the HTML content can be rendered correctly.

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

    • Select the available subtotals: Select the functions that can be used to calculate subtotals for the metric. To do this, open the drop-down list. 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 all subtotals from displaying when the metric is used on a report, clear all subtotal types.

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

      • Default InheritValue: 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 InheritValue: 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.

  4. You can set VLDB properties and take advantage of unique, database-specific optimizations.
    1. Click Advanced Properties.
    2. To see how the different options for a property affect the SQL, select an option and click SQL Preview.
    3. The Metrics section contains the following properties:
      • Null Check: Determines how to handle arithmetic operations with null values. The options are:
        • Use Inherited Value - Default Level (default): The metric uses the report or project level setting to determine how to handle null checking. The inherited value displays.
        • Do nothing: The database, rather than the Analytical Engine, handles checks for null values.
        • Check for NULL in all queries: Checks for null values in all queries.
        • Check for NULL in temp table join only: Checks for null values only for temporary table joins.
      • 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:
        • Use Inherited Value - Default Level (default): The metric uses the report or project level setting to determine how to handle division by zero. The inherited value displays.
        • 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.
      • Integer Constant in Metric: Determines whether to add a .0 after integer values in the metric. The default value is Use Inherited Value from the DBMS level. The inherited value displays.
      • Smart Metric Transformation: Determines whether the Analytical Engine dynamically adjusts the aggregation method used for the metric based on the dimensionality of the data being queried. This means that the Engine can choose the most efficient way to calculate the metric, potentially leading to faster query performance and more efficient resource usage. This property is particularly useful for improving performance when dealing with complex metrics that may require different aggregation methods based on the context of the report or dashboard. The default value is inherited from the default level. The inherited value displays
      • Transformable AggMetric: 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:

        • Use Inherited Value - Default Level (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. The inherited value displays.
        • 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.
      • Subtotal Dimensionality Use: Determines how subtotal dimensionality is utilized for dynamic aggregation during query execution. You can specify the level of detail that should be considered when calculating subtotals for metrics. You can also optimize how subtotals are calculated based on the specific needs of your reports and dashboards, potentially improving performance and accuracy in data representation. The options are:
        • Use Inherited Value - Default Level (default): The metric uses the report or project level setting to determine how subtotal dimensionality is used. The inherited value displays.

        • Use only the grouping property of a level metric for dynamic aggregation: Only the grouping attributes of the metric are used for calculating subtotals.

        • Use only the grouping property of a level subtotal for dynamic aggregation: Similar to the previous option, but applies to subtotals specifically.

        • Use both the grouping and filtering property of a level metric for dynamic aggregation: Both grouping and filtering attributes of the metric are considered for subtotals.

        • Use both the grouping and filtering property of a level subtotal for dynamic aggregation: Similar to the previous option, but applies to subtotals specifically.

    4. The Query Optimizations section contains the following properties:
      • 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:
        • Use Inherited Value - Default Level (default): The metric uses the report or project level setting to determine how to handle queries performed on multiple partitioned tables.
        • Disable: 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.
        • Enable: 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.
    5. The Analytical Engine section contains the following properties:
      • 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 Use Inherited Value from the Default Level. The inherited value displays.

    6. The Dynamic Sourcing section contains the following properties:
      • 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 Use Inherited Value from the Default Level. The inherited value displays.
    7. You can view your changes before saving. Enable View Edits.
    8. You can export the property names and values to a Comma Separated Values (csv) file by clicking Export.
    9. Click OK to save your changes to the VLDB properties.
  5. Click Save to save the metric.

Related Topics