Technical Articles
Integrate it! – SAP BW. Extract data from remote MS SQL Server. Step by step guide
Prerequisites.
You have SAP BW system on HANA database running on Linux OS (for example, on RHEL 7.x)
and remote MS SQL Database running on Windows Server OS as a source system. For testing purpose you want to know more about Extract, Transform, Load (ETL) (for example abstract sales data) from MS SQL Server into SAP BW InfoProvider (for example in Standart DataStore Object, DSO for short)
Quote.
“The increased scale and complexity of SAP and non-SAP systems challenges require that we look for
ways to simplify the system-integration requirements”
This quote about a series of blog-posts (“Integrate it…”) about integration between different SAP and non-SAP systems
Another posts from this series (The list is being updated.)
Integrate it! – SAP and MES integration. Step-by-step guide
https://blogs.sap.com/2020/04/16/integrate-it-sap-and-mes-integration.-step-by-step-guide/
Integrate it! – Qlick View/Sense and SAP integration. Step-by-step guide
https://blogs.sap.com/2020/03/24/integrate-it-qlick-view-sense-and-sap-integration.-step-by-step-guide/
Resolution
Step 1. Install the Microsoft ODBC driver for SQL Server on SAP Application Server (Linux machine)
Install ODBC-driver:
#RedHat Enterprise Server 7curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repoexitsudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflictssudo ACCEPT_EULA=Y yum install msodbcsql17sudo ACCEPT_EULA=Y yum install mssql-toolsecho ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profileecho ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bashrcsource ~/.bashrc

Create necessary symlinks:
ln -s /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbc.so.1ln -s /usr/lib64/libodbcinst.so.2.0.0 /usr/lib64/libodbcinst.so.1
Step 2. Create SQL user
Open MS SQL Management Studio
Connect to Target DB
Security – Users – Create new user
Provide correct permissions for data reading and correct permissions to DB Schema (E.g. dbo or sap or something else)
Step 3. Next steps in SAP BW (briefly):
- Create a DB Connection to the source system.
- Create DataSource
-
Create InfoArea, InfoCatalog and InfoObjects
-
Create DSO
-
Create transformation between DataSource and the DSO
-
Create InfoPackage
-
Execute InfoPackage and load data from the Source system to the DSO
Step 4. Create a DB Connection to the source system
Step 4.1 Define Logical Systems (optional)
Transaction – BD54 (Define Logical Systems)
Add New Entries
Define Logical System for Remote MS SQL DB

N.B. This is an optional step. We can define logical system straightway in RSA1
Step 4.2. Create a Source System
Transaction – RSA1 (Modeling – DW Workbench)
Source System – DB Connect – Create…

DB Connect Parameters
Source System = Source System Logical System from Step
DB Connection = Source System
DBMS = MSS
Username / DB password = Username and password from Step 3
Conn.info = MSSQL_SERVER=[DB hostname/IP] MSSQL_DBNAME=[DB NAME] OBJECT_SOURCE=[DB Schema, for example dbo]

- Check
- Activate
Step 5. Extract data from MS SQL. Verification.
View number of Database Tables/Views


N.B. Sign of lightning near Technical Name means that you can’t extract data from these views/tables
Tip: For tables with lowercase names we can create UPPERCASE Views in MS SQL Management Studio

Step 6. Create DataSource.
Transaction – RSA1 (Modeling – DW Workbench) – DataSources
Step. 6.1. Create an Application Component.
Step. 6.2. Create DataSource inside Application Component.
Table/View – VIew in our SourceSystem

Proposal fields

Read Preview Data. 10000 records from View in Source System

- Check
- Activate
Step 7. Extract data from MS SQL. InfoArea / InfoObject Catalog.
A typical BW system has large numbers of different objects (DataSource, InfoCube and so on)
For example, we can organize all objects related to our abstract Sales data in one InfoArea (for example ZSALES)
objects related to Warehouse to another InfoArea (for example ZWAREHOUSE) and so on
Transaction – RSA1 (Modeling – DW Workbench)
So Create InfoArea for our “Sales” set of objects
Quote: “An InfoObject catalog is a collection of InfoObjects grouped according to application-specific criteria. There are two types of InfoObject catalogs: Characteristic and Key figure…For example, all the InfoObjects that are used for data analysis in the area of Sales and Distribution can be grouped together in one InfoObject catalog“
For example:
ZSALES_CHAR (Characteristic)
ZSALES_KF (Key Figure)
Step 8. Create InfoObjects corresponding to fields in MS SQL Server. BW Modeling Tools for Eclipse.

We must use the BW Modeling Tools for InfoObject maintenance.
Prerequisites for this step:
Eclipse 2018-12 and above with installed ABAP Development Tools (ADT)
Installation of BW Modeling Tools described here.
BW Modeling Tools Installation Guide:




Open Perspective – >BW Modelling
File -> New -> BW Project
Provide connection details and credentials to SAP BW system (Dev).
N.B. You must have SAP Logon with a propper connection to BW-system installed on the same machine.
Step 9. Extract data from MS SQL. Modeling in BW Modeling Tools.
For example, we have three tables at the remote MS SQL database.
Dimension-tables (SKU, Sales Points) and one Fact-table (with fields: price, wholesale_price, quantity, etc)
For each field in this tables in BW Modeling Tools create InfoObject (File – > New -> InfoObject)
InfoArea – InfoArea from Step #7
Descripton – For example DB.Scheme.Table.field in remote DB

- Check BW Object
- Activate BW Object
Step 10. Create a DSO.
Transaction – RSA1 (Modeling – DW Workbench)
Goto InfoProvider ->InfoArea (from Step 7)
Create DSO
Key Fields -> InfoObject Direct Input (InfoObject (id) from Step 9)
Data Fields -> InfoObject Direct Input (InfoObject from Step 9)
Links:
DSO – Step by Step (Part 1 of 2) : Creation, Extraction, Transformation
https://blogs.sap.com/2014/01/20/dso-step-by-step-part-1-of-2-creation-extraction-transformation/
DSO – Step by Step (Part 2 of 2) : Loading, Activation and Maintenance
https://blogs.sap.com/2014/02/18/dso-step-by-step-part-2-of-2-loading-activation-and-maintenance/
Step 11. Create Transformation DataSource -> DSO.
Source – DataSource from Step 6
Target – DSO from Step 10

- Check
- Activate
Step 12. Create InfoPackage
Transaction – RSA1 (Modeling – DW Workbench)- DataSourrse
Create InfoPackage

Execute InfoPackage.
Immediately or in the background via a scheduled batch job

Step 13. Monitor InfoPackage

Step-by-Step Analysis

Extraction, Transfer and Processing must be in status – “Everything OK”

Step 14. Create DTP
Create a Data Transfer Process (DTP for short)
Source – DataSource from Step 6
Target – DSO from Step 10

Execute DTP

Step 15. Check data in DSO
Transaction – RSA1 (Modeling – DW Workbench)- DataSourrse – InfoPackage
Display Data
Troubleshooting
Error:
Investigation:
Transaction SM21 (System log)
Resolution:
Step #1 missing or incorrect
Resolution
SAP Basis routine task with examples from real life was provided
Hi Roman,
This is a great article, very informative and helpful.
I have a Linux Server SLES 15 SP01 running, and I am trying to install SAP HANA on it.
I do not have the latest HANA DB version software to install.
Need some help installing the HANA DB on my Linux server box, can be done remotely, if this is something you can help me with, please let me know
My email is mrivaplata@yahoo.com
Thank You!
Good one
You also can define in DataSource the selection fields for infoPackage with Selection and Sel. Opts.