Macro: Refresh vs RefreshData vs SetRefreshBehaviour

[former_member354722]

What's the difference between using Refresh and RefreshData? The guide says both can "redraw" the crosstab. According to the guide:

"Refresh" is used "to initially refresh the data in the workbook". If the data source is already refreshed, "crosstabs are redrawn". "RefreshData" updates data "from the server and the crosstabs are redrawn."

I'd think "Refresh" will take the longest time since it would expand rows and columns for any new data found(?). "RefreshData" refreshes only the cells of the current crosstab. That's my guess. However, RefreshData takes 8x longer than Refresh in my example crosstab which seems to contradict my thoughts on what Refresh and RefrehData are doing.

1. Open workbook

2. Refresh or RefreshData?

3. Refresh crosstab cells only...RefreshData.

3. Refresh/redraw entire crosstab...Refresh.

However, RefreshData always takes way more time.  I tested refreshing one crosstab back to back and got the results below:

Related Question...

How do these relate to using PauseVariableSubmit with SAPSetRefreshBehaviour? What does SAPSetRefreshBehaviour do compared to Refresh and RefreshData? I'm asking because my workbook opens, update variables, refresh workbook---this is done using SAPSetRefreshBehaviour. Is there a faster way possible maybe thru Refresh and RefreshData?

For example, which is better (assuming #2 is somehow possible):

Example 1 (my current method):

1. SAPSetRefreshBehaviour Off
2. PauseVariableSubmit On
3. Update variables
4. PauseVariableSubmit Off
5. SAPSetRefreshBehaviour On

Example 2:

1. PauseVariableSubmit On
2. Update variables
3. PauseVariableSubmit Off
4. Call Application.Run("SAPExecuteCommand", "Refresh", "DS_1")
5. Call Application.Run("SAPExecuteCommand", "Refresh", "DS_2")
6. Call Application.Run("SAPExecuteCommand", "Refresh", "DS_3")

BONUS question:

What refresh method is used when clicking OK on the variable prompt screen? Refresh, RefreshData or something similar but does extra steps?

[Former Member · Accepted solution]

Hi John,

RefreshData will actually fetch any new data from the database, while refresh uses all caching available and will not display new data since the last refresh.

If my memory is correct, RefreshData came in with a new version of Analysis for Office.

When clicking ok on the prompt screen (or refresh from the contextmenu), it is definitely RefreshData, as you will see any new records added.

If your current method 1. does not refresh the datasource several times for each variable send (which I think is the case), then it is already optimal and I dont see a reason to think about changing to method2.

Best regards,

Victor

[former_member354722 · Accepted solution]

Hi, Victor. Here's my thought thus far:

• "Refresh" will perform a "RefreshData" equivalent if the datasource has not yet been refreshed. If it has been refreshed, "Refresh" will simply query from the cache.
• "RefreshData" will always query the server.
• "SAPSetRefreshBehaviour=On" always performs a RefreshData equivalent.

The User Guide is a bit confusing when it mentions Refresh and RefreshData both "redraw" crosstabs and to use Refresh on an initial refresh. Maybe "Refresh" can perform two different actions depending on the state of the crosstab. "RefreshData" came later when you want to always query the server regardless of the crosstab's state.

I'm not clear on when you would only want to refresh from a cache (vs. server).

[Karol-K]

Hi John,

actually all three are described in the End User documentation:

SAP BusinessObjects Analysis, edition for Microsoft Office 1.4 SP8 – SAP Help Portal Page

->

http://help.sap.com/businessobject/product_guides/AMS14/en/14SP8_aaoffice_user_en.pdf

are some informations missing there?

Regards, Karol