OBIEE 12c with Amazon Redshift
Lessons learnt during our recent implementations
Before we move on to the OBIEE setup with Amazon Redshift there are few configuration files and overall steps that needs to be followed. These files are as shown below in same order:
- Install Amazon Redshift ODBC Driver
- Configure connectivity to Amazon Redshift and edit below files. Last section in this post mentions the steps for each of them in detail.
- Copy dbfeatures.ini
- Setup Database Object and Connection Pool in the RPD
- Test connectivity to Redshift DB from BI server using nqcmd
Below is the high level step by step process run through.
1.Get Drivers from Amazon
First of all we must have to right drivers. We can get the drivers from Amazon. Navigate to below link, and download drivers.
2. Setup/Configure RPD Settings and DSN
Next step is to configure the OBIEE RPD. Now there is an exhibit 1, appended towards the end of the post, and it is assumed that steps listed in there are already performed before setting up the DSN. If not then please perform those steps using admin access.
Next step would be the setup and configuration as shown below
3. Using Redshift Client Tools
In order to query the redshift database you may use any of the SQL client tools, which can be used with Redshift. There is no official Amazon provided client tool available at the writing of this post. We can select any open source or licensed version IDE. As Redshift supports PostgreSQL JDBC, hence the tool must be specifically compliant to support it.
4. It can be Aginigty or SQL J DeveloperWB
5. Design Data Model & Create report and troubleshoot with these points below in mind
- Amazon Redshift is a columnar database, which is in contrast with relational databases
- Amazon Redshift database is one of the derivatives of Postgress SQL, as per Amazon, Amazon Redshift is based on PostgreSQL 8.0.2
- OBIEE Queries seemingly do not always work in the same way as they do in Oracle Database, or SQL Server DB.
- Query must be tweaked for certain functions such as ifnull, casting, and divide by zero operations. Due to which it gets sometime a bit time consuming to debug the code.
a.Editing odbc.ini, copy below section in this file.
[ODBC Data Sources]
Description= Amazon Redshift ODBC
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so (default location, once you upgrade or reinstall the driver, file name may change)
ServerName= <Hostname of RedShift>
Port=<Port of RedShift>
b.Editing odbcinst.ini , copy below section in this file.
Add the following to odbcinst.ini file
Description= Amazon Redshift ODBC Driver (64-bit)
Edit “/opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini” and update the following.
d.Another setting we need to change is replacing dbfeatures.ini file at below location
This file can be found in the oracle installation pack or from oracle support.
Finally test the connectivity and import the tables in rpd.