1 Answer
- Newest
- Most votes
- Most comments
0
There are several approaches to connect AWS Redshift to Excel with automatic refresh capabilities:
- 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
- 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
- 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
- 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
- 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
Relevant content
- AWS OFFICIALUpdated a year ago
- AWS OFFICIALUpdated 7 months 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