OBIEE – Calculation Measures

Calculated measures is used to perform calculation of facts in logical tables. It defines Aggregation functions in Aggregation tab of logical column in the repository.

Create New Measure

Measures are defined in logical fact tables in repository. Any column with an aggregation function applied on it is called a measure.

Common measure examples are − Unit Price, quantity sold, etc.

Following are the guidelines to create measures in OBIEE −

• All aggregation should be performed from a fact logical table and not from a dimension logical table.

• All columns that cannot be aggregated should be expressed in a dimension logical table and not in a fact logical table.

Calculated measures can be defined in two ways in logical tables at BMM layer in Administration tool −

• Aggregations in logical tables.
• Aggregations in logical table source.

Create Calculated Measures in Logical Tables using Administration Tool

Double-click on the column name in the logical Fact table, you will see the following dialog box.

Go to Aggregation tab and select the Aggregate function from the drop-down list → Click OK.

You can add new measures using functions in Expression builder wizard in Column source. Measures represent data that is additive, such as total revenue or total quantity. Click on the save option at the top to save the repository. This is also called creating measures at logical level.

Create Calculated Measures in Logical Table Source using Administration Tool

You can define Aggregations by a double-click on Logical table source to open logical table dialogue box.

Click on Expression builder wizard to define expression.

In Expression builder, you can choose multiple options like - Category, functions, and mathematical functions.

Once you select the category, it will show the subcategories inside it. Select the subcategory and mathematical function, and click on the arrow mark to insert it.

Now to edit the value to create measures, click on source number, enter the calculated value like multiple and divide → Go to Category and select logical table → Select column to apply this multiple/division to an existing column value.

Click OK to close the Expression builder. Again click OK to close the dialog box.