Identifying SAP BusinessObjects queries using END_SQL

NOTE: I originally wrote this article for XI R2 in 2008 but it has been updated to include information about XI 3.x and BI 4.0.

Here’s a useful trick that can help both SAP BusinessObjects universe designers and database administrators identify the true origin of queries. Using this technique, we can now determine 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.  But this comment can use @Variable functions from the universe, making the comment’s value 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 SAP BusinessObjects Enterprise XI R2, the semantic layer can dynamically determine 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 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.

Keep in mind that certain database platforms such as Teradata strip out comments, preventing a DBA from seeing the information we are intending to share.  If your organization uses Teradata, check out this helpful article from Dave Rathbun about ConnectInit and BEGIN_SQL.

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

About Dallas Marks

As a business intelligence architect, author, and trainer, I help organizations across the United States harness the power of business intelligence, primarily (but not exclusively) using SAP BusinessObjects products. I prefer piano keyboards instead of computer keyboards when not blogging or tweeting about business intelligence.