SQL to Query Report Library - OTBI Catalog

Question

Summary

Looking for SQL or tables to report a list of all reports, properties and folder location

Content

Does anyone have a SQL or know the tables that would contain the information found in OTBI - specifically looking for a way to list out all the reports names, description, path and other properties associated with our report library

Thank you.

Lisa G. · Answer

Thank you everyone for the feedback. I appreciate it.

[Deleted User] · Answer

+1 to Nathan's "Your OTBI does come with a web services .../analytics/saw.dll?wsdl so you could use that to write some code to read the catalog."

OTBI doesn't provide the same capabilities as a real on-premises installation of OBIEE or OAS or a proper cloud distribution like OAC.

Nathan CCC · Answer

Hi Lisa,

Usage tracking will tell you what your users did use. With an "on-premise" install you can set up usage tracking so that the metadata repository database (rpd) will write to a database table every logical and physical query. However with OTBI in your cloud application I am not aware that usage tracking is turned on by default.

The catalog is not stored in a table. The catalog is a set of folders and files on the application server.  With an "on-premise" install you can use the client tool called Catalog Manager to read and report on your catalog to, for example, get a list of what is in the catalog.  But again with with OTBI in your cloud application I am not aware that you can connect to the catalog online using catalog manger. I assume the connection is blocked by the network.

Your OTBI does come with a web services .../analytics/saw.dll?wsdl so you could use that to write some code to read the catalog.

Alternatively you could archive the catalog from the cloud then unarchive it to a local on premise version of obi 11.1.1.9 so that you can use catalog manager.

Also if you are only interested in the "reports" that have been configured as a scheduled service? then you could get a list of those from the setup table in your oracle cloud application schema. I think this is table REQUEST_HISTORY. There is also a table that tells you which dashboards and reports may have been mapped to the analytics and reports tab that you get on some of the work areas. Sorry I cannot remember the name of that table right now.