In our last blog we talked about simplified version of SAP Data migration process i.e. ETL with preload and postload validations.

Today, in this blog i will cover the most commonly used ETL tool for SAP data migration which is SAP Business Objects Data Services or it is commonly known as SAP BODS.

It is not possible to cover everything about tool but I will try to give overview about different functionalities technically and how they are utilized through the ETL process.

What’s in it for you?

This blog focuses on giving overview on SAP BODS as ETL tool, how we can utilize this tool to perform SAP data migration, different transformations etc.

Let’s get into the tool!

SAP BODS is vast ETL tool and consists of many components, listed below.

• Designer.
• Repository.
• Management Console
• Job Server.
• Engines.
• Access Server.
• Adapters.
• Real-time Services.
• Address Server.

Each of the above component have different role and vary in functionality, from the data migration perspective or ETL process we will focus on Designer, Repository and Management Console components of SAP BODS.

SAP BODS Designer-

Designer is user interface for SAP BODS where you actually create projects, jobs, workflows etc to deifne the ETL process for a data migration.

Layout/Sections-

Designer Image1

Refer Designer Image1, I have explained the different sections of it below-

1.Project Area – This is project area which will show current project on which user is working, you can access jobs, workflows, dataflows etc from here.

2.Workspace- This is basically your development area where you will create dataflows, transformations etc.

3.Local Object Library – Local object library will show all the projects, Jobs, Dataflows, datastores, transformations etc available in the local repositories.

4. Tool Pallete – Using this tool pallete, yo can add different objects to your project like dataflows, workflows, scripts etc

5.Menu bar- This has important options/features for quick access like execute job, validations etc

Designer Image2

Designer image2 shows various features available to access in local repo like projects, transformations, formats etc.

Above explained points are high level overview on different regions of designer.

Lets get into how ETL process is performed using the tool

Extraction:

• SAP BODS can be used to extract data from different data sources, they can be databases, applications etc.
• To extract data, you need to create datastore ( connection to source) by providing connection details
• Datastores are of different types – Application datastores, Database Stores, Adapter datastores etc,
  • Application datastores – which connects to external applications directly like SAP etc.
  • Database datastores – which connects to underline databases like SQL, Oracle etc
• Above 2 datastores are most commonly used
• You can also create file formats and use as a source like text files, excel files
• Creation of datastores, file formats can be done from local object library- Data store option in SAP BODS

Below link has details which data sources are supported. You can connect to data source in 2 ways generally-

1. Directly for supported applications and databases by providing necessary connection details like server name, user, password and so on
2. Using ODBC setup, you can create data source in ODBC and provide the necessary details within bods

https://help.sap.com/docs/SAP_DATA_SERVICES/e54136ab6a4a43e6a370265bf0a2d744/578fdc786d6d1014b3fc9283b0e91070.html?version=4.2.8

Transformation:

• Transformation of data in sap bods can be done using various transformations available across different categories – Platform, Data Integrator, Data Quality, Text Data Processing
• Platform – These are the most used transforms for some mandatory transformations like
  • Query transform to query data from input, mapping, filtering etc
  • Merge- to merge data from multiple similar sources
  • Case- Segeregate data into multiple outputs based on conditions
  • SQL- to execute sql queries inside bods and so on
• Data Integrator- These transforms mainly used for some complex transformations like comparison of datasets, history preserving, pivoting etc.
  • Table Comparison – Used for comparison of two datasets and generate opcodes based on it like insert, updated, delete etc.
  • Pivot – Rotate columns to rows
  • Reverse Pivot- Rotate rows to columns and so on
• Data Quality – These transforms are used for data quality and cleansing checks mainly.
  • Match Transform – Used to find similar records based on fuzzy logic
  • Address Cleanse –  Used to identify incorrect addresses and so on
• Transformation step is important to structure and map your data as per the target system rules and above different types of transforms are used to achieve the same
• Data set generated in transformation step gives you overall records for a object which would be transferred to validations.
• Transforms mentioned in this step can be accessed using Trnsformation section under local object library

Load :

• SAP BODS can be used to load data to target system through below different ways
  • Direct Load  – In this approach tool itself is being used to load data into target system by sending IDOCs to target SAP system
    • SAP Target system datastore can be used to import required idoc to transfer data.(see below image)
    • 
    • Validated data should be strutured as per IDOC structure and mapped accordingly i.e source structure should be mapped with its corresponding target structure
    • This will be used to generate idocs in the target system that can be processed within sap system
  • File Generation – In this approach, data files/ loadfiles are generated which are then loaded through LSMW (old ecc) or migration cockpit in S4 hana
  • Table Population – In S4 hana system, staging tables can be populated with your validated data in the structure defined by migration cockpit tool using sap bods

Repository in SAP BODS-

1. Repository in bods is important aspect of tool, this is basically which holds your objects like Projects, jobs, dataflows, tables etc.
2. Commonly used repositories are Local and Central
3. Local repositories are used for development purposes by individual bods developers
4. Central repository is used to handle multi user environment and code/ETL Projects maintenance and migration between different environments
5. Central repository provides features like checkout/checkin code, verion management etc.

Additional Functionalities of SAP BODS Tool –

Auditing – SAP BODS provides data auditing functionality which can be used to ensure your data is being transfered within dataflow correctly from source to target

Performance Tuning-

SAP BODS provides various ways of tuning your ETL jobs development and make the execution faster like pushdown sql technique etc.

I had came across very good blog for the same and sharing here for your reference. I will also post the detailed one from my experience in upcoming blogs.

https://blogs.sap.com/2014/03/15/quick-tips-for-job-performance-optimization-in-bods/

Error Handling and Recovery –

SAP BODS provides various ways of techniques to handle the errors in the tool like most commonly used is try/catch to handle exceptions.

I had came across very good blog for the same and sharing here for your reference. I will also post the detailed one from my experience in upcoming blogs.

https://blogs.sap.com/2014/09/04/error-handling-and-recovery-mechanisms-is-sap-data-services-42/

SAP BODS Management Console –

1. SAP BODS comes with the management console tool which is a web-based user interface to manage job execution, job scheduling, auto technical  documentation and some reporting etc.
2. Most commonly used feature of management console is Administratsor section which is used for job monitoring, execution and scheduling
3. Using administrator section, you can access your repositories respective of your environments and corresponding projects and jobs. It has 3 subsections mainly –
   1. Batch Job Status – To monitor jobs in execution and check error or trace logs
   2. Batch job configuration – This is used to access your jobs and gives you various actions to perform for the jobs like execute, schedule and export
   3. Reporsitory schedules- This shows you job scheduled in that repository, you can display/modify job schedules here
4. Every environment has its own management console like – DEV, QA, Prod

Code Migration/Project Migration to higher environments

1. Central repository can be used to move code from one environment to another
2. You have different check out and checkin options to maintain your job in central repo and also you can label the each version to identify at later time.
3. This task is mainly performed by admin team.
4. In higher environments developers/users do not have direct access to designer to execute their jobs, they have to use management console to run the jobs

We have come to the end of this blog, I have tried touch various functionalities and feature of sap bods etl tool and tried to explain them in nutshell here so this will definitely give you a good start to learn this tool.

I will be covering important topics around sap bods etl tool in my upcoming blogs so stay tuned!!

Please do provide your feedback which will help me in imporving my content and sharing more relevant knowledege on this community

Do follow and subscribe to learn and get insights in the space of Data Migration and Quality and their key topics. Please do share within your connections/networks.