Delegated measure

When you look at the projection aggregation options in BO3.1 one of them is a Database delegated.

14.1

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.

14.2

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.

14.3

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.

14.4

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s