| 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
- 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:
- Log on to the machine where OBIEE is installed.
- 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. - Rename the file odbccurs.so to odbccurs.so.OLD.
- Navigate to the directory where opmnctl is located.
- Execute the
./opmnctl stopall command. - After the process shuts down, execute the
./opmnctl startallcommand. - Run the dashboard using OBIEE to verify that the queries are executed in parallel.
- 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 |
|
|
Vertica Documentation |
|
|
Big Data and Analytics Community |
|
|
|
|
|
