CustomSQL

[JohnJustus]

Hi,

I am developing a Webi and writing some Custom SQL on the SQL Viewer.
When I use any new objects to Result objects pane, my old custom SQL got reverted back to SQL generated by the query.
How can I avoid this, because I will be dragging often some objects in the query panel and every time I do that I have to rewrite the code again.Copy and paste also did not work. Any workaround on this.
Thanks..

[vigi_guna]

When you add a new object to your "Query result" pane, the custom SQL has to be changed. Because, the number of columns returned (and data type) must match with the number of objects in the query result (and its data type). So, it is mandatory to change the query based on objects added/removed from the query results. However, I am not aware of "locking" the custom SQL.

[weaver]

You are probably best to use the ViewSQL feature and make a copy of the SQL before you start. You can then add a column in a text editor or query tool, test your code, then paste it back into Webi's SQL window.

Another hint is to rename the query in Webi. Instead of having it called Query 1 or something like Sales ... rename it DoNotChange. Then other developers will at least know that there is custom SQL behind the definition.

[JohnJustus]

Ok. Thats a useful information about renaming the query.
Perhaps, Do you know what would be disadvantages of using Custom SQL in the Webi reports? Have you faced any issues after deployment and if we need to modify the report later what challenges can be faced?

Thanks..

[Mak 1]

[JohnJustus]

Hi Mak,
Thanks for the information. I am very new to this CustomSQL stuffs on Webi.
What you mean by Custom SQL has no place in a, production, Business Objects environment, IMO.?

Thanks..

[Andreas]

Typically, a WebIntelligence document is based on a universe, the universe is essentially a SQL generator. If the universe changes those changes propagate to the Webi documents automatically.

If you override and hardcode the SQL code you are setting yourself up for a maintenance nightmare. Instead of handling everything centrally at the universe level you now have to deal with each single data provider in each single Webi document for maintenance. For esample a table name changes? Well, have fun changing each hardcoded-SQL data provider you ever created.. Your documentation is always up-to-date, right?

You might as well use a different tool alltogether, as one of the main benefits of WebIntelligence is the ease of use and ease of maintenance provided by the semantical layer (=universes).

[REB01]

[JohnJustus]

yes I agree with Andreas and mak. I will think about fixing the issue in the universe level itself instead of fixing it in the report level (using Custom SQL)
But in regards to production, I don't think the user can modify the report, at least in our environment. Is there any other scenario in production?

Thanks..

[Mak 1]

[weaver]

Yep CustomSQL is evil. In 2000+ reports in our current deployment there would be less than 10 that have needed it.

One other issue is in migrating. If you migrate from one version of BO to another your customSQL might break. So you might be OK now, but not for the next release.

Another tip that I sometimes use. If you have CustomSQL, copy it into a text document. Then load that text document into Infoview. You will now have a Webi doc called ABC.wid and a text doc called ABC_sql.txt

At least then if someone innocently edits your report, you can at least get back to the old SQL.

[JPetlev]

We actually had a need a while ago with XIR2 to use Custom SQL... If I remember right, it was two different occasions.. once was because there was a function that BO didn't support properly via the Universe (Though I suspect it was actually a faulty universe design), and the other had something to do with stored procedures which are NOW supported in Universe directly.

What we did to try to 'prevent' any issues.. is we had an object created in the universe that was called "!!CUSTOM SQL USED!!" = "Custom SQL In Use".

Our best-practice training basically told users that should they ever find the need to use Custom SQL, to use that object and make sure it appears FIRST in the Result pane objects. That way any subsequent person would be able to easily identify that custom sql was used, and ideally not overwrite it by accident.

Of course that doesn't solve the main issues brought up here of support and whatnot.. but if you find yourself having to use Custom SQL AND allowing users to change queries, then it is a good stop-gap measure to at least identify when it happens.


EDIT:
I forgot to mention that today I do use Custom SQL, but only in the universe LOV's at times... when I do that, I always place an object both above and below that object in the list that specifies "Custom SQL in Object ABOVE/BELOW" which is 'hidden' so it is only shown to developers. To store the SQL I placed it in one of the information tech tabs on the object itself as a backup. It works nicely.

[Mak 1]

Some useful tips there, guys, when dealing with the custom SQL "problem".

[JohnJustus]

Hi weaver,

Anyway I had to go with Custom SQL because of time limitations and so on.. The report will go production soon.
Instead of having a copy of the custom SQL, how about having a copy of the report itself. Do you or any one see any disadvantages in this case.
Thanks everyone for their tips.
Thanks..