Dave's Adventures in Business Intelligence

Years ago we implemented query banding as a way to track queries being submitted from Business Objects. As I’ve mentioned we’re moving into Tableau as well now, and Tableau also supports query banding. When we started with Business Objects there were two choices: Transaction and Session. For no reason that I can remember, we opted for Transaction mode.

When we started to implement query banding for Tableau we discovered that it only worked for Session mode. That presented a problem.

Ultimately our code went from this:

some_table.user_name = GetQueryBandValue(1,'ClientUser')

to this

some_table.user_name = GetQueryBandValue(1,'ClientUser') OR some_table.user_name = GetQueryBandValue(2,'ClientUser')

The challenge was that the OR clause impacted query performance. We tried this:

some_table.user_name IN (GetQueryBandValue(1,'ClientUser'),GetQueryBandValue(2,'ClientUser'))

…but that also did not perform well. Interestingly enough we ended up with this:

some_table.user_name = GetQueryBandValue(1,'ClientUser') || GetQueryBandValue(2,'ClientUser')

Since only one of the two entries would ever have a value, using the concatenation operation || to put them both together and avoid the OR and the IN clause seemed to work well.

Now there is a better option. Based on this article, we are now implementing query banding using a zero option, as in:

some_table.user_name = GetQueryBandValue(0,'ClientUser')

There are different parameter options now that I feel certain were not available when we started or we would have been using them. According to the Teradata document linked above, the codes are:

• 0 = Return the first value found for the name=value pair. If the same name is found in both Transaction and Session then Transaction will be used
• 1 = Transaction mode
• 2 = Session mode
• 3 = Search the name=value pairs and find the item specified in the QBName input parameter

We now use mode 0 (zero) to check for both. I suspect 3 is used only when you need to override the default behavior of zero to check Transaction Mode first.