How to get the list of columns being used in a DV Report

Jahnavi-Oracle

Summary:

Is there a way to find or get the list of columns being used in a DV Report?

Like we had a way to create a document with report name and the columns being used in Analytics report with the help of Catalog Manager.

Version (include the version you are using, if applicable):

OAC July 2022

Michal Zima

As far as I know, OAC/OAS does not offer such functionality (for "classical" analysis, you could use "report" in Catalog Manager, listing columns used in analysis). You could possibly "parse" JSON definition of DV workbook, but this would require manual tasks, it is not something which you can automate.

In my opinion, this should be another functionality of long promised Rest API for OAC/OAS (but still not delivered, if I omit manipulation with snapshots via Rest API) .

Gianni Ceresa

There is a way, but you will not like it...

A DV workbook is just an object in the catalog. The classic OBIEE/OAS/OAC objects were all XML and Catalog Manager was able to get these info out in a report as Michal said.

DV workbook went through various iterations from XML containing JSON, to JSON containing XML and other weirdness. But now kind of stick with JSON as the definition of a DV workbook. No need to say that JSON in a tool that always used only XML is the reason why Catalog Manager can't do reports on DV workbooks...

Your solution is what Michal said: read the JSON and get what you need out of it.

I did write a tool doing full lineage across the whole OBIEE/OAS/OAC product (RPD, datasets, catalog content, DV workbooks etc.), it's possible, but require some time and to know the tool inside out because the doc doesn't really cover all the pieces...

Aiswarya G

Excellent @Gianni Ceresa. Do you have the tool available in GitHub?

Gianni Ceresa

No really, it's a product/service.

Jahnavi-Oracle

Thank you @Michal Zima @Gianni Ceresa .

Just to confirm, even to "parse" JSON definition of DV workbook(as Michal mentioned), we need to go to each workbook individually and do it manually, right?

Gianni Ceresa

You can script the job, but yes: find all the workbooks you need (catalog search can help) and process them one by one.

That's why a script will be useful: lot faster and will not get crazy after a few workbooks...

Michal Zima

If you are OAC customer (I assume, this is your case), you can extract offline version of BI catalog from BAR archive and then you can a kind of automate traversing directory (offline BI catalog hase real directory structure on file system) and finding DV workbook definitions (under DV workbook folder there is file with name _projectdefn, which represents JSON definiton of DV workbook). Or you can use Gianni's tool for doing that - already finished solution (as Gianni mentioned it is paid product/service) - ask @Gianni Ceresa for it.

For OAS you could do the same but using BI catalog stored in DB tables of BIPLATFORM schema (which is the original storage of BI catalog).

Gianni Ceresa

Or the webservices, the good old one that is part of the tool for 10+ years: you can recursively loop through catalog objects or search them by type and then get the "definition" (the code inside the file when on disk), all by webservice.

The downside of this approach compared to what Michal suggest is that the security is enforced: your webservice calls only see what the account you use is allowed to see.