Share this article:

Configuring OBIEE for Optimal Performance with Vertica

About Vertica Tips and Techniques

Vertica develops Tips and Techniques to provide you with the information you need to use Vertica with software that our technology partners create. This document provides guidance using one specific version of Vertica and one version of the partner product. While other combinations are likely to work, Other versions may not have been tested.. This document provides guidance using the versions of Vertica and OBIEE as of November, 2016.  

OBIEE Overview

Oracle Business Intelligence Enterprise Edition (OBIEE) is a Business Intelligence platform that includes capabilities such as ad hoc querying, interactive dashboards, and mobile analytics. OBIEE helps businesses analyze data to make strategic business decisions. OBIEE is available as a Windows 32-bit or 64-bit application. OBIEE uses the ODBC driver to connect to your Vertica database.

Before you connect to Vertica, you must define the database type in OBIEE in one of two ways:

  • Create a new data source in the OBI physical layer. When prompted for a database type, specify that OBIEE connect to Vertica using ODBC.
  • Import a Vertica schema into the OBI physical layer. OBIEE recognizes Vertica as an ODBC source database.

Based on the ODBC database type, OBIEE automatically populates a list of database features. OBIEE assigns default values that set each database feature as supported or not.

Typically, if OBDC supports the feature, for any query that uses that feature, the Oracle BI Server pushes that query down to Vertica to execute. Doing so usually results in optimal performance. If Vertica does not support a given features, OBIEE reads the data into the OBIEE server cache and performs the calculations there.

Configuring Database Features Settings in OBIEE

For optimal efficiency when connecting to Vertica using OBIEE, Feel free to customize the list of supported features. There are two ways to modify the supported features settings in OBIEE:

  • In the Database dialog Features tab, to indicate that Vertica supports that feature, check the Value. To specify a feature as unsupported, uncheck the Value

 obiee1.png

  • To define the supported and unsupported database features in your Oracle_Bi_Home\server\Config\INI file, enter lines like the following, using the syntax feature_name_SUPPORTED, such as LEFT_OUTER_JOIN_SUPPORTED

To indicate a database feature as unsupported, delete the feature_name_SUPPORTED line from the Oracle_Bi_Home\server\Config\DBFeatures.INI file.

Recommended Database Features Settings for Vertica

The following table provides information about:

  • Feature: Name of the database feature, with _SUPPORTED appended.
  • Default Value: The value that OBIEE assigns to each database feature depending on your data source. When you specify Vertica as the data source, OBIEE automatically uses the generic ODBC connection. The values listed in the table are the defaults for any data source that uses the generic ODBC option.

ON typically means that OBIEE pushes any query that uses that feature down to Vertica. OFF typically means that OBIEE executes any query that uses that feature. The table identifies exceptions to this behavior.

  • Recommended Value: The recommended value for that feature. For optimal performance with Vertica, make sure to change these settings where they differ from the default.

Where ON/OFF is indicated, either setting is acceptable. Changing the setting does not change how a query is executed.

  • Action: How a query is executed based on the recommended value. If this field is blank, OBIEE executes the query.
  • Function: The database capability.
Feature Default Value Recommended Value Action Function/Operator

CALENDAR_EXTRACT_DAY_OF_QUARTER_SUPPORTED

 OFF

OFF

DAY_OF_QUARTER()

CALENDAR_EXTRACT_DAY_OF_WEEK_SUPPORTED

 ON

ON

Push down to Vertica

DAY_OF_WEEK()

CALENDAR_EXTRACT_DAY_OF_YEAR_SUPPORTED

 ON

ON

DAY_OF_YEAR()

CALENDAR_EXTRACT_DAY_SUPPORTED

 ON

ON/OFF

DAY()

CALENDAR_EXTRACT_HOUR_SUPPORTED

 ON

ON

Push down to Vertica

HOUR()

CALENDAR_EXTRACT_MINUTE_SUPPORTED

 ON

ON

Push down to Vertica

MINUTE()

