NOTE: I wrote this post in 2008 for XI R2 but it has been updated over time for XI 3.x and BI 4.0.
Here’s a useful trick that can help a database administrator identify the source of Business Objects queries. Use the END_SQL universe parameter to append a comment to SQL statements. The comment can include @Variable functions from the universe, making the comment’s behavior dynamic.
For classic universes built with the Universe Design Tool (formerly known as Universe Designer or just Designer), you can set universe parameters by clicking File -> Parameters from the menu or click the Parameters button on the toolbar. Then navigate to the Parameter tab.
If you are using XI R2, Business Objects can dynamically determine the user name and document name using the @Variable function.
/* Hard coded Universe Name - @Variable('BOUSER') - @Variable('DOCNAME')*/
XI 3.0 introduced several new @Variables, so you can get a bit fancier and use an @Variable for the universe name.
/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */
The Information Design Tool introduced with BI 4.0 also supports universe parameters like END_SQL. To set, click on the “Properties” tab of the Data Foundation Layer (*.dfx file) and click the “Parameters” button.
For additional information about END_SQL, check out this thread on the BusinessObjects Board (BOB).
One problem we have discovered on this technique with Teradata… the driver takes out comments before sending the SQL to the database. So much to my chagrin, our END_SQL documentation never makes it to the DBA team…
Dave did you try double commenting?
i.e /* /* …… *\ *\
or with Asci functions for the comments
i.e /* ASCI(…) …… ASCI(…) *\
regards,
Hakan
Another trick we’ve used is to include the following on a WHERE clause:
@VARIABLE(‘BOUSER’)=@VARIABLE(‘BOUSER’)
Puts the userid in the resulting SQL (no comment needed) … and always evaluates to TRUE
Chris, thanks for sharing. Your approach is potentially better than mine, as some database platforms strip out the comments in their management tools.
For Teradata, it’s better to use the BEGIN_SQL parameter, along with the Teradata Query Banding feature. In fact, Teradata can use this feature to implement Database security.
For the solution
@VARIABLE(‘BOUSER’)=@VARIABLE(‘BOUSER’)
how does the DBA manage to parse that section from the entire SQL statement?
Thanks,
Mike