Skip to content

Connecting tables from AWS Redshift to excel with automatic refresh

0

I want to connect AWS redshift to excel . excel can be scheduled to refresh for updated values from redshift. how to connect redshift to excel. what are different ways to the following . or there is any other alternative for this ?

asked a year ago434 views

1 Answer
0

There are several approaches to connect AWS Redshift to Excel with automatic refresh capabilities:

  1. Using ODBC/JDBC Connection:
  • Install the Amazon Redshift ODBC or JDBC driver on your computer
  • In Excel, go to Data > Get Data > From Other Sources > From ODBC
  • Configure the connection using your Redshift cluster credentials
  • Once connected, you can set up automatic refresh schedules in Excel through Data > Refresh All > Connection Properties
  1. Using AWS PrivateLink:
  • This provides a secure connection method if your Redshift cluster is in a VPC
  • Create a VPC endpoint for Redshift using AWS PrivateLink
  • Configure security groups to allow access from the endpoint
  • Connect Excel to Redshift through this secure endpoint
  1. Using VPN or AWS Direct Connect:
  • Establish a secure VPN connection between your network and AWS VPC
  • Or use AWS Direct Connect for a more reliable, dedicated connection
  • Configure security groups to allow traffic from the VPN/Direct Connect
  • Connect Excel using the private IP address of your Redshift cluster
  1. Using a Serverless API Approach:
  • Create an AWS Lambda function that queries Redshift and returns data
  • Expose this Lambda function via API Gateway
  • Connect Excel to this API endpoint using Power Query
  1. Using Power BI as an Intermediary:
  • Connect Power BI to Redshift
  • Create the necessary data models and queries
  • Export data from Power BI to Excel
  • Schedule automatic refreshes in Power BI

For security best practices:

  • Use IAM roles and policies with least privilege principles
  • Ensure SSL/TLS encryption for connections
  • Implement proper network security using NACLs and security groups
  • Consider implementing multi-factor authentication

When choosing your approach, consider factors like security requirements, data volume, refresh frequency needs, and your technical expertise.
Sources
Accessing Redshift data in PowerBI | AWS re:Post

answered a year ago

  • can 5th option be done using amazon quicksight ? and 4th option i tried but excel gives access forbidden error while connecting API endpoints through power query

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.