How can we retrict Long running queries to database

[Former Member]

Hi All,

I want to restrict long running queries to the database from web intelligence through designer.

Is there any setting for this on the universe level.

SQL server 2005,BO XI 3.1

Regards

Kultar

[PPaolo]

Hello,

in Designer open the Universe then choose File/Parameters. This opens the Universe parameters window.

Go to the Controls tab, you can set the max time or max result size of a query executed in WebI.

Hope that it helps

PPaolo

[Former Member]

Thanks for the reply!!

Do we have any such setting(report\universe) throgh which we can do that other then the excute time in parameters for universe.

If we can pass some type of SQL statement throgh webi\designer which can retrict the query ??

Regards

KP

[PPaolo]

Hello,

sorry but I don't understand well this second question.

If you are asking about the ability to pass some commands to the database to modify the execution of the query, we have a Universe parameter (same window as before, last tab) called "Begin_SQL".

Whatever text you put to Begin_SQL will be sent to the database before each query.

In a similar way, there is an "End_SQL" parameter where you can put text which will be sent to the DB server after the execution of the query.

E.g. in Teradata if you set Begin_SQL to

set query_band='MyUser=''@Variable('BOUser")'';' for transaction;

you will send the BO user name to the Teradata query banding feature right before the execution of the query.

PPaolo

[Former Member]

Hi,

You can define predefined filters and/or mandatory filters in the universe.

Moreover you can restrict the time, the number of rows at universe and at query level.

Last, you can also restrict the amount of data retrieved per user when applying security.

Regards,

Didier

[Former Member]

The Begin SQL has some problem in executing the set commands.

this is why i am looking for other options.

Cheers

KP

[Former Member]

Thanks for the update Didier.

Request you to send me steps throgh which we can restrict time limits at the report level.

Regards

KP

[Former Member]

Hi,

When you are editing the query in the Web Intelligence query panel, there is a preperties tab where you can set the rows limit and the time limit (see attached screenshot).

Regards

Didier