How to Connect Excel to Alli Data (Redshift ODBC Driver Setup)
You need to be connected to the PMG VPN (AWS VPN) before proceeding
Instructions
Before you can pull data from Alli into Excel you will need to download and install an ODBC driver using the Mac or Windows instructions below:
ODBC Driver Instructions
Download the ODBC Driver using one of the following links and follow the instructions below
Walk through the ODBC Driver instructions below
| 1 | Submit a request to IT to receive an ODBC license key and Alli Service Desk for your Redshift credentials. |
| 2 | Download the Driver using this link: http://www.actualtech.com/downloads/Actual_ODBC_Pack.dmg |
| 3 | Double click on the "Actual ODBC Pack.pkg" icon to run the installer. |
| 4 | Open the "ODBC Manager" located in your "/Applications/Utilities" folder. |
| 5 | Click on the "System DSN" tab and then the "Add" Button  |
| 6 | Select the "Actual Open Source Databases" driver and click "ok".  |
| 7 | A new window will pop up. On this new window click "Licenses". Here you will enter the license key IT has provided you. Then click "Continue"  |
| 8 |
NOTE: in steps 8 and 9, you will need to enter the correct Redshift cluster you need access to. There are now different clusters containing different clients. Check the list here, if you’re unsure. Enter your redshift connection configurations then click "continue". Note: you may be connecting to a different cluster than the one shown in this example  |
| 9 | Copy the configurations as shown in the image below, making sure to use the correct server.  |
| 10 | Click the check box for "Connect using SSL encryption" then click "OK". (Leave the rest of the fields blank as shown below.) Now click "Continue".   |
| 11 | Copy the configurations as shown in the image below. Click "Continue".  |
| 12 | Click "Continue" on the last slide and your all set! Your ODBC driver is now ready allowing you to pull data from Redshift into Excel. |
Go to https://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-connection.html
“Install and configure the Amazon Redshift ODBC drive on Microsoft Windows”
Select either the 32-bit or 64-bit ODBC driver. This MUST match with excel or any other tool that will use this driver.
For example: You would download and install the 64-bit driver if you are using 64-bit excel.
Amazon ODBC Installation Screenshots:
Run the corresponding ODBC Data Sources (32 or 64).
Select “Add” in the User DSN tab.
Select the Amazon Redshift driver.
Fill in the fields below and your individual User and Password.
Data Source Name: redshift.data.pmg.com
Server: redshift.data.pmg.com
(NOTE: you may need a different server, depending on the client. Please check this list and enter accordingly)
Port: 5439
Database: datawarehouse
Select Test to verify your connection. If successful, select OK to save the configuration.
Pulling Data into Excel
Open Excel and go to the “Data” Tab.
Click “Get Data (Power Query)” and then click “From Database (Microsoft Query)”
Select the redshift ODBC data source and click “OK”.
Select any data source, click “Return Data”
Select a cell for the data to populate into.
If you see data loaded into Excel, everything is set up correctly!
Limits & Restrictions
Access Alli Redshift requires a whitelisted IP or VPN connection