SAP Analysis for Microsoft Office – Storage of workbooks

[steffen_heine]

SAP Analysis for Microsoft Office workbooks can be saved locally, on SAP Business Warehouse or on the SAP Business Intelligence Platform. With 2.8 SP18 or higher workbooks can be also saved on the SAP Analytics Cloud repository.

SAP Analytics Cloud as repository

When having the setting EnableSacAsRepository enabled, Analysis for Office users can store workbooks (xlsx and xlsm files) on the SAP Analytics Cloud file repository. In Analysis for Office the preferred platform must be Selectable or SAP Analytics Cloud.

To save the workbook in the SAP Analytics Cloud file repository navigate in Microsoft Excel to File and Analysis. There users find the option to save workbooks on SAP Analytics Cloud:

Save Workbook

 

After an SAP Analytics Cloud logon, the folders of the file repository are shown in which the workbook can be stored. Via right-click it is also possible to rename and delete Analysis Workbooks directly on the file repository. After saving a message toast will confirm the successful upload.

SAP Analytics Cloud folders

In SAP Analytics Cloud users can search and filter for Analysis Workbooks like the other artefacts e.g., stories. Also, workbooks can be shared with other users and added to the catalog. The SAP Analytics Cloud Content Network allows to move workbooks to different SAP Analytic Cloud tenants. For that scenario the SAP Analysis for Microsoft Office setting SystemTransportMap described in the previous blog is helpful.

SAP Analytics Cloud repository

Pre-requisite on SAP Analytics Cloud side is that the SAP Analytics Cloud tenant is running on Cloud Foundry and has at least version 2023.10 (QRC3 2023) deployed. Also, the user needs to be assigned to a role which has the create permission Analysis Workbook enabled. For opening workbooks, the read permission is required.

Analysis Workbook permission

Further helpful settings:

• RemoveDataBeforeSaving: To avoid that critical data within the workbook is stored on SAP Analytics Cloud the setting removes all data before the workbook is saved. When opening the workbook, the user first needs to refresh the data. This setting can be promoted to an perMachine setting, so that a business user cannot overwrite it in the components tab.

 

• SkipLogonToBIPDialog: The optional BI Platform logon is not shown anymore when the preferred platform is not set to Business Intelligence Platform.

 

More information in the Admin and User guide.

 

Logon to SAP Analytics Cloud

The SAP Analysis for Microsoft Office Edition for SAP Analytics Cloud requires a mandatory SAP Analytics Cloud logon before accessing any connection. With SP18 this logon is less strict when using connections to on-Premise backends, e.g. SAP BW, only. After a successful logon to the SAP Analytics Cloud tenant a token is stored locally for the user (%AppData% -> Local\SAP\Cof). As long this token is valid (max. 29 days) there is no further logon to SAP Analytics Cloud needed. SAP Analysis for Microsoft Office is trying to refresh the token in the background when Analysis is used (max. once per day). More information you find here.

Hint: The SAP Analytics Cloud URL for the logon is stored in the setting LastSelectedSacSystem.

 

Maintenance of SAP Analysis for Microsoft Office

Based on the latest SAP Analytics Business Intelligence Statement of Direction, also the support for SAP Analysis for Microsoft Office was extended to at least end of 2029. Even there is no further feature improvement planned, corrections for customer issues will be delivered regularly with upcoming support packages.

[sap_cohort]

Sounds like great functionality.  Couple of questions if anyone can help.

Do AO Workbooks stored in SAC need to access only SAC DataSources or can the AO Reports also access S4 DataSources as well?

With the AO Workbooks stored in SAC is there any performance issues with accessing SAC DataSources vs S4 DataSources?

Any ability to schedule workbook runs and saving results?

Thanks for any details you can provide.

[pbaumann]

Hi steffen.heine !

Nice feature, long waited for. Thanks!

[steffen_heine]

Hi Ken,

you can connect against all data sources:

Regarding the performance: You need to download the workbook from SAC which leads to a couple of seconds for SAC authentication and download from SAC server to you local machine.

