System Replacement – Analysis for Microsoft Office

What is System Replacement?

Any Excel workbook with SAP Analysis for Microsoft Office content such as a crosstab from a Data Source has information about the particular backend system that Data Source belongs to. There are scenarios that the underlying system information has to be replaced, so that the workbook then targets other systems which have the same (or identical) Data Source(s).

Requirement: A workbook created in development environment using the Data Sources from Dev system needs to be refreshed against QA or Productive backend systems.

Terminology:

• Source System: A backend system that is already existing in the workbook
• Target System:  A backend system to replace one or many source systems

Open System Replacement Dialog in SAP Analysis for Microsoft Office

There are two ways to trigger System Replacement Dialog in SAP Analysis for Microsoft Office, given that a valid workbook is open in Excel;

1. Analysis (ribbon) → Click Display → Components (tab in Analysis Task Pane) → Context Menu on Workbook name → Replace System…

   Figure 1 – Trigger System Replacement Dialog from Analysis Task Pane

2. File (Excel backstage) → Analysis → Customize Analysis → Options → Platform (tab in Options dialog) → Replace System (button)
   

   Figure 2 – Trigger Options Dialog in Excel Backstage

   Figure 3 – Replace System in Options Dialog

Views in System Replacement Dialog

There are 3 main views in System Replacement Dialog (from SAP Analysis for Microsoft Office version 2.8 SP 16), namely;

1. Workbook (Replacements) View
   Displays all source systems from the workbook and their (selected) target systems
2. System Landscape View
   Shows all backend systems available in local / remote landscapes (to select as a target system)
3. System Property (edit) View
   UI page to edit the properties of the selected target system

Workbook (Replacements) View

This is the very first view that comes up when the dialog is shown. Here you can see information of all systems available in the workbook, grouped either by system id or by individual owner artifact. These views are different (logical) representations of the same system information available in the workbook.

Each system information item existing in this view has a type, as described in the table below.

Type	Description
	System (multiple) information grouped by system id
	Data source, which is from a particular backend system
	Planning object from a particular backend system
	An individual backend system

Grouping and Filtering

• System Id: An identifier for a backend system and unique within the landscape. If two system information instances have the same system id, then both of them are pointing to the same backend system
• Owner Artifact: Each system information instance is owned by either a data source or a planning object

Show Additional System Details

Simply by clicking on each system information item or on its expand button , the additional system details can be seen (when expanded ). Those are the other properties that are required for a connection to the particular backend system to be established in SAP Analysis for Microsoft Office.

Figure 6 – Expand details of a system information item

NOTE: Not all system information item has those additional properties. In that case, expand button is hidden.

Filtering by System Type or Owner

There, the system information items can be further filtered either by system type (All available types in the workbook such as BW, HANA, SAP Analytics Cloud etc.) or by owner artifact type (Data Source or Planning Artifacts), depending on the grouping that you have selected.

Figure 7 – Types of available filtering of System Information Items

However, by default only the following types of source (from the workbook) and target systems (in landscape view)  are displayed;

Select a Replacement (Target)

On the workbook view, when a system information item is selected, a group of buttons appears so that you can edit / select a replacement for the selected source system.

•    Add / select a (target system) replacement from landscape
•    Remove already selected target (replacement) system
•    Edit the already selected target system or a copy of source

Once the Add button is clicked, it navigates to the landscapes view.

Figure 8 – System landscape view

In the landscapes view, you can select a target Landscape and then a System Provider under it. A landscape can be either local or remote  . E.g. SAP Logon landscape or HANA is local (thus a local provider) whereas SAP Analytics Cloud – Live connections landscape has remote providers (i.e. each SAP Analytics Cloud system added in SAP Analysis for Microsoft Office with Live connections enabled are displayed as a provider). Availability of target landscapes can be changed by the Admins by manipulating the system replacement rules (check this note)

Figure 9 – System Info Providers, both local and remote

Figure 10 – SAP Analytics Cloud Live Remote providers, i.e. Live connections enabled SAP Analytics Cloud systems

For each provider under a remote landscape, it is required to logon    to that particular system once, if it has not been logged-on to that remote provider already. For providers that have been already logged-on, the Logon button is disabled.

