OL3: Data Extraction

Configure AWS Secrets Manager

You can get the secrets manager ARN from the output of the CDK output or CloudFormation output. Note the ARN key to search for the secrets entry to update in AWS console. aws-console.find.amazon-connect

Navigate to AWS Console -> All Services -> Security, Identity, & Compliance -> Secrets Manager and find your secrets for ODP using your ARN key copied earlier and click on your secret name. aws-console.find.amazon-connect

Scroll down and navigate to Click Retrieve Secret value aws-console.find.amazon-connect

Click edit to update the your SAP user ID and password for connecting to the backend SAP application and pulling data using OData/ODP

aws-console.find.amazon-connect

Note: If you are using the SAP WebGUI for this lab you can update the same userid and password in the Secrets Manager

Configure AWS Lambda:

Navigate to AWS Console -> All Services -> Compute -> LambdaFunctions -> Click on the Lambda function with description Sample Lambda function to for extracting from ODP

aws-console.find.amazon-connect

1.1 Create a Lambda test event for this exercise aws-console.find.amazon-connect

1.2 Scroll down and validate environment variables section in the same lambda function module. Note the datas3bucket value

aws-console.find.amazon-connect

1.3 Setup Amazon S3: With dataS3Bucket environment variables bucket-name in lambda, search Amazon S3 bucket service from the below path

AWS Console->All services-> Storage->S3->Input the previously copied bucket name in the search field and click the bucket which matches the bucket name aws-console.find.amazon-connect

1.4 Create a folder by visiting create folder option in Amazon S3

  • SalesHeader
  • SalesItem
  • Partner
  • Product

aws-console.find.amazon-connect

Validate DynamoDB and S3:

2.0 A DynamoDB table is also created to store the metadata for extraction. Navigate to AWS Console -> All services -> DynamoDB -> Tables -> Click on the DynamoDB table matching the CDK or cloud formation output.

aws-console.find.amazon-connect

Note: Make sure you have created the respective OData services before proceeding.

Extracted data will be saved to S3 Bucket. A DynamoDB table is also created to store the metadata for extracts. The lab setup sample Lambda function to demonstrate usage of the lambda layer

2.1 The next step is to run the lambda and populate data in these folders. The following environment variables need to be passed and the lambda function needs to be run individually for each service.

Note: Your Entityset might be named differently! Check in Gateway Client (TCODE /iwfnd/gw_client). You can also adjust the dataChunkSize to fetch more items at once!

Object odpServiceName odpEntitySetName dataS3Folder
Business Partner ZPARTNERS_SRV EntityOfSNWD_BPA Partner
Product ZPRODUCT_SRV EntityOfSEPM_IPRODUCT Product
Sales Order Header ZSALES_SRV EntityOfSEPM_ISO SalesHeader
Sales Order Item ZSALES_SRV EntityOfSEPM_ISOI SalesItem

Validate if you are using right SAP Application host name or IP address and port in the lambda environment variables. If your SAP system is in a public subnet use the public hostname. If in private subnet, use private hostname.

aws-console.find.amazon-connect

2.2 Make sure the lambda default Timeout (-> Basic settings) is set to 1 minute or below to allow faster troubleshooting in case of issues.

2.3 Execute the lambda by clicking the test event. Execute the test event 4 times, one for each service mentioned environment variables tables above

Note: Results will be displayed upon successful ODP extraction

aws-console.find.amazon-connect

2.4 Validate all the S3 folders if data is available in json format. Click on the file in your S3 folder and download the file to your local drive

aws-console.find.amazon-connect

Setup for AWS Analytics Services:

Create an Metadata using AWS Glue Crawler. Navigate to AWS Console All services -> Analytics -> AWS Glue:

3.1 Click on Crawler and Add Crawler

aws-console.find.amazon-connect

3.2 Name the Crawler as SAP Demo and next Choose S3 and select the bucket name where ODP data is stored

aws-console.find.amazon-connect

3.3 Next, Select NO for Add another data store -> and in the next screen create an IAM role for the Glue service (ex: sapdemo) as indicated below``

aws-console.find.amazon-connect

3.4 Choose Run on Demand in the next screen and click next and click add a database as name the database as sapdemo shown below

aws-console.find.amazon-connect

3.5 Click Next, Review configuration and finish.

3.6 Run Glue crawler by selecting the crawler and then Run Crawler option. 4 tables should be created upon completion.

After Crawler execution, tables added will change to 4., you can review the tables created under tables in Glue

aws-console.find.amazon-connect

3.6 Launch Amazon Athena. Navigate to AWS Console All services->Analytics->Athena->Get Started->from left side panel select the database Sapdemo

aws-console.find.amazon-connect

If you are using Athena for the first time, you will need to setup an S3 bucket. On the top panel, please click on the link to set up a query result location to store your query results. *See the SETTINGS option in the right most corner of the query editor menu lane

aws-console.find.amazon-connect

3.7 In the next step, click in preview table for each of the tables, the results will be like below.

aws-console.find.amazon-connect

3.8 In the query window, create the below three views. For the audience with a strong SQL background, you can left outer join with a single query with left outer jouns as well, The idea is to combine this data into a single view. Note: Execute each of the create view statements separately in Athena query window

Query 1 :


CREATE OR REPLACE VIEW sales AS 
SELECT
  "a"."salesorder" "salesorder1"
, "a"."transactioncurrency" "currency"
, "a"."netamountintransactioncurrency" "netamounth"
, "a"."salesorderlifecyclestatus"
, "a"."salesorderdeliverystatus"
, "a"."salesorderoverallstatus"
, "a"."salesorderpaymentterms"
, "a"."customeruuid"
, "date_parse" ("substr"("a"."lastchangeddatetime", 1, 8), '%Y%m%d') "OrderDate"
, b.*
FROM
  salesheader a
, salesitem b
WHERE ("a"."salesorderuuid" = "b"."salesorderuuid")

Query 2:


CREATE OR REPLACE VIEW saleswithproduct AS 
SELECT
  a.*
, "b"."product" "productid"
, "b"."producttype"
, "b"."productcategory"
FROM
  sales a
, product b
WHERE ("a"."productuuid" = "b"."productuuid")

Query 3 :


CREATE OR REPLACE VIEW saleswithproductpartner AS 
SELECT
  a.*
, "b"."bp_id"
, "b"."company_name"
FROM
  saleswithproduct a
, partner b
WHERE ("a"."customeruuid" = "b"."node_key")

Query preview of sales with product partner view should look like below:

aws-console.find.amazon-connect

Congratulations, You are now ready to visualize the data using Amazon QuickSight!