Purpose

A Universe should be designed to deliver reports that maximize the performance of data returned.  Database delegated measures are a feature in universe design that can greatly impact reporting delivery.

Benefits of Smart Measure

Database delegated measures can improve query performance.  Taking advantage of this built in feature can give a great boost to the speed of the query result set returned in reporting.  A projected aggregation is set for each measure object in the universe design.  Database delegated objects perform the calculation on the database server and return only a calculated result set.

Setup the Database Delegated Measure object

Universe Designer:   Double click a universe measure object, on the properties for the measure object, select database delegated from the drop list of projected aggregation.  Click OK.

Information Design Tool (SAP BI 4.0):  In the Business Layer, Select a measure Object, Select Delegated from the Projection Function pick list.

Behavior of Database Delegated Measure objects

A basic design element of the universe is setting a projected aggregation level for measure objects.  This sets the dynamic aggregation levels for your reporting tools.  Typically a measure object is set to sum, count, min, max and avg.  These determine the behavior of a measure object on the report.  When a measure object is set to SUM, it returns all rows for dimension objects listed in the query.  Webi then perform the calculation of the measures depending on what dimension objects are combined on the query.  For example if a query is created with Year and Quarter and Sum of Revenue. The report will return a sum, by those dimension objects.  If the Quarter dimension object is removed, the Sum of Revenue will adjust to the dimension object, in this case, Year on the report.  This is a trademarked feature of Web Intelligence called semantically dynamic.

When the option of database delegated is selected as the projected aggregation, it sets the projected aggregation to None and the dimension objects are no longer semantically dynamic.  The database then performs the work of summing the measure, in this case the revenue and the completed result set is returned.

The performance improvement occurs because a smaller number of rows are returned to reports.  Imagine this compared with a high volume of rows and multiplied by many reports.

In the above example (high volume of data – 3 years), if a query is run with year, quarter and sum of revenue, then  report will return a result set of just the summed year and quarter. If on the report block, the quarter is removed and a sum of year is required, the Cells will return #TOREFRESH.  When this occurs, the SQL must be regenerated to accommodate the new aggregation level.

When two blocks exist of different aggregation levels, the SQL must regenerate to accommodate each grouped level.  This is called grouping sets.  A query refresh regenerates the SQL.  A SQL is created for each table block, year, quarter and sum, and then another SQL for year and sum.  The SQL uses a union command to combine results together.

Following the SQL rules for combined queries, when unions are used objects they must have the same number of objects so reports generates a place holder.

Non-Additive measures

Some measures such as ratio, average and weight can only be set to a projected aggregation of None in the universe.  By using the projected aggregation of Database delegated it pushes the calculation of these objects to the database server.  It is recommended for ratio’s and averages to set the projected aggregation to database delegated.

Points to be considered when using Smart Measures

• A refresh will be required for each SQL query statement that requires aggregation.

• When purging data, each SQL query in the report will be regenerated and refreshed.

• Delegated Measure Objects are not recommended for Ad-hoc report usage.

• Delegated Measure Objects can be used for Standard / Static reports which give very good performance.

• When using multiple levels of aggregation a separate SQL query for each grouped set on the report.

• When using aggregated tables it is strongly recommended to ensure the aggregated tables have consistent data. Or inconsistent results may occur.

• OLAP and Essbase universes automatically define measure objects as database delegated.

What parameters should not be used with Smart Measures?

A Smart Measure cannot be combined with the JOIN_BY_SQL parameter.  These parameters should not be used together in the same universe.