How to clear cache in SAP Analysis for Microsoft Office

When SAP Analysis for Microsoft Office (AfO) is being reinstalled or upgraded there can be several errors popping up causing not possible to reuse/refresh the existing reports. Basically, the AfO is crashing or freezing with errors like following.

 

"An exception occurred in one of the data sources. SAP BI Add-in has disconnected all data sources. (ID-111007)"

"Nested exception. See inner exception below for more details."

 

Root cause of the errors like those is that when the AfO is upgraded/uninstalled, the process process does not clear the cache of the AfO. To correct it the cache needs to be cleared manually.

First folder of the cache to look for to be cleared is:

"c:\Users\<USER_ID>\AppData\Roaming\SAP AG\SAP BusinessObjects Advanced Analysis\cache\"

In this folder the cache files located that falls under a naming convention like this:

<SID>.cache

 

Other folder to look for is COF (Common Office Framework) directory out of %APPDATA% that is accessible under link:

"%APPDATA%\SAP\Cof"

It that points to folder:

"c:\Users\<USER_ID>\AppData\Roaming\SAP AG\SAP BusinessObjects Advanced Analysis\cache\"

 

Once the cache is cleared start the AfO from Windows Start menu (All Aps -> SAP Business Intelligence -> Analysis for Microsoft Office) and it should be possible to refresh AfO reports.

 

More information:

2979452 - An exception occurred in one of the data sources. SAP BI Add-in has disconnected all data sources [1e04-3ff5-15]

AfO wiki

How to launch AfO report via URL

This is a continuation of my earlier blog post about launching AfO documents (reports) from SAP GUI. Analysis for Office (AfO) documents can be shared with users also by sending them just a simple URL that will open the document in either MS Excel or MS Powerpoint applications. The URL is basically an SICF (SAP Internet Connection Framework) service for Analysis documents. It has following syntax:

 

http(s)://<server>:<port>/sap/bw/analysis?application=<application>&object_type=<type>&object_id=<id>&var<VAR1_TECH_NAME>=<val_of_variable1>&var<VAR2_TECH_NAME>=<val_of_variable2>

 

A value of application parameter can be excel or powerpoint. The value of object_type parameter can be document (for workbooks and presentations), query, query_view or infoprovider. Finally, the value of object_id is the technical name of the object in the BW system, e.g. AfO report technical name. Parameters of the report’s selection screen can be supplied as well – see the URL’s parameter like var for passing variables values etc.

What happens when the URL is accessed is that a web browser gets a file with an extension sapaox (e.g. analysis.sapaox). Over that file depending on BW’s backend configuration either logon popup window is displayed and the user provides logon credentials there or via Single Sing On (SSO) target AfO application is opened (Excel/Powerpoint) and the AfO report is shown in there. The sapaox file is technically an XML file that contains ticket which allow the authenticated login to the backend server (BW servers BusinessObjects BI platform server).

However, in order to this function working there is a one prerequisite. The SICF service mentioned above needs to be activated via t-code SICF.

 

More information:

Online docu

Launch AfO from SAP GUI

2658872 - Analysis Office: How to ensure secure connections in Analysis Plugin

3105370 - Using and troubleshooting SAC Live Data Connections in Analysis Office

Launch AfO from SAP GUI

Here is a brief blog post regarding t-codes that enables launching of Analysis Office (AfO) from SAP GUI.

 

t-code          ABAP prog                       Description

RAAOE          RS_AAOE_LAUNCHER         Launch Analysis Office Excel

RAAOP          RS_AAOP_LAUNCHER         Launch Analysis Office PowerPoint

 

Both t-codes in background are running 2nd ABAP report: RS_AO_LAUNCHER. That one can be also run standalone as well. It has following selection screen:

As you can see, the selection looks the same regardless of what t-code was used to launch it. Just a description of the report (title) is different: Analysis Excel Launcher vs Analysis PowerPoint Launcher.

Following are fields available on the selection screen:

Client Only – Launches just either Excel or PowerPoint w/o opening any reports. User can login to SAP BW system and open reports from the Excel / PowerPoint.

Document – Opens Excel or PowerPoint with AfO document (workbook) specified in input field below called Document

Query – Opens Excel or PowerPoint with BEx/BW Query specified in input field below called Query

Query View – Opens Excel or PowerPoint with BEx/BW Query View specified in input field below called Query View

Notice that Document/Query/Query View field is showed/disabled depending in which position the radio button is switched to.

Logging to Analysis for Office with different (no SSO) user

Single Sign On authentication scheme is very popular and useful for users. While user is successfully authorized by OS with the same login ID is allowed to access to other applications. In many organizations, it is deployed also for SAP systems. In one of my previous posts, I described how to avoid SSO for Analysis for Office. However, in case user has no possibility to edit entries to SAP systems in SAP Logon Pad – simply just deactivating the SSO for particular SAP system will not help.

