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, and BI 4.1.

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.

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.

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 about ConnectInit and BEGIN_SQL.

For more 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.