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

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

      20 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

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Henry,

      there will be improvements for SP18 (May 2022) which will resolve the customer issue.

      You can reach out to Dhammika or myself for details, otherwise we will post the changes in May.

      Regards,

      Steffen

      Author's profile photo Raj Mohan Yandamuri
      Raj Mohan Yandamuri

      Hello Dhammika,

       

      This is a very informative blog!

       

      I do have a question about how we can adopt this in a system migration scenario.

      The premise is that when an existing BW system is migrated to a new DB/Cloud, the hostnames on the BW system is bound to change. This will affect all the existing AO workbooks for the entire user community.

      In such scenario, while System Replacement option perfectly works to connect existing workbooks to "new" systems, it would be too taxing to perform this activity (specifically if the user base is large and AO workbooks are widely used).

      Do we have a way to leverage this System Replacement option on a mass scale? Similar to the way we can maintain SAP GUI logon pad connections via a configuration file?

      Looking forward to hear your views on this!

       

      Thank you!

      Best Regards,

      Raj Mohan Y

       

       

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

      Hi Raj,

      Thanks for asking about mass replacement. For this specific scenario, the following would work without any replacement for all local workbooks;

      1. In SAP Logon, make sure you keep the same old name, but update the underlying system information pointing to the new system  (e.g. change  message server, group etc. as necessary)
      2. Then in AO Technical Configuration, make sure you set
      3. Then all the workbooks created with the old connection would work and once saved after refresh, the workbook will have the new system information persisted in it.

       

      For more details about AO connections, please refer to SAP Note  https://launchpad.support.sap.com/#/notes/3090746

      Thanks & Best Regards,
      Dhammika

      Author's profile photo Raj Mohan Yandamuri
      Raj Mohan Yandamuri

      Hello Dhammika,

       

      This is a very insightful and informative post!

      I do a have a question about how we can adopt the System replacement option on a mass scale.

      The premise is that, when a BW system is migrated to a new DB/Cloud, the existing locally stored AO Workbooks can no longer connect to the "new" system and performing a 'Refresh' of workbook will fail. In this scenario we can leverage the System Replacement option to connect to the "new" system.

      However, if the User base is large and there are large number of workbooks involved, using this option would be too taxing. In such scenario, do we have a way to leverage the System Replacement option on a mass scale? Perhaps similar to updating the SAP GUI Logon pad via a configuration xml file.

      Looking forward to hear your views on this.

       

      Thank You!

      Best Regards,
      Raj Mohan Y

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Raj,

      there is no mass replacement planned within AO directly.

      Regards,

      Steffen

      Author's profile photo Agata Janeczek
      Agata Janeczek

      Hello Dhammika Gunasekara Ratnayaka Mudiyanselage,

      nice article. All is clear and from the connections available switches I can see that BW to BIP switch is possible, but in my case:

      1. the "Replace system" option is available also without logging off - is that correct and should be used or obligatory request to log off before:
        Replace%20system%20option
      2. I am opening workbook stored on BI Platform with direct connection to BW and can't change the connection to BI Platform - am I missing any setting? That was available in version 2.8.12 that we are now upgrading to 2.8.17:
        BO%20missing
      3. Regarding the above also tried without logging off and thought to maybe see the BIP connections in the existing connections but the result is in both cases similar:
        Existing%20connections
        I will appreciate your support as got completely confused.

        Kind regards Agata

      Author's profile photo Marc Schweizer
      Marc Schweizer

      Hello Together,

      i would also be interested in a solution for the desciption of Agata.

      best regards

      Marc

      Author's profile photo Steffen Heine
      Steffen Heine

      This issue should be resolved in the latest SP.

      Author's profile photo Xin Lu
      Xin Lu

      We have same issue even BW has the latest SP28, and Analysis for Office ver. 2.8 SP18. We don't have problem in AO 2.8.10 as we can select replacement system as BIP. We will appreciate your support as this got us completely confused.

      Author's profile photo Ismael Heinen
      Ismael Heinen

      Hi Xin Lu,
      The latest AO revision where this issue is already solved is AO 2.8.19. The correction is documented in KBA 3339089.

      Author's profile photo Puneet Chawla
      Puneet Chawla

      Thanks for the great blog currently we are on Analysis for office BOBJ and planning to move on analysis for office SAC , the question is will the existing AFO report run fine after change in connection from BOBJ to SAC as the AFO will point to SAC model going forward and HANA DB as the backend source.

      Or the AFO reports will need to be built fresh from scracth as this is a change from one system type to another

      Please suggest

      Author's profile photo Steffen Heine
      Steffen Heine

      Hi Puneet,

      AFO does not use SAC live models. It queries directly the HANA DB. Therefore the switch of the AFO editions should be no problem.

      Regards

      Steffen

      Author's profile photo Puneet Chawla
      Puneet Chawla

      Thanks for the response Steffen, given the case our client is using AFO BOBJ edition with HANA DB On prem as source and want to replace it with AFO SAC edition with HANA Cloud as source going forward. How do you think this can be achieved if AFO does not use SAC live models