Connect to AWS Redshift database from BI4
This document describes the steps to connect Amazon Redshift data warehouse (cloud services) using generic JDBC connection.
Step 1:
To connect with AWS RedShift using JDBC, you need to have redshift JDBC drivers or supporting drivers from vendor.
You can download JDBC drivers from amazon portal based on version.
http://docs.aws.amazon.com/redshift/latest/mgmt/configure-jdbc-connection.html
Here is the direct link to download .Jar file which supports to connect from BI4.1 direct tools.
https://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC4-1.1.1.0001.jar
Step 2:
Place the downloaded .JAR file where business objects installed (client/server).
C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java
Step 3:
Open ‘jdbc.sbo’ file under C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc in notepad/xml editor and add below code to the file:
<ClassPath>
<Path>C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\RedshiftJDBC4-1.1.1.0001.jar</Path>
</ClassPath>
This looks like below:
<?xml version=”1.0″ encoding=”UTF-8″?><DriverConfiguration xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:noNamespaceSchemaLocation=”../sbo.xsd”>
<Defaults>
<Class JARFile=”dbd_jdbc”>com.sap.connectivity.cs.java.drivers.jdbc.JDBCDriver</Class>
<JDBCDriver>
<ClassPath>
<Path>$ROOT$/drivers/java/dbd_jdbcwrapper.jar</Path>
</ClassPath>
<Parameter Name=”JDBC Wrapper”>com.sap.connectivity.cs.java.drivers.jdbc.wrapper.JDBCWrapper</Parameter>
</JDBCDriver>
<Parameter Name=”Family”>Generic</Parameter>
<Parameter Name=”SQL External File”>jdbc</Parameter>
<Parameter Name=”SQL Parameter File”>jdbc</Parameter>
<Parameter Name=”Description File”>generic_jdbc</Parameter>
<Parameter Name=”Driver Capabilities”>Query</Parameter>
<Parameter Name=”Extensions”>jdbc</Parameter>
<Parameter Name=”Connection Shareable”>Yes</Parameter>
<Parameter Name=”Shared Connection”>No</Parameter>
<Parameter Name=”Array Fetch Available”>True</Parameter>
<!– define "magic" value 0 for Array Fetch Size to use default JDBC Fetch Size –>
<Parameter Name=”Array Fetch Size”>0</Parameter>
<Parameter Name=”Transactional Available”>No</Parameter>
<Parameter Name=”Array Bind Available”>False</Parameter>
<Parameter Name=”Max Rows Available”>No</Parameter>
<Parameter Name=”Query TimeOut Available”>No</Parameter>
<Parameter Name=”Optimize Execute”>True</Parameter>
</Defaults>
<DataBases>
<DataBase Active=”Yes” Name=”Generic JDBC datasource”>
<JDBCDriver>
<ClassPath>
<Path>C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\postgresql-9.2-1004.jdbc3.jar</Path> — If you use postgresql drivers
<Path>C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\drivers\java\RedshiftJDBC4-1.1.1.0001.jar</Path>
</ClassPath>
<Parameter Name=”JDBC Class”>$JDBCCLASS$</Parameter>
<Parameter Name=”URL Format”>$DATASOURCE$</Parameter>
</JDBCDriver>
<Parameter Name=”Array Fetch Size”>10</Parameter>
</DataBase>
</DataBases>
</DriverConfiguration>
Step 4:
Create universe connection in IDT/UDT.
New > Relational connection
Provide name and click on next.
Select type of connection and click next.
Provide User Name, Password, JDBC URL and JDBC Class Details here.
Note: You JDBC URL should include the database name
JDBC URL: jdbc:redshift://xxxxx.redshift.amazonaws.com:0000/database
JDBC Class: com.amazon.redshift.jdbc4.Driver
Now test connection.
Click Finish to create connection.
Note: You can use other drivers like postgresql/Greenplum also to connect redshift.