When you look at the projection aggregation options in BO3.1 one of them is a Database delegated.
Here is an example how it can be used and be very useful in a report.
We have a simple dataset where all objects on the left are dimensions and on the right where Avg Amount Sold is a measure. There are 2 values of a measure for the same Year and Month that’s why it’s aggregated using Average function in both SQL and projection aggregation.
Now if we remove a Month object from the block a projection aggregation will recalculate results further using Average. However the result will be based on a dataset from the block and not a dataset from the database. This gives incorrect results.
In order to get correct results we can set a projection aggregation to Database delegated. After the change we bring again Year, Month and Amt Sold in the query and put all of them in a block. When we remove a Month object we get #TOREFRESH error which indicates that we need to run the query again. After the refresh we get a correct result.
Let’s take a look at the SQL in a data provider.
This is an original generated SQL:
SELECT Calendar.Yr, Calendar.Mth, AVG(Sales.Amount_sold) FROM Sales INNER JOIN Calendar ON (Sales.Week_id=Calendar.Week_id) GROUP BY Calendar.Yr, Calendar.Mth
This is a new generated SQL after removing a Month object from the block:
SELECT 1 AS GID, Calendar.Yr, Calendar.Mth, AVG(Sales.Amount_sold) FROM Sales INNER JOIN Calendar ON (Sales.Week_id=Calendar.Week_id) GROUP BY Calendar.Yr, Calendar.Mth UNION SELECT 0 AS GlD, Calendar.Yr, NULL, AVG(Sales.Amount_sold) FROM Sales INNER JOIN Calendar ON (Sales.Week_id=Calendar.Week_id) GROUP BY Calendar.Yr
We see that BO created a UNION consisting of 2 datasets, one with the aggregation at the Year, Month level and the second one with the aggregation at the Year level. This way a microcube in BO contains enough data to show correct results in a block. However remember that if you decide to use that measure in multiple aggregation contexts within a report BO will store as many UNION parts as required causing a query to retrieve more data and slowing it down.