SAP Analysis for Microsoft Office – Replacement of data source connections

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>

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 (planned for Q2 2023) will describe how this relates to the storage of workbooks.