emergingbytes.com Technology blog for data warehousing, business intelligence, iPhone Apps Development, Android Apps development and facebook applications

Teradata Query Banding In SAP Business Objects Information Design Tool

Usually customers require tracking and auditing of users and queries they are running on DWH/Teradata. There is no one way of tracking queries run by BI application users. An application user is not necessarily a Teradata user, therefore,  query band is a useful feature for tracking logons and queries generated from BO and BO users. Queries are logged in DBQL along with necessary information. For instance:

• Application (Business Objects) username
• Universe name
• Document name
• etc (further details can be found in SAP BO documentation)

This is achieved using query banding feature provided in Teradata and is exploited in SAP Business Objects using 'BEGIN_SQL' parameter for SQL Generation. An example of BEIGN_SQL string is as follows:

SET QUERY_BAND='USER='@Variable('BOUSER');Document='@Variable('DPNAME')';' for transaction;

As per SAP documentation, it should work fine and it does work fine if you are using it in Universe Design Tool. But when used in Information Design Tool, it returns an error due to problem with SQL generation. Issue is due to @Variable function which returns a value enclosed in single quotes. For some reason, this is taken care of in Universe Design Tool but since Information Design Tool is new, it causes SQL error on Teradata.

Until SAP development team fixes this issue, a workaround can be used for query banding in Information Design Tool and that is to call a Teradata Stored Procedure in BEGIN_SQL and pass necessary parameters to it as inputs. Query band can then be set within the procedure.

For further details or queries, leave comments and we will reply.