如何将AWS Redshift数据库连接至Excel并制作动态报表？

Prerequisites #

• AWS Redshift cluster details: Endpoint, port (default 5439), database name, valid DB credentials (username/password or IAM credentials)
• For ODBC method: Official Amazon Redshift ODBC Driver installed

Method 1: Use Excel's Built-in Amazon Redshift Connector (Power Query) #

• Open Excel, navigate to the Data tab.
• Click Get Data > From Database > From Amazon Redshift.
• Enter your cluster's endpoint, port, and database name in the pop-up.
• Choose your authentication method:
  • Username/Password: Input your Redshift DB username and password.
  • AWS IAM: Use your AWS Access Key ID/Secret Access Key, or default machine credentials if configured.
• Click OK, then select the tables you want to import. Use the Power Query Editor to filter, transform, or clean data before loading.
• Choose to load data directly to a worksheet, or create a connection-only setup for later use.

Method 2: Use ODBC Driver #

1. Set Up ODBC DSN:
   • Open ODBC Data Source Administrator (search "ODBC Data Sources" in Windows Start menu).
   • Go to User DSN or System DSN tab, click Add.
   • Select the Amazon Redshift ODBC Driver, click Finish.
   • Configure the DSN:
     • Data Source Name: A descriptive name (e.g., Redshift-Reporting)
     • Server: Redshift cluster endpoint
     • Port: 5439
     • Database: Target database name
     • User ID/Password: Redshift DB credentials
   • Test the connection and save the DSN.
2. Connect from Excel:
   • Go to Data tab > Get Data > From Other Sources > From ODBC.
   • Select your created DSN, click OK, then select tables and load data as needed.

Build Dynamic Reports #

• Convert Data to Excel Table: Select the loaded data range, go to Insert > Table. Check "My table has headers" and confirm. This ensures data syncs on refresh.
• Create Pivot Tables: Select the Excel Table, go to Insert > PivotTable. Drag fields to Rows, Columns, and Values to build your report layout.
• Add Interactive Slicers: Click the PivotTable, go to PivotTable Analyze > Insert Slicer. Pick fields (e.g., date, region) to let users filter data interactively.
• Set Up Data Refresh:
  • Manual refresh: Right-click the table/pivot table and select Refresh.
  • Scheduled refresh: Go to Data > Queries & Connections. Right-click your Redshift connection, select Properties. Under Usage, enable "Refresh every X minutes" or configure a schedule (available in Office 365/Power BI integrated Excel).
• Dynamic Calculations: Use structured references (e.g., Table1[Revenue]) with functions like INDEX/MATCH or SUMIF to create formulas that update automatically when data refreshes.

Troubleshooting #

• Ensure your Redshift cluster's security group allows inbound traffic from your IP address on port 5439.
• Verify credentials are correct (check for typos, expired IAM keys if using IAM auth).
• Update the ODBC driver to the latest version if using that method.
• Confirm the Redshift cluster is in a running state.

内容的提问来源于stack exchange，提问作者Md Ehsaan Shaikh