For example, if you need to select a SAP Analytics Cloud system itself as the target, you have to select SAP Analytics Cloud landscape. But if you need a BW Live connection under a particular SAP Analytics Cloud system, then you have to first select SAP Analytics Cloud – Live Connection landscape and the particular SAP Analytics Cloud System as System Provider  (a.k.a. the live connections provider).

In addition to the remote and local connections, all the Existing Connections    are also displayed as a (virtual) local landscape. No BIP systems are displayed under existing connections as they cannot be used as a target backend system for a data source.

You can select a single target system from the grid at the bottom of the window. Once the target system is selected, similar to the Workbook (first) view, here also you can see the additional details available in the target system.

Figure 11 – Selected target system with details

Once a matching target system is selected from the grid, the     is enabled, only if it is a matching target to the source system from the workbook view, as per the following table and system replacement rules described here.

Default Rules for Allowed Replacements 

NOTE: Though it is not required or recommended for most business use-cases, default rules can be edited via SAP Analysis for Microsoft Office private Admin (type=“String” configurationLevel=“PerMachine”) configuration setting called SystemReplacementRules (not published).

This is a complex setting, i.e. the value (xml) shall be included within a   <![CDATA[ …  ]] block. Not only allowed replacements, but also allowed source systems, allowed targets and properties displayed on the UI for each system type can be adjusted using this setting (xml).

From Analysis for Microsoft Office version 2.8 SP 18, instead of the XML content itself, a path of such an XML file on a file server or an open (no logon needed) http end-point can be set as the value of SystemReplacementRules setting. Again, when changing the value directly in configuration file  %programdata%\SAP\Cof\Ao_app.config  the aforesaid  CDATA  block surrounding the value is required (but not via settings UI). Once set, Analysis for Microsoft Office will parse and load the rules / UI / behavior according to the content of the specified XML file, instead of the standard behavior and look.

Once Apply button is clicked, it automatically navigates back to the workbook view where now you can see the replacement selected in (previous) landscape view.

Figure 12 – A replacement has been assigned. KXX → BXX

Also it is possible to copy an existing source system and paste it as a target (again, only when such a replacement is allowed by rules)

Figure 13 – Select a replacement by copy / paste

Edit Target System Information

By clicking the   button in Workbooks view, you can edit either an already selected target system or a copy of the source system (as the target). Here, not all properties can be edited / changed freely or randomly, as some of the property values are read-only or when edited, it is validated and accepted only when valid for the given property.

Figure 14 – Edit System Information View

However, changing a system property to an invalid value will result in an unusable workbook (or cannot be refreshed afterwards). Nevertheless, the user has to make sure that the workbook is not broken and points to the correct backend system(s) after system replacement by carrying out extensive testing.

When there is at least one target (replacement) available, then the OK button in Workbook view gets enabled. When pressed, it replaces (in memory) the system information in the workbook to the new target systems.

If a target system has been selected in Grouped (by System Id) view, then that target system information is applied to all the source systems with the same System Id in the workbook, unless there is an explicit (individual) target system assignment in the Owner Artifact view.  I.e. the target systems selected in Owner Artifacts view has the priority over the target systems selected via Grouped by System Id view. Finally, user has to save the workbook in Excel for the replaced system(s) to be persisted permanently.

Summary

SAP Analysis for Microsoft Office System Replacement plays an important role in re-targeting a workbook created against one backend system (e.g. Dev) to another backend system (e.g. Prod), without loosing the crosstab data or data source information. In the System Replacement dialog, there are multiple source and target system types available in Workbook view and System Landscape view. In Workbook view, the source systems are shown in two groups, i.e. by System Id and Owner Artifact. Given that it is a match as per the System Replacement rules, the user can select a target (replacement) system from different local or remote landscapes. Also the user can further edit the target system (in System Property view) or even directly copy an existing source system as a target. Once the replacement is done, it is required to explicitly save the workbook (in Excel) for the newly replaced (target) system to be permanently persisted to the workbook.

Finally, please share your feedback or thoughts about the new System Replacement dialog in SAP Analysis for Microsoft Office in a comment and you can read more blogs / posts about the product here. Also you are welcome to post / answer any questions regarding SAP Analysis for Microsoft Office and follow my profile for similar content.