KB260001: How to connect to Amazon RedShift using the Amazon RedShift ODBC Driver in MicroStrategy Analytics Enterprise 9.4.1

SUMMARY
The following document describes the steps needed to connect to an Amazon RedShift database using the Amazon RedShift ODBC Driver in Strategy Analytics Enterprise 9.4.x - 10.x
Steps to connect to Amazon RedShift using the Amazon RedShift ODBC Driver on Linux

1. Modify the <REDSHIFT_PATH>/amazon.redshiftodbc.ini file. The file needs to have the following changes made:
   
• Change the DriverManagerEncoding setting to use UTF-8
• Comment out the iODBC Driver manager 
• Un-comment out the "AmazonDM /unixODBC" Driver manager
• After the changes, the edited options should look similar to the below example:
  

  
  [Driver]
  DriverManagerEncoding=UTF-8
  ErrorMessagesPath=/usr0/odbc/amazon/redshift/linux/1.2.1.1001/ErrorMessages
  LogLevel=0
  LogPath=/usr0/odbc/amazon/redshift/linux/1.2.1.1001/
  # Generic ODBCInstLib
  #   iODBC
  #ODBCInstLib=libiodbcinst.so
  #   AmazonDM / unixODBC
  ODBCInstLib=libodbcinst.so

  
  
1. Modify the <MSTR_HOME_PATH>/env/ODBC.sh file to add an entry for the Amazon RedShift Driver. This can be inserted at the end of the file and should be done as shown below:
   

   
   #
   # ODBC Driver for Amazon Redshift
   #
   REDSHIFT_PATH=<REDSHIFT_PATH>
   AMAZONREDSHIFTODBCINI=<REDSHIFT_INI_PATH>
   export AMAZONREDSHIFTODBCINI
   
   if [ "${REDSHIFT_PATH}" != '<REDSHIFT_PATH>' ]; then
           export REDSHIFT_PATH
   
           mstr_append_path LD_LIBRARY_PATH "${REDSHIFT_PATH:?}"/
           export LD_LIBRARY_PATH
   
   fi

   
    
   
• The REDSHIFT_PATH should point to the location of the folder containing the drivername.so file(example: /odbc/amazon/redshift/linux/1.2.1.1001/lib/32>
• The AMAZONREDSHIFTODBCINI should point to the path containing the amazon.redshiftodbc.ini file which was modified in step 1. (example: /odbc/amazon/redshift/linux/1.2.1.1001/lib/32/amazon.redshiftodbc.ini)
2. Add an entry to the odbc.ini file in the <MSTR_HOME_PATH>.
   
• Add an entry under the [ODBC Data Sources] section as shown below:
  

  
  [ODBC Data Sources]
  MyAmazonRedShiftDSN=Amazon Redshift (x86)

  
  
• Add an entry at the bottom of the odbc.ini file as shown below:
  

  
  [MyAmazonRedShiftDSN]
  Description=Amazon Redshift ODBC Driver (32-bit) DSN
  Driver=<path to libamazonredshiftodbc32.so>
  Server=<server>
  Port=<port>
  Database=<database>
  locale=en-US

  
  

Once these steps have been followed, users should be able to connect to Amazon RedShift using the Amazon RedShift ODBC driver. 
Steps to Connect the Amazon RedShift ODBC Driver on Windows
On Windows, the setup is much simpler; simply fill out the options in the new connection similar to the following (DSN, Server, Port, Database, and Credentials will all depend on your setup) and connection should work.