Luckily, there is another option. First following settings of the AfO need to be adjusted. In Options part of Analysis Customizing available menu on MS Excel: File -> Analysis -> Customize Analysis -> Options:

Switch to Advanced tab and choose and enable following two check boxes:

-      Allow Client and language Selection for SSO Logon

-      Force Logon Window for SSO Logon on BIP

Now try to open Insert Data Source -> Select Data Source for Analysis a BW report while selecting menu on Analysis ribbon, and Skip below popup window:

On next pop-up, choose particular BW SAP system, which you want to log on into:

Choose Cancel for next pop-up:

Finally, you are prompted to enter logon criteria for non-SSO user:

More information:

Bypassing SSO in Analysis Office (AO)

Logging to SAP BPC Add-in with different (no SSO) user

What is SAP Analysis for Microsoft Office, edition for SAP Analytics Cloud

As per SAP's "BI Convergence Strategy 2018" there is an edition of  SAP Analysis for Microsoft Office (AfO) that works with SAP Analytics Cloud (SAC). It is called "SAP Analysis for Microsoft Office, edition for SAP Analytics Cloud" - "AfO-SAC". This means that AfO-SAC is integrated to SAC so there will be in future just one solution that integrates with both on premise and cloud-based data sources. This is what SAP calls as bridging the gap between cloud and on-premise systems.

In particular within AfO-SAC can: consume models (including both models analytics and planning ones) from SAC (an SAC connection can be created in AfO-SAC = as data source), store AfO workbooks in SAC environment, store AfO workbooks having SAC models as data sources locally, working with SAC hierarchies, enter planning data of SAC planning model in a crosstab in AfO analysis (front-end cell locking is used), etc.

Although that there are currently many restriction within the products (see online docu and roadmap) the AfO-SAC is being heavily developed together with strong support on SAP BW backend side. Which with the newest SP 16 (soon to be released) for BW 7.5 version is a kind of “go-to release” for all organizations that wants to use SAC.

As of AfO version 2.7 the AfO-SAC version follow the original AfO versions. Just it is market as different component - ANALYSISOFFICE_FOR_SAC.

More information:

Official docu

roadmap

Switching to BEx Query Designer from SAP BI front end tools

Running BEx Query Designer (QD) directly from BI front-end tool is very common case especially when the QD opened directly the query, which is displayed in the front end tool. Having this function in place means no need to open the QD separately and no need to find the particular query in the QD while it is opened.

BEx Analyzer has this function for a very long time. It is available via menu Business Explorer -> Analyzer -> Choose Add-Ins, and from BEx Analysis Toolbox, choose Tools -> New Query.

In Analysis for Office (AfO) which is supposed to be replacement for BEx Analyzer this function was missing in its earlier versions. However at least in 2.6 the function is there. It needs to be enabled first by customizing the AfO’s UI. This is available via menu Analysis -> Custumize Analysis -> Custumize User Interface. 

On this pop-up window under Ribbon, part there is a Tools section available and under that one “Launch Query Designer” needs to be ticked off.

Finally, the BEx QD is available on AfO's ribbon:

Managing settings of AfO

Settings or configuration of Analysis for Office (AfO) was done via adjusting an registry settings for AfO of version 1.x in a client machine. However, this was changed and as of version, 2.x the configuration of the AfO tool can be done via maintaining XML configuration files. There are the config files available for Administrator and regular user. There are 2 files available for each of user:

Files for administrator are located in folder: %PROGRAMDATA%\Sap\Cof

        Ao_app.config

Cof_app.config

And for user in folder: %APPDATA%\Sap\Cof

Ao_user_roaming.config

Cof_user_roaming.config

Another way to access the settings is via Excel menu File -> Analysis -> Customize Analysis -> Technical Configuration.

More info:

2083067 - How to maintain settings for Analysis Office 2.x

Online docu

Versions of Analysis for Office

Last update: 17JAN2022

Here’s a short overview on what versions of an Analysis for Office tool (the new BEx) are available.

AfO SPS                 Release Date (RTC)          Note

2.8

2.8 SP 12                09.12.2021                       3051730

2.8 SP 11                07.10.2021                       3051741

2.8 SP 10                29.07.2021                       3051755

2.8 SP 9                  15.06.2021                       2993498 

2.8 SP 8                  08.04.2021                       2993475

2.8 SP 7                  11.02.2021                       2993485

2.8 SP 6                  27.11.2020                       2927196

2.8 SP 5                  06.10.2020                       2924400

2.8 SP 4                  29.07.2020                       2924387

2.8 SP 3                  29.05.2020                       2917177

2.8 SP 2                  30.03.2020                       2679436

2.8 SP 1                  10.02.2020                       2679485

