Teradata Query Band Configuration
OBIEE can be set up for Teradata Query Banding. To enable Query Banding, the appropriate SQL
is added as a connection script that is executed before each query. The connection script is found in the
connection pool properties for the Teradata database.
Tips/Traps:
·
The query banding must be set for the session.
·
Different connection pools can have different
Query Band settings. This can be useful
when setting different priorities with TASM.
·
OBIEE uses a setting in the connection pool to
control the number of queries simultaneously executing on Teradata. This setting can be bypassed if TASM and
query banding are used.
Teradata Trusted Session Configuration
OBIEE can also be set up to use Trusted Sessions (TS). The same technique that is used for Query
Banding is used for Trusted Sessions.
SQL is sent to Teradata using a script that is executed before each
query.
Tips/Traps:
·
The Proxy user and proxy role used for Trusted Sessions
is not built into OBIEE. To get around
this limitation a Table can be built that holds the proxy user and role. OBIEE variables can then be used to pull the
user and role based on the OBIEE login.
Write-Back Configuration
OBIEE has the capability to write data to the Teradata
database. This could be used by users
who want to do planning or budgeting on a small scale. They could use OBIEE
both as a reporting tool and also for entering sales quotas or projections
etc. In Answers, write-back is configured on a report by report
basis. The columns that are available
for write-back appear in a box format for the end user.
Tips/Traps:
·
Each connection pool name must be unique in the entire
.rpd file.
·
Make the table that write-back is enabled for “not
cacheable”. This is an attribute on the
physical property box for the table under the “general” tab.
SQL Extensions – Evaluate Function
To enable access to database functions that OBIEE does not
currently support there are 3 APIs available.
These APIs allow features and functions to be imbedded into logical SQL
and passed through to the Teradata database just as if they were built-in.
ODBC Settings
OBIEE uses ODBC to connect to the Teradata database. Teradata ODBC needs to be installed on the
same platform as your OBIEE server.
There is one setting to change when configuring the Teradata ODBC
driver. This is the Response buffer
size. Setting this to the maximum number
will help with the retrieval of large data sets. The maximum allowed is 1,048,576.
Parameters for the OBIEE server should be in sync with the settings
on Teradata
Keeping the parameters in the NQSConfig.ini file in sync
with the settings on Teradata will maximize performance.
•
NULL_VALUES_SORT_FIRST specifies if NULL values
sort before other values (ON) or after (OFF).
For Teradata, NULL_VALUES_SORT_FIRST should be set to ON.
•
LOCALE specifies the locale in which data is
returned from the server. This parameter also determines the localized names of
days and months.
•
SORT_ORDER_LOCALE is used to help determine
whether a sort can be done by the database or whether the sort must be done by
the Oracle Business Intelligence Server.
As long as the locale of the database and OBI server match, OBI will
have the database do the sorting. If
they do not match, OBI will do the sorting on the server.
Tuning Teradata for OBIEE
Tuning Basics:
•
Primary Index (PI) - Use the join access paths
to the tables if possible
•
Secondary Indexes (SI) – Use on filter columns
•
Statistics collected and kept up-to-date for PI,
SI, join, filter columns and indexes
Advanced Techniques:
•
Semantic layer views
>
Avoid outer joins
>
Avoid functions in join columns
>
Avoid mismatch of data type in join columns
•
Use Partitioned Primary Indexes (PPI)
•
Use Aggregate Join Indexes (AJI)