There is no plan for scheduling workbook runs on SAC.

Regards,
Steffen

[michael_e]

Dear Steffen,

thank you for the update.

Your blog says, that support of variants and precalculation/scheduling is not planned. I think this is a major disadvantage compared to BI platform. How could I schedule workbooks in the future? Do I have to run the BI platform in parallel to SAC?

If we store AfO-workbooks on Netweaver: Are they accessible from SAC?

Best regards,

Michael

[JensBraun]

Hello Michael,

you can make workbooks stored in Netweaver accessible in a sense, that you can add them to the SAP Analytics Cloud Catalog by using the web services provided in Netweaver. This would be a possibility to provide a central entry point for all reports.

But this is not the same level of integration that Steffen describes in the blog post as users.

Best regards,
Jens

 

[andreasja_schneider]

Hello, thanks for this blog.

 

Is there a way to embedd a URL in an SAC Story, which will open Analysis for Office on the user's Laptop, assuming it is installed in the default location on the user's laptop?

 

Thanks,
Andreas J.A.

[former_member13910]

Hi Steffen,

Thanks for your blog series on MS Integration. On this blog, Where can we find the setting "EnableSACasrespoitory"?

Thanks

Preeti

[steffen_heine]

Hi Andreas,

I would try to point in SAC Story to an URL of a AFO launcher file which is then opening AFO when downloaded.

https://help.sap.com/docs/SAP_BUSINESSOBJECTS_ANALYSIS_OFFICE/df90d3701aba42b9a4351caa387bd672/391d0...

Regards,
Steffen

[steffen_heine]

Hi Preeti,

the setting you find in Excel under: File -> Analysis -> Customize Analysis -> Technical Configuration -> Search for EnableS -> Activate it.

Make sure that the SAC tenant you connect to is on wave 2023.10 or higher.

Regards,
Steffen

[varunraina]

Thanks, steffen.heine for sharing this. Very helpful!

One QQ, After setting it and while trying to save a workbook to SAC, I am getting the below error. Any idea what could have attributed to this?

Thanks,

Varun Raina

[ps_sapbi]

Hello Steffen,

Good information !.

Consider a scenario where some of the customer would like to save on IT cost while implementing SAC ans AFO. We know SAC comes with embedded AFO but that would require SAC license.

We assume - AFO with Standard Edition will cost less compare to AFO Edition of SAC.

Question 1 : If user uses AFO standard edition the they would require BOBJ licence ...correct ?-

Question 2 :  If answer to Question 1 is yes -then storing workbook would have only one option i.e. storing on BI Platform - assuming customer does not have BOBJ or SAP BW in their landscape instead has SAP Datasphere.

Question 2 : What sort of additional license is require to store AFO on BI Platform - Having just AFO standard edition enable us to store workbook on BI platform.

 

Appreciate your insight here.

 

Thanks,

Prashant Shah.

[simadireddy]

Hi steffen.heine ,

Thank you for sharing this, we are on SAC 2023.16.1.

I am able to see the Analysis Workbook option in the filter dropdown:

 

The Enable SAC option is also not available on the setting screen:

However, when i try to save the Excel file i dont see the option - "Save a Workbook on the SAC Platform".

 

Please let me know what I am missing.

 

Regards,

Siddharth M

[shivakrishnaj]

Hi Siddharth,
This feature supports from AO 2.8 SP18 onwards
So make sure you have AO SP18 client version.

Thanks & Regards,
Shiva

[steffen_heine]

Hi Prashant,

1. BOBJ or BPC license. I think the license cost per user are higher than for a SAC user, but SAP Sales knows that best.


2. Storage on BOBJ, BW and SAC are supported for both editions. Or any Excel native storage. Datasphere as a storage is not planned.


3. You need to have a BI Platform running. Both AFO editions can store on BI Platform.

Regards,

Steffen

[steffen_heine]

Hi Varun,