2.8 SP 0                  28.11.2019                       2679473

2.7

2.7 SP 10                17.03.2019                       2874414

2.7 SP 9                  12.12.2019                       2772754

2.7 SP 8                  27.09.2019                       2772753

2.7 SP 7                  29.07.2019                       2772706

2.7 SP 6                  19.07.2019                       2814781

2.7 SP 5                  21.03.2019                       2738666

2.7 SP 4                  01.02.2019                       2733028      

2.7 SP 3                  21.12.2018                       2609916      

2.7 SP 2                  05.10.2018                       2609982      

2.7 SP 1                  01.08.2018                       2609829                

2.7 SP 0                  13.6.2018                         2609974

2.6

2.6 SP 3                  17.5.2018                         2557367

2.6 SP 2                  21.03.2018                       2519188

2.6 SP 1                  07.12.2017                       2519128

2.6 SP 0                  04.12.2017                       2519256

2.5

2.5 SP 4                  20.02.2018                       2557401

2.5 SP 3                  14.12.2017                       2541311

2.5 SP 2                  28.09.2017                       2423532

2.5 SP 1                  01.08.2017                       2423329

2.5 SP 0                  31.5.2017                         2423523

2.4

2.4 SP 5                  29.11.2016                       2483758      

2.4 SP 4                  01.07.2017                       2402003

2.4 SP 3                  29.05.2017                       2401970

2.4 SP 2                  06.03.2017                       2401950  

2.4 SP 1                  13.01.2017                       2360880  

2.4 SP 0                  28.11.2016                       2360879

2.3

2.3 SP 3                  26.05.2016                       2367092

2.3 SP 2                  20.09.2016                       2289542

2.3 SP 1                  22.07.2016                       2289571

2.3 SP 0                  24.05.2016                       2276887

2.2

2.2 SP 4                  20.11.2015                        2215168

2.2 SP 3                  05.05.2016                       2241328

2.2 SP 2                  11.01.2016                       2241327

2.2 SP 1                  28.12.2015                       2241326

2.2 SP 0                  16.11.2015                       2215163

2.1

2.1 SP 3                  29.05.2015                       2204161      

2.1 SP 2                  14.09.2015                       2164363

2.1 SP 1                  23.07.2015                       2160993

2.0

2.0 SP 4                  30.07.2015                       2164371

2.0 SP 2                  09.03.2015                       2091746

1.4

1.4 SP 17                 08.03.2016                      2241062

1.4 SP 16                08.03.2016                       2241061

1.4 SP 15                07.12.2015                       2139225

1.4 SP 14                30.09.2015                       2139224

1.4 SP 13                20.07.2015                       2139223

1.4 SP 12                14.05.2015                       2139222

1.4 SP 11                09.03.2015                       2059340

1.4 SP 10                10.12.2014                       2059338

1.4 SP 09                10.10.2014                       1910506

1.4 SP 08                29.07.2014                       1910505

1.4 SP 07                12.05.2014                       1910504

1.4 SP 06                24.3.2014                         1910503

1.4 SP 05                05.12.2013                       1909252

1.4 SP 04                14.10.2013                       1909251

1.4 SP 03                05.09.2013                       1854154

1.4 SP 02                23.07.2013                       1854153

1.4 SP 01                11.06.2013                       1854112

1.4 SP 0                  11.06.2013                       1843946

1.3

1.3 SP 09                24.06.2013                       1814288

1.3 SP 08                24.04.2013                       1814287

1.3 SP 07                05.04.2013                       1841790

1.3 SP 06                31.01.2013                       1740318

1.3 SP 05                10.12.2012                       1740317

1.3 SP 04                25.01.2013                       1740316

1.3 SP 03                25.01.2013                       1740315

1.3 SP 02                24.08.2012                       1740314

1.3 SP 01                20.07.2012                       1740313

1.2

1.2 SP 10                12.09.2012                       1698671

1.2 SP 09                21.08.2012                       1698670

1.2 SP 08                23.07.2012                       1698669

1.2 SP 07                09.07.2012                       1739684

1.2 SP 03                19.03.2012                       1677666

1.1

1.1 SP                     N/A                                   1518359

 

Useful information:

1518359 - SAP BusinessObjects Analysis Office & BIP Add On Release schedule

Online documentation help.sap.com/boao

2482784 - Best Practices in Analysis for Office

SCN wiki: wiki.scn.sap.com/wiki/display/BI/BI-RA-AO+-+SAP+BusinessObjects+Analysis%2C+edition+for+Microsoft+Office

wiki.scn.sap.com/wiki/display/BI/Analysis+Office+Installation+and+Update

Feedback to improve Analysis for MS Office:

influence.sap.com/SAPBusinessObjectsAnalysiseditionforMSOffice