ETL integration options in SAP BW/4HANA explained
SAP’s flagship Data Warehousing application BW/4HANA has broken with the tradition of application level integration with external ‘Extract Transform and Load’ (ETL) tools. Integration with tools like SAP Data Services and HANA Smart Data Integration now takes place in the HANA layer, outside the BW/4HANA application. Jan van Ansem explains what the consequences might be if you are planning on using an ETL tool for your BW/4HANA interface.
SAP Data Services is often used by customers to transform and load data from non-SAP systems into the SAP Business Warehouse (BW or BW on HANA, BWoH) application. Data Services allows you to easily connect to a wide variety of data sources and design complex transformations in an easy way, using a graphical user interface for designing ETL processes. I have worked with Data Services for many years and developed a great appreciation for this ETL tool. I was even more happy when SAP introduced Smart Data Integration (SDI), which provides the same functionality as Data Services but is an integrated part of the HANA platform. It came as a bit of a shock to me when I realised that in BW/4HANA, integration with tools like Data Services and SDI has moved outside the BW application layer. ETL integration now takes place in the HANA native layer, which is an architectural improvement, but for existing Data Services users for BW this imposes both license implications and conversion challenges.
The good news: architectural improvements
SAP have delivered on their promise to simplify BW. Integration with ETL tools is easier, more aligned with Enterprise Data Warehouse standards, and far more flexible than it was when integration took place through PSA tables. This is mainly achieved by removing the PSA layer from the BW/4HANA application and using native HANA tables instead.
HANA tables have clear benefits over PSA:
- HANA tables can be used by any application, where PSA tables can only be used by the BW application
- Connectivity to HANA tables is managed through the HANA platform, providing many different mechanisms to load and update tables, whereas PSA tables can only be loaded through the available options in the BW application, of which there are far fewer.
- PSA tables are a BW application specific feature and housekeeping on them is often overlooked. The move to HANA tables means the architecture is more aligned with general data warehouse architectures and management of HANA tables is much easier than managing PSA tables.
The trend to move from Enterprise Data Warehouse (EDW) to Big Data Warehouse (BDW) means that the integration of data from a wide variety of sources becomes more important. The integration of the traditional back-end systems for EDW (S/4HANA, and other SAP source systems) still takes place in BW/4HANA directly through the Operational Data Provisioning (ODP) framework. Most other sources can now easily be integrated in BW/4HANA through the HANA native layer. This is great news for the data warehouse architects and engineers, but possibly not such good news for those who pick up the bill, as I will show in the next paragraph.
The challenge: Licensing
With BW/4HANA, it is no longer possible to use external ETL tools to load directly into the BW/4HANA application layer. Integration with ETL tools takes place in the HANA native layer but creating target tables in the HANA layer directly is not permitted under the HANA runtime license for BW/4HANA. A HANA Enterprise license, which would allow you to create target tables in HANA native, would make your Data Warehouse significantly more expensive. So, what are the options? Let’s have a look at a few different scenarios.
Scenario 1: Big Data Warehouse with Smart Data Integration
If you are planning to grow your EDW (reporting on standard back-office applications) to a BDW (combining back-office data with social media, sensor data and other Big Data sources) then you are looking to combine the capabilities of BW/4HANA with HANA platform capabilities. In this scenario you’ll want a HANA Enterprise license. This allows you to use SDI for your ETL, you can create as many HANA native tables for your BW/4HANA inbound layer as you like and of course you have many other great features of the HANA platform at your disposal.
I expect this will become a more common scenario in the near future, when organisations realise the benefits of the tight integration between traditional EDW and the Big Data Warehouse platform.
Scenario 2: BW/4HANA with SAP Data Services as an ETL tool
Many customers are currently on BW or BW on HANA (BWoH) and use SAP Data Services as an ETL tool. When moving to BW/4HANA, the Data Services processes will need to be converted. Instead of loading to the BW application directly, Data Services will now load into HANA tables. Besides the fact that it will be a considerable effort to change all Data Services processes, there are license implications as well. A runtime license does not allow you to create HANA native tables directly, so customers are faced with the cost for an HANA enterprise license.
SAP have confirmed that they are working on a solution to overcome this licensing issue. More news about this is expected later this year. I can only hope that SAP will not only resolve the licensing issue but also come up with a conversion program to replace the BW targets in dataflows with HANA targets.
My advice for those customers who are now on BW or BWoH and are thinking of moving to BW/4HANA is to discuss their options with their SAP account manager.
Scenario 3: BW/4HANA with other ETL tools
If you are using a non-SAP ETL tool for loading non-SAP data to BW or BWoH and you are thinking of moving to BW/4HANA then you are in the same boat as Data Services users: You can no longer load into BW/4HANA directly, but you will have to load to a HANA native staging layer. The difference is that it is less likely that SAP will come up with a discounted licensing model to soften the blow. Again, speak to your SAP account manager to discuss your options.
The ETL integration options explained
The question how to use Data Services and SDI with BW/4HANA has frequently popped up on SCN and other discussion forums. The answers have not always been helpful. Different sources sometimes had contradicting information. I hope this blog has made clear that ETL tools no longer integrate with the BW/4HANA application directly. Integration takes place in the HANA native layer. From an architectural perspective this is an improvement, but in some cases, there are license implications. These licensing questions are best discussed with your SAP account manager. For any other questions about ETL and BW/4HANA please feel free to get in touch!
Well explained, Thanks for the update Van. I understand that, using SAP dataservices to extract data in to BW layer is not permitted going forward. is it also going to be applicable for SDI replication data source also? can you clarify on this note? Below document talks about the setting up a datasource to receive SDI replications into BW layer. if this is going to be replaced with HANA enterprise Extraction process?
https://assets.cdn.sap.com/sapcom/docs/2016/02/98df360a-617c-0010-82c7-eda71af511fa.pdf
Thanks
Sreekanth
Hi Sreekanth,
Thanks for your comments and question. The scenario you found in the 'How To' guide is still valid in BW/4HANA. I think the title of the How To guide is a bit misleading. It uses 'SDI' in its title, but when you study the guide, you'll notice that only SAP HANA SDA functionality is used.
SDA is still a supported data source in BW/4HANA.
People sometimes use SDI and SDA interchangeably but they are two different things, as I am sure you know:
SDA: Provides virtual access to remote databases
SDI: Provides ETL functionality, and can use virtual tables from SDA as a source or target.
In the 'How To' guide, the Twitter Adapter is a virtual source made available through the SDA framework, and accessible from BWoH and/or BW/4HANA.
No SDI functionality is used in the How To guide.
Hopefully that answers your question.
Jan.
Thanks Jan for the reply. I did read the document, it is not SDA virtual access to data from remote source. It is physical data replication into BW using SDI adapter, however there are no flowgraphs to used to do ETL, but it is kind of ETL to load the data into BW. So it may be the case that, only replication is possible and not the flowgraph, then the transformations can be done using BW transformations.
Thanks
Sreekanth
Hi Sreekanth,
Perhaps I haven't got the same understanding of the terminology... which would be fine if it was just that, but I suppose there could be license implications as well.
On SAP HANA there is the Data Provisioning framework which delivers Data Provisioning agents and the 'Data Provisioning Adapters' (DPA).
The way I understand it, is that you use DPA within SDA for creating virtual tables for remote systems.
The Smart Data Integration component delivers two functions:
- Flowgraphs (ETL)
- Replication Tasks (real-time replication)
Both SDI functions can be done based on local sources (within the same HANA system) or with a remote source and/or target, using the virtual tables from SDA.
I imagine (but am not sure) that just using the DPA with SDA requires a different license compared to using DPA with SDA and SDI (flowgraph or replication) - but I might be wrong... I don't get involved with licensing discussions.
It is also my understanding that there are no 'SDI Adapters' - as the Adapters are part of DPA and can be used by SDA (and then through SDA in SDI) but I have seen the term 'SDI adapter' in SAP documentation so it seems SAP is not entirely consistent in the terminology.
Anyway.. as you rightly pointed out, in the Twitter scenario in the 'How To' document there is no flowgraph involved, neither is there an SDI replication task. When you look at the screenshot of the datasource on page 13, there is mentioning of a HANA Smart Data Access connection type.
To me, this looks like the components used are DPA (for the Twitter adapter) and SDA (for the creation of the Data Source in BW).
So the good news is, this still works in BW/4HANA and, if I am right about licensing (please check with your account manager) in this scenario you don't need a license for SDI, only for SDA and the Twitter Adapter.
This is my understanding of what is covered by DPA, SDA and SDI but perhaps this is not entirely in line with SAP's official definition.... It would be great to see this properly explained in a SAP help or Wiki document instead of in comments on a blog 🙂
Thanks for your reply Sreekanth, much appreciated.
nicely explained thanks Van..I liked the point, there is no SDA adapter or SDI adapter, it is DPA adapter. It makes sense that, BW/4 HANA using DPA + SDA functionality to process the data in there.
Thanks
Sreekanth
I sometimes find that the account managers may not know all the details on SAP BW and HANA licensing. Please see this screenshot I got from an SAP conference.
Liked and shared.
Thx
Great blog Jan! Clarifies a lot.
One question: having a SDA type source system in B4 is still possible right? That would generate the virtual tables and views on the HANA side. Or did sap remove this option aswell?
I still see the option so I reckon if you have a remote SDI source, you could stil create a datasource on the BW side and load via a DTP. B4 would generate the views
Ronald, thanks for the feedback.
Source system type Smart Data Access (SDA) is indeed still available and I am a big fan of SDA. This works really well if you don't need to do complex transformations before the data enters the BW/4HANA application.
My blog is specifically about using ETL functionality before hitting the BW/4HANA application layer - as many customers currently do with for example Data Services. Some transformation scenarios are a lot easier to resolve in an ETL tool instead of a BW transformation.
In BW/4HANA this is still possible. What I was trying to achieve with the blog is to make solution designers and developers aware that they need to go through HANA native tables instead of PSA.
Hi Jan!
I'm a big fan of SDA too!
But in a license perspective: the usage of a SDA source system for a "BW data load" into "BW data flow" is fully compatible with REAB license?
Is this scenario the "federation scenario" described in SUR?
Thanks
Andrea
Jan,
Thanks for the blog!
You stated the following:
"Integration with ETL tools takes place in the HANA native layer but creating target tables in the HANA layer directly is not permitted under the HANA runtime license for BW/4HANA. A HANA Enterprise license, which would allow you to create target tables in HANA native, would make your Data Warehouse significantly more expensive ."
Why not create a Direct Update aDSO and use the underlying HANA tables as target tables?
Also, can we still create ABAP transparent tables in SE11 in BW/4HANA? If so, why not use it to create the HANA tables?
Thanks.
Regards,
Mel Calucin
Hi Mel,
Thanks for your question, that is a valid point you raise. The way I see it is that BW is an application and all dataloads should be handled through the BW application interfaces. When you load data directly in the generated tables from an ADSO definition, you will not see this data as load requests in your ADSO.
Although technically possible, this scenario is not a supported / recommended scenario by SAP.
Your second suggestion - to create ABAP transparent tables, is a suggestion I hadn't considered, that is a very creative solution.
For the customer I currently work for, this wouldn't work. The complete Acquisition Layer is to be build in HANA native (as all sources are non-SAP sources) and I wouldn't want to maintain all those table definitions in SE11. If you have just a few tables the maintenance becomes less of an obstacle though I suppose.
Strictly speaking, you would still be using a unsupported way to load your tables - when creating tables in SE11 you make use of the Netweaver application (although according to the documentation this doesn't exist anymore in BW/4HANA) - so you should load the tables through the Netweaver application. Basis administrators are not very keen to grant access to application tables directly, outside SAP authorisations - because it is not a supported scenario.
There are of course many ways to skin a cat and some solutions which are in a 'grey' area from a licensing/support perspective might well work in specific use cases. I like the creative solutions but I wouldn't feel comfortable recommending them in general, as a standard part of your Data Warehouse approach. Even when you use this as an exception I would recommend to check with SAP if they support such scenario.
Hi Jan,
Thanks for your comprehensive blog. Two comments:
Best regards,
Rudolf Hennecke
Development Manager
SAP BW/4HANA Data Warehousing
SAP SE
Great blog! Just one question, there was a discussion around allowing BODS to load/connect to BW application layer in later released of BW/4HANA. Has that been done or that won't be an option in the future as well.
Hi Arminder
SAP Data Services can now direct update data to SAP BW/4HANA 2.0 aDSO Inbound table with setting "Write Interface Enabled".
Before BW/4HANA 2.0 above was not possible, had to load data into HANA Tables then to BW.
https://blogs.sap.com/2019/02/05/recap-sap-bw4hana-2.0-webcast-bw4hana-asug-qa/
Regards, Carlos.
Great blog. Thanks for taking the time to explain this.
Question – what options are available to extract data from a SalesForce system into an on-prem BW4/HANA 2.0 system. In all the documentation I have come across, it refers to SuccessFactors as an example when talking about Cloud applications. Looking for an architecture recommendation specific to Salesforce and BW4/HANA integration.
Regards,
Hello Saurabh,
I'm probably dealing with the same problems you are, and I was wondering how you solved them.
Regards.