OBIEE with Amazon Redshift

Moshe Romano is a great guy, working at Perion. I remember him lecturing few years ago about his implementation of OBIEE aggregation awareness with details at Oracle DB and aggregation with Microsoft OLAP cube. Later they wanted to work with OBIEE and Amazon Redshift database.  

Redshift can use ODBC  but you don’t want to limit the functionality to the general ODBC.
Perion used postgresql ODBC driver.

Since Redshift is not supported by Oracle as data source, Moshe had a long process of trial and error, to determine the correct database features for Redshift.

Don’t treat the following as any recommendation, maybe, just a first step. It should not be viewed as advice. Just description of what he did.

Moshe Romano advised:

• Disable connection pooling.
• Enforce schema name.
• Don't be shy to use Evaluate functions.
• Configure the DB features properly.

When you start with default 1.1.1.7 / 11.1.1.9 configuration of ODBC 2.0 at Administration, what are the DB features he changed?

This is the default, basic ODBC:

The following were changed:

Checked:

RIGHT_OUTER_JOIN_SUPPORTED, FULL_OUTER_JOIN_SUPPORTED, NESTED_OUTER_JOIN_SUPPORTED, UNION_SUPPORTED, UNION_ALL_SUPPORTED, FIRST_SUPPORTED, LAST_SUPPORTED, RANK_SUPPORTED, TOPN_SUPPORTED, BOTTOMN_SUPPORTED, DATE_LITERAL_SUPPORTED, TIME_LITERAL_SUPPORTED, DATE_TIME_LITERAL_SUPPORTED, CALENDAR_EXTRACT_WEEK_OF_MONTH_SUPPORTED, CALENDAR_EXTRACT_SHORT_NAME_OF_MONTH_SUPPORTED, CHAR_LENGTH_SUPPORTED, POSITION_SUPPORTED, LOCATE_SUPPORTED, MOD_SUPPORTED, SINH_SUPPORTED, COSH_SUPPORTED, TANH_SUPPORTED, TRIM_SUPPORTED, MULTIPLE_STATEMENTS_PER_CONNECTION_SUPPORTED, CAST_SUPPORTED, DERIVED_TABLES_SUPPORTED, CORRELATION_NAME_SUPPORTED, PREDICATE_SCALAR_SUBQUERY_SUPPORTED, VALUE_SUBQUERY_SUPPORTED, CORRELATED_SUBQUERY_SUPPORTED, COMPARISON_SUBQUERY_SUPPORTED, EXISTS_SUBQUERY_SUPPORTED, IN_SUBQUERY_SUPPORTED, QUANTIFIED_SUBQUERY_SUPPORTED, PERF_PREFER_IN_LISTS, STDDEV_SAMP_SUPPORTED, STDDEV_POP_SUPPORTED, EXCEPT_SUPPORTED, INTERSECT_SUPPORTED, RUNNING_COUNT_SUPPORTED, NULLIF_SUPPORTED, CREATE_AS_SELECT_SUPPORTED, INSERT_SELECT_SUPPORTED, SUBTOTALLING_SUPPORTED, ROWNUM_SUPPORTED, CREATE_VIEW_SUPPORTED, WITH_CLAUSE_SUPPORTED, PERF_PREFER_INTERNAL_STITCH_JOIN, DUAL_TABLE_SUPPORTED, SUBQUERY_IN_CASE_SUPPORTED, DENSE_RANK_SUPPORTED, MULTI_COLUMN_IN_SUPPORTED, ROWNUM_LIMIT_SUPPORTED, ROWNUM_OFFSET_SUPPORTED.

Unchecked:

NULL_VALUES_SORT_FIRST, TIMESTAMP_ADD_SUPPORTED, TIMESTAMP_DIFF_SUPPORTED, LENGTH_SUPPORTED, PI_SUPPORTED, DEGREES_SUPPORTED, RADIANS_SUPPORTED, COT_SUPPORTED, LTRIM_SUPPORTED, RTRIM_SUPPORTED, INSERT_SUPPORTED, REPEAT_SUPPORTED, CONVERT_SUPPORTED

This is the result: