Data Extraction and Transformation

Data Extraction and Transformation

Smart Data Integration - Data Transformation.

Flow Graphs

An operator which helps transform data from a remote source into SAP HANA Cloud either in batch or real-time mode. A flow graph performs a configurable series of data transformation operations, such as

  • Joining
  • Filtering
  • Cleansing
  • Masking
  • Aggregation.

  • Smart Data Integration SAP HANA Smart Data Integration is an ETL tool, which can be used to load data in either in batch or real-time modes for SAP HANA on premise or in the cloud by using pre-built and custom adapters.

  • Data Provisioning Agent The Data Provisioning Agent manages all SDI Adapters and connections to SAP HANA Cloud, and acts as the communication interface between SAP HANA Cloud and the Adapter.

  • Database Synonyms A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Try it out!

Start by creating a database synonym, this synonym will be used to access the Flow Graph target table FGT_DE_HE_CUSTOMERS.

Create a database Synonym

Links Business Application Studio database artifacts (tables) to the SAP HANA Cloud database schema.

  1. Start Command Palette, and choose Create SAP HANA Database Artifact option

    Create SAP HANA Database Artifact option

  2. Choose Synonym (hdbsynonym) as the artifact type and provide ETLSynonym as the artifact name and then choose Create.

  3. Click on the ellipsis (…) under Object Name.

  4. Click on the dropdown for services and choose the USER_CONN_ service by clicking on the checkbox next to it.

  5. After adding the connection, search for the table FGT_DE_HE_CUSTOMERS in the search column. Select the FGT_DE_HE_CUSTOMERS table which resides in the schema of your user and click Finish.

  6. Now deploy the ETLSynonym.hdbsynonym by clicking on the rocket button on the top right-hand side. Inspect the Terminal output for any errors.

Create Virtual Table to Azure

  1. Create a virtual table to access data residing in Microsoft SQL server which is hosted in Azure. Open Command Palette. Choose SAP HANA: Create SAP HANA Database Artifact option

  2. Choose Virtual Table (hdbvirtualtable) as the artifact type. Specify the artifact name : VT_CUSTOMERS

  3. Once virtual table editor opens, close it by selecting the close (x) button in the tab. (Select Save if prompted when closing the editor). ​

​

  1. Right click VT_CUSTOMERS file in src folder and select Open With…

    ​

  2. Select Text Editor.

    ​

  3. Replace the syntax with the code provided below and Click on the deploy button located next to VT_CUSTOMERS.hdbvirtualtable in SAP HANA PROJECTS

Code Snippet
1
VIRTUAL TABLE "{placeholder|userid}_NS::VT_CUSTOMERS" AT "RSSQLserver"."<NULL>"."<NULL>"."SDI_USER.AZ_CUSTOMERS"

Create a Flow Graph

Create a Flow Graph that extract customer detail by region and country in to target table FGT_DE_HE_CUSTOMERS extracting the VT_CUSTOMERS table from Azure into the SAP HANA Cloud tenant classical database schema .

  1. To do this, in SAP Business Application Studio, go to Command Palette.

    ​

  2. Choose SAP HANA: Create SAP HANA Database Artifact option.

  3. Choose Flow Graph (hdbflowgraph) as the artifact type and provide CustomerByRegionFlowGraph as the artifact name and then click Create.

  4. Once the editor loads, click on the + sign on the canvas.

  5. Select Data Source from the menu and place it on the canvas by clicking anywhere on the blank canvas.

  6. Click on the gear icon in the DataSource to configure the data source.

  7. Find and click on the HANA Object.

  8. Find and click on the Services drop down and ensure that Target Container Service is selected.

  9. In the search bar, type VT_CUSTOMERS and from the results, select the _NS::VT_CUSTOMERS and then click Finish.

  10. Click Apply.

  11. Click on the + sign and chose Projection.

  12. After selecting Projection, click anywhere on the canvas and the Projection artifact would appear. Connect the DataSource output to Projection IN port and click on the gear icon on Projection to configure the Projection.

  13. Find and click on the Filters symbol.

  14. Create a data Filter that will filter the data according to customer country and region. Click on Apply to finish configuring the Projection.

    Code Snippet
    1
    "CUSTOMER_REGION" = 'HE' AND "CUSTOMER_COUNTRY" = 'Germany'

Adding SAP HANA Cloud Data Target

  1. Click on the + sign and choose Data Target.

  2. Click on the canvas next to the Projection and Data Target will appear. Now connect the Projection out to Data Target input and click on the gear icon on Data Target to configure the Data Target.

  3. Choose the HANA Object.

  4. In the search box type synonym, select the FGT_DE_HE_CUSTOMERS target table that were created during the first Getting Started and Introduction module of this Experience Guide.

  5. Verify the Object Name and click Apply.

  6. Deploy the CustomerByRegionFlowGraph Flow Graph by clicking on the deployment button. Conform that there are no errors.

Execute the Flow Graph

  1. Find and click the Execute button on the Flow Graph canvas.

  2. Click OK if prompted.

  3. Navigate to the Tables section, and find the FGT_DE_HE_CUSTOMERS table created. Click on the Open Data to examine the Data in the table.

  4. Inspect the result set, 201 rows are found.

Congratulations!! Successfully used SDI Flow Graph to transform data in a SAP HANA Cloud table.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn