O.B.I.E.E: OBIEE and Working with Oracle Enterprise Metadata Management

Today I got a chance to explore on Metadata Management Tool and thought of sharing information with you on how to get started. As a prerequisite I assume you have installed already OEMM, if not take a look at this wonderful rittmanmead blog and especially on Why and How to use Oracle Metadata Management 12c. Part 1: Getting Started

Overview Section

Metadata Management is a very handy tool for developers or business users who wanted to know source lineage with minimal effort or wanted to perform an impact analysis. There are very few implementation will take care to store metadata information (How a metric calculated, what is a source of presentation attribute etc) and majority of times it involves manual work, by using this tool with very minimal effort we can achieve same thing and this can be expandable to any complex implementation easily.

With this tool we can achieve 1) Analysis to data warehouse and to source Lineage 2) Can perform impact analysis (how many reports exists with attribute "Order Detail" 3) Can add user comments to metadata ( How a measure being calculated or Why we are in doing this way only etc) 4) Can easily integrated with majority of databases and etls and has built in scheduler to schedule the jobs. 5) Visualize datamodels. 6) Visualize architecture diagram.

It has 2 major components – 1) Metadata Manager MM : This is kind of technical work where we configure about metadata and perform impact analysis or discover lineage or display Architecture Diagrams etc. Whereas 2) Metadata Explorer ME: This allow users to explore/search about metadata. In following sections I will explain in more detailed.

Working with Metadata Manager MM:

a) As soon as you login to MM you will see a "Welcome" page from left side pane (Repository Pane) , by using right click menu on Repository , choose "New" Folder and created a folder as per your need.

b) Highlight folder and click on new "Model" inside a newly created folder.

c) Provide a name to your datamodel – here I am saying "OBIEE" and select Import type as " …Enterprise Edition".
There are 3 types of OBIEE imports can be performed,

a. Oracle Business Intelligence (OBI) Answers : With this type , we can import catalog.

b. Oracle Business Intelligence (OBI) Server : With this type import we can import RPD – there is no direct way to import RPD directly first we have to convert RPD into UDML or XML using repository tools then feed it to MM.

c. Oracle Business Intelligence (OBI) Enterprise Edition: With this type of import we can import catalog as well as rpd-udml.

d) Now in "Import Setup" tab on "Create Model" pop-up provide all required information

Server URL: This is the url where analytics services are running.

File: UDML or xml file where RPD details are available

As previously said , we will not be able to feed whole RPD, we need to convert it to xml or udml.

For UDML file creation - $OracleBIHome$\server\Bin\nQUDMLGen.exe -U Administrator -P Administrator -R paint.rpd -O paint.udml -N -Q -8

In order to convert RPD as XML – use following command.

Run the bi-init.cmd from directory ORACLE_INSTANCE\bifoundation\OracleBIApplication\coreapplication\setup\bi-init then

Execute biserverxmlgen -R paint.rpd -P Admin123 -O paint.xml -N -Q -8

Rest keep them as per your need. And test connectivity by clicking Test link. If there are any errors then fix them.

e) Optionally you can configure "Import Schedule" , "Security" and "Triggers" tabs.
Perform Import operation, it will display import messages as shown below.

f) After import your will be able to view model "OBIEE" and double click on it, it will display 2^nd pane , i.e. Model Directory it displays all the objects from RPD and Catalog.

g) Highlight any report and double click on it (example "Contribution Waterfall" report) now in 3^rd pane it is displaying what are views that report had and presentation attributes it has.
h) Highlight any attribute on 3^rd pane – in Properties pane (4^th pane) you will be able to see that object properties.

Now coming to Utilizing Metadata Manager:

Typical datawarehouse look like below, from source layer we will bring data using ETL into warehouse layer from there using OBIEE analysis will create reports, to

Performing Lineage: Now let me walk you throw performing a lineage on following highlighted columns

Click on Trace data lineage , this will pop what is the scope is it in model or configuration.

Configuration is nothing but couple of models configured as one configuration, example – majority of times we will have source system – so create a model for source system, then if we have ETL's then DAC or Informatica or ODI may exists so create a model for them , and one model for OBIEE which includes RPD and Catalog, now with these 3 models create one configuration.

Now we can see its lineage – Once you expand each semantic layer by expanding – you will be able to see under that layer how it is changed or mapped. Example – once we expand BI Repository you will be able to see all 3 layers of RPD and how an attribute mapped.

Now let's take a look at Impact Analysis example:

Highlight any presentation attribute which you want to perform impact analysis

And it is showing – source to Presentation lineage.

Now let's learn about Metadata Management Explorer :

You can able to launch explored from tool bar menu , in web URL you will see something like "/MM/Explorer" and prompt you to select configuration, select one.

You will be able to view BROWSE objects based on the configuration you selected. Here in picture it is showing only Database and Oracle Business Intelligence as in my configuration only those 2 repositories are there. By using browse capabilities we will be able to view corresponding technology each and every object.

Here is one such sample – browse Oracle Business Intelligence –Tree view – expanded dashboards to see what are all the pages(Children) are there.

Now coming to Metadata Explorer Search tab: I see this is very powerfull tool as we can search for metadata by technology.

Here is sample search results are shown here.

Here are few search key words and expected results , in short make use of + or - symbols to restrict results.

To search for	Example	Result
Any words	sales order	Any result containing the word "sales" or the word "order"
Exact phrase	"sales order"	Any result containing the exact phrase "sales order"
All words	+sales +order	Any result containing BOTH the words "sales" and "order"
Exclude words	sales –order	Any result containing the word "sales" but cannot contain the word "order"
Wild card end	sale*	Any result containing part of a word beginning with "sale"
Parent and child	sales.order	Any result where the parent is named "sales" and the child is named "order". E.g., the attribute "order" contained within the entity "sales".
Exact name	.order	Any result only containing the word "order"
Object Type	type:Column	Any result which is of type "Column"
Property Type	property:name	Any result where the search criteria matches the name

Bottom line : With a minimal configuration effort we will be able to perform impact/lineage across all technologies.

Happy Sunday !!!