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.
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.
Scroll down and navigate to Click Retrieve Secret value
Click edit to update the your SAP user ID and password for connecting to the backend SAP application and pulling data using OData/ODP
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
1.1 Create a Lambda test event for this exercise
1.2 Scroll down and validate environment variables section in the same lambda function module. Note the datas3bucket value
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
1.4 Create a folder by visiting create folder option in Amazon S3
- SalesHeader
- SalesItem
- Partner
- Product
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.
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.
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
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
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
3.2 Name the Crawler as SAP Demo and next Choose S3 and select the bucket name where ODP data is stored
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``
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
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
3.6 Launch Amazon Athena. Navigate to AWS Console All services->Analytics->Athena->Get Started->from left side panel select the database Sapdemo
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
3.7 In the next step, click in preview table for each of the tables, the results will be like below.
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:
