The following steps were performed using a HLC Test environment using OBIEE 11.1.7 ( patch level 131017 ). The aim was to ensure it was possible to configure an OBIEE RPD to have two connection pools, one being an Oracle RDBMS, the other being an Amazon Redshift instance. The final test was to ensure a dashboard could easily display content from the Oracle and Redshift environments alongside each other without any issues.
This would mean migration going forward could be managed, the idea that the new environment would report primarily from the AWS Redshift instance, but with the ability for a Connection pool in the OBIEE RPD to be configured to connect to the existing DAS Oracle RDBMS. This would allow one OBIEE environment to publish “old” and “new” dashboards, whilst old developments are either replaced or migrated to the new environment.
Below are the steps taken to configure a Default OBIEE RPD which currently connects to a local Oracle 11gR2 instance, to also then connect to an AWS Redshift Cluster. This assumes an Amazon AWS cluster has already been created and loaded with sample data. See Redshift Blog page for details on the initial setup of the Proof Of Concept (PoC) HLC AWS Instance.
• Download and install the PostGres ODBC Drivers onto the Windows machine you will use to configure the OBIEE RPD via the OBIEE Admin Tool. Use this link http://www.postgresql.org/ftp/odbc/versions/msi/ and download “psqlodbc_09_03_0400”. If found this to be the most reliable version, and worked on a Windows 7 64 Bit Environment.
• Unpack the windows installer and run and install the driver, once installed confirm the driver is available i..e start > control panel > Administrative Tools > Data Sources (ODBC), click the Drivers Tab, you should see that the PostgreSQL driver is available
- Click the System DSN Tab, click Add, select “PostgreSQL Unicode(x64)” driver, configure with the Data Source name you will use within the RPD Configuration. You will need to confirm the AWS Redshift Cluster Hostname and Port and username/password from within the AWS Redshift console, once configured click the test button to confirm. You will need to ensure the network firewall has been opened for the AWS listening port, in this case 5439
- Take an offline copy of the existing OBIEE RPD, then open offline.
- In the Physical layer create a new database, name it Redshift, set the database type to be “ODBC Basic”, leave all other defaults for now
- Within the Redshift Database, create a new Connection Pool, name this redshift. You should now be able to select the Data Source Name created above in the ODBC Administrator i..e “HlcRedshift”, supply the username and password, also specify the AWS hostname and port number as previously collected and entered in the ODBC Administrator. Accept all other defaults
- Right Click the new connection pool, and select import meta data, select tables etc, and you should now be able to import the test Redshift tables, and import into the Redshift Database object in the physical layer, click finish.
- For the purposes of this test, a simple modelling exercise was carried out between the “date” and “event” table, so the RPD should now look like this
- To test the ODBC connection, right the date table, then select “View Data”, if all is well you should see the data returned within the OBIEE RPD Admin Tool
- You are now in a position to upload the new RPD to OBIEE, so upload the new RPD using the Enterprise Manager
Server Configuration
This is specific to environments that are running OBIEE Server on a UNIX environment. The following steps are required to ensure the OBIEE Server is able to use the ODBC DSN. You have configured within the RPD, to connect to the AWS Redshift Cluster. Always a good idea to ensure the host can connect to the AWS Cluster, prove this via a telnet <host> <port> and ensure you get a positive response i.e
oracle@192.168.28.114’s password:
Last login: Tue Oct 28 08:14:30 2014 from 192.168.28.12
[oracle@hlcdb01 ~]$ telnet hlc-dw-instance.cuuvb6sl3b8y.us-west-2.redshift.amazonaws.com 5439 Trying 54.69.183.99…
Connected to hlc-dw-instance.cuuvb6sl3b8y.us-west-2.redshift.amazonaws.com.
Escape character is ‘^]
With the server > AWS Cluster connectivity confirmed, we can now configure the OBIEE Middleware to connect to the AWS Cluster via the ODBC Driver. OBIEE does in fact ship with valid ODBC drivers that can be used to connect to various external sources. Depending on your OBIEE Release its best to ensure OBIEE opmn feature is configured to use the latest DataDirect 7.1 drivers. Full instructions are here
With the OBIEE Opmn configuration now set to use the latest ODBC Drivers, we can continue with the ODBC configuration to ensure the OBIEE server can connect to the AWS Redshift Cluster
- Be sure the following directory does exist “$MW_HOME/Oracle_BI1/common/ODBC/Merant/7.0.1”
- cd $MW_HOME/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup
- Make a backup of the existing odbc.ini
- Edit odbc.ini, within the “ODBC Data Sources” Section Add the name of the new ODBC Data source, define it as “PostgreSQL”, so it should look something like this :-
[ODBC Data Sources]
AnalyticsWeb=Oracle BI Server
Cluster=Oracle BI Server
SSL_Sample=Oracle BI Server
HlcRedshift=PostgreSQL
- Add a new Data Source, name it the same i.e. [HlcRedshift], should look something like this. Edit the Driver,Hostname,PortNumber,LogonD and Password as required :-
[HlcRedshift]
Driver=/u01/app/obiee/Oracle_BI1/common/ODBC/Merant/7.0.1/lib/ARpsql26.so
Description=DataDirect 7.0 PostgreSQL Wire Protocol
AlternateServers=
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=hlc
DefaultLongDataBuffLen=2048
EnableDescribeParam=1
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=hlc-dw-instance.cuuvb6sl3b8y.us-west-2.redshift.amazonaws.com
HostNameInCertificate=
InitializationString=
KeyPassword=
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=hlcadmin
MaxPoolSize=100
MinPoolSize=0
Password=Hlcadmin1
Pooling=0
PortNumber=5439
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
TrustStore=
TrustStorePassword=
ValidateServerCertificate=1
XMLDescribeType=-10
- Save the odbc.ini file, restart the OBIEE opmnctl services i.e. opmnctl stopall / opmnctl startall or use the OBIEE EM Console
- Once restarted, login and create a new Analysis in the Redshift Subject area. You should find its now possible to create a dashboard that publishes data from the local Oracle Database, and the AWS Redshift Instance :-
