Skip to Content
Product Information
Author's profile photo Dhammika Gunasekara Ratnayaka Mudiyanselage

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;

🛈 Prerequisite: Workbook shall be offline (not refreshed)

  1. Analysis (ribbon) → Click DisplayComponents (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) → AnalysisCustomize AnalysisOptionsPlatform (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

Figure 4 – View: Source Systems, Grouped by their System Ids

Figure 5 – View: Source Systems by Individual Owner Artifact

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;

SYSTEM TYPE ON PLATFORM
BW System
BW System BIP
BW System  (Live) SAP Analytics Cloud
HANA
HANA BIP
HANA (Live) SAP Analytics Cloud
SAP Analytics Cloud
DWC
S/4HANA (Cloud BW)

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  Apply%20Button   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 

Source System Target System Source & Target Interchangeable
Type Platform Type Platform
BW System BW System
BW System BW System BIP TRUE
BW System BW System SAP Analytics Cloud TRUE
BW System BIP BW System SAP Analytics Cloud TRUE
BW System SAP Analytics Cloud BW System SAP Analytics Cloud
BW System BIP BW System BIP
BW System S/4HANA (Cloud BW)
BW System BIP S/4HANA (Cloud BW)
BW System SAP Analytics Cloud S/4HANA (Cloud BW)
HANA HANA
HANA HANA BIP TRUE
HANA HANA SAP Analytics Cloud TRUE
HANA BIP HANA SAP Analytics Cloud TRUE
HANA SAP Analytics Cloud HANA SAP Analytics Cloud
HANA BIP HANA BIP
SAP Analytics Cloud SAP Analytics Cloud
S/4HANA (Cloud BW) S/4HANA (Cloud BW)
S/4HANA (Cloud BW) S/4HANA (Cloud BW) SAP Analytics Cloud
S/4HANA (Cloud BW) SAP Analytics Cloud S/4HANA (Cloud BW) SAP Analytics Cloud
DWC DWC
DWC SAP Analytics Cloud DWC SAP Analytics Cloud

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).

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 Excel 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.

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Basavaraj Roddanavar
      Basavaraj Roddanavar

      Hi Dhammika,

      Very nice Blog!

      Best Regards,

      Basavaraj

      Author's profile photo Dhammika Gunasekara Ratnayaka Mudiyanselage
      Dhammika Gunasekara Ratnayaka Mudiyanselage
      Blog Post Author

      Thanks Basavaraj!!

      Any information missing? Please let me know.

      Author's profile photo Russell Gaunt
      Russell Gaunt

      Hi

      For AFO users licensed via SAC, can you explain the SAP solution for accessing on prem BW4 and s4 report if the internet or SAC are down?

      It appears that without successful validation of the SAC account the user cannot proceed to access on prem BW reporting or S4 reporting via AFO

      Thanks

      Russell

      Author's profile photo Henry Banks
      Henry Banks

      Hi Dhammika Gunasekara Ratnayaka Mudiyanselage

      Would you be in a position to suggest to Russell Gaunt if there's an AfO configuration option that stops it checking for SAC on startup?

      (i believe most of their AfO work is using connections defined in the local SAPGUI logon pad, SAC is predominantly just being used as a licensing mechanism)

      Could this behaviour be being caused by local AfO workbooks (that use SAC as the connection manager) hence the background check?

      Regards, H

      Author's profile photo Dhammika Gunasekara Ratnayaka Mudiyanselage
      Dhammika Gunasekara Ratnayaka Mudiyanselage
      Blog Post Author

      Hi Henry,

      I guess you are referring to AO SAC Edition, though there is no SAC at startup in AO standard edition. Please check it out.

      Best Regards,
      Dhammika

      Author's profile photo Henry Banks
      Henry Banks

      Hi Dhammika Gunasekara Ratnayaka Mudiyanselage

      Yes that's right, Russell Gaunt is licensed for “Analysis Office edition for SAP Analytics Cloud” through SAP Analytics Cloud.

      Is there a way to stop it looking for SAC on launch? after start up, the customer is mainly going AfO to BW directly.

      regards, H

      Author's profile photo Dhammika Gunasekara Ratnayaka Mudiyanselage
      Dhammika Gunasekara Ratnayaka Mudiyanselage
      Blog Post Author

      Hi Henry Banks ,

      I guess it is required for licensing purposes, but if user selects to remember his decision, then it will happen in the background even without user noticing it.

      However, if the customer has BW licenses, I guess he can simply use the standard edition (please check with licensing colleagues), all the SAME features, but no SAC logon at the beginning .

      Best regards,
      Dhammika