Generate data insights by using AWS Mainframe Modernization and Amazon Q in QuickSight

Created by Shubham Roy (AWS), Roshna Razack (AWS), and Santosh Kumar Singh (AWS)

Environment: PoC or pilot	Technologies: Mainframe; Analytics; Migration; Modernization; Machine learning & AI	Workload: IBM
AWS services: AWS Lambda; AWS Mainframe Modernization; Amazon QuickSight; Amazon S3

Summary

If your organization is hosting business-critical data in a mainframe environment, gaining insights from that data is crucial for driving growth and innovation. By unlocking mainframe data, you can build faster, secure, and scalable business intelligence to accelerate data-driven decision-making, growth, and innovation in the Amazon Web Services (AWS) Cloud.

This pattern presents a solution for generating business insights and creating sharable narratives from mainframe data by using AWS Mainframe Modernization File Transfer with BMC and Amazon Q in QuickSight. Mainframe datasets are transferred to Amazon Simple Storage Service (Amazon S3) by using AWS Mainframe Modernization File Transfer with BMC. An AWS Lambda function formats and prepares mainframe data file for loading into Amazon QuickSight.

After the data is available in Amazon QuickSight, you can use natural language prompts with Amazon Q in QuickSight to create summaries of the data, ask questions, and generate data stories. You don't have to write SQL queries or learn a business intelligence (BI) tool.

Business context

This pattern presents a solution for mainframe data analytics and data insights use cases. Using the pattern, you build a visual dashboard for your company's data. To demonstrate the solution, this pattern uses a health care company that provides medical, dental, and vision plans to its members in the US. In this example, member demographics and plan information are stored in the mainframe datasets. The visual dashboard shows the following:

• Member distribution by region

• Member distribution by gender

• Member distribution by age

• Member distribution by plan type

• Members who have not completed preventive immunization

After you create the dashboard, you generate a data story that explains the insights from the previous analysis. The data story provides recommendations for increasing the number of members who have completed preventive immunizations.

Prerequisites and limitations

Prerequisites

• An active AWS account

• Mainframe datasets with business data

• Access to install a file transfer agent on the mainframe

Limitations

• Your mainframe data file should be in one of the file formats supported by Amazon QuickSight. For a list supported file formats, see the Amazon QuickSight documentation.

  This pattern uses a Lambda function to convert the mainframe file into a format supported by Amazon QuickSight.

Architecture

The following diagram shows an architecture for generating business insights from mainframe data by using AWS Mainframe Modernization File Transfer with BMC and Amazon Q in QuickSight.

The diagram shows the following workflow:

1. A mainframe dataset containing business data is transferred to Amazon S3 by using AWS Mainframe Modernization File Transfer with BMC.

2. The Lambda function converts the file that's in the file-transfer destination S3 bucket to comma-separated values (CSV) format.

3. The Lambda function sends the converted file to the source dataset S3 bucket.

4. The data in the file is ingested by Amazon QuickSight.

5. Users access the data in Amazon QuickSight. You can use Amazon Q in QuickSight to interact with the data by using natural language prompts.

Tools

AWS services

• AWS Lambda is a compute service that helps you run code without needing to provision or manage servers. It runs your code only when needed and scales automatically, so you pay only for the compute time that you use.

• AWS Mainframe Modernization File Transfer with BMC converts and transfers mainframe datasets to Amazon S3 for mainframe modernization, migration, and augmentation use cases.

• Amazon QuickSight is a cloud-scale BI service that helps you visualize, analyze, and report your data in a single dashboard. This pattern uses the generative BI capabilities of Amazon Q in QuickSight.

• Amazon Simple Storage Service (Amazon S3) is a cloud-based object storage service that helps you store, protect, and retrieve any amount of data.

Best practices

• When you create the AWS Identity and Access Management (IAM) roles for AWS Mainframe Modernization File Transfer with BMC and the Lambda function, follow the principle of least privilege.

• Ensure that your source dataset has supported data types for Amazon QuickSight. If your source dataset contains unsupported data types, convert them to supported data types. For information about unsupported mainframe data types and how to convert them to data types supported by Amazon Q in QuickSight, see the Related resources section.

Epics

Set up AWS Mainframe Modernization File Transfer with BMC

Task	Description	Skills required
Install the file transfer agent.	To install AWS Mainframe Modernization File Transfer Agent on your mainframe, follow the instructions in the AWS documentation.	Mainframe system administrator
Create an S3 bucket for mainframe file transfer.	Create an S3 bucket to store the output file from AWS Mainframe Modernization File Transfer with BMC. In the architecture diagram, this is the file-transfer destination bucket.	Migration engineer
Create the data transfer endpoint.	Create an S3 bucket to stage the input mainframe file for AWS Mainframe Modernization File Transfer with BMC. To create the mainframe data transfer endpoint, follow the instructions in the AWS documentation.	AWS Mainframe Modernization specialist

Convert the mainframe file name extension for Amazon QuickSight integration

Task	Description	Skills required
Create an S3 bucket.	Create an S3 bucket for the Lambda function to copy the converted mainframe file from the source to the final destination bucket.	Migration engineer
Create a Lambda function.	To create a Lambda function that changes the file extension and copies the mainframe file to the destination bucket, do the following: Sign in to the AWS Management Console, and navigate to the AWS Lambda console. Choose Create function, and then choose Author from scratch. For Function name, enter a name for your function. In the Runtime dropdown list, choose Python.3.X. Expand Change default execution role, and then choose Create a new role with basic Lambda permissions. Choose Create function. Choose the Code tab, and then paste the S3CopyLambda.py Python code that's provided in the Additional information section. The Python code was generated by using Amazon Q Developer in the Microsoft Visual Studio integrated development environment (IDE). Edit the destination_bucket_name to the name of the S3 bucket that you created previously, and change destination_file_key to the mainframe file name. Deploy the Lambda function.	Migration engineer
Create an Amazon S3 trigger to invoke the Lambda function.	To configure a trigger that invokes the Lambda function, do the following: On the Lambda console, open the Functions page. Choose the Lambda function. In Function overview, choose Add trigger. In the Trigger configuration dropdown list, choose S3. In the Bucket field, enter the name of your source bucket. In the Event type dropdown list, choose All object create events. Select the I acknowledge that using the same S3 bucket for both input and output is not recommended check box, and then choose Add. For more information, see Tutorial: Using an Amazon S3 trigger to invoke a Lambda function.	Migration lead
Provide IAM permissions for the Lambda function.	IAM permissions are required for the Lambda function to access the file-transfer destination and source dataset S3 buckets. Update the policy associated with the Lambda function execution role by allowing s3:GetObject and s3:DeleteObject permissions for the file-transfer destination S3 bucket and s3:PutObject access for the source dataset S3 bucket. For more information, see the Create a permissions policy section in Tutorial: Using an Amazon S3 trigger to invoke a Lambda function.	Migration lead

Define a mainframe data transfer task

Task	Description	Skills required
Create a transfer task to copy the mainframe file to the S3 bucket.	To create a mainframe file transfer task, follow the instructions in the AWS Mainframe Modernization documentation. Note: Specify Source code page encoding as IBM1047 and Target code page encoding as UTF-8.	Migration engineer
Verify the transfer task.	To verify that the data transfer is successful, follow the instructions in the AWS Mainframe Modernization documentation. Confirm that the mainframe file is in the file-transfer destination S3 bucket.	Migration lead
Verify the Lambda copy function.	Verify that the Lambda function is initiated and that the file is copied with a .csv extension to the source dataset S3 bucket. The .csv file created by the Lambda function is the input data file for Amazon QuickSight. For example data, see the Sample-data-member-healthcare-APG file in the Attachments section.	Migration lead

Connect Amazon QuickSight to the mainframe data

Task	Description	Skills required
Set up Amazon QuickSight.	To set up Amazon QuickSight, follow the instructions in the AWS documentation.	Migration lead
Create a dataset for Amazon QuickSight.	To create a dataset for Amazon QuickSight, follow the instructions in the AWS documentation. The input data file is the converted mainframe file that was created when you defined the mainframe data transfer task.	Migration lead

Get business insights from the mainframe data by using Amazon Q in QuickSight

Task	Description	Skills required
Set up Amazon Q in QuickSight.	This capability requires Enterprise Edition. To set up Amazon Q in QuickSight, do the following: To get the Amazon Q add-on, follow the instructions Step 1: Get the Q add-on in the AWS documentation. To use the generative BI capabilities in Amazon Q, upgrade your users' accounts. Follow the instructions in the AWS documentation. Create an Amazon Q topic by using the dataset that you created previously. Follow the instructions in the AWS documentation. To configure the topic metadata so that it's natural language‒friendly, follow the instructions in the AWS documentation.	Migration lead
Analyze mainframe data and build a visual dashboard.	To analyze and visualize your data in Amazon QuickSight, do the following: To create the mainframe data analysis, follow the instructions in the AWS documentation. For the dataset, choose the dataset created in the previous step. On the analysis page choose Build visual. In the Create topic for analysis window, choose Update existing topic. In the Select a topic dropdown list, choose the topic that you created previously. Choose Topic linking. After you link the topic, choose Build visual to open the Amazon Q Build a Visual window. In the prompt bar. write your analysis questions. The example questions used for this pattern are the following: Show member distribution by region Show members distribution by age Show member distribution by gender Show member distribution by plan type Show member not completed preventive immunization After you enter your questions, choose Build. Amazon Q in QuickSight creates the visuals. To add the visuals to your visual dashboard, choose ADD TO ANALYSIS. When you're finished, you can publish your dashboard to share with others in your organization. For examples, see Mainframe visual dashboard in the Additional information section.	Migration engineer

