All you need to know about #TOREFRESH
Today I would like share my knowledge about the #TOREFRESH message, tackle some of the myths around it and show you, what you can do in order to avoid the message.
What does it mean #TOREFRESH?
When you see this message, the tool is reminding you that a refresh is needed before you can see the data in the report.
Is #TOREFRESH an error message?
No, it is a kind reminder.
When does #TOREFRESH appear?
Typically it happens after changing the current navigation state, e.g. after adding or removing a dimension from a table. It also happens when applying report filters like Input controls or using simple report filters (dropdowns) in the Filter Bar. The #TOREFRESH message happens only when the requested aggregation cannot be calculated out of Web Intelligence’s internal cache, the famous micro-cube. It only happens when there are key figures involved that cannot be calculated on the fly out of the micro-cube, this kind of key figures are called database delegated.
An example of when #TOREFRESH happens can be seen in following video:
Pop quiz: There are only two records that display #TOREFRESH, all others can be correcty displayed right away, why?
What are database delegated key figures?
Some key figures cannot be calculated in the frontend, therefore they must be calculated at the backed, the projection function for conducting the calculation in the backend is called database delegated or simply delegated. Behind the scenes, new separate queries are being sent to the database in order to get the value for this key figures. A synonym for database delegated key figures is smart measures.
In case of universes, then database delegated key figures are explicitly defined as such in the business layer. In case of direct access to BEx queries, this is a bit more tricky as web intelligence will decide which key figures are delegated and which ones not. This decision-making capability was improved in BI4.0 SP04, before that, all key figures were simply treated as delegated.
How can I check if a key figure is database delegated?
In case of universes, you can go to the business layer and double click on the key figure, there search for the corresponding Projection Function and if it says Delegated, then you have got yourself one.
In case of direct access to BEx queries you need to go into Web Intelligence’s query panel or available objects and hover over the key figure, there you will find a message saying Aggregation: Sum or Aggregation:Delegated. Calculated key figures from BEx are treated as delegated, even if the two basis key figures are set up as Sum. Restricted key figures will be treated as its original key figure. Here how to check if your key figure is database delegated:
Can I avoid the #TOREFRESH message by changing the aggregation of key figures?
Yes, but only in some cases. As the #TOREFRESH message only appears with key figures that are set as database delegated, one of the solutions at hand could be changing all aggregations to SUM. While this is correct in some cases, there is also a good chance that the data that you will be seeing is not correct anymore, so think twice and double-check the outcome before making this change.
Can I avoid the #TOREFRESH message by deactivating Query Stripping?
Yes, but in only in some cases. Query Stripping will avoid calling too much data if you do not need it in the report (Difference between query panel and a table in your report). But it is not always the case that the aggregation level that you are requesting, can be fulfilled by requesting the lowest level of detail as defined in the query panel.
How can I avoid the #TOREFRESH message in general?
This is one of the tricks that only few people know, but it is as simply as mighty: You can fill in the micro-cube with different aggregation levels, by inserting hidden tables with the needed aggregation levels! For example if your report consists of four dimensions, you could add 4 different tables, first one with one dimension, second one with two dimensions and so forth. With this trick, you will be feeding the micro-cube with more aggregations levels, and therefore avoiding the #TOREFRESH message because the request can be fulfilled out of the internal cache.
Following video repeats the situation demonstrated in the video above, but this time with an additional table to the right:
What is the auto-refresh feature?
Auto-refresh is a feature released in BI 4.1 that refreshes the document automatically when needed, therefore you will not see the #TOREFRESH message .It simulates that you would click on refresh after seeing the #TOREFRESH message, so be aware that it could in fact refresh more than you would like to. Here an example of avoiding the #TOREFRESH message with the automatic refresh feature of BI4.1:
I hope you enjoyed and perhaps even learned something new while reading this document.