Guide to Integrate AWS S3, Redshift, and Power BI

In this project, we will replicate a previous workflow done in Azure but using Amazon Web Services (AWS). The goal is to load data into an S3 Bucket, then transfer it to Amazon Redshift, and finally connect Power BI to Redshift for analysis and visualization. Below are the detailed steps to complete this project.

Step 1: Create a Bucket in S3

1. Access S3:

   • Log in to the AWS console.

   • Navigate to S3 from the services panel.

2. Create a New Bucket:

   • Click on "Create bucket".

   • Assign a unique name to the Bucket and select a region.

   • Configure the access options and policies according to your needs.

   • Complete the Bucket creation.

Step 2: Configure IAM for S3 Access

1. Create an IAM Group:

   • Access IAM from the AWS services panel.

   • Create a new group called S3.

   • Assign the AmazonS3FullAccess policy to this group.

2. Create an IAM User:

   • Create a user named PowerBI.

   • Assign the user to the S3 group.

   • Generate an Access Key and Secret Access Key for this user.

   • Download the credentials and store them securely.

Step 3: Configure Python to Upload Files to S3

1. Install Boto3:

   • If not already installed, install Boto3 by running pip install boto3.

2. Python Code to Upload Files to S3:

   https://github.com/Cuchuflim/redshift-and-PowerBI/tree/main

import boto3
from botocore.exceptions import NoCredentialsError

ACCESS_KEY = 'your_access_key'
SECRET_KEY = 'your_secret_key'
REGION = 'your_bucket_region'


def upload_to_aws(local_file, bucket, s3_file):
    s3 = boto3.client(service_name='s3', 
                      region_name= REGION,
                      aws_access_key_id= ACCESS_KEY,
                      aws_secret_access_key=SECRET_KEY)
    try:
        s3.upload_file(local_file, bucket, s3_file)
        print("Upload Successful")
        return True
    except FileNotFoundError:
        print("The file was not found")
        return False
    except NoCredentialsError:
        print("Credentials not available")
        return False

bucket_name = 'XXXXXXXXXXXXXXXX'
upload_to_aws('path/to/your/file', bucket_name)

Step 4: Configure Amazon Redshift

1. Create a Redshift Cluster:

   • Access Redshift from the services panel.

   • Create a new cluster and select the most economical node type and the minimum number of nodes.

   • Ensure that "Publicly accessible" is enabled in the network and security configuration.

2. Load Data from S3 to Redshift:

   • Use the Redshift SQL interface or third-party tools to copy data from S3 to Redshift.

Step 5: Connect Power BI to Amazon Redshift

1. Configure Security Rules in VPC:

   • Access the VPC Dashboard in AWS.

   • Select "Security Groups".

   • Find and select the security group used by Redshift.

   • Edit the inbound rules to allow connections:

     • Type: Redshift

     • Protocol: TCP

     • Port: 5439

     • Source: My IP (your IP address) or IPv4 and IPv6

     • Description: (optional)

   • Save the rules.

2. Connect Power BI:

   • Open Power BI Desktop.

   • Select "Get Data" and choose "Amazon Redshift".

   • Enter the Redshift cluster information and your credentials.

   • Once connected, you can visualize and analyze the loaded data.

Summary

By following these steps, you will have set up a complete workflow to load data into S3, transfer it to Redshift, and connect it to Power BI for analysis. This integration will allow you to leverage AWS's storage and analysis capabilities along with Power BI's powerful visualization tools.