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, we may not have tested the specific versions you are using.
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, you can 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 |
Push down to Vertica |
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 |
|
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 |
Push down to Vertica |
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 |
|
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 |
Push down to Vertica |
FIRST_VALUE() |
|
LAST_SUPPORTED |
OFF |
ON |
Push down to Vertica |
LAST_VALUE() |
|
MEDIAN_SUPPORTED |
OFF |
ON |
Push down to Vertica |
MEDIAN() |
|
STDDEV_SAMP_SUPPORTED |
OFF |
OFF |
Push down to Vertica |
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 |
|
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 |
Optimizing Settings with OBIEE
-
If you experience issues with the data type wrapper in OBIEE where wrong values are displayed, use the following workaround. It is mainly due to the OBIEE wrapper. In the OBIEE Analytics environment:
-
Go to Criteria > right-click EntityId column> Edit Formula.
-
Click Custom Headings so that the column name remains the same.
-
We used these fields as an example to demonstrate the workaround for the data wrapper error.
-
Change the
"TRAFFICO_NON_VALORIZZATO"."EntityId"tocast(cast("TRAFFICO_NON_VALORIZZATO"."EntityId" as Numeric(9,0)) as Integer) -
Change the
"TRAFFICO_NON_VALORIZZATO"."TimeKey"tocast("TRAFFICO_NON_VALORIZZATO"."TimeKey" as Timestamp)
Similarly, apply cast function for any column showing incorrect results be it Integer, Time, Date, Timestamp and so on.
-
-
- You must always use AL32UTF8 character set when working on creating a Repository DB such as Oracle.
-
The Explain Plan feature can be used to analyze queries to Optimize result-set:
explain SELECT sales_quantity, sales_dollar_amount, transaction_type, cc_name FROM online_sales.online_sales_fact INNER JOIN online_sales.call_center_dimension ON (online_sales.online_sales_fact.call_center_key = online_sales.call_center_dimension.call_center_key AND sale_date_key = 156) ORDER BY sales_dollar_amount DESC;
Access Path: +-SORT [Cost: 263K, Rows: 5M (NO STATISTICS)] (PATH ID: 1) | Order: online_sales_fact.sales_dollar_amount DESC | +---> JOIN HASH [Cost: 16K, Rows: 5M (NO STATISTICS)] (PATH ID: 2) | | Join Cond: (online_sales_fact.call_center_key = call_center_dimension.call_center_key) | | Materialize at Output: online_sales_fact.sales_quantity, online_sales_fact.sales_dollar_amount, online_sales_fact.transaction_type | | +-- Outer -> STORAGE ACCESS for online_sales_fact [Cost: 9K, Rows: 5M (NO STATISTICS)] (PATH ID: 3) | | | Projection: online_sales.online_sales_fact_super | | | Materialize: online_sales_fact.call_center_key | | | Filter: (online_sales_fact.sale_date_key = 156) | | | Runtime Filter: (SIP1(HashJoin): online_sales_fact.call_center_key) | | +-- Inner -> STORAGE ACCESS for call_center_dimension [Cost: 10, Rows: 200 (NO STATISTICS)] (PATH ID: 4) | | | Projection: online_sales.call_center_dimension_super | | | Materialize: call_center_dimension.call_center_key, call_center_dimension.cc_name
- In order to increase the Result-Row size into the presentation Layer of OBIEE, navigate to the OBIEE Directory at the following location and edit the instanceconfig.xml file <ResultRowLimit> tag:
OBIEE Directory>Oracle_Home>user_projects>domains>bi>configconfig>fmwconfig>biconfig>OBIPS>instanceconfig.xml