CALENDAR_EXTRACT_MONTH_OF_QUARTER_SUPPORTED

 OFF

OFF

MONTH_OF_QUARTER()

CALENDAR_EXTRACT_MONTH_SUPPORTED

 ON

ON

Push down to Vertica

MONTH()

CALENDAR_EXTRACT_QUARTER_OF_YEAR_SUPPORTED

 OFF

ON

Push down to Vertica

QUARTER_OF_YEAR()

CALENDAR_EXTRACT_SECOND_SUPPORTED

 OFF

ON

Push down to Vertica

SECOND()

CALENDAR_EXTRACT_SHORT_NAME_OF_MONTH_SUPPORTED

 OFF

ON/OFF

MONTHNAME()

CALENDAR_EXTRACT_WEEK_OF_QUARTER_SUPPORTED

 OFF

OFF

WEEK_OF_QUARTER()

CALENDAR_EXTRACT_WEEK_OF_YEAR_SUPPORTED

 ON

ON

Push down to Vertica

WEEK_OF_YEAR()

CALENDAR_EXTRACT_YEAR_SUPPORTED

 ON

ON

Push down to  Vertica

YEAR()

TIMESTAMP_ADD_SUPPORTED

 ON

ON

Push down to  Vertica

TIMESTAMPADD()

TIMESTAMP_DIFF_SUPPORTED

 ON

ON

Push down to  Vertica

TIMESTAMPDIFF()

CURDATE_SUPPORTED

 ON

ON/OFF

CURDATE()

CURTIME_SUPPORTED

 ON

ON/OFF

CURTIME()

CURTIMESTAMP_SUPPORTED

 ON

ON/OFF

CURTIMESTAMP

NOW_SUPPORTED

 ON

ON/OFF

NOW()

BETWEEN_SUPPORTED

 ON

ON

Push down to  Vertica

BETWEEN

IN_SUPPORTED

 ON

ON/OFF

In

LIKE_SUPPORTED

 ON

ON

Push down to Vertica

LIKE

NULL_SUPPORTED

 ON

ON

Push down to Vertica

IS NULL

ASCII_SUPPORTED

 ON

ON

Push down to Vertica

ASCII()

BIT_LENGTH_SUPPORTED

 OFF

ON

Push down to  Vertica

BIT_LENGTH()

CHAR_SUPPORTED

 ON

ON

Push down to  Vertica

CHAR()

CHAR_LENGTH_SUPPORTED

 OFF

ON

Push down to Vertica

CHAR_LENGTH()

CONCAT_SUPPORTED

 ON

ON

Push down to  Vertica

CONCAT()

INSERT_SUPPORTED

 ON

ON

Push down to  Vertica

INSERT()

LEFT_SUPPORTED

 ON

ON

Push down to Vertica

LEFT()

LENGTH_SUPPORTED

 ON

ON

Push down to  Vertica

LENGTH()

LOCATE_SUPPORTED

 OFF

OFF

LOCATE()

LOWER_SUPPORTED

 ON

ON

Push down to Vertica

LOWER()

OCTET_LENGTH_SUPPORTED

 OFF

ON

Push down to  Vertica

OCTET_LENGTH()

POSITION_SUPPORTED

 OFF

ON

Push down to  Vertica

POSITION()

REPEAT_SUPPORTED

 ON

ON

Push down to  Vertica

REPEAT()

REPLACE_SUPPORTED

 ON

ON

Push down to Vertica

REPLACE()

RIGHT_SUPPORTED

 ON

ON

Push down to  Vertica

RIGHT()

SPACE_SUPPORTED

 ON

ON

Push down to Vertica

SPACE()

SUBSTRING_SUPPORTED

 ON

ON

Push down to  Vertica

SUBSTRING()

TRIM_SUPPORTED

 OFF

ON

Push down to  Vertica

TRIMBOTH()

TRIM_SUPPORTED

 OFF

ON

Push down to  Vertica

TRIMLEADING()

TRIM_SUPPORTED

 OFF

