Why do we need Query Banding?

By enabling Database Query Log (DBQL) and installed the performance data collection and reporting (PDCR) database in all Teradata environments the organization will have plethora of log information, statistical information and other audit information.
The DBQL and PDCR log and store historical data about queries including query duration, CPU consumption and other performance metrics. The data offers information to calculate suspect query indicators such as large-table scans, skewing and highly consumptive joins. In addition, the data provides diagnostic metrics to help tune applications, monitor performance, manage capacity and operate the Teradata system at peak efficiency.
To fully leverage DBQL and PDCR capabilities, it is imperative for the BI group to enable query banding in universe, which will provide us a valuable tool to band each BusinessObject query submitting to Teradata with a number of attributes and values that provide detailed information about the query. The information allows us to effectively tune BusinessObjetcs applications by identifying and tuning target reports and proactively preventing report performance problems.
Query banding is especially valuable for our BusinessObjects applications since we use the generic accounts to access Teradata database. Query banding will enable us to identify the unique attributes of each request.

How to set Query Banding in universe parameter?

Teeadata database supports passing of parameters before the SELECT statement. The BEGIN_SQL parameter allows us to prefix SQL statements with the query banding values each time a SQL statement is generated. Below are steps to set up the query banding in universe:

• Start the Universe Designer
• Open your Universe (for existing Universes, please import them first)
• Click on File menu -> Parameters…
• Click on Parameter tab
• In the "Parameter" list, click on BEGIN_SQL parameter
If does not exist, add one.



• Enter value for BEGIN_SQL:
Copy following name/value pairs into the Value box:

SET QUERY_BAND = 'USER = '@Variable('BOUSER')'; DOCNAME = '@Variable('DOCNAME')'; DPNAME = '@Variable('DPNAME')'; DPTYPE = '@Variable('DPTYPE')'; UNIVERSE = '@Variable('UNVNAME')'; UNIVERSEID = '@Variable('UNVID')';' FOR TRANSACTION;

Variable description:
a) BOUSER - user login
b) DOCNAME - the name of the document
c) DPNAME - the name of the Data
d) DPTYPE - the type of the Data
e) UNVNAME - the name of the universe
f) UNVID - the ID of the universe used

• Click Replace



• Click OK
• Save the Universe
• Export the Universe to CMS