Work with Cube Measures

This topic describes what you need to know to understand, create, and modify a cube's measures.

Topics:

About Measures in Multidimensional Data Sources

Always verify aggregation rules after importing a cube. In most cases Semantic Modeler assigns aggregation rules correctly when you import a cube.

However, if a measure is a calculated measure, then during import Semantic Modeler sets the aggregation rule to None. Therefore it's important that you examine the aggregation rule for all measures after importing a cube to verify that Semantic Modeler assigned the aggregation rules correctly.

For all measures with None assigned as the aggregation rule, contact the data source's administrator to verify that the value is accurate.

When you need to select the aggregation rule for a physical cube column, be sure to select the aggregation rule carefully so that the measures are correct. Setting the aggregation rule correctly might improve performance.

For more information about how to set the measure's aggregation rule, see Change a Multidimensional Measure's Aggregation Rule.

About Externally Aggregated Measures

If in the physical layer you assign a multi-dimensional measure an aggregation rule of External Aggregation, the Oracle Analytics query engine bypasses its internal aggregation mechanisms and uses the pre-aggregated measures.

When you import such a column into the logical layer, Semantic Modeler assigns these measures an aggregate value of None.

Use these guidelines to help you work with pre-aggregated measures:

  • External aggregation only applies to multidimensional data sources that support these complex calculations.

  • You can't assign external aggregation to measures from non-multidimensional data sources. If the Oracle Analytics query engine supports the required aggregation rule and the rule can be mapped to a relational data source, then it isn't complex and doesn't require external aggregation.

  • There is only one aggregation rule for a logical measure. Therefore, a single logical column can't federate a non-complex aggregation rule for a mapping to a non-multidimensional source with a complex aggregation rule for a mapping to a multidimensional source. Instead, you need to create one logical measure for each source and then create a third logical measure that derives from the first two.

  • You can mix non-complex measures from non-multidimensional data sources with non-complex measures from multidimensional data sources if they're aggregated through the Oracle Analytics query engine.

Change a Multidimensional Measure's Aggregation Rule

A measure's aggregation rule determines how the Oracle Analytics query engine aggregates the measure in relation to the cube table's dimensions.

In most cases when you import a cube into the physical layer, Semantic Modeler assigns the aggregation rules correctly. In some cases you might need to reassign a measure's aggregation rule.
See About Measures in Multidimensional Data Sources.

Use the following guidelines to assign a measure's aggregation rule in the physical layer:

  • If the generated physical queries to the database should send an aggregation function, then set that function as the aggregation rule. For example, if the aggregation function is SUM(revenue), set the aggregation rule to Sum.

    With this setting, the Oracle Analytics query engine typically sends the aggregation to the database in the query, but might also perform aggregations itself in certain situations.

  • If the data for this measure shouldn't be aggregated in the query or by the Oracle Analytics query engine, use the External Aggregation rule. It's important to choose this setting when the measure uses a more complex calculation inside the data source than the Oracle Analytics query engine can replicate with a simple aggregation rule such as calculations for ratios, consolidations, and allocations. This option is also useful when the cube persists a full set of pre-aggregated results.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon.
  4. In the Physical Layer pane, browse for and double click the measure that you want to change the aggregation rule for.
  5. Click the Measures tab and in the measures table locate and double click to select the measure. Click Detail view to view its properties.
  6. Click the Aggregation Rule field and select an aggregation rule.
  7. Click Save.

Set a Measure Column's Properties

Use this topic to set the properties for a new or existing measure column.

Use this information to help you set a column's properties:

  • Type - Indicates the column's data type. Use caution when changing the data type. Setting the values to data types that are incorrect in the underlying data source might cause unexpected results. If there are any data type mismatches, correct them in the semantic model or reimport the columns that have mismatched data types.

    If you reimport columns, you also need to remap any logical column sources that reference the remapped columns. The data type of a logical column in the business model must match the data type of its physical column source. The Oracle Analytics query engine passes these logical column data types to client applications.

    Longvarchar and longvarbinary data types are supported for writing complete Logical SQL statements into usage tracking tables for debugging purposes. They aren't supported for general-purpose queries, and can't be displayed in the Oracle Analytics query endine.

    When using multidimensional data sources, if you select VARCHAR, you must specify a value in the Length field.

  • Nullable - Specifies whether null values are allowed for the column. If null values can exist in the underlying table, you need to select this option. This allows null values to be returned to the user, which is expected with certain functions and with outer joins. It's generally safe to change a non-nullable value to a nullable value in a measure column.

  • Aggregation rule - Specifies how the Oracle Analytics query engine aggregates the measure in relation to the cube table's dimensions. Use this information to help you set this field:

    • If the generated physical queries to the database should send an aggregation function, then set that function as the aggregation rule. For example, if the aggregation function is SUM(revenue), set the aggregation rule to Sum.

      With this setting, the Oracle Analytics query engine typically sends the aggregation to the database in the query, but might also perform aggregations itself in certain situations.

    • If the data for this measure shouldn't be aggregated in the query or by the Oracle Analytics query engine, use the External Aggregation rule. It's important to choose this setting when the measure uses a more complex calculation inside the data source than the Oracle Analytics query engine can replicate with a simple aggregation rule such as calculations for ratios, consolidations, and allocations. This option is also useful when the cube persists a full set of pre-aggregated results.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon.
  4. In the Physical Layer pane, browse for and double click the cube table where you want to create the measure or that contains the measure that you want to update the properties for. Click the Measures tab.
  5. Create a measure or open an existing measure:
    • To create a measure, click Add Column Create icon. Then click Detail view.
    • To open an existing measure, in the measure list locate and click the measure. Then click Detail view.
  6. Set the measure's properties.
  7. Click Save.