ON

Push down to  Vertica

TRIMTRAILING()

UPPER_SUPPORTED

 ON

ON

Push down to Vertica

UPPER()

CAST_SUPPORTED

 OFF

ON/OFF

CAST()

IFNULL_SUPPORTED

 ON

ON/OFF

IFNULL()

DATE_TIME_LITERAL_SUPPORTED

 OFF

ON

Push down to Vertica

TO_DATETIME

BOTTOMN_SUPPORTED

 OFF

ON/OFF

BOTTOMN

MOVING_AVG_SUPPORTED

 OFF

ON/OFF

MAVG

MOVING_SUM_SUPPORTED

 OFF

ON/OFF

MSUM

NTILE_SUPPORTED

 OFF

ON/OFF

NTILE

PERCENTILE_SUPPORTED

 OFF

ON/OFF

PERCENTILE

RANK_SUPPORTED

 OFF

ON/OFF

RANK

RUNNING_COUNT_SUPPORTED

 OFF

ON/OFF

RCOUNT

RUNNING_MAX_SUPPORTED

 OFF

ON/OFF

RMAX

RUNNING_MIN_SUPPORTED

 OFF

ON/OFF

RMIN

RUNNING_SUM_SUPPORTED

 OFF

ON/OFF

RSUM

TOPN_SUPPORTED

 OFF

ON/OFF

TOPN

ABS_SUPPORTED

 ON

ON

Push down to Vertica

ABS()

ACOS_SUPPORTED

 ON

ON

Push down to  Vertica

ACOS()

ASIN_SUPPORTED

 ON

ON

Push down to  Vertica

ASIN()

ATAN_SUPPORTED

 ON

ON

Push down to Vertica

ATAN()

ATAN2_SUPPORTED

 ON

ON

Push down to  Vertica

ATAN2()

CEILING_SUPPORTED

 ON

ON/OFF

CEILING

COS_SUPPORTED

 ON

ON

Push down to  Vertica

COS

COT_SUPPORTED

 ON

ON

Push down to  Vertica

COT

DEGREES_SUPPORTED

 ON

ON

Push down to  Vertica

DEGREES

EXP_SUPPORTED

 ON

ON

Push down to Vertica

EXP

FLOOR_SUPPORTED

 ON

ON

Push down to  Vertica

FLOOR

LOG_SUPPORTED

 ON

ON

Push down to  Vertica

LOG

LOG10_SUPPORTED

 ON

ON

Push down to Vertica

LOG10

MOD_SUPPORTED

  OFF

ON

Push down to  Vertica

MOD

PI_SUPPORTED

 ON

ON

Push down to Vertica

PI

POWER_SUPPORTED

 ON

ON

Push down to Vertica

POWER

RADIANS_SUPPORTED

 ON

ON

Push down to Vertica

RADIANS

RAND_SUPPORTED

  OFF

ON

Push down to  Vertica

RAND

ROUND_SUPPORTED

 ON

ON

Push down to  Vertica

ROUND

SIGN_SUPPORTED

 ON

ON

Push down to Vertica

SIGN

SIN_SUPPORTED

 ON

ON

Push down to  Vertica

SIN

SQRT_SUPPORTED

 ON

ON

Push down to  Vertica

SQRT

TAN_SUPPORTED

 ON

ON

Push down to  Vertica

TAN

TRUNCATE_SUPPORTED

 ON

ON

Push down to  Vertica

TRUNCATE (TRUNC)

DATABASE_SUPPORTED

 OFF

ON/OFF

DATABASE()

USER_SUPPORTED

 OFF

ON/OFF

USER()

DATE_LITERAL_SUPPORTED

 ON

ON

Push down to  Vertica

DATE

TIME_LITERAL_SUPPORTED

 ON

ON

Push down to  Vertica

TIME

TIME_LITERAL_SUPPORTED

 ON

ON

Push down to  Vertica

TIMESTAMP

SUM_SUPPORTED

 ON

ON

Push down to  Vertica

SUM()

