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.
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 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).
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
Everyone,
Thanks for your comments about using BEGIN_SQL to implement Teradata query banding. I’ve ammended the article to include a link to Dave Rathbun’s article on the subject.
Regards,
Dallas
Any method to resolve this issue on Netezza?
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?