Skip to Content
Author's profile photo Former Member

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 &quot;magic&quot; 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.

Select_Driver.png

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.

 
Test_Connection.png

Click Finish to create connection.

Note: You can use other drivers like postgresql/Greenplum also to connect redshift.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.