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.
- 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
|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 (Live)||SAP Analytics Cloud|
|HANA (Live)||SAP Analytics Cloud|
|SAP Analytics Cloud|
|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|
|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||SAP Analytics Cloud||TRUE|
|HANA||BIP||HANA||SAP Analytics Cloud||TRUE|
|HANA||SAP Analytics Cloud||HANA||SAP Analytics Cloud|
|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||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.
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.