Exporting data from the SAC to a BW using the ODATA service

For most users of a BW who want to enter SAC planning, the question will arise as to how data can be loaded from the BW into the SAC and plan data from the SAC into the BW. If the SAC introduction is not to take place with a big bang, data must be transferred between the old and the new solution.

A typical scenario for the gradual introduction of SAC is to first concentrate on the implementation of various planning scenarios and to continue to leave reporting in BW. For this, the planning figures have to be written back into BW.

We looked at how well this works and where the pitfalls are. The good news: it works. But as always, there are a few "little" things to consider that make this path more difficult in certain scenarios.

In this blog, we describe our experiences with transferring plan figures recorded in SAC back into BW.

Overview

Pro

- An individual conversion logic can be implemented in BW with a BADI.
- The retraction can be planned regularly
- A pull out of BW is planned for the year 2022.
- The interface is sufficiently performant, 300,000 data records could be transferred in a few minutes
- Several key figures can also be transferred from the new data model
- Data records in the target ADSO are deleted based on the deletion criteria

Restrictions

- Compounded characteristics must be set up cleanly in the BADI, as the SAC usually does not provide them directly.
- The subsequent adjustment of a filter or a mapping of an ODATA export job is not possible. This must be created anew each time
- The export can only be started by an administrator. A trigger or a link to a button in the planning layout, for example after changing planning figures, is not possible.
- Minor adjustments are necessary for the characteristic for the version, if this is a key field in the target ADSO. (The leading "public." must be removed for public versions).
- A delta export must be developed "manually". However, deleted records can be deleted using the export method "Delete and replace" based on selected characteristics (see below and note 2998439 - No deletion of some target data)
- The mapping and the deletion criteria must be clearly noted when creating the data. These can no longer be viewed afterwards
- The export job is executed immediately when it is created; it is not possible to separate the development from the first test or transfer.

Note

- The data export is started immediately when the export job is created, otherwise we did not manage to create the job. Nevertheless, the job appears afterwards in the overview as not executed.
- Experience with the ODATA interface is helpful in setting up as well as in troubleshooting
- As a test scenario for successful export, one should create a reconciliation report in Excel with access to the data in BW and SAC for a classic data model. Unfortunately, this does not work for the new data model, where you have to reload the data into the SAC in order to perform a reconciliation.

Setting up an export job

