Michael wrote…
We are trying to get the SQL from a BO query running in the Document Agent under BO 4.1.2. …
Michael,
David Rathbun from Integra Solutions wrote a very good explanation of this issue
so I thought I would include it here again.
David,
I hope you don’t mind if I pass on your comments.
Unfortunately, 4.x DAS does not work the same way as the 3.1 batch feature. Under DAS, the SQL is not submitted - the document is. And since you can’t find the SQL in the document, there is no way to edit or otherwise process it
prior to running the schedule. (There may be a way with scripting, but I am not familiar with all of the scripting features.)
This is a major departure from the way batch used to work. Under 3.1 batch processing, only the SQL was sent to the server. It required a UNIX server to
process the scripts, so other platforms were not supported. (Although I imagine you could work up a substitute.) The data was captured from the SQL (via a CREATE TABLE AS… statement) and stored in a temporary database table.
Importing the results simply was a matter of selecting rows from the temporary
table and downloading them into the report on the client workstation.
Now to Document Agent… instead of sending SQL you send the complete document. There is a server configured with DAS… which is essentially a big
schedule monitor (like cron). When a document is ready to run, DAS spawns a copy of the complete client BusinessObjects software. In other words, instead
of just getting the data (like batch) the entire report is refreshed. It’s as of you
hired a person to sit at a console and click on the Refresh button at scheduled
intervals and then do a Send To User operation when the report finished. That’s it.
One side problem is this: under 3.1 Macro Objects could be processed by the Batch system. Since the Macro Object was evaluated by the client and just the
SQL code sent to the batch processor, there were no problems. Using User Defined Objects under 4.x this is no longer possible. When the report is sent
to DAS, unless the UDO file is already present on that server the report will fail.
The bottom line is this: to optimize your SQL, you will need to evaluate tricks that can be done by the universe Designer. Or, you could learn how to update the SQL using the scripting language and send the document out with a custom script. Good luck with either method that you choose!
Dave Rathbun Integra Solutions
Listserv Archives (BOB member since 2002-06-25)