[SAP HANA Academy] SAP HANA Cloud Integration for Data Services

In a series of 14 videos the SAP HANA Academy's  Tahir Hussain "Bob" Babar shows how to preform the most common SAP HANA Cloud Integration for Data Services tasks. SAP HANA Cloud Integration (HCI) is SAP's strategic integration platform for SAP Cloud customers. HCI provides out-of-the-box connectivity across cloud and on-premise solutions. Beneath the real-time process integration capabilities HCI also contains a data integration part that allows efficient and secure usage of extract, transform and load (ETL) tasks to move data between on-premise systems and the cloud.

Bob's 14 tutorial videos are linked below with accompanying synopses. Please watch the videos here or on the SAP HANA Academy's HCI-DS playlist.

HCI Data Services: Overview

To open the series Bob a provides quick chalkboard overview of how data services works within SAP HANA Cloud Integration. This series will show you how to install HCI-DS agents and how to build various datastores to connect to SAP ERP or BW system, flat files, a OData provider, AWS, and a weather service in WSDL format. In addition the series will teach you how to build tasks that will extract data from these various sources, transform it using HCI-DS functions and load it into a SAP HANA schema in the SAP HANA Cloud Platform.

Bob finishes this introductory video by walking through the basics of the seven HCI-DS tabs.

HCI Data Services: Agent Install

The second video in the series shows how to download, install and configure the data services agent on a Windows machine.

This video assumes you’ve already activated your HCI-DS account. Once logged in to HCI-DS, click the link to download agent package in the agents tab and download the 64 bit for Windows version from the service market place.

While following along with the simple download check the box next to specify port numbers to view the four port numbers that will be used for inter-process communication. For the agent to function we only need the HCP port open for outbound traffic. Leave all of the defaults while finishing the installation of the data services agent.

To configure the connectivity to HCI first log in with the administrator user. This is usually the SCN ID of your HCP account. You can allocate the administrator role to your HCP user from the administration tab of HCI-DS.

Next you will need an agent configure file. To find this go to the HCI-DS agents tab and select new agent. Name the agent and allocate it to an existing or new group. Download the configuration text file on the next screen and save it to your machine. Copy the configuration file and then create and save a new file that can be linked to the HCI-DS agent configuration page. 

Once you’ve uploaded the file select yes in the wizard to successfully start the agent. Refreshing the agents tab will display a green box that confirms the HCI-DS agent has been started and is properly connected.

HCI Data Services: SAP HCP HANA Datastore

Continuing the series, Bob shows how to create a datastore that connects to a SAP HANA schema in HCP. Access to the schema is secured through an access token generated via the HCP client console.

To create a HCP connection navigate to the HCI-DS datastores tab, click on the configuration option and enter the HCP administrator account name and schema ID. To get the access token, you must connect to the agent machine that stores your HCP client. In that machine open a command prompt window and set the proper proxies. Bob outlines the commands to enter that will eventually generate the access token. Paste the token into the datastores tab and save. Clicking connect will successfully connect to the HCP datastore.

Access to the various objects that will be created must be given to the granted role we are using with HCP. After logging into the SAP Web-based Development Workbench open a new SQL console and execute the line of code displayed below. This will generate a role name.

Next execute the SQL statement below using that role name. This will allow the user to insert, update and delete on the schema. Now no security ride issues will occur when using HCI-DS to load data into the SAP HANA Schema in HCP.

HCI Data Services: OData Datastore

Bob’s next video will take data from an OData provider and store it in a SAP HANA Schema in HCP. To build a new datastore click the add button in the datastores tab. Choose adapter as the type and OData as the adapter type. Set the depth to 2 so all the relationships can be expanded one level deep.

In a browser go to services.odata.org and click on the Read-Only Northwind Service link and change V3 in the URL to V2 as currently only OData version 2 is supported. 

Note you may need to change the proxy for the OData adaptor on your agent machine. If so, in the Stat menu go to SAP Data Services Agent and then click on configure agent. Choose to configure adaptors and set the adaptor type to OData. Bob’s agent is internal, so he adds wdf.sap.corp to his host, http, and https proxy in the additional Java Launcher text box.

