OEMM: Configuring Data Lineage Analysis

1. Open Repository > Oracle Tutorial > BI Reports and notice the Reports (Folder) and BI Repository (RPD Content).

   

   Trivia: RPD is not an acronym, it does not stand for anything! It is just a metadata file extension.

2. Open BI Repository to see the logical/physical/presentation layers. Explore the BI RPD nodes.

   

   You can browse the content in OEMM without needing to install or use the BI Admin tool.

3. Now open Reports > User Folders > My Folders > Products Report and view it graphically by clicking Data and Semantic Flow overview (). It shows the report and its link to the presentation layer.

   

   You may need to collapse some sections by clicking collapse-left ().

This is the report that will be analyzed for data lineage. Leave the tabs open for the next topic.

1. After browsing the report, you want to see where the data is coming from. Collapse () left and right () side panes to have a better view of the report. You can click Fit Contents () to automatically zoom and adjust the graph.

   

   You can always expand the collapsed panels by clicking those left and right icons again.

2. Right-click table(1) in the layout...

   

   ...and select Trace Lineage > Trace Lineage (Advanced).

   Use Trace Lineage (Advanced) because it allows various options. "Trace Lineage" and "Trace Impact" are simplified actions and do not allow various options. It is best to always use the "Trace Lineage (Advanced)" action for lineage, impact and semantic analysis since it allows complete flexibility.

3. In Configuration field, click Search ().

   

   Do not click Trace yet.

4. Select the configuration you created in the last OBE: Sales Reporting Application.

   

   Click Select.

5. Click Trace.

   

6. OEMM finds the source model/table/columns for this report.

   

   Leave the report open after you have looked at it for the next topic.

This displays a simple lineage.

Currently you can only see the final objects that make up the lineage. To see the intermediate objects, perform the following tasks:

1. In the same Data Lineage for table(1) report from the last topic, click End Objects Only pull-down, and select All Objects.

   

2. Quite a few more objects are now shown.

   

   Leave the report open after you have looked at it for the next topic.

You have expanded the scope of the analysis to include all objects.

1. In the same Data Lineage for table(1) report from the last topic, click Download Details.

   

   Use whatever dialog your web browser uses to save the .csv file to a destination folder of your choice.

2. Open the comma-separated variable (.csv) file using a spreadsheet viewer such as Microsoft Excel or OpenOffice.

   

   Remember that as soon as you download it, there is a chance it will start to get out of sync! You can close the spreadsheet.

   The columns Package/Classifier/Features are generic terms for object hierarchy in any model. For a database model, they correspond to Schema/Table/Columns. Leave the data lineage report open after you have looked at it for the next topic.

You exported the information to a .csv file which can externally be turned into an .xls spreadsheet which can be printed to a .pdf document.

1. In the same Data Lineage for table(1) report from the last topic, click Show Lineage Graph.

   

   It does not matter at this point what, if anything, is selected in the report.

2. Drill down into the detail by clicking expand and collapse in the upper-right corners of the boxes.

   

   This is what it looks like before you drill down (initially collapsed to only top-level showing).

3. This is the same diagram expanded down a few levels.

   

   You can drill deeper if you wish. Leave this graph open for the next topic.

This is the static version of the lineage graphs.

1. Expand all of the objects down to their lowest level. You can do this manually, or by selecting the object and then on the toolbar is an icon that expands the entire object all at once. Then rezoom to Fit ().

   While expanding all nodes using , you will be asked whether you want to expand at "Feature level" or at "Classifier level." In OEMM, model hierarchies are abstracted as Classifier/Feature. Usually the Feature level is the lowest level in any model. In a Database model, the Classifier level maps to Table and the Feature level maps to column.

   

   Click To Feature level to get the expanded view below, then rezoom to fit.

   

2. Select SAMP_REVENU (right in the middle of the page), and on the toolbar click Highlight. This highlights the path from start to finish going through this object.

   

   Click on any empty space in the diagram to unhighlight the path. Try different objects.

3. Select an individual object, such as SAMP_REVENU, and examine the Properties panel (which may need to be re-expanded).

   

   In this case it is showing a view, and the SELECT statement that generated the view. Click on any empty space in the diagram to unhighlight the object.

4. The thinner lines (shown here) are expression links. Select an individual link, such as from REVENUE to REVENUE, and the details are shown in the Operations box. It shows the operation SUM(REVENUE) taking place between source and target for this link. If there are more than one transformation taking place then they will all be displayed with different contexts. Here Context refers to an individual mechanism where the operation takes place. Double clicking on the context will take you the in model browser where the operation takes place. In this case, double clicking on the context REVENUE will open the DB model "DB - Dimensional DW" and point to the view/column where the operation is defined.

   

5. Select another individual link, such as from ORDER_DATE to PER_NAME_YEAR, and it displays the Link Type. The thicker lines (shown here) are ETL processes. In this case, double clicking on Mapping LD_TIME_DIMS_87 will open the "Load to Dimensional DW" model and point to the ODI mapping where this operation is taking place.

   

   You can get the same information in the hovering pop-up (transient), or in the Properties panel (permanent). Leave the graph open for the next Topic.

All of the objects are interactive with varying amounts of detail available and shown.

1. Using the highlighted link from the previous topic, from ORDER_DATE to PER_NAME_YEAR link, right-click on it to drill into the ETL mapping.

   

   Select Trace ETL Details.

2. Tracing data lineage into ETL only exposes the data flow relevant to earlier lineage graph. You can see then in the mapping PER_NAME_YEAR, dates are calculated by summing the order date fields from the source.

   

3. If you want to browse the full mapping, right-click to Show in Metadata Browser (LD_TIME_DIMS in this case).

   

   Do not bother doing it, but you could if you wanted to.

This illustrates drilling down into the ETL processes.