Product Information
SAP Analysis for Microsoft Office – Storage of workbooks
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.
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.
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
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.
Hi Prashant,
Regards,
Steffen
Hi Steffen Heine !
Nice feature, long waited for. Thanks!
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
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
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.
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/391d09af5ad6461184fd88d63b2514c0.html
Regards,
Steffen
Hi Steffen,
Thanks for your blog series on MS Integration. On this blog, Where can we find the setting "EnableSACasrespoitory"?
Thanks
Preeti
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
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
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
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
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
Hi Siddharth,
This feature supports from AO 2.8 SP18 onwards
So make sure you have AO SP18 client version.
Thanks & Regards,
Shiva
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.
Hi Torsten, thanks for raising the issues. SP20 should have resolved them. Regards, Steffen
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
Hi Vijay,
I created another blog: https://blogs.sap.com/2023/11/17/sap-analysis-for-microsoft-office-move-workbooks-from-bi-platform-to-sap-analytics-cloud/
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
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
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
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.