EDUCAÇÃO E TECNOLOGIA

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 7

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

exit

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts

sudo ACCEPT_EULA=Y yum install msodbcsql17

sudo ACCEPT_EULA=Y yum install mssql-tools

echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile

echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bashrc

source ~/.bashrc

Create necessary symlinks:

ln -s /usr/lib64/libodbc.so.2.0.0 /usr/lib64/libodbc.so.1

ln -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):

  1.  Create a DB Connection to the source system.
  2. Create DataSource
  3. Create InfoArea, InfoCatalog and InfoObjects

  4. Create DSO

  5. Create transformation between DataSource and the DSO

  6. Create InfoPackage

  7. 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 – DSO from Step 10

Target – DataSource from Step 6

  •  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. Check data in DSO

Transaction – RSA1 (Modeling – DW Workbench)- DataSourrse – InfoPackage

Display Data

Resolution

SAP Basis routine task with examples from real life was provided