Data writeback from SAC to SAP BW4 HANA through Open Data Protocol (OData) Services
The purpose of this article is to explain the integration mechanism between two SAP platforms – SAP Analytics Cloud (SAC) and SAP BW/4 HANA (SAP Business Warehouse) through OData (Open Data Protocol) services that provides data writeback feature. This integration opens a plethora of opportunities for businesses to make informed decisions with advanced and flexible analytic capabilities.
Introduction to SAP Analytics Cloud and SAP BW/4 HANA:
SAP Analytics Cloud (SAC) is a cloud-based business intelligence and analytics platform developed by SAP. It is a software as a service (SaaS) tool that combines planning, reporting and predictive analytics into a single solution. SAC provides a user-friendly and interactive interface for data analysis, allowing users to visualize and explore data in real-time with the help of charts, tables, and graphs.
One of the key functionalities of SAP Analytics Cloud (SAC) is that it provides the possibility to build own planning models and applications natively within SAC. During project implementation, there will certainly be a need to export data in SAC models to SAP Business Warehouse which we call as SAP BW or SAP BW/4 HANA.
SAP BW is a data warehousing and analytics solution provided by SAP that enable organizations to store, access, and analyze large amounts of data from multiple sources. It also provides real-time data analysis and decision-making capabilities to organizations. SAP has provided data storage objects in BW system which physically stores data in transparent tables and is referred to as advanced data store object (ADSO)
What is OData Services?
OData (Open Data Protocol) services is a standard protocol for creating and consuming RESTful (Representational State Transfer) web services.
RESTful web services use HTTP methods such as GET, POST, PUT, and DELETE to perform operations on data.
SAP provides OData services for accessing and manipulating data in SAP systems using URL based service calls. It enables cloud-based and mobile solutions to access SAP data seamlessly using a uniform format.
The SAP NetWeaver Gateway acts as a mediator between various systems and applications. Through push mechanism, the data gets written from SAC to BW/4 HANA ADSO
The entire process of data flow from BW to SAC and data writeback from SAC to BW is illustrated in the below diagram
Why OData Services?
The reason to choose OData Services as data transfer mechanism is because of the following benefits
- Standardized Interface: OData services provide a simple and uniform way for accessing and manipulating data in SAP systems
- Integration: It enables broad integration across products between non-SAP and SAP applications easily
- Implementation flexibility: OData services can be implemented in various programming languages and can interact with different system components
- Data security: OData services in SAP have robust security mechanisms to ensure that only authorized users can access and manipulate data
- Metadata consistency: OData services follow a unified metadata format, ensuring that data consistency is maintained across different systems and applications
How to establish connection from SAC to SAP BW/4 HANA through OData Services?
The OData services connection works in such a way that, one connection can be used to create any number of export jobs in SAC which can writeback data to different target ADSOs. Below are the steps involved in establishing connection from SAC to SAP BW/4 HANA ADSO
- Decide the SAC planning model from where the data needs to be sent to BW/4 HANA ADSO
- Login to SAC, open the SAC planning data model and select “data management” workspace
- Create export job under “Export Jobs” section by selecting export data mechanism as “OData Services”
- The system will ask for the connection to be used while creating the export job. This connection would already be present in SAC created using the data service URL. If not available, a new connection needs to be created with the help of SAP basis team
- Now select the target ADSO by choosing the corresponding technical name, perform field mapping from SAC as source to ADSO table as target
- Save the export job and execute it in SAC. The data starts getting transferred to ADSO in BW/4 HANA
Design considerations for OData Services during data writeback
We need to keep in mind certain parameters from design perspective while using OData Services as data writeback from SAC to BW/4 HANA
- Only an administrator at SAC side can trigger the export jobs created through OData services
- Only write interface enabled ADSOs of type Standard Datastore Object are supported. Planning Mode ADSO is not supported
- Compounding characteristics in ADSO cannot be handled in any special way. All the superior characteristics, along with the compounding characteristic should be mapped in SAC
- Before data export, SAC always converts fiscal dimension to calendar dimension. Hence, fiscal period characteristic in ADSO is not exposed for export. You can map only 0CALMONTH but not 0FISCPER from SAC to BW ADSO
- Export of Account calculations in SAC model is currently not supported
- Measure should be defined in ADSO by only these types of fields: CURR, QUAN and FLTP or a Key Figure
- Dimension should be defined in ADSO by types of fields: CHAR, CUKY, UNIT, NUMC, DATS, TIMS and LANG or a Characteristic. All other data types will be ignored and not exposed for export in the OData service
Overall, OData services support quick and easy data transfer from SAC to BW for data writeback. It doesn’t require separate BW dataflows to be established, thereby reducing implementation efforts. There is no data loss with this transfer mechanism and all data present in SAC model gets inserted successfully to BW ADSOs without the need to separately activate ADSO requests. The data writeback process can also be easily automated by scheduling the export jobs in SAC thereby reducing any manual interventions.
Thus, data writeback from SAC to SAP BW/4 HANA is a powerful tool for organizations looking to unify their data and gain deeper insights. By leveraging the cloud-based analytic capabilities of SAC, users can easily create engaging visualizations and dashboards, while simultaneously tapping the potential of robust data modeling and analytic capabilities of SAP BW/4 HANA. This enables users to analyze complex data sets quickly and make data-driven decisions. Users can also act on insights in real-time, increasing their ability to stay agile and responsive to business needs. Ultimately, data write back is an important feature to help organizations improve their data-driven decision-making capabilities and stay competitive in today’s fast-paced business environment.
Been using this for over a year now and can say it works very well to get the data into our BW environment.
The only frustrating part is that once an export job is created it can't be modified or copied for easy reuse so it has to be rebuilt. As long as you document your steps in the process you will be fine. Overall performance is good and would be interesting to see if you could use the same trigger to start a Process Chain in BW to continue the processing.
Thanks for the article!
Hi Garth Thomson - As per my knowledge, the same trigger cannot be used to start process chain in BW. Both SAC export jobs and BW process chains needs to be scheduled separately.
Agree with Garth Thomson’s comments above
not being able to modify the export job or even to see what data was included in it is a real limitation.
I get round this with the following method
I have a second SAC model that contains the measures and dimensions that I want to send to BW from the original SAC model
to send data I run a data action with 2 steps
step 1 deletes all data from model 2
step 2 is a data copy step with parameters to control selection of time periods , version , measures etc
The Odata job is created in model 2 and is set to send the entire contents of model 2. This way I control what is sent by defining the data slice beforehand and I never need too recreate the odata export job
Hi Peter Warren - I agree...Thanks for sharing the details regarding creation of 2 SAC models to handle such scenarios.
There are open customer influence requests for
Please vote - it helps push these up the priority list.
Thanks for the great blog.
We are on 2023.8.3 (Server) and 2023.8.5 (Client) versions on the SAC tenant. Going to the 'Export Jobs' is now giving me only 2 options to Export data : 1. Export Model as File and 2. Export Data to SAP BPC.
Clicking on the 'i' icon on the Export jobs is no longer giving me option to select any 'Legacy' options which I remember included ODATA Serice, Export to S4HANA.
I am wondering if SAP has already planned to no longer provide 'OData Service' as an option to Export data? Is this option already obsolete / planned to be obsoleted ??
Any views on this would be extremely helpful.
PS: On SAC version 2023.2.17 (Client), I have seen the 'OData Service' option after enabling Legacy options from the 'i' icon in Export jobs.
There is a note on this , it also confused me whenthe option to export to odata disappeared after Q1 release
You now have to click on the Export Jobs information icon in the Data Management view to Enable Legacy Export
Thanks Peter for getting back.
Unfortunately, for me the option to Enable Legacy Export is not coming up at all when I try to create a new Export Job.
I don't have any previous Export Job configured either where I can check if the option is available for selection.
Hi Vishwa Anand,
Can you please let us know if export job connection is established for your SAC tenant. If it is not, then it could be one of the possible reasons for "Enable Legacy Export" button not appearing when you click on "i".