AVG_SUPPORTED

 ON

ON

Push down to  Vertica

AVG()

COUNT_SUPPORTED

 ON

ON

Push down to  Vertica

COUNT()

MIN_SUPPORTED

 ON

ON

Push down to  Vertica

MIN()

MAX_SUPPORTED

 ON

ON

Push down to Vertica

MAX()

COUNT_DISTINCT_SUPPORTED

 ON

ON

Push down to  Vertica

COUNT DISTINCT

FIRST_SUPPORTED

 OFF

ON

FIRST()

LAST_SUPPORTED

 OFF

ON

Push down to Vertica

LAST_VALUE()

MEDIAN_SUPPORTED

 OFF

ON

Push down to  Vertica

MEDIAN()

STDDEV_SAMP_SUPPORTED

 OFF

OFF

 STDDEV()

STDDEV_POP_SUPPORTED

 OFF

ON

Push down to Vertica

STDDEV_POP()

LTRIM_SUPPORTED

 ON

ON

LTRIM()

RTRIM_SUPPORTED

 ON

ON

RTRIM()

ORDERBY_SUPPORTED

 ON

ON/OFF

ORDER BY

GROUP_BY_SUPPORTED

 ON

ON

Push down to  Vertica

GROUP BY

COUNT_STAR_SUPPORTED

 ON

OFF

COUNT(*)

AND_SUPPORTED

 ON

ON

Push down to Vertica

AND

OR_SUPPORTED

 ON

ON

Push down to Vertica

OR

NOT_SUPPORTED

 ON

ON/OFF

NOT

GREATER_THAN_SUPPORTED

 ON

ON

Push down to Vertica

LESS_THAN_SUPPORTED

 ON

ON

Push down to Vertica

LESS_EQUAL_THAN_SUPPORTED

 ON

ON/OFF

<=

GREATER_EQUAL_THAN_SUPPORTED

 ON

ON/OFF

>=

NOT_EQUAL_SUPPORTED

 ON

ON

Push down to  Vertica

<> 

EQUALITY_SUPPORTED

 ON

ON

Push down to  Vertica

"="

ADD_SUPPORTED

 ON

ON/OFF

+'

SUBTRACT_SUPPORTED

 ON

ON/OFF

-'

MULTIPLY_SUPPORTED

 ON

ON/OFF

*'

DIVIDE_SUPPORTED

 ON

ON/OFF

/'

INTEGER_LITERAL_SUPPORTED

 ON

ON

Push down to  Vertica

INT literals (1, +255)

STRING_LITERAL_SUPPORTED

 ON

ON

Push down to  Vertica

String literal "hello"

CREATE_VIEW_SUPPORTED

 ON

ON

Push down to Vertica

CREATE VIEW

UNION_SUPPORTED

 ON

ON

Push down to Vertica

UNION

UNION_ALL_SUPPORTED

 ON

ON

Push down to  Vertica

UNION ALL

INTERSECT_SUPPORTED

 OFF

ON

Push down to  Vertica

INTERSECT

INTERSECT_ALL_SUPPORTED

 OFF

ON

Push down to Vertica

INTERSECT ALL

EXCEPT_SUPPORTED

 OFF

ON

Push down to Vertica

EXCEPT

EXCEPT_ALL_SUPPORTED

 OFF

ON

Push down to Vertica

EXCEPT ALL

GROUP_BY_EXPR_SUPPORTED

 ON

ON

Push down to  Vertica

GROUP BY Expression

HAVING_SUPPORTED

 OFF

ON

HAVING clause

UNARY_MINUS_SUPPORTED

 ON

ON

Push down to Vertica

(-1)

QUANTIFIED_SUBQUERY_SUPPORTED

 OFF

ON

WHERE NOT IN ….

IN_SUBQUERY_SUPPORTED

 OFF

ON

WHERE  value…IN..

EXISTS_SUBQUERY_SUPPORTED

 ON

ON

Push down to Vertica

WHERE EXISTS