Copy the modified Northwind Service URL and go into the datastores tab of HCI-DS. Build a new Datastore named OData with an OData adaptor type. Paste in the Northwind URL as the endpoint URL and click save to create the adaptor.

Click the test the connection button to confirm the connection is working. The ultimate test is to see if you can import an object. So click on the import objects option, choose Alphabetical_list_of_products and click import.  Now you have created an OData datastore, set the proxies to successfully connect and imported OData services from the provider.

HCI Data Services: OData to HCP Task

This tutorial video shows how to take data from an OData datastore and load it into a SAP HANA Schema in HCP.

First create a table in the SAP HANA Schema in HCP. Next build a project with a task that contains a data flow. This task's data flow will take data from the OData provider and store it in SAP HANA. Bob shows how to create an Extract, Transform, and Load dataflow that joins the data source to the target query.

Mapping the OData input to the SAP HANA output is done by dragging input column names to their corresponding output column names. A green box will appear if the OData to HCP task has been executed successfully. The view history provides a trace log, monitor log and error log that displays what was processed.

HCI Data Services: Flat File Datastore

The sixth video of the HCI-DS series shows how to configure the DS agent to extract data from a file that sits on a Windows client. First Bob has to configure a change to the directories of the SAP DS agent by adding a simple CSV file that contains employee names. After that Bob builds a new datastore with a file format group as the type and a specific root directory. Now that a link to the file directory has been established, the file formats need to be built.

In this tutorial Bob elects to create the file format from scratch. Bob chooses comma as his column delimiter, default as his newline style, none for his text qualifier, and marks so that the first row contains column names. After Bob specifies a pair of columns as integer and varchar(50) he has finished setting up the file format.

HCI Data Services: Flat File to HCP Task

Continuing from the previous video this tutorial shows how to use HCI-DS to load data from a flat file (CSV) on a Windows client to a SAP HANA schema on HCP. In HCI-DS create a new task in the projects tab with the previously loaded flat file as the data source.

Next Bob builds a new target in the SAP HANA Web-based Development Workbench using a script he has already written that creates a column table named FILE_EMPLOYEES. After selecting HANA_ON_HCP as the target datastore, Bob saves the task and defines the data flow.

Bob imports the newly created table as a the data flow’s target object. Bob uses EMPLOYEES.csv as the source file and then joins it to the target query. After auto mapping the columns by name the data flow is created, verified and executed.

HCI Data Services: MySQL to HCP Task

Bob shows how to use the HCI-DS platform to load data from a MySQL database on a Windows client into a SAP HANA Schema within HCP. Bob has built an ODBC user and connects to his MySQL schema. Bob creates a new datastore that has a MySQL database type and ODBC as the data source.

After importing the table, Bob creates a new task in the projects tab. Bob selects his source and target and then builds a MySQL table in the SAP Web-based Development Workbench. Bob imports the table as his target object in the data flow and then adds a source table before then joining it to the target query. Once mapping is completed and validation is successful Bob executes the task.

Bob further verifies the connection’s success by inserting a new value into one of the rows in his MySQL table. After re-executing the task Bob sees the new value displayed in the SAP Web-based Development Workbench.

HCI Data Services: WSDL Datastore

In the next HCI-DS tutorial video Bob demonstrates how to use HCI-DS to create a datastore for a WSDL Web Service that will provide current weather data based on an inputed zip code.

The XML for the WSDL can be viewed here: wsf.cdyne.com/WeatherWS/Weather.asmx?WSDL.

The input parameter will be a zip code. The output will contain the zip code's forecast, temperature, city, wind, pressure, etc.

Bob builds a new WSDL datastore with a SOAP Web Service as the type and the URL listed above as the path. Then he imports GetCityWeatherbyZIP as an object. Instead of columns WSDLs have a request schema and a reply schema. For this WSDL the zip code is the request schema and corresponding weather information is the reply schema.

HCI Data Services: WSDL to HCP Task

Continuing along from the previous video Bob shows how the weather data from the WSDL is outputted into a SAP HANA schema in HCP.

First Bob creates a new column table in the SAP Web-based Development Workbench called WSDL Weather by executing a SQL statement.

