Product Information
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)
- 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
- 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;
- Workbook (Replacements) View
Displays all source systems from the workbook and their (selected) target systems - System Landscape View
Shows all backend systems available in local / remote landscapes (to select as a target system) - System Property (edit) View
UI page to edit the properties of the selected target system
Workbook (Replacements) View
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
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
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
![]() 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 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).
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
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.
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.
Hi Dhammika,
Very nice Blog!
Best Regards,
Basavaraj
Thanks Basavaraj!!
Any information missing? Please let me know.
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
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
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
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
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
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
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
Hi Raj,
Thanks for asking about mass replacement. For this specific scenario, the following would work without any replacement for all local workbooks;
both to true (checked in AO settings UI)
For more details about AO connections, please refer to SAP Note https://launchpad.support.sap.com/#/notes/3090746
Thanks & Best Regards,
Dhammika
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
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:
I will appreciate your support as got completely confused.
Kind regards Agata