Creating Measure Objects

Measure objects contain aggregate functions that map to facts in the database. They represent the metrics by which you want to compare dimensions. Measure objects are semantically dynamic, meaning that the values they return in a query vary depending on the dimension and attribute objects that are used with them.

You can see that two separate queries, using the same Sales Revenue measure object, but different dimension objects, result in the measure returning different values.

The most regularly used aggregates are:

• Sum

• Count

• Average

• Maximum

• Minimum

However, there are others that can be used. The full set of aggregate functions is held in the Numeric functions selection list of the SQL expression editor dialog box.

You can check the High Precision property associated with the Numeric Data Type for applications supporting high precision number formats . An example of this is the IEEE 754-2008 Decimal Floating Point format supported in SAP BusinessObjects Web Intelligence 4.2 and higher. With this property checked in the measure object, the application using the universe can use its high precision number format to improve the precision of its calculations.

When a query uses a measure object with a dimension or attribute object, the query definition automatically infers a GROUP BY clause in the SELECT statement. Why?

When the SELECT clause line contains an aggregate, everything outside of that aggregate in the clause must appear in the GROUP BY clause.

Here the query includes only the Sales Revenue measure, so the inference engine does not include a GROUP BY clause in the SQL statement.

When a query uses at least one dimension or attribute object, and a measure, the inference engine includes a GROUP BY clause in the SQL statement. This clause includes all the objects, except the measure, as follows:

• The SELECT clause shows the objects and measure selected in the query with the syntax including the aggregate function.

• The GROUP BY clause includes all the objects except the aggregate.

Here the query includes two dimensions (Country and Region), so the inference engine includes both dimensions in the GROUP BY clause. As a result, the values returned for the Sales Revenue measure object are aggregated to a lower level, the Region. This mechanism in the inference engine allows the measure objects to adapt dynamically to other associated objects.

When you test a measure, be far more rigorous in your checks than with a dimension or an attribute. This rigor is necessary for you to confirm that the values of the measure aggregate correctly, both at the SELECT and PROJECTIONS levels.

Remember the three steps to testing a dimension or an attribute object are:

1. Check that the objects exist.
2. Check the inferred SQL.
3. Check the query results.

For measure objects, the additional step is:

• Make a query with a minimum of two dimensions and a measure.

To test the inferred SELECT statements for a measure object, make at least two separate queries using different dimension objects to produce different levels of aggregation. Three or more queries are preferable.

In each instance, check the following:

• The inferred SQL of the query. In particular, check that the GROUP BY clause has been inferred correctly.

  Hint

  If it has not been inferred, it is likely that you have set a calculation and not an aggregate in the Select field of the measure object properties.

• Check that the query produces the correct results.

To test for projected aggregation, build a query in SAP BusinessObjects Web Intelligence. Ensure that this query contains at least two dimension objects as well as the measure object you are testing. This query allows you to project from data levels above the lowest level in the microcube, and therefore test projection aggregation.

The Information Design Tool allows designers to create measures whose Projection aggregation is delegated to the database. These measures are called Delegated.

A universe designer can create a delegated measure when conventional projection aggregation does not provide an accurate result for the measure, such as for:

• Complex averages, such as weighted averages (an average of a percentage)

• Ratios

• Other measures that do not aggregate along all the dimensions in a report

• OLAP sources where measure aggregations are already available in the OLAP cube

Delegated measures are available for all relational and OLAP data sources.

The delegated measure represents an extension of reporting tool calculations by supporting aggregation within the database, and makes nonadditive measures available within the universe.

You benefit from delegated measures because they:

• Can increase querying efficiency

• Use database-specific syntax to improve performance and provide optimization on the internal architecture of all vendors

• Extend support of calculations for all BI 4 reporting tools

By default, Web Intelligence calculates measures based on the objects used within the query. The dimensions used in the query are called a grouping set. The delegated measure has the database calculate the aggregation of the measure for any subset of dimensions projected in the report.

For example, a query that retrieves Country, Region Name, and Avg Sales Total dimensions would, by default, calculate the Avg Sales Total GROUPed BY Country and Region.

However, Web Intelligence cannot accurately calculate the correct Avg Sales Total for fewer dimensions, such as the Avg Sales Total for Country only.

This average appears accurate, but it is not. The reporting tool is summing the values shown in the Avg Sales Total column and then dividing them by four (since there are four regions in the USA). The values in the Avg Sales Total Not Delegated column are already calculated averages, so the reporting tool is actually calculating the average of averages.

The delegated measure, on the other hand, calculates the values for all possible subsets of the dimensions in the report. Thus, it would calculate Average Sales Total per:

• Country

• Country and Region

To create a delegated measure in the universe, in the properties of the measure, set the Projection Function to Delegated. This action will delegate the calculation to the database.

Keep in mind the following best practices for effective use of delegated measures:

• Use a delegated measure to replace multiple query aggregates.

• Use a delegated measure on calculations that could give inaccurate results when calculated in the report (such as a complex average).

• When creating the delegated measures in the business layer, enter identifying text in the description field of the Measure properties dialog box, on the Definition tab. This text ensures that report designers can quickly recognize a delegated measure when they glide their mouse over it in a query.

Do not use delegated measures when you apply a filter to an aggregated value from your query and further aggregate it within the report.

Web Intelligence cannot determine how the filter affects the calculation of the delegated measure and, instead, returns the cell error code #UNAVAILABLE.

For example, do not use delegated measures when there are any of the following:

• Report filter on a dimension that is not in the dimensional context

• Report filter on the formula

• Formula in the dimensional context of the measure

However, for drill filters and simple filters combined with AND at the first level of drilling, the context takes the related dimension into account.

Do not use a delegated measure when a standard measure works accurately. You do not want to use database resources if you do not need to.