Create a data story with Amazon Q in QuickSight from the mainframe data

Task	Description	Skills required
Create a data story.	Create a data story to explain insights from the previous analysis, and generate a recommendation to increase preventive immunization for members: To create the data story, follow the instructions in the AWS documentation. For the data story prompt, use the following: Build a data story about Region with most numbers of members. Also show the member distribution by medical plan, vision plan, dental plan. Recommend how to motivate members to complete immunization. Include 4 points of supporting data for this pattern. You can also build your own prompt to generate data stories for other business insights. Choose Add visuals, and add the visuals that are relevant to the data story. For this pattern, use the visuals that you created previously. Choose Build. For example data story output, see Data story output in the Additional information section.	Migration engineer
View the generated data story.	To view the generated data story, follow the instructions in the AWS documentation.	Migration lead
Edit a generated data story.	To change the formatting, layout, or visuals in a data story, follow the instructions in the AWS documentation.	Migration lead
Share a data story.	To share a data story, follow the instructions in the AWS documentation.	Migration engineer

Troubleshooting

Issue

Solution

Unable to discover the mainframe files or datasets entered in Data sets search criteria for Create transfer task in AWS Mainframe Modernization File Transfer with BMC.

First, check the connection by choosing Data transfer endpoints on the AWS Mainframe Modernization Transfer with BMC console. If the last heartbeat time is greater than two minutes, the connection for file transfer hasn't been established. If the last heartbeat time is less than 2 minutes for the agent running on the mainframe, the connection to the agent is successful. Proceed to step 2. Check the AWS Secrets Manager setup. A secret key must be configured in Secrets Manager with a key of userId (uppercase I) with a value of the mainframe's user ID and a key of password with the value of the mainframe password. The userId and password secret keys are case-sensitive and must be entered as is.

Related resources

To convert mainframe data types such as PACKED-DECIMAL (COMP-3) or BINARY (COMP or COMP-4) to a data type supported by Amazon QuickSight, see the following patterns:

• Convert and unpack EBCDIC data to ASCII on AWS by using Python

• Convert mainframe files from EBCDIC format to character-delimited ASCII format in Amazon S3 using AWS Lambda

Additional information

S3CopyLambda.py

The following Python code was generated by using a prompt with Amazon Q Developer in an IDE:

#Create a lambda function triggered by S3. display the S3 bucket name and key
import boto3
s3 = boto3.client('s3')
def lambda_handler(event, context):
print(event)
bucket = event['Records'][0]['s3']['bucket']['name']
key = event['Records'][0]['s3']['object']['key']
print(bucket, key)
#If key starts with object_created, skip copy, print "copy skipped". Return lambda with key value.
if key.startswith('object_created'):
print("copy skipped")
return {
'statusCode': 200,
'body': key
}
# Copy the file from the source bucket to the destination bucket. Destination_bucket_name = 'm2-filetransfer-final-opt-bkt'. Destination_file_key = 'healthdata.csv'
copy_source = {'Bucket': bucket, 'Key': key}
s3.copy_object(Bucket='m2-filetransfer-final-opt-bkt', Key='healthdata.csv', CopySource=copy_source)
print("file copied")
#Delete the file from the source bucket.
s3.delete_object(Bucket=bucket, Key=key)
return {
'statusCode': 200,
'body': 'Copy Successful'
}

Mainframe visual dashboard

The following data visual was created by Amazon Q in QuickSight for the analysis question show member distribution by region.

The following data visual was created by Amazon Q in QuickSight for the question show member distribution by Region who have not completed preventive immunization, in pie chart.

Data story output

The following screenshots show sections of the data story created by Amazon Q in QuickSight for the prompt Build a data story about Region with most numbers of members. Also show the member distribution by medical plan, vision plan, dental plan. Recommend how to motivate members to complete immunization. Include 4 points of supporting data.

In the introduction, the data story recommends choosing the region with the most members to gain the greatest impact from immunization efforts.

The data story provides an analysis of member numbers for the top three regions, and names the Southwest as the leading region for focusing on immunization efforts.

Note: The Southwest and Northeast regions each have eight members. However, the Southwest has more members that aren't fully vaccinated, so it has more potential to benefit from initiatives to increase immunization rates.

Attachments

To access additional content that is associated with this document, unzip the following file: attachment.zip