OBIEE 11G Catalog Manager Best Practices Part 2
Continuing from my previous blog on Catalog Manager Best Practices, I will offer some additional tips and I will elaborate on the different ways to change the column datatype formats and to extract the catalog report for any column or report related information.
The following topics will be covered:
- Catalog Manager Extract Report
- Datatype Formats XML
Catalog Manager Extract Report:
The general questions we usually get from users regarding the obiee columns usage and their related information are outlined below:
- How to find the column formula defined in BMM layer?
- What is the presentation column’s physical table name and column name?
- Who are the report owners and when was it modified last time?
- What are the Rpd variables used in report?
If you want to modify/update your metadata definition, the developer should be aware of the impact of that change in already developed content.
To answer all these questions, we need to build a column lineage, from dashboard to subject area, to physical column, and then to the column in source system. We have many tools to build this data lineage, but within OBIEE itself, we have some great information that can be used to build the Data Lineage.
To build OBIEE Data Lineage, we need to extract the objects information from OBIEE catalog manager
OBIEE Web Catalog Data:
The “All Web Catalog” objects information can be found using the Catalog Manager Report. Generate a catalog report using Catalog manager and then export it to a tab delimited CSV file.
To export the OBIEE Web Catalog report to a CSV file:
- Open OBIEE Catalog Manager and connect to the catalog that contains the metadata that you want to include in the lineage.
- Select Tools -> Create Report.
- In the Create Catalog Report dialog, select the following columns to include in the report: Name, Owner, Path, Formula, Folder and other columns needed as per our requirement.
- Click OK, to generate the report.
- Save the report to local folder, for example c:\local\data_lineage.csv.
- The file will be saved as tab delimited CSV file with all the information of the columns that we pulled.
Datatype Formats XML:
Each and every metric require a data format to be set as per. The requirement of the column format can be a precision of 2 decimals, comma separated, currency (‘$’ symbol), negative numbers, or date.
Theese changes can be completed in different ways in Answers and Catalog Manager as below.
We usually set the format on the column on the answers page as below
Column ->Column Properties -> Data Format
Catalog Manager Data Format Location:
Use Catalog Manager to find the data format for each column in the subject areas. Below is the location from Catalog Manager.
Right click on subject area -> Properties ->Edit Xml
The necessary changes can be made from the edit xml page and changes can be saved online.
Manually creating the xml file:
We can create the xml file manually by taking care few things which are highlighted as below
Sample format of single column
<sawsavedformat:columnSavedFormat xmlns:saw=”com.siebel.analytics.web/report/v1.1″ xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:type=”sawsavedformat:regularColumnSavedFormat” columnName=”"Human Resources – Workforce Profile"."Workforce Profile Facts"."%Regular + Temporary Employees"”>
<saw:formatSpec suppress=”repeat” wrapText=”true” hAlign=”right” vAlign=”top” imagePlacement=”right” interaction=”action”>
<saw:dataFormat xsi:type=”saw:percent” minDigits=”1″ maxDigits=”1″ commas=”false” negativeType=”minus” />
Human Resources – Workforce Profile -Subject Area
Workforce Profile Facts – Folder Name
%Regular + Temporary Employees -Column Name
After manually creating the file with appropriate data formats, use the below location to deploy the file: