executed webi query and records retrived and time

[pradeep_putty]

Hi,

we want to see what is the query that a webi report

hit the database ,and how much time it took to

execute query and how many records it brought back from

db side.

In which logs will i find this info.

i kept the log level to very high and i can not see a query in

connection or webi server logs.

Thanks ,

Pradeep.

[pradeep_putty]

sorry we are on 4.0 sp 2.9

yes i have already configured auditing ,

anyway i have found a KB article saying that an adapt has been raised for the same.

1708193 - Select statement is not captured in Auditing

even though i get the select from auditing it wont help for us to see how those row count restrictions are working when applied at different level.

[pradeep_putty]

Thanks guys for your replies,

i am sorry for not being clear regarding my question,

i want to know which logs can i find which query is hitting the db,time and records retrieved,

either webi logs or connection server logs or APS logs or nay temp data or only in audit data,

i am sure it will be in logs some where.

we want to know this because we want to see how our restrictions are working,

we can keep restrictions at the direct connection parameters file or

specific db file or at universe level or at webi report level.

so we want to see how these mechanism works.

this even gets interesting when we have multiple sql's involved when context comes into picture.

[former_member184594]

Okay then,

You should enable Auditing first and then create an universe and reports. Check the links below for enabling Audting database and creating universe

http://www.forumtopics.com/busobj/viewtopic.php?t=134455

http://scn.sap.com/people/adrian.westmoreland/blog/2009/06/28/the-bi-platform-audit-database--part-1

http://scn.sap.com/people/rajasekar.nonburaj2/blog/2009/06/25/business-objects-auditing-in-xir3

http://it.toolbox.com/blogs/rajasekar/business-objects-auditing-in-boxir3-33303

http://sapbogroup.blogspot.com/2010/04/bo-xi-audit-db-setup.html

[pradeep_putty]

Thanks Zahid,

but,

=DataProviderSQL()

=DataProviderType()

are not returning any data. they are returning #error ,did you test on your side on which patch version you are.

[Former Member]

Hi Pradeep,

1) In order to see the SQL click SQL  on the query toolbar to display the "SQL Viewer" dialog box.

2) The average time to execute the query can be found from the average runtime of the report from the activity universe. In order to do that you have to enable auditing on the particular processing server involved in this workflow, and the data from the auditing will be available on the auditing database. The activity universe will connect to the auditing database to fetch the data. Please not that if you are using BI 4.0 you have to create your own activity universe, but for XI R 3.1 you have a readymade activity universe.

3) In order to find the number of records, you can use the web intelligence function to count the rows in the report.

Hope this helps.

Thanks

Arijit

[former_member184594]

1. If you want to see the SQL returned by the query in your report use this formula

=DataProviderSQL()

2. If you want to see the execution time in your report use this formula

=LastExecutionDuration()

3. If you want to see the number of records retrieved from database in your report use this formula

=NumberOfRows()

[Henry_Banks]

Hi,

here are a few others:

=PromptSummary()

=QuerySummary()

=DataProviderSQL()

=LastExecutionDuration()

.. etc

some good features in the Formula Editor

Regards,

H

[pradeep_putty]

Thanks Zahid/Henry,

but,

=DataProviderSQL()

=DataProviderType()

are not returning any data. they are returning #error ,did you test on your side on which patch version you are.

[kalpesh_pandey]

I think there was an option to see the query in the Webi reports. When you design the report then this option is available.  We can run that query inside DB directly and see how many records it is bringing back.

So try to edit the report and then look into the query.

Thanks,