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.
Overview
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.
This is a discussion of how to set this feature in the Universe, what impact this has on the reporting SQL and what are some of the expected results in Web Intelligence (Webi.)
How to set the database delegated measure object
Universe Design Tool: 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: In the Business Layer, Select a measure Object, Select Delegated from the Projection Function pick list.
An Explanation 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 WebI. Imagine this compared with a high volume of rows and multiplied by many reports.
Using the same example, if a query is run with year, quarter and sum of revenue, then WebI 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 WebI 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.
What are the expected behaviors in 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.
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.
No comments:
Post a Comment