LIKE_ESCAPE_CLAUSE_SUPPORTED

 OFF

ON

LIKE with escape clause

DISTINCT_SUPPORTED

 ON

ON

Push down to Vertica

DISTINCT keyword

NULL_VALUES_SORT_FIRST

 OFF

ON

ORDER BY NULLS FIRST or LAST

EXPRESSIONS_IN_ORDERBY_SUPPORTED

 OFF

ON

CASE expression with ORDER BY

ORDER_BY_COLUMNS_IN_SELECT_SUPPORTED

 OFF

ON/OFF

ORDER BY columns in SELECT

CREATE_AS_SELECT_SUPPORTED

 OFF

ON/OFF

STDDEV_POP_DISTINCT_SUPPORTED

 OFF

ON/OFF

STDDEV_POP(DISTINCT ….)

STDDEV_SAMP_DISTINCT_SUPPORTED

 OFF

ON/OFF

STDDEV_SAMP(DISTINCT ….)

SQL89_COLUMN_ALIAS_COnFORMANCE_SUPPORTED

 OFF

ON/OFF

SELECT column AS…

CONVERT_SUPPORTED

 OFF

ON/OFF

CONVERT()

IFNULL_SUPPORTED

 OFF

OFF

IFNULL()

LEFT_OUTER_JOIN_SUPPORTED

 ON

ON

Push down to Vertica

Left Outer Join

RIGHT_OUTER_JOIN_SUPPORTED

 OFF

ON/OFF

Right Outer Join

FULL_OUTER_JOIN_SUPPORTED

 OFF

ON

Push down to  Vertica

Full Outer Join

NESTED_OUTER_JOIN_SUPPORTED

 OFF

ON

Push down to  Vertica

Query having outer and inner joins among various tables

CASE_IF_SUPPORTED

 ON

ON

CASE IF ..

CASE_LOOK_UP_SUPPORTED

 ON

ON

CASE

COMPARISON_SUBQUERY_SUPPORTED

 OFF

ON

Subqueries in comparison

INTERVAL_LITERAL_SUPPORTED

 OFF

ON

INTERVAL '123' YEAR(3)

PREDICATE_SCALAR_SUBQUERY_SUPPORTED

 OFF

ON

Scalar subquery

DERIVED_TABLES_SUPPORTED

 ON

ON

Push down to  Vertica

Table formed from SELECT query

INSERT_SELECT_SUPPORTED

 ON

ON/OFF

PARAMETERS_IN_HAVING_CLAUSE_SUPPORTED

 OFF

ON/OFF

HAVING clause is not pushed down to Vertica. OBIEE does not push down the HAVING_SUPPORTED property to Vertica.

VALUE_SUBQUERY_SUPPORTED

 OFF

ON/OFF

Value from a subquery

CORRELATED_SUBQUERY_SUPPORTED

 OFF

ON/OFF

Value from a subquery input for outer query

Optimized Settings with OBIEE

Configure your database feature settings in OBIEE for optimal efficiency when connecting to Vertica. If you experience issues with parallel execution of OBIEE queries in Vertica, use the following workaround. These changes are specific to the Progress DataDirect Driver Manager that is bundled with OBIEE:

  1. Log on to the machine where OBIEE is installed.
  2. Navigate to the lib directory where the Progress DataDirect Driver Manager is installed. For this document: /home/oracle/middlewarehome/Oracle_BI1/common/ODBC/Merant/7.1.6/lib.
  3. Rename the file odbccurs.so to odbccurs.so.OLD.
  4. Navigate to the directory where opmnctl is located.
  5. Execute the ./opmnctl stopall command. 
  6. After the process shuts down, execute the ./opmnctl startall command. 
  7. Run the dashboard using OBIEE to verify that the queries are executed in parallel.
  8. Run the following command on the OBIEE environmentl. This will make sure that the “cursor” library is not loaded. 
lsof –u oracle | grep odbccur

For More Information

For More Information About… See…

OBIEE

http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/overview/index.html

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: