Work with Logical Column Aggregation

This topic provides information about the different ways that you can set up logical column aggregation.

Topics:

• About Levels of Aggregation

• Set Aggregation Rules for a Measure Column

• Set an Aggregation Level Based on a Dimension for a Measure Column

• Associate an Attribute with a Logical Level in Dimension Tables

About Levels of Aggregation

Only perform aggregations on measure columns. Measure columns should exist only in logical fact tables.

You can select different aggregation rules for different dimensions that are associated with a logical column. Suppose someone queries the aggregate column along with one dimension, you may want to use one type of aggregation rule, whereas with another dimension, you may want to use a different aggregation rule. For example, number of employees is a count on all dimensions except on the time dimension where the aggregation rule would be last.

When the default aggregation rule is Count Distinct, you can specify an override aggregation expression for specific logical table sources. For example, you may want to specify override aggregation expressions when you're querying different logical table sources that already contain some level of aggregation.

You can choose the EVALUATE_AGGR aggregation rule to enable queries to call custom functions in the data source. Use this aggregation rule when the aggregation must be done in an external data source.

By default, data is considered sparse. However, you might have a logical table source with dense data. A logical table source is considered to have dense data if it has a row for every combination of its associated dimension levels. When setting up aggregate rules for a measure column, you can specify that data is dense only if all the logical table sources to which it's mapped are dense.

For measures where the aggregation rule is the same in all dimensions, select one of the aggregate functions from the Aggregation Rule list. The function you select is always applied when a user or an application requests the column in a query, unless an override aggregation expression has been specified. When you select Count Distinct as the default aggregation rule, you can specify an override aggregation expression for specific logical table sources. Choose this option when you have more than one logical table source mapped to a logical column and you want to apply a different aggregation rule to each source.

Set Aggregation Rules for a Measure Column

You need to specify aggregation rules for mapped logical columns that are measures.

If your measure has different aggregation rules for different dimensions, for semi-additive measures, then you choose Based on dimensions as the measure's aggregation rule. See Set an Aggregation Level Based on a Dimension for a Measure Column.

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 Logical Layer.
4. In the Logical Layer pane, browse for the table with the logical column that you want to add an aggregation rule to.
5. In the logical table, click the Columns tab.
6. In the column table, click the column to highlight it, and then click Detail view to view its properties.
7. In the logical column's Aggregation properties, click the Aggregation Rule field and select an aggregation rule.
8. Click Add Aggregation by Level and in the Dimension field select a table source.
9. Click the Logical Level field and select the level of aggregation. What you choose is the minimum level of aggregation, and the measure won't be aggregated below the level you choose.
10. Click Save.

Set an Aggregation Level Based on a Dimension for a Measure Column

The majority of measures have the same aggregation rule for each dimension. Some measures can have different aggregation rules for different dimensions.

For information about setting up dimension hierarchies, see About Level-Based Hierarchies and About Parent-Child Hierarchies.

For example, a bank could calculate account balances averages over a specific time, but calculated averages on individual accounts with a simple summation for a period. You can configure dimension‐specific aggregation rules. You can specify one aggregation rule for a given dimension and specify other rules to apply to other dimensions.

Choose Based on dimensions as the measure column's aggregation rule if your measure has different aggregation rules for different dimensions, for semi-additive measures.

When setting up the aggregation, selecting the Data is dense option indicates that all sources that the column is mapped to have a row for every combination of dimension levels that they represent. Incorrect results are returned if you select this option and the measure column's table source doesn't contain dense data.

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 Logical Layer.
4. In the Logical Layer pane, browse for the table with the logical column that you want to add an aggregation rule to.
5. In the logical table, click the Columns tab.
6. In the column table, click the column to highlight it, and then click Detail view to view its properties.
7. In the logical column's Aggregation properties, click the Aggregation Rule field and select Based on dimensions.
8. Click Add Aggregate by Dimension and in the dimension field select a dimension.
9. Click the Formula list and select an aggregation rule, or click the Expression Builder icon to use the Expression Editor to create the aggregation rule.
10. If all the logical table sources that the column is mapped to are dense, then select the Data is dense field.
11. Click Save.

Associate an Attribute with a Logical Level in Dimension Tables

You can associate attributes with a logical level.

You can associate measures with levels from multiple dimensions and aggregate to the levels specified. A measure is associated to a level is called a level-based measure. A level-based measure is computed at that grain, even when the query context has a lower grain. For example, if yearlySales is associated to year level, it's computed at the yearly level in the following query: Select month, yearlySales.

Dimensions are displayed in the Dimensions list. If the attribute is associated with a logical level, the level appears in the Levels list.

Another way to associate a measure with a level in a dimension is to expand the dimension tree in the logical layer, and then use drag-and-drop the column on the target level. See Level-Based Measure Calculations.

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 Logical Layer.
4. In the Logical Layer pane, browse for the table with the logical column that you want to add an aggregation level to. Double-click the column.
5. In the logical table, click the Columns tab.
6. In the column table, click the column to highlight it, and then click Detail view to view its properties.
7. In the logical column's Aggregation properties, click the Aggregation Rule field and select an aggregation rule.
8. Click Add Aggregation by Level and in the Dimension field select a logical table source.
9. Click the Logical Level field and select a level.
10. Click Save.