An O-Data service must be created on the BW page (see blog https://www.zpartner.eu/sap-analytics-cloud-sac-planning-write-back-to-sap-bw-with-odata/).

Setting up export jobs on the SAC page is quick, as usual depending on the number of characteristics involved. But be careful, all filters have to be set in advance! If another data set is to be exported, a new export job must be set up. Errors must be expected here. If a characteristic is assigned incorrectly, the process must be carried out from the beginning.

These two limitations make the test as well as the correction of the connection challenging. Either one works without filters and always exports all data. Consequently, no adjustment or new creation of the export job would be necessary. Otherwise, a new test would have to be carried out after each adjustment of the filter.

Figure 1: Assignment of characteristics to target dimensions

As already mentioned, it is no longer possible to display the filter settings and the assignments of a created export job.

Activities on the BW side

In some cases, the imported data must be adjusted on the BW side. For this purpose, the badi RSBPC_ODATA_EXPORT in the extension spot RSBPC_ODATA can be used. In our case, this was necessary in the following cases:

- Compound characteristics - Here the parenthesis must be added, the interface does not recognise this automatically.
- Time characteristics - No automatic derivation takes place here if different time characteristics occur in the target aDSO (e.g. 0CALYEAR next to 0CALMONTH).
  It may be necessary to create the a-DSO accordingly to avoid this and use the navigation attributes of 0CALMONTH instead.
- Version - is exported from a public version, which should probably be the case most of the time. Then this text is "public. and must be removed in BW to avoid an error.
- The business-specific conversions have been made here
- Since calculated key figures cannot be exported, they have been calculated here.

Delta - Procedure

When creating the export job, it is possible to determine after which characteristic the data in the target ADSO is to be deleted in advance. This ensures that entries deleted in the SAC are also deleted in the target ADSO if the data are transferred several times. A clean concept is necessary for this.

Figure 2: Selection of characteristics for deletion selection

If you want to transfer a real delta process and only the changed data, you have to make this possible yourself using a suitable data model. One possibility is to create an artificial delta in advance. This works by creating two additional models. In the first one, you save the status after the last transfer (after image), in the second one (delta data) the difference between the "after image" and the current status. Then make sure that the delta data has been exported cleanly before creating a new after image.

Trigger of the export

At the moment, the export can only be done by a user with administrator privileges. This user can execute the job immediately, schedule it once or periodically. In the case of periodic scheduling, there are no further restrictions or exceptions; this is carried out 24/7. Thus, at the moment, a maximum of one hourly repetition is possible for the timely transfer of data (see screenshot). Since in this case there is still no automatic delta transmission, the system is challenged.

The export cannot be started via a button on the SAC interface. A pull from BW is only announced for 2022.

Figure 3: Scheduling options of an export job

Test & adjustment of the data

Since for each filter adjustment or correction in the export interface, it has to be completely recreated, one should consider from the beginning how the successful export can be quickly tested. Either you directly import the data into the SAC and compare them there or you use the AfO plugin and compare the data pots on a suitable aggregated level in Excel. But beware: this is only done if you use the classic SAC model, as neither AO nor the SAC plugin for Excel currently support the new model:

https://help.sap.com/viewer/c637c9ff5d61457eb415ce161e38e57b/cloud/en-US/34f1ea12d1904164b342fb09abf21a0c.html?q=new%20model

Note 3085993 - Troubleshooting guide for SAC, add-in for MS Office

Tips

At the following points we had to adjust settings via the points from ZPARTNER's blog (see below) to make the export work:

- In the transaction /IWFND/MAINT_SERVICE, ensure that a system alias for RSBPC_ODATA_EXPORT_SRV is available (see note 2527329).
- In the O-Data connection, the URL must be specified with HTTPS if only the HTTPS connection is set up in the Cloud Connector. When testing the RSBPC_ODATA_EXPORT_SRV service, the link is used without SSL.
- The error message "Job failed while running a plugin." in the SAC refers to an error in the SAP gateway. This log can be viewed with TA/IWFND/ERROR_LOG.

One should keep in mind that the first export is already started when the export job is created. The number of transferred records is displayed, but still the newly created export job is marked as not yet executed.

Figure 4: Message of the first successful transfer after creation of the job, which is nevertheless marked as "Not yet executed".

Our tests were carried out with the following system status:

SAC 2021.14.10 (Client)

SAC 2021.14.7 (Server)

BW/4 SAP_BASIS 753 0005

BW/4 SAP_GWFND 753 0005

SAP - Notes

SAP partly describes these points in its notes:

- 2838883 – SAC-BW-Writeback – Part 1
- 2847623 – SAC-BW-Writeback – Part 2
- 2942754 – SAP-Analyitcs-Cloud-Writeback in BW 750 -> min. SP 18 necessary!
- 2913078 - Support of the "Delete and Replace" option
- 3026199 – Tips and Tricks of Analyzing BW Data Integration with SAC issues
- 2998439 - No deletion of some target data
- 2936269 - No memory on export
- 3011069 - Checkpoints: Export Import between BPC and SAC
- 1797736 – SAP Gateway Troubleshooting Guide
- 2527329 – No System Alias found for Service ‚<service name>‘ and user ‚<current user>‘

Conclusion

It is pleasing to see that the interface works. If the planned figures are not necessary "at the same time" as they are entered in the SAC in BW, the limitations mentioned should not be a major problem. However, the data model should not contain too many characteristics, as the mapping must be carried out more often. This limit depends on personal endurance. However, if you want to see the data in BW without too much delay directly after the capture, you will probably switch to another solution.