At the time of this tutorial’s creation HCI-DS doesn’t yet have a row generation transform that will output one row. So Bob needs to force HCI-DS to output one row by creating a table with just a single row. Bob creates a new flat file that contains 1 as the value in its first and only row in his Windows client. Now Bob creates a new datastore for the single rowed table and adds an integer column named ID.

Bob creates a new task with file format as the source and HANA_ON_HCP as the target. Then Bob starts building the data flow by importing the WSDL weather table and adding it as the target object.

Bob then imports the One_Row.csv table and joins it to the transform. Within the transform Bob selects the GetCityWeatherByZip WSDL as the output and enters a New York City zip code in the mapping.

Next Bob adds a Web Service transform to the data flow and joins it to the first transform. After choosing GetCityWeatherByZip as the output in the transform, Bob maps the pair of WSDLs together. So he has joined the source table that contains the zip code input parameter to the WSDL. Continuing on Bob joins the WSDL function call to an XML map and joins the XML map to the target query.

In the XML map Bob adds all of the reply columns (city, wind, temperature, etc.) from the input to the output. Finally in the target query Bob elects to auto map by name. Then Bob saves, validates and executes the task.

Bob verifies the connection by running a select on the WSDL table in the SAP Web-based Development workbench to see the current weather data for the SAP office in New York City.

HCI Data Services: ERP to HCP Task

In this tutorial video Bob shows how to use HCI-DS to extract data from an ERP provider, filter the data and then load it into a SAP HANA Schema in HCP.

First in the SAP HANA Web-based Development Workbench Bob creates a column table called ERP_Customers via a SQL statement. In HCI-DS Bob creates a new datastore with SAP Business Suite Applications as the type and uses an agent that exists on the same SAP system. After entering his personal credentials, client number and system number, Bob saves and then tests the connection of his new datastore.

Next Bob imports the table before creating a new task with an ERP source and a HANA_ON_HCP target. Then Bob imports the ERP_Customers table as the target object. When working with an ERP source you have three additional ABAP transformation options. Bob connects the source to the target query, maps the columns together and then elects to filter for where the region = NY. After validating and executing the data flow, Bob runs a select statement in the SAP HANA schema and sees all of the customers in New York.

HCI Data Services: Using Functions

In this video Bob highlights how to use some of the available functions within the query transform in a HCI-DS dataflow.

Bob replicates and renames the ERP task he built in the previous video. Bob wants to remove all of the trailing zeros in front of each customer number for all US-based customers. So in the target query of the replicated ERP task’s data flow Bob selects the Customer_ID in the output and navigates to the mapping transform details tab below. Bob elects to run a ltrim string function on the Customer_ID column with 0 as the trim_string.

Now after closing, validating and executing the ltrim modified replicated task, Bob confirms that the zeros have been removed from the Customer_ID by running a select statement on his ERP table in the SAP Web-based Development Workbench.

HCI Data Services: Sandbox to Production

In the series’ next video Bob demonstrates how to promote a task from the sandbox environment to the production environment so it can be scheduled. To promote a task first select it and then choose promote task under more actions.

After executing the task in the production environment Bob now has the option to schedule it. When building a new schedule you can determine the starting time and the frequency in which it will run.

The administration tab allows admins to create additional users with a developer, operator or administrator role. Notifications can be set so an email is sent when a task is executed successfully or fails.

HCI Data Services: Loading Data From AWS

Bob’s final video in the HCI-DS series shows how to load data from a Amazon Web Services file into the SAP HANA Cloud Platform with HCI-DS.

First Bob opens a specific port, 8080, on his AWS instance. Next Bob creates a new HCI-DS agent and lets that agent know where the folder containing his AWS data is located on his Windows machine. Bob lists this same file as the root directory in his new datastore to connect to the file.

After creating a new column table in the SAP Web-based Development Workbench, Bob begins to build a new task in the projects tab. In the target query of the task’s data flow Bob maps the source columns from the AWS text file to the corresponding columns in the HCP target table before executing the task.

Bob is able to verify that this task has successfully connected the AWS file to HCP by adding an additional row to his AWS text file on the Windows machine. Then after re-executing the task and re-run running the select statement on the SAP Web-based Development Workbench, Bob’s AWS table in HCP now has that additional row.

SAP HANA Academy over 900 free tutorial videos on using SAP HANA and SAP HANA Cloud Platform.

Source: scn.sap.com