looks like the SAC tenant has not wave 2023.10 or higher yet. From August 20 all SAC tenants should have finally received this update. Please create an incident if the issue still appears afterwards.

Regards,

Steffen

[steffen_heine]

Hi Siddharth,

please share also your AFO version. Looks like it is not SP18 yet. Would recommend to wait for SP19 to be released this week and try directly this version. The setting is also enabled by default when platform is Selectable or SAP Analytics Cloud in the Options. 

Regards,

Steffen

[torsten_wirth]

Hi, we are getting the following error when clicking on save in AO after selecting a folder in SAC:

[617f-761d-7a] Could not get a valid SAC connection for INA_SAC Test / Dev_<our_sac_tenant>.eu10.hcs.cloud.sap_WB-01

The funny thing is that everything before is working which means I can explore the SAC folder structure in AO and getting the connection is also done before for getting access to BW.

How does this technically work? As far as I know AO does not support INA which is used for communication SAC <> BW instead AO is using BICS. At the point of saving INA is used?

Furthermore why there is not valid connection when data is accessed via SAC before and folder tree on SAC can be explored without problems.

Edit: That's a bug with SP18 it's not working for me. With SP19P1 it is working but found another bug after transport on SAC the workbook is visible via AO search but not in folder structure.

[vijaysureyen]

Hi Steffen,

 

I have followed your steps and now I'm able to Save the AFO reports in SAC tenant. But when I migrate the AFO report from DEV to QA which I saved in SAC , I'm getting the same data it's not pointing towards QA .

Is it possible to move the AFO report in SAC from one tenant to another or any connection setup need for that??

 

 

Thanks

Vijaysureyen

[Lothar_Köder]

Hello Steffen,

We really appreciate this possibility! But we're wondering how an AO file can be updated within SAC tenant. A simple overwriting of an existing workbook fails:

Deleting the file in SAC and saving the workbook with the same name doesn't help due to the fact that the workbook gets a new ID then.

Could you maybe share how to replace/update the existing workbook, please? Or is this currently not possible and needs to be implemented in the future?

Many thanks in advance!

Best regards

Lothar

[steffen_heine]

Hi Lothar,

sorry for the late reply. Do you have update rights in the Analysis Workbook privilege in SAC? Could you try SP19 or 20. If it is still not working please open an incident and we will look into because it should work to overwrite the workbook.

Thanks,

Steffen

[steffen_heine]

Hi Vijay,

I created another blog: https://blogs.sap.com/2023/11/17/sap-analysis-for-microsoft-office-move-workbooks-from-bi-platform-t...

Maybe this helps. Currently you need to use the SystemTransportMap setting as we do not change the SAC URL during transport when using live connections.

Regards,

Steffen

[steffen_heine]

Hi Torsten, thanks for raising the issues. SP20 should have resolved them. Regards, Steffen

[nsatishreddy21]

Hi Steffen,

 

Followed your steps and was able to successfully save the Analysis Workbook in SAC, but it seems to get stored as content type FILE and when I try to filter for Analysis Workbooks, it shows empty list.

Saved in SAC Platform

 

Saved

 

Listed as FILE in SAC

 

FIlter for Analysis Workbook does not work.

 

I tried to use the Direct URL to open the file and it get's downloaded and opened up the AFO, but when I add prompts (variables) and their values to hyperlinks, it just goes to the SAC Folder and doesn't work.

 

 

[Lothar_Köder]

Hello Steffen,

Thanks for your reply!

We solved the problem in the meantime. The key is to open the file via analysis tab in AO from SAC platform. A changing and saving afterwards works fine then. When the file is opened via files area in SAC this isn't possible.

Best regards

Lothar

[Agata]

Hi @steffen_heine 

is that planned to enable storing of the workbook variants in SAC? 

Looking forward to hear from you.

Kind regards Agata Janeczek

[steffen_heine]

@ nsatishreddy21 Please use SP18 or higher. The type must be Analysis workbook then

@Agata For workbook variants we have a SAC dependency and this is currently not planned.