Oracle Transactional Business Intelligence

SQL to Query Report Library - OTBI Catalog

Received Response
711
Views
4
Comments

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. 

Answers

  • FPonte
    FPonte ✭✭✭✭✭

    Hi Lisa.

    As far as I know that is not possible.

    You may do usage tracking.

    Oracle Support Document 1532638.1 (What steps need to be performed to implement Usage Tracking of report and analyses execution for OTBI/OBIA and BI Publisher) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=1532638.1

    Cheers

    Fernando.

     

     

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    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.   

  • +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.

     

  • Lisa G.
    Lisa G. ✭✭✭

    Thank you everyone for the feedback. I appreciate it.