Measure objects retrieve numeric data that is the result of calculations on data on the database. Measure objects are semantically dynamic: the values they return depend on the objects they are used with.
For example, if you include City and Sales Revenue in a table, revenue per city is calculated. If you include Year and Sales Revenue, revenue per year is calculated.
How I create a measure ?
You create a measure by using aggregate functions. The five most common aggregate functions are the following:
You set projection aggregation on the Properties page of the Edit Properties sheet for a measure (right-click object > Object Properties > Properties).
Setting aggregate projection for a measure
When you create a measure you must specify the way the aggregate function will be projected onto a report. Returned values for a measure object are aggregated at two levels of the query process:
- Query level.
Data is aggregated using the inferred Select statement.
- Microcube to block level.
When data is projected from the microcube to the block in a report. This projection function of measures allows local aggregation in the microcube.
A microcube is a conceptual way to present the data returned by a
query before it is projected onto a report. It represents the returned values
held in memory by a Business Objects reporting product.
The block level is the 2 dimensional report that a user creates with the returned data.
To create a report, a user can :
- choose to use all, or only some of the data held in the microcube
- do aggregate functions on the returned values in the microcube (local aggregation) to create new values on a report.
The two levels of aggregation fit into the query process as follows:
The diagram shows the following processes in a query:
- User creates a query in Web Intelligence.
- Select aggregation : SAP BO - Web Intelligence infers the SQL from the query and sends a Select statement to the target database.
- The data is returned and stored in the microcube. This is the first aggregation level.
- Projection aggregation : The microcube projects the aggregated data onto the report. Data is split out in the Query pane requiring aggregation to lower levels. This is the second aggregation level.
When an aggregation from the microcube is required to show measure values at a higher level, the microcube is projected into a block.
Non-additive measures : Database delegated projection function
In a universe, any measure can hold a projection function (Sum, Min, Max, Count, and Avg). The projection function is used to aggregate the measure locally in SAP BO - Web Intelligence when the number of dimensions displayed in a report is smaller than the number of dimensions in the query result set.
Non-additive measures, such as ratio, average, and weight, can only be shown at the same aggregation level as the query result set. Therefore, non-additive measures generally have their projection function set to None in the universe.
The projection function Database delegated allows you to delegate the aggregation of a non-additive measure to the database server. These are called smart measures in SAP BO - Web Intelligence. A smart measure has its projection function set to Database delegated on the properties page of the object properties.
For OLAP universes based on MSAS and Essbase data sources, all measures are created in the universe with the projection function set to Database delegated by default.
Example: Smart measure In this example, a query contains two dimensions: Country and Region, and three measures: Order Amount, Delivered Amount, and % of Delivered Amount.
The sum for % Delivered is incorrect because it is a summation of the % Delivered column. If this measure has a projection function set to Database delegated in the universe, when the user refreshes the report, Web Intelligence connects to the database to calculate the correct value.
The some functions such as ratio function (Average) must be used with caution. When calculating an average from a column, the behavior of this function can be unexpected it is not configured correctly.