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)
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.
Delegated Measure Benefits
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
Delegate Measure Calculations
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 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.