4

We want to build up a new Data Warehouse from scratch, based on SAP Tables. For the extract, transform and load (ETL) procedure, we want to use Microsoft SQL Server Integration Services. There are many vendors on the market like Theobald, CData etc, which provide a framework to access SAP data. But there is also a SAP .NET Connector 3.0 provided from SAP itself to access the data via RFC.

Do you have any experiences with these different solutions? According to the DWH concept we just want to access the tables and are focused on fast transfer (good transfer performance) of the SAP data.

Thank you for your help.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Ivo
  • 303
  • 2
  • 15
  • 1
    This may help: https://stackoverflow.com/questions/54067226/move-sql-server-database-data-to-sap-bw/54209247#54209247 – Hadi Aug 07 '19 at 14:42
  • It depends on what DB backend is behind your SAP system. Sometimes it is faster to connect them directly without SAP – Suncatcher Aug 08 '19 at 22:54
  • 1
    I have used Theobald to extract SAP data in past. It is good enough to extract data from SAP tables , query and cubes. It also supports hierarchical data but make sure you have good understanding whatever you are going to extract. As it is sometimes quite tricky to use correct filters – Ritika Aug 09 '19 at 06:18
  • @Hadi: We don't use SAP BW, we have a Data Warehouse in SQL Server as target. – Ivo Aug 26 '19 at 12:53
  • @Suncatcher: I don't want to bypass SAP security by access the data directly. – Ivo Aug 26 '19 at 12:58
  • @Ritika: We have been using Theobald since now. But we set up a new Data Warehouse and wanted to check if there are any alternatives with a possibly better performance. – Ivo Aug 26 '19 at 12:58
  • 1
    Apart from Theobald, I have used Alteryx to extract SAP data, but it was not very good in case of hierarchical data in SAP. But also considering that I used a very old version, may be newer versions of alteryx have better catch on SAP data. – Ritika Aug 27 '19 at 03:53
  • Our company AecorSoft also offers such SSIS/SAP integration product if you need an option to compare. – AecorSoft Aug 27 '19 at 13:27
  • @Ivo I am facing the same problem as you, I want to extract data from SAP to Postgres warehouse that I want to build. Did you arrive at any conclusion? Thanks – PrivateOmega Sep 16 '19 at 03:01
  • @Ritika How does theobald work? Does it directly connect to whatever database (HANA in linux or MSSQL in windows) and query the data from there? And also can it be used as a base for building a connector for an application that I am building which can work as CDC(Change data capture) or taking snapshot of data whenever I feel like? Thanks. – PrivateOmega Sep 16 '19 at 03:04
  • @PrivateOmega some reading for your information: https://stackoverflow.com/questions/56152541/extracting-data-from-sap-to-sql-server/56658748#56658748 – AecorSoft Sep 17 '19 at 12:39
  • @PrivateOmega I have just used it to connect it to SAP BW cubes and queries. So not sure about other functionalities. But I doubt it can connect to HANA in Linux or MSSQL in windows. You can go through its website or try downloading the free trial https://theobald-software.com/en/ – Ritika Sep 18 '19 at 04:53

3 Answers3

1

I'd prefer stick to the native SAP solutions. Here are the options:

  1. SAP SLT Very simple but efficient solution. Features are quite limited (no joins, no merging from multiple sources) but seems to be ideal for one-time transformation.
  2. SAP Data Services (SAP BODS) More sophisticated tool that allows building scenarios of ETL from any sources to any destinations, including complex transformations, scripts, etc.
  3. SAP MDM/MDG More conceptual solution which includes data quality, data governance, validation, de-duplication, etc. MDM and MDG are actually two different products but here we place them as single for the sake of simplicity. They are used presumably for instant replication process between SAP systems, but MDM is more versatile and can be used standalone, and for any data source.
  4. SAP PI The most sophisticated, feature-rich and complex solution which doesn't seem reasonable choice for 1-time transfer. More about differences.
  5. SAP Data Hub SAP solution for Big Data sources which seems redundant for your case.

It's all depends of the scope of your task (mirroring, replication or transformation), complexity of data, necessary transformations (simple or complex), periodicity (ad-hoc or instant), budget and others.

If you just need to move your DWH as is into MS SQL I suggest to use SLT as the most performance-efficient way. If you need more sophisticated transformations I suggest using SAP BODS. Other points seems not relevant for your task.

Suncatcher
  • 10,355
  • 10
  • 52
  • 90
  • I need to move the SAP data in the source tables to MS SQL for process them into the SQL Server Data Warehouse. There is no existing SAP DWH. As we want to execute the ETL task via Integration Services: Are any of those solution compatible with SSIS? – Ivo Aug 27 '19 at 07:06
  • All these tools are backend-agnostic they are kinda competitors to SSIS. SSIS is intended to direct DB-connection which will bypass native SAP security. – Suncatcher Aug 27 '19 at 15:53
  • @Suncatcher Is it possible for a user to build an ETL tool using a framework like Apache Kafka? And also can any of these be used as a base for building a connector for an application that I am building which can work as CDC(Change data capture) or taking snapshot of data whenever I feel like? Thanks. – PrivateOmega Sep 16 '19 at 03:05
  • [Kafka Adapter](https://blogs.sap.com/2018/08/26/apache-kafka-integration/) is available for PI (pt 4), so yes, it is possible. – Suncatcher Sep 16 '19 at 19:46
  • What concerns CDC, this functionality was introduced in SAP BODS (pt 2) [since version 4.2](https://blogs.sap.com/2015/08/03/new-feature-of-bods-42-and-bods-41/) for databases MSSQL, ASE, DB2 – Suncatcher Sep 16 '19 at 19:50
  • @Suncatcher Thanks. I will check it out. But is there any other way to connect to SAP other than using BODS? – PrivateOmega Sep 18 '19 at 05:22
0

You can also connect through Azure Data Factory or Synapse

https://learn.microsoft.com/en-us/azure/data-factory/connector-sap-table?tabs=data-factory

We are using SAP CDC linked service in pipelines on Synapse to get data from SAP into our lakehouse, that works quite well. Weve also used a direct export (incremental) from SAP SLT directly into our SQL database which worked without any trouble for years.

Its also possible through SSIS eg. through CData connector or OLEDB.

marritza
  • 22
  • 5
-1

Saptodb is very useful for data transfer from SAP to SQL data warehouse. You can transfer RFC functions by filtering. You can transfer sap tables. You can transfer xml files periodically by creating simple windows task schedulers. It doesn't need programming. it is free.

https://github.com/ferhatdemirci/saptodb

sosruko
  • 1
  • 1