SAP Analysis for Microsoft Office - Replacement of data source connections

[steffen_heine]

The recent blog focused on the differences between the Standard and SAP Analytics Cloud edition for SAP Analysis for Microsoft Office. As more and more customers have SAP Analytics Cloud (SAC) purchased, a switch of the edition might be favorable. In addition to the installation the replacement of the data sources from BI platform connections to direct or SAC Live connections needs to be considered within the workbooks. SP16 contains enhancements for replacing data source connections. In this blog an example is shown how to exchange a BW-based data source from BI platform towards SAC Live connection. With SP17 a new setting SystemTransportMap was introduced which allows to do a re-pointing between SAP Analytics Cloud tenants (works also for Data Warehouse Cloud tenants). This helps to react on URL changes e.g., when workbooks based on Live connections or models need to be pointed from a test to a productive SAC tenant.

Replacement of data source connections

System replacement plays an important role in re-targeting a workbook without losing the crosstab data or data source information. In this example a workbook on the BI Platform is opened and not refreshed. Then the Replace System button in the Options of SAP Analysis for Microsoft Office is enabled.

Options - Replace System

On the left side the connections currently used in the workbook are shown. In the example the workbook is only containing one connection. On the right side the target connections need to be specified.

Replace system - Add replacement system

Therefore, SAP Analytics Cloud - Live Connections and the correct SAP Analytics Cloud tenant is chosen. After a successful logon the corresponding BW system connection can be selected.

Replace system - Select SAC Live Connection

After applying the new target connection is shown next to the current connection. When confirming all data sources in the workbook will be changed towards the target connection automatically. To store the changes the workbook needs to be saved.

Replace system - Finish

More details to other combinations of system replacement you can find in this blog.

Setting SystemTransportMap

In case the URL of an SAP Analytics Cloud or Data Warehouse Cloud (DWC) tenant changes e.g., from a test to a productive tenant, the setting SystemTransportMap can be used instead of replacing the system information for each workbook. The setting can contain a path to a local (see A below) or on a network share stored XML file. Also, the whole XML string can be maintained in the setting directly (see B below). The XML string allows one or many mappings between source and target systems. If there is no mapping indicated, then the system information stored in the workbook is used.

System Transport Mapping XML file

---

Example for mapping between SAC test and productive tenant:

XML file Mapping.xml:

<?xml version="1.0" encoding="UTF-8"?>
<SystemTransportMap UsePlatformSystem="false" Version="1.0">
<Transports>
<TransportInfo Source="https://sac-test-url.cloud/" Target="https://sac-prod-url.cloud"/>
</Transports>
</SystemTransportMap>

 

SystemTransportMap setting:

A) Point to file Mapping.xml in local folder:

  A.1) Entered in Analysis technical configuration:

  A.2) In Ao_app.config: <!--default of 'SystemTransportMap': value="" -->

<SystemTransportMap><![CDATA[C:\Mapping.xml]]></SystemTransportMap>

B) Whole xml string maintained directly in Ao_app.config:

<!--default of 'SystemTransportMap': value="" -->
<SystemTransportMap><![CDATA[<?xml version="1.0" encoding="UTF-8"?><SystemTransportMap UsePlatformSystem="false" Version="1.0"><Transports><TransportInfo Source="https://sac-test-url.cloud/" Target="https://sac-prod-url.cloud/"/></Transports></SystemTransportMap>]]></SystemTransportMap>

---

 

Setting the attribute UsePlatformSystem to true will change the behavior of configured transports to use the platform the workbook is opened from as the target for transports. More information in SAP Note 3348897.

In addition to SystemTransportMap, the setting EnforceSystemTransport prevents that when the XML mapping file is not accessible (due to network or other issues) any SAC or DWC data source is being refreshed. Also, an error popup is shown saying that the file is not accessible. Other data sources in the workbook such as BW or HANA are not affected. This ensures the consistency of data as only the productive target systems from the mapping file are used for all end-users.

If a workbook is successfully refreshed and the mapping file was used, the System Alias field in the Components tab shows a [Transported] label for the data source. Please be aware that the target system information from the mapping file is not persisted in the workbook.

System Alias in Properties

The next blog will describe how this relates to the storage of workbooks.

[former_member246849]

Nice blog explaining transport workflow for SAC and DWC in Analysis Office...

[JensBraun]

Great news Steffen, this will further simplify the move to SAP Analytics Cloud!

[zahly]

Thanks for a nice blog series - we are on the move from AO std to AO SAC edition!

One small thing that would help the switch is if we could set a default/pre-selected value in the Landscape dropdown for the Target System (e.g. SAC Production instead of BW). Do you know if this is possible?

[wounky]

Interesting to see that the DWC connection switch of the model is already supported in AfO, whereas in SAC there is no mapping available, and the model must be switched widget by widget, resulting in the loss of configuration. Are there any plans to introduce this feature in SAC/DWC as well?

[steffen_heine]

Hi Sebastian,

for AfO we had the system replacement functionality already and extended it to support the new data sources introduced in the past 2 years. I am sure SAC will also introduce this in the future. Please follow this influence item for updates: https://influence.sap.com/sap/ino/#/idea/229434

Regards,
Steffen

[steffen_heine]

Hi Yngvar,

no you cannot set a default/pre-selected system in the SAC Live Connection dropdown. But we will change the sorting order to ascending with SP18. So then you can at least rename the SAC system description accordingly to appear first by default.

Regards,
Steffen

[wounky]

Thanks, indeed, it also exists in SAC, just not for DWC 😉

[Agata]

Hello steffen.heine

very interesting blog and nice feature. I have two questions related.

1. If currently we have AFO workbooks stored on BO, what would be the recommendation when we change the connections to via SAC?


2. How about scheduling? BO enables scheduling. Are there plans to enable scheduling AFO workbooks containing the via SAC connections to be scheduled in SAC?

Looking forward to hear from you or anyone who already knows the recommendations or road maps. 😉

Agata

[steffen_heine]

Hi Agata,

with 2023.10 or QRC3 you can store workbooks also on SAC repo.

There is no plan currently to support scheduling on SAC for AFO workbooks. This will remain a limitation.

Regards,
Steffen

[jknoeckl]

Hello Steffen,

Thank you very much for the information.

We have a 2-tenant landscape (SAC) as well as a BW4H (2.0 SP14) system landscape.
So far we have deployed our AfO workbooks in a 2 system landscape with the BI Platform (BOE).

After the QRC3 2023 update of the SAC, we have now moved the AfO workbooks from the BOE to the SAC.

Regarding the topic of system mapping, I now have a question.

Is it planned on the part of SAP that the mapping of the systems will be solved differently in the future?
For us, both methods A) and B) mentioned in the blog are out of the question.
From our point of view, this must be ensured in the SAC transport (analogous to OLAP Connections in the BOE "world") (or as known from the BW backend "world").
Another approach (although not ideally positioned) would be to be able to maintain the system mapping directly in the AfO workbook (as an additional option). We are aware that this would have to be done per workbook.
From our point of view, however, compared to local/network xml files or adaptation of the AO .config file, this has the great advantage that nothing would have to be ensured on the user's installation of AfO.

Our current approach/work around is to provide the workbooks with the BW live connection (with SSO in our case) from the prod tenant with the connection to the prod BW during development on the development tenant of the SAC before the transport, and switch them back to the prod tenant on the test tenant after the transport. Has of course the disadvantage to change this before each transport and to put it back again. But for this we do not need to ensure any settings (local xml or customization config file of AfO) on the PC of the users.

Another question: Is it planned that the usage of the calls of Analysis Workbooks can be monitored in the SAC System Overview (analog stories, models)?

Many thanks and greetings
Joachim

[steffen_heine]

Hi Joachim,

sorry for the late reply.

SystemTransportMap is just an AO side solution because we have no better solution on SAC side yet. I think this will become more relevant and hopefully in 2024 we can achieve something with the SAC team. The workaround on your side if of course a lot of manual effort we should avoid.

I dont think that we get a monitoring on SAC side.

Regards,

Steffen