ORACLE BUSINESS INTELLIGENCE WORLD

Oracle Business Intelligence and Data Warehousing Practice Notes and Knowledge Repository

  • About Me

    Hello Friends,

    This is Santosh Kumar Gidadmani, a Business Intelligence and Data Warehouse Enthusiast passionate about blogging articles in the BI, Data warehousing, space. This is my attempt to share my experience and knowledge on Oracle BI & Data Warehousing Subjects.

  • OBIEE 11g Certified Implementation Specialist

  • Oracle Partner Network Certified Specialist

  • Visit My Profiles





  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 73 other followers

  • Blog Hits Since Nov 2010

    • 358,305 Hits
  • Live Traffic

  • Visitors Country

    Since-Mar'12Free counters!

Implementing Query Banding in OBIEE 11g using Teradata Database

Posted by Santosh Kumar Gidadmani on September 1, 2014

Query banding allows you to pass parameters into the teradata environment, which will help teradata DBAs to analyze the queries and tune the performance.

Using OBIEE connection pool, you can configure query banding and pass every query run into teradata database table called dbc.dbqlogtbl.

1. Add the below SQL script to the OBIEE Connection pool in execute before query section.

set query_band = ‘ApplicationName=OBIEE;ClientUser=Valueof(NQ_SESSION.USER);’ for Session;

You can add other requests to get more information but below is an example to get user details.

QB1

Click new and add the above script and save the RPD, restart the services.

QB2

2. Test the query banding configurations.

Firstly, make sure the OBIEE analysis server metadata is reloaded, run couple of reports.

Run the Teradata Studio or SQL Assistance and query for the below table.

select appid, username, queryband, starttime, statementtype, querytext

from dbc.dbqlogtbl where queryband is not null and appid=’nqsserver’ order by starttime desc

The query banding must be set for the session. Setting for transaction won’t work due to the way that OBIEE sends the SQL to the Teradata Database, other arguments may be added to the query band.

QB3

You can also see that the query banding tab is also added in the Teradata ViewPoint.

Screen shot of Teradata Viewpoint before configuring query banding.

QB4

After implementing query banding, a new tab is added to the query monitor screen.

QB5

Thanks,

Santosh

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: