What do you get when you combine Power Query with Amazon Web Services? You get a powerful new data source for your business needs and open a world of analytics possibilities. I’ll have some of that!
I am certainly not an expert in this area but know enough to share my knowledge.
Most of this stuff is only done during setup. After that, it’s just another Power Query data source.
Let’s get started!
- Amazon Web Services – leading provider of on-demand cloud computing platforms
- Amazon RedShift database – data warehousing database available thru Amazon Web Services
- JDBC (Java Database Connector) driver – used to connect to an Amazon Redshift database
- ODBC (Open Database Connectivity) connector– it connects your computer to the JDBC driver to the Amazon Redshift database
Here’s the architecture: Here’s what happens:
- Power Query connects to the Amazon Redshift JDBC driver using the ODBC data source connector. Then, Power Query talks to the Amazon Redshift database hosted on Amazon Web Services.
- Data is then passed from the Amazon Redshift database via the Amazon Redshift JDBC driver back to Power Query.
The 3-step process:
- Download and install the Amazon Redshift JDBC driver to your laptop
- Create an ODBC connection to the Amazon Redshift JDBC driver
- Enter the ASW connection string information into the ODBC connection
Download and install the Amazon Redshift JDBC driver on your laptop. You need to do this step before creating the ODBC connection. Otherwise, the JDBC driver will not be available for selection during ODBC setup
- go to https://is.gd/fdRkrq to download the latest JDBC driver (example shown below)
- save to your laptop (such as is in Downloads folder) and install (double-click to start the install process)
- Create the ODBC connector to the Amazon Redshift JDBC driver on your laptop (A – F shown below)
- Search for ODBC desktop app on Windows 10. The simple way is to select the magnifying glass at the bottom of your desktop, type ‘ODBC,’ press ‘Enter’ and it should appear. Select the appropriate version (64 or 32-bit).
- This should open the ODBC Data Source Administrator window. Select ‘Add’
- select ‘Amazon Redshift (x64) which you installed in the previous step
- select ‘Finish’
Notice that there are many other drivers which you can set up as a data source. That’s the beauty of ODBC – it enables communication for so many different types of data sources.
Enter the AWS connection information into the Amazon Redshift JDBC driver contained in the ODBC connector
Example of the required Amazon Redshift connection settings information (highlighted in yellow)
the Port should be 5439, but all other settings will be unique to your setup
I recommend asking your IT department/database admins for connection string info. Or, ask whoever is the administrator of the Amazon Redshift database you’re using.
You can test the connection by pressing ‘Test.’
Press ‘OK’ to finish
Are you with me so far? Now comes the fun part – – using Power Query to connect to the AWS data.
- Data > Get Data > From Other Sources > From ODBC
- Select the Amazon Redshift JDBC driver as the Data source name
- Press ‘OK’ and you’ll be connected to the AWS Redshift database you defined in your ODBC connection
You can now select whatever table or view is available to you and import to Power Query – – just like an Excel, CSV or any other data source.
Notice that the data source names in the Power Query selector are the same as in the ODBC Data Source Administrator window. This is not a coincidence. These connectors link Power Query to other data sources as long as the data source is first defined in the ODBC administrator section.
You can also enter SQL code to your ODBC connector by selecting ‘Advanced options’. This is tremendously helpful for creating ‘lights out’ automated systems. But, that’s a topic for a future post.
To recap, Power Query can import data from an Amazon Redshift database using the ODBC connection (which utilizes a JDBC driver).
Simply amazing. And the same driver can be used in different Excel workbooks on your computer.
I use this to grab AWS data, transform it using Power Query applied steps and create custom data analysis reports. Having everything within the Excel environment allows me to create charts, visualizations and data grids.
What’s your experience with ODBC and Amazon Web Services? Any tips and tricks you’d like to share in the comments section?
Let’s learn together.
Did you find this article easier to understand than the average “tech” article?
We like to think that is no accident. We’re different. First of a new breed – the kind who can speak tech, biz, and human all at the same time.