I have been trying to use the new option 'Database Delegated' to overcome distinct count issues in my universe
I do do not return the correct results using distinct count, so I changed the projection to 'Database Delegated'. It works...sometimes....more often than not, it returns #TOREFRESH in the field. I just wondered if there were any were any guidelines for its use...
It always work by itself in a block, but returns #TOREFRESH when combined with certain dimensions..
When you set a measure aggregation function to "database delegated", the aggregation provided by the relying data source is not automatically generated and requires sometimes the user to refreh the query.
That's why the #TOREFRESH message has been designed for: the user needs to refresh manually the query.
#TOREFRESH appears when you create a block (table or chart) that does not contain all the objects selected in the query.
It also should happen when you create a section or a break or when you add a sum for a section/break/block.
This choiice has been driven by the fact that it would have been not performant and complex to compute all aggregations possible for a given query.
#TOREFRESH appears in a Web Intelligence Report when the measure used in the query has a projection function set to Database Delegated (Smart Measure) in the universe and Scope of Analysis is set to a specified level in the Web Intelligence Report. Thus, when the user drills through in the report, Web Intelligence needs to delegate the calculation to the database and hence needs to connect to database but Scope of Analysis stops it from going to the database. (Because scope of analysis brings in all data to a level set right at the time of first query and not during each drill). This is by design.
To avoid #TOREFRESH, you can remove the scope of analysis option in Web Intelligence Report. By doing so, each time when a drill operation is performed, a new query is performed and the Database Delegated measure will be able to delegate the calculation of function to the database.
Reference: SAP Internal
Hope this helps.
#Refresh error comes some times if you have some formula based on some characteristic value i.e. let suppose if you Product ID and Product Description at the Universe Level and you created a new Object at Webi Level which concatenate bot h the ID and Description try to remove any such formula in the webi report .
I know it may sound wearied but it worked in my case, so give it a try.
Yes, I'm sorry, but database delegation is the standard with WebI/BICS.
This will change to sum() for keyfigures without exception aggregation in Feature Pack 3 (SP3, ex-BI4.1).
Development is looking for ways to control the aggregation type even later in the product.