How to load data from S/4HANA into BW via SDA
I had the pleasure again to be part of the S/4HANA Mentor workshop and as always, it was a very interesting three days. Being a Business Intelligence guy, I am a especially interested in everything which has to do with embedded analytics in S/4, but also in the integration with existing platforms like Business Objects and last, but not least, Business Warehouse.
When discussing SAP Activate and Best practices, it was brought to my attention that there are a lot of best practices how to integrate S/4 with Business Objects and BW. You can find the best practices at the following location:
SAP Best Practices for analytics with SAP S/4HANA
One of the interesting integration scenario’s one might come across with is the ability to take a HANA view (or CDS view) and persist that in BW. Think about scenario’s where you might like to take the accounts payables or receivables of a certain moment and build that up from a historical perspective. As normal real time analytics would show you the open items at a certain key date, it will make sense to calculate snapshots per month to show open items in a trend.
In this blog post, I would like to show you how easy it is to go from a real time view of your data into a persistent way of looking at the data.
Connecting S/4 to BW
In short we want to take an existing HANA model (one of the 3000+ views) and use that as a datasource in BW.
We will be using a combination of SDA (smart data access), an open ODS view and an Advanced DSO to go from a virtual scenario into a persistent scenario
The open ODS view architecture:
The generation of an open ODS into an ADSO is something I’ll show you in the next paragraphs.
Creating the SDA connection
Creating a remote connection to your S/4 system is a piece of cake. Go into the HANA studio of the receiving BW system, go to provisioning and create a new connection
Be sure to select the correct driver, the IP address of the source system and log on with your system user.
If all is ok, you will get the following screen
Now the fun starts. Select your view. I suggest you look at this blog post to get an idea on how to select the view you’re looking for
SAP S/4HANA Embedded Analytics – A detailed Walkthrough (Part 1)
For my example I will be taking the S4_HANA_SDA_sap.erp.sfin.fi.ar/FAR_CUSTOMER_LINE_ITEMS source and add that as a virtual (remote table) by right clicking on the view.
After adding the virtual table you can do a quick SQL content query to the results from my source table
Connecting the view to BW
Moving along to my BW system to connect the remote table to an Open ODS view.
Go to your info area of choice, right mouse click and create open ODS view
As I want to keep my ADSO fairly aggregated, I only select a number of fields
We now have a virtual connection via BW to S/4!
From Open ODS to Advanced DSO
Persisting it only takes two more steps:
Press generate dataflow to create the data source
Press a second time to get your flow generated
And ready we are with our ADSO and data flow!
I am just going to make one small modification in the ADSO and transformation by adding today’s date as part of my key so that I truly have a snapshot per day
Load and activate the ADSO
We have data!
Let’s do a quick test in Analysis for Office
All done, we have snapshot AR data from S/4 per day!
Thank you for reading!
Great post (as usual) and even greater that you're mastering "Interoperability"
Grt and please keep up the good work!
Interoperability has always been my favorite topic 😉
great blog, quick question, the example you have taken is of embedded BW or its also possible in separate BW sever?
if its embedded, is that also possible in case of dedicated BW server ?
I am writing a proposal and trying to justify need of separate BW server rather than embedded BW.
Very interesting stuff Ronald!
Is there any way that a bw system not in HANA can be connected to S4 HANA.
Sure, extractors, CDS views, SLT, tons op options!
Nice blog, very clearly steps were defined.
Very good explanation. One question still : Do we have only SDA option to access S4Hana CDS views virtually? I am asking only for virtual access.
We are going to use cost center hierarchy of BW (need to persist hierarchy data in bw), and standard CDS views of S4hana to consolidate in the composite provider for a reporting requirement.
Please advise how do I activate and use Bw's component of cost center hiearchy to persisit in b4 hana in embedded s4 hana, to load cost center extractor and MD objects.
is it also possible to go the other way round? I’d like to access data from BW in S/4HANA. Should I use SDA for this specific use case?
How do we load data in BW on Non HANA DB from S4 HANA. I understand existing BW extractors will work, however, how can the data be loaded from custom CDS views.