In the table editor, right-click a column and optionally click Copy or Delete as appropriate.

In the table editor, click the column that you want to edit or click Add Column.

Change settings on the Overview tab as needed.

Edit the display name and description.

Change the sort order.

By default, columns are sorted based on the data in the column and reports display data in this order. To sort a column based on the data in another column, select Sort by a different column and select the Sort By value you prefer. For example, instead of sorting a Month Name attribute alphabetically, you could sort by month number, such as 1 (January), 2 (February), 3 (March), and so on.

In the Fact Tables area, click the fact table for which you want to create measures.

In the Columns list, change the aggregation rule for the appropriate columns to specify that they're measures.

To apply the same aggregation rule to multiple columns, Shift + click or Ctrl + click the appropriate columns.

Aggregation options include:

None: No aggregation.

Sum: Calculates the sum by adding up all values.

Average: Calculates the mean value.

Median: Calculates the middle value.

Count: Calculates the number of rows that aren't null.

Count Distinct: Calculates the number of rows that aren't null. Each distinct occurrence of a row is counted only once.

Maximum: Calculates the highest numeric value.

Minimum: Calculates the lowest numeric value.

First: Selects the first occurrence of the item.

Last: Selects the last occurrence of the item.

Standard Deviation: Calculates the standard deviation to show the level of variation from the average.

Standard Deviation (all values): Calculates the standard deviation using the formula for population variance and standard deviation.

Tip:

Some calculated measures are Pre-Aggregated. These measures have calculations involving measures that already have an aggregation applied. To edit a calculation that contains pre-aggregated measures, click the column name. See also Creating Calculated Measures.

For most measures, the same aggregation rule applies for each dimension but for some measures you’ll want to specify one aggregation rule for a given dimension and specify other rules to apply to other dimensions.

Time dimensions are most likely to require different aggregation. For example, Headcount (calculated measure) typically aggregates as SUM across Organization and Geography dimensions but SUM does not apply for a Time dimension. Aggregation for the Time dimension should be LAST, so you can show Headcount on the last week or day of the year.

Select the dimension you want to aggregate differently, for example Time.

Select an aggregation rule for the dimension.

If required, override aggregation for another dimension.

Click Done.

When dimension-specific aggregation rules are defined for a measure, you see an asterisk * next to the aggregation rule in the Columns table. For example, Sum*.

By default, all the columns in the fact table are displayed in reports. Deselect the Available box for any columns that you don’t want to display. You can use Shift + click or Ctrl + click to select multiple rows.

Click Cancel to cancel any of your changes.

Click Done to return to the table editor.

Creating Calculated Measures

If a fact table does not include all the measures that you need, then you can create calculated measures. For example, you can create a calculated measure called Average Order Size using the formula Revenue/Number of Orders.

In Data Modeler, lock the model for editing.

In the Fact Tables area, click the fact table for which you want to create measures.

In the Columns area, click Add Column.

In the New Column editor, enter a name and description for the column.

Then, enter an expression directly in the Expression box, or click Full Editor to display the Expression editor.

If you include aggregated columns in the calculation, aggregation on the columns is ignored.

Creating Derived Attributes

You can create custom or derived attributes for dimension tables that are based on an expression. For example, you can use an expression to concatenate multiple address columns into a single Full Address column.

In Data Modeler, lock the model for editing.

In the Dimension Tables area, click the dimension table for which you want to create derived attributes.

In the Columns area, click Add Column.

In the New Column editor, enter a name and description for the column. Then, enter an expression directly in the Expression box, or click Full Editor to display the Expression editor. See Creating Expressions in the Expression Editor.

Creating Expressions in the Expression Editor

About the Expression Editor

When modeling data, you can use the Expression Editor to create constraints, aggregations, and other transformations on columns. For example, you can use the Expression Editor to change the data type of a column from date to character. You can also use the Expression Editor to create expressions for data filters.

The Expression Editor contains the following sections:

The Expression box on the left-hand side enables you to edit the current expression.

The toolbar at the bottom contains commonly used expression operators, such as a plus sign, equals sign, or comma to separate items.

The Expression Elements section on the right-hand side provides building blocks that you can use in your expression. Examples of elements are tables, columns, functions, and types.

The Expression Elements section only includes items that are relevant for your task. For example, if you open the Expression Editor to define a calculated measure, the Expression Elements section only includes the current fact table, any dimension tables joined to that table, plus any fact tables indirectly joined through a dimension table. Similarly, when you define a derived attribute, you see the current dimension table, any fact tables joined to that table, and any dimension table joined to those fact tables.

Another example is that time hierarchies are only included if the Time fact table is joined to the current table.