List of reports using direct database request

wfortson

Is there a way to produce a list of the reports from the catalogue that are using direct database request?

thanks

William

Gianni Ceresa

Did try by generating a report with Catalog Manager?

It probably tells you something special for analysis built on a direct database request.

As alternative, you can do a full search in the catalog for every analysis having the special XML attribute that exists only in DDR analysis: connectionPool="

Both ways should work, both are done by using Catalog Manager (the tool, not the web page)

wfortson

Thanks!!! That worked. You can create a catalog report for Analysis to get a list Names, Path, and SQL. It's in the SQL column where you can filter for "Execute Physical Connection" for the reports with direct database requests.

wfortson

On a slightly different note. I have gone through several of the reports for the Admin tool. Is there one that can show me the Physical Tables of Table Type "Select" where the SQL has been given? I want a list of all the SQL being used. We need to check for certain tables being used from time to time.

Gianni Ceresa

To be honest no idea, I really try to stay away from using a SELECT as table in the RPD because it hide the logic of things, it's "safer" to create a view in the database with the logic, a lot simpler to track the logic and audit.

If you can't get an RPD report giving you that, another way of getting there could be generate the XML version of the RPD (in a single file) and look there inside (the XML will specify everything your RPD has). You will find the tags <PhysicalTable> and there somewhere you will get the SQL query instead of the table name.

As the XML format is defined, you could easily script a parser returning you a list of physical tables in your RPD being a SELECT.

I don't have an example piece of code available, but it will not be difficult, the XML is fairly easy to read (for this need, other pieces are a bit more "exotic").

wfortson

We tried to export the RPD as XML but it generated a bunch of files. How do you do it in a single file?

Gianni Ceresa

That's one I have handy:

biserverxmlgen.sh|.cmd -R <RPD file path> -P <RPD password> -O <destination XML file>

The script is next to all other scripts (runcat etc.).

When done via the GUI it does split every object into a file and type of objects in folders: you could still do the job, but it will need to go through a bunch of files and folders. With the single file it's just easier.

Christian Berg-Oracle

https://community.oracle.com/tech/apps-infra/discussion/comment/16803923#Comment_16803923

The Query Repository tool has this simply by choosing "Type"="Select".

If your target is to automate this, then you can follow MOS Doc ID 1574480.1, section "ExecuteQuery".

Sindhu-Oracle

Hi Gianni Ceresa, i am trying to find the direct database request via the catalog manager, but unfortunately in the search criteria connectionPool=" did not result in any data. Also, checked the way wfortson mentioned, no luck there either. Could not find the sql column from the available column list at all. Please help/correct here.

Christian Berg -Oracle

@Sindhu -Oracle please open your own thread and handle incoming responses yourself. This one is 3 years old and belongs to a different user.