Identifying SAP BusinessObjects queries using END_SQL

NOTE: I originally wrote this article about XI R2 in 2008 but I have since updated it to include information about XI 3.x, BI 4.0, BI 4.1, and BI 4.2.

Here’s a useful trick that can help both SAP BusinessObjects universe designers and database administrators find the true origin of queries. Using this technique, we can identify which report, universe and user is generating a potentially problematic SQL statement and take corrective action. The END_SQL universe parameter is typically used to allow universe designers to append additional SQL such as database hints to SQL statements. But it can also be used to add a seemingly benign SQL comment. Because this comment can use @Variable functions from the universe, its contents become dynamic.

For classic universes built with the Universe Design Tool (formerly known as Universe Designer or just Designer), set universe parameters by choosing File -> Parameters from the menu or click the Parameters button on the toolbar. Next, navigate to the Parameter tab.

END_SQL parameter in Universe Design Tool

If you are using SAP BusinessObjects Enterprise XI R2, the semantic layer can dynamically identify the user name and document name using the @Variable function.

/* Hard coded Universe Name - @Variable('BOUSER') - @Variable('DOCNAME')*/

SAP BusinessObjects Enterprise XI 3.0 introduced several new @Variables, so you can get a bit fancier (see related article, Using @Variable Functions in the Universe) and use an @Variable for the universe name. Now the entire END_SQL expression is dynamic.

/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */

The Information Design Tool introduced with SAP BusinessObjects Business Intelligence 4.0 (BI4) 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.

Information Design Tool END_SQL

Keep in mind that certain database platforms such as Teradata strip out comments, negating the value of this trick and preventing a DBA from seeing the information we wish to share. If your organization uses Teradata, check out this helpful article from Dave Rathbun or this SAP Community Wiki from Jacqueline Rahn about ConnectInit and BEGIN_SQL.

For more information about END_SQL, check out this thread on the BusinessObjects Board (BOB).

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting.

11 thoughts on “Identifying SAP BusinessObjects queries using END_SQL

  1. 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…

  2. Dave did you try double commenting?

    i.e /* /* …… * *

    or with Asci functions for the comments

    i.e /* ASCI(…) …… ASCI(…) *

    regards,

    Hakan

  3. 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

    1. Chris, thanks for sharing. Your approach is potentially better than mine, as some database platforms strip out the comments in their management tools.

  4. 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.

  5. For the solution
    @VARIABLE(‘BOUSER’)=@VARIABLE(‘BOUSER’)

    how does the DBA manage to parse that section from the entire SQL statement?

    Thanks,
    Mike

    1. Not familiar with Netezza. Does it make error if END_SQL is used? Or does Netezza not pass the END_SQL SQL comment so a DBA can see it?

Comments are closed.