Store a repository of all catalog data as tables that can be queried using SQL

Marcelo Finkielsztein

Description

I suspect the following already exists and i have just missed it.

• store catalog items into SQL tables. 
• grant select access to admins on those tables.
• implement version control system over the catalog source code

I would like to have all the data available via "Catalog Manager" stored into SQL tables, extending the capability of the current USAGE_TRACKING tables.

I would ike to be able use SQL to run all the queries I can do today manually on the Catalog Manager UI. 

Examples ... I would like to be able to select from catalog_objects where ...

• where lower(saw_src_path) like '%example%';
• where lower(object_name) like '%budget%'
• where object_type in ('AGENT', 'ANALYSIS')
• where user_id in ('mfinkielsztein')
• where owner = 'example'
• where lower(description) like '%example%'
• where created_ts > x or modified_ts > x
• where lower(object_XML) like  '%<saw:columnOrderRef%'
• etc

In addition: I would like to have all our Catalog Items stored into a Repository with Version Control capabilities.

Thanks,
Marcelo Finkielsztein

Use Case and Business Need

Use Cases: 

• Managers request statistical usage related to the repository of catalog items.  How many analyses were created / modified last month.  Who created or modified them.
• Administrators and Developers need to revert an analysis to its status as of date=xxx. 
• Administrators and Developers need to store version control comments, documenting what has been changed on a specific catalog item.
• Administrators neeed to search through XML source code of catalog items to find XREF cross references, dependencies, etc. 
• Example: what agents send e-mails to a given e-mail address.  This can be answered by querying XML definition of agents.
• Example: (dependencies).  Using SQL, ontain a report of all analyses that use a given physical table / column
• Developers can define and monitor KPI about Catalog metadata.

 

 

 

More details

• store catalog items in SQL tables. 
• grant select access to admins on those tables.
• implement version control system over the catalog source code

Original Idea Number: dbc88ee711

Michal Zima

Hi Marcelo,

actually OAS (and I assume OAC as well) is internally storing BI catalog object definitions in tables in BIPLATFORM schema (as opposed to OBIEE 12c, where BI catalog was physically directory structure on server filesystem) - those tables are following (this is result of my internal exploration, so no official guarantee, you will not find those information in official OAS/OAC documentation, since this is intentionally "hidden" - you are supposed to use official "interface" to access BI catalog - thus Catalog Manager):

CSS_SI_FILES - main table with all BI catalog objects

CSS_SI_ACL_GROUPS - ACL for BI catalog objects

CSS_FILE_CONTENT - storing definitions (XML, JSON) of BI catalog objects - in BLOB column, usually in compressed form (GZIP)

So if you are "on-premise" (OAS) customer, you can already (without guarantee) query those tables in BIPLATFORM schema. For OAC, I doubt, that you have acccess to them.

Rgds

Michal

 

Marcelo Finkielsztein

Hello.

I am an OAC Customer, with on premise database.  I do not see a schema named BIPLATFORM.

any advice ?

Michal Zima

Hi,

BIPLATFORM schema is one of the "supporting" schema, leveraged by OAS instance. For OAS, creation of those supporting DB schemas is part of the installation procedure.

For OAC I guess, those schemas are created automatically (somewhere...) during provisioning of OAC instance.

But you your questions (in respect of OAC) should be addressed by Oracle guys, I am not entitled to it (and I dont know the answers).

Rgds

Michal