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
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):
- 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
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
- SAP Managed Tags:
3 Comments
