Index

Lesson 1 – Get Up & Running
Lesson 2 – Creating Models & Harvesting Metadata
Lesson 3 – Stitching Metadata to Tracing Data Lineage & Impact

Introduction

Importing and harvesting metadata is the very first step towards your journey to data governance in OEMM. You are using OEMM for several reasons such as tracing impact analysis and data lineage. In order to achieve that, you need to stitch the models which you have harvested earlier. In this lesson, I’m going to show you how simple it is to do that, but before I get started, let me explain some definitions that we’ll be using throughout this lesson and later ones.

Stitching: The automated process of validating a set of selected models in order to connect design and operational metadata elements to form relationships.

Data Lineage: The data lifecycle that includes the data’s origins and where it moves.

Configuration: A container for the models you need to stitch together. a “Configuration” will hold all stitched relations, architectural diagrams, etc…

The purpose of this lesson is to show you how to stitch your metadata. As a prerequisite, you need to have your models imported which I described how in lesson 2. I’ll be using some sample models that I have in my VM for the purpose of this lesson, which are NOT related to the ones I have harvested in lesson 2.

Creating new “Configuration”

The very first step you need to do is creating a new “Configuration”, to do so you need to right click on any folder you have in your OEMM repository or your repository root, then New => Configuration:

In the “Create Configuration” window, give your configuration a name and add some description, then click on “Create”:

You should now see an empty white area in your new configuration with two tabs, “Model Manager” and “Architecture Diagram”:

From the OEMM repository, start dragging and dropping the models you harvested/imported into the new “Configuration”:

In this lesson, I’ve imported 4 different models: Oracle Dimensional DW, Oracle OLTP source, Oracle Data Integrator project (Load to Dimensional DW) and an Oracle BI Report:

As you can see, we have some warning signs on two of the models, this is due to the need of telling OEMM the connections input/output. Confused? Okay, let me explain this further. In the ODI model I imported, I have two physical “Data Stores”, one connects to my OLTP, and the other to my target BI server. In OEMM, I need to say that “X” connection is taking data from “Y” model/store. To do so, click on the model you have warning on, right click = > Edit Connection, or directly from the toolbar click on “Edit Connection”:

You should get something like the following that allows you define the data store for each connection:

Make the right selections and notice that your selection/s are actually the models you already have in your “Configuration”:

OEMM is capable of matching the right schemas/objects/attributes with each other, but in case it couldn’t, you can map them later on after you are done with stitching. It’s a step-by-step process, and OEMM will guide you thoroughly. Once ready, click on OK:

I did the same thing for my BI Report model, except that I choose “Ignore” for connections that are not relevant to my “Configuration”, and for the reports I’m concerned about, I defined the correct data store, which in this case is the “Dimensional DW” that ODI mapping load into:

Everything should make sense to you by now. My “Configuration” now is ready to be “stitched”, to start the process, click on “Validate” on the top right corner of the “Configuration” canvas:

The process should take a few seconds, depend on your server resources and the models you are trying to stitch. After I clicked on “Validate”, my “Configuration” was validated:

If you notice, there are some warnings in my stitched configuration. Normally you need to fix those warnings if needed, or simply ignore them if they are not related to what you are trying to achieve. In my case, the warnings were due to some unmatched objects/attributes. If attributes don’t match, the metadata analysis for those attributes can’t be done. It also will cause errors in deployed applications. To check those, click on the “Model” having the warning/s = > Edit Connection => select the connection having the warning => “Open Connection”:

Doing that will open a tab with mappings between attributes, browsing through those mappings, notice that there are unmapped objects on the source, because there is no matching ones on the target. Those which are “mapped” have the “link icon next to them:

You can even search or drill down to see the mapping status of attributes under a certain object:

You may want to filter on stitched/unstitched as well by clicking on the filter icon on the toolbar to make it easier to identify those attributes:

For this lesson, I can safely ignore those warnings as they are not related to my final goal, which is tracing data lineage of a specific report in Oracle BI. Let’s close this window, and click on “Architecture Diagram” tab in the configuration:

You should get something like the following:

If the layout is out of order, you can have it ordered nicely by clicking on Edit = > Layout:

The architecture diagram represents the flow of data across enterprise. As you can see, I got my OLTP source, then having ODI mappings extracting from an OLTP and loading into a data warehouse, and eventually built some reports on top of my DW using Oracle BI. This is great in so many ways, as a person who has no idea how your enterprise architecture looks like, you just had it generated.

There is a bunch of stuff I can do in my configuration, but the focus of this lesson is showing you how to stitch your metadata in order to do more with them. I’ll be showing you next two main features in OEMM; tracing data lineage and impact analysis.

Trace Data Lineage

I’ll show you now how to trace data lineage in your architecture. You can accomplish that in different ways, I’ll start with the graphical way. Make sure you have your “Configuration” open, and double click on any of your models in the “Architectural Diagram”. I did so on the BI Reports. Click on “Model Connection Overview” icon on the toolbar:

Doing so resulted in:

Now I’ll dig deeper by opening the “Product Report”, to do so right click on it => Open:

Now I have my “Product Report” opened, I’ll go even deeper by clicking on “Data Flow Overview” icon:

Doing that, I got the following detailed report with the presentation layer, criteria and layout:

I’ll avoid the temptation of what you can do here and stick to the lesson objective. I wanted to trace data lineage for “table (1)” in my report layout. To do so, right click on the table => Trace Lineage => Trace Data Lineage:

In the “Advanced Lineage Options”, select “Graph/Flow” then click on “Trace”:

I got this:

Let me expand the “Dimensional DW” and “OLTP source”:

As you notice, I was able to see the full lineage of my report. I can even see the used columns, I’ll do that by expanding the three tables under “OLTP source”:

Nice! But where is my ELT (the ODI mappings)? To see them, you can expand all nodes by right click on an empty area in the canvas => Expand All Nodes Completely:

On the diagram, notice the “bolded” lines, they represent the ELT that happened here. If you click on any bolded line, you’ll notice the operation in the “Detail” panel at the bottom:

To trace the ELT, right click on the bold line => Trace ETL Details:

As a result, I got the following:

Nice! You can do the same thing even to column level, not only table level, in the report. Test it yourself by right clicking on any column and tracing the lineage.

If you prefer to have the linage in a “Text/List” format rather than graphical, you perform the same steps. Let me demonstrate it for you; back to our “Products Report” diagram, right click on any node or attribute, here I did on attribute => Trace Lineage => Trace Data Lineage:

In the options dialog, select “Text/List” and click on “Trace”:

I got the following list:

Let’s see all objects lineage, not only end ones. To do so, click on End Objects Only => All Objects:

I got the following (I collapsed some nodes to make it fit):

Trace Data Impact

It’s very important to be able to know the impact of a certain object/attribute on other applications/users/systems. This reduces risk and makes the analysis part much easier for development and other purposes.

Back to our “Architectural Diagram”, let’s double click and open the “OLTP source” node:

The model will be opened, and you may navigate through its metadata:

Let’s trace the impact of an attribute in a table. I choose the column “AMOUNT” under a table called “SRC_ORDER_LINES”. To trace the impact for it, right click on it => Trace Data Impact:

Keep the options as is, including “Text/List” as Display. Then click on “Trace”:

Show “All Objects” impacted by clicking on the “Show” dropdown => All Objects:

You’ll see all those objects impacted by that attribute. To see it graphicly, click on “Show Lineage Graph” icon:

You’ll get a nice collapsed nodes flow like this:

Which you can expand one by one (if you wish), or all at once by clicking on the expand icon on the toolbar:

I didn’t expand all nodes to make it simpler to show you the diagram here. I expanded the OLTP source => OLTP_SRC => SRC_ORDER_LINES:

And then I expanded BI Reports => Products Report => Layout => Table (1):

See how accurate and informative that was? I expanded the Dimensional DW one level only and got:

You may highlight the path for better tracing. To do so click, on any node/attribute, then click on “Highlight” on the toolbar:

Let’s trace the ETL, to do so, I expanded the node connecting the OLTP source to the Dimensional DW, and now I got a bolded line. Right click on it => Trace ETL Details:

Resulted in:

Within configurations, there are so many things you can still do. In later lessons, I’ll make sure to dedicate one to introducing other functionalities and tricks.

Summary

In this lesson, we have learned how to stitch data models together. We also learned how to trace data lineage and impact analysis. The overall process is very visual and requires no pre-knowledge in your enterprise architecture.

If you have any questions, please do write in the comments area or message me.

Issam