OBIEE Lineage Dashboard - RPD Metadata Analysis and Catalog (Answers) Metadata - combine these two

Question

Hi All,

We have created two different tables for the OBIEE Lineage or OBIEE Metadata analysis of RPD and the Catalog (Answers) - the purpose is to do some impact analysis

like 1). if there is a column name change what are the tables, reports that are impacted in the RPD metadata and also the Catalog (Answers) Reports

For this purpose we have created - a table for the RPD Metadata with the following information - the below is for the RPD

SUBJECT_AREA,

PRESENTATION_TABLE,

PRESENTATION_COLUMN,

PRESENTATION_COLUMN_DESC,

BUSINESS_MODEL_NAME,

DERIVED_LOGICAL_TABLE,

DERIVED_LOGICAL_COLUMN,

DERIVED_LOGICAL_COLUMN_DESC,

DERIVED_EXPRESSION,

LOGICAL_TABLE_NAME

LOGICAL_COLUMN_NAME

LOGICAL_COLUMN_DESC,

LOGICAL_TABLE_SOURCE,

LOGICAL_EXPRESSION,

DATABASE_NAME,

PHYSICAL_CATALOG_NAME,

PHYSICAL_SCHEMA_NAME,

PHYSICAL_TABLE_NAME,

PHYSICAL_COLUMN_NAME

we also created another table for the Catalog(Answers) metadata - below are the fields

CATALOG_NAME,

CATALOG_FOLDER,

CATALOG_DESC,

CREATED_BY,

MODIFIED_BY,

CATALOG_TABLE,

CATALOG_COLUMN,

CATALOG_SUBJECT_AREA,

CATALOG_SEESION_VARIABLE

if I want to do some analysis with the data based on the RPD metadata - RPD Metadata table is helpful to identify the column name, table name etc - this is fine

if I want to do some analysis with the data based on the CATALOG metadata - Catalog metadata table is helpful to identify which report is using the column name etc - this also works fine

the issue is there is no concrete/solid PRIMARY KEY between these two tables - if I join them on SUBJECT_AREA name we are getting around 13 million records

I would like to combine the data so that I can see all the data related to the impact in one place - see the list of column names, table names, and also the report that are being used in

since there is no PRIMARY KEY - I do not just want to join with the SUBJECT_AREA name -

can anyone please suggest some ideas on how to deal with this - may be join them but pass the subject area name that is selected in the prompt to the RPD as  a filter

please let me know if more information is required.

Thanks a lot for your time

Gianni Ceresa · Answer

The SampleApp just take the one based on the same OBIEE version you are using (11g or 12c), so you can easily copy/paste things and reuse them directly.

For your 2 tables I don't see a problem if you have 52k items in one and 33k in the other, they are not supposed to be the same as they are something else.

Also keep in mind it isn't a 1=1 relationship.

For 1 subject area column your RPD table can contain 10 rows, because that single column come from many tables etc.

And 1 subject area column can be used in 0 or 1 or 12342 web catalog objects.

3409791 · Answer

Thank you Gianni - you are right! the 12 million records are because I joined at the subject area level - I do not have a key field in each table to join with

I have 52,800 in the RPD metadata table and 33,000 in the catalog metadata table - is there any way I can generate a KEY field such that it is consistent in both tables

that can be used to join - which one is the sample application you referred in the link - thanks a lot

Gianni Ceresa · Answer

Hi,

If you look into OBIEE Samples there is an example of this impact analysis (column/subject area <-> analysis, prompt etc.). They also provide scripts to get the data and load them and of course you have the RPD model and the web catalog objects.

If you want to do it with what you have of course you can't join at the subject area level because the column "City" on one side will be joined with "First Name" on the other side.

So you must go down to the column which is the "smallest" objects of the presentation layer. Doing a join at the column level must already give you better results.