OEMM: Harvesting Metadata from Various Sources

1. The detailed instructions for installing and starting OEMM were contained in the first OBE: Installing OEMM.

2. Start the OEMM services (Tomcat) on the Windows PC as a Service.

   

   If Tomcat opens a console window (with a Java icon on the running bar), leave the window open but minimized.

   There are alternative ways to (auto)start Tomcat in the background.

3. Log in to the local OEMM on a web browser at URL http://localhost:11580/MM/ (right-click this link to open it in a new tab).

   

   The default password is Administrator.

You should now be logged into a running instance of OEMM.

Folders help you organize your models. You can make as many or as few as you need.

1. Create a new folder by right-clicking on Repository and then select New > Folder.

   

2. Name it My Demo. The Description is optional but recommended.

   

   Click Create to continue.

You should now have an empty folder for storing and organizing your models.

First you are going to make a model for the HR sample schema (which we will not use), and then you will just look at the pre-created one for OLTP (which we will use).

1. Right-click the My Demo folder, and select New > Model.

   

2. Name it HR Source.

   On the Import from pull-down, scroll down and select Oracle Database (via JDBC).

   

   Stay on this panel, do not click Create yet.

3. Click the Import Setup tab and enter the following case-sensitive values:

   • Host: localhost or the actual host IP address
   • Port: 1530 or 1521 as appropriate
   • Service: orcl
   • User: SYS as SYSDBA
   • Password: oracle (displays as *******)
   • Schema: HR
     (You can either type the schema name or you can use the to browse for it. Note a blank means all schemas.)

   

   Click Test to verify your parameters.

   If the test was successful, click Create to continue.

4. You must have at least one version, and there currently is none, so click Yes to continue.

   

5. The Log Messages show the progress.

   

   Click Yes to continue.

6. The Metadata Browser is displayed. Drill down as far as you can until you can see the columns in REGIONS.

   

You have created a new model (though we will not be using it).

1. Just like you did for the HR schema, examine the pre-created model for Oracle Tutorial > DB - OLTP Source. Drill down until you can see the columns in SRC_CITY.

   

   Close the DB - OLTP Source tab when you are done looking at it.

2. Similarly, examine the pre-created model for Oracle Tutorial > DB - Dimensional DW. Drill down until you can see the columns in AG_BISAMPLE. (There are many more tables than are shown here.)

   

   Close the DB - Dimensional DW tab when you are done looking at it.

These models were pre-created. Interestingly, neither of the schemas still exist on the database even though both metadata models persist.

1. On the Linux desktop, click the ODI 12c Studio launcher.

   

   Double-click it only ONCE, otherwise you will get multiple copies.

2. Click Connect to Repository, and accept the defaults.

   

   Click OK to continue.

3. Go to the Designer tab to familiarize yourself with the kinds of objects (Projects and Models) that are out there in the ODI Master and Work Repositories.

   

ODI Studio is now started. The repository in the database was available before this, as long as the database is running.

First you are going to make a model for ODI (which we will not use), and then you will just look at the pre-created one for Load to Dimensional DW (which we will use).

1. Right-click the My Demo folder, and select New > Model.

   

2. Name it Orders App.

   On the Import from pull-down, scroll down and select Oracle Data Integrator (ODI).

   

   Stay on this panel, do not click Create yet.

3. Click the Import Setup tab and enter the following case-sensitive values:

   • Oracle Home path: C:\Oracle\Middleware\Oracle_Home
   • URL: jdbc:oracle:thin:@mylinuxdb:1521:orcl (See URL note below))
   • Master Repo User: prod_odi_repo
   • Master Repo Password: oracle
   • ODI User: SUPERVISOR
   • ODI User Password: SUPERVISOR (displays as *******)
   • Work repository: WORKREP
   • Scope: Projects
   • Content: (Leave this blank for the moment, but it cannot remain permanently blank.)
   • Context: Global

   

   Use the to browse for Content. (This doubles as a connectivity test.)

   The URL hostname depends on whether you are running the database and OEMM on the same host or not. If so, then use localhost:1530. If not, then use the actual host IP address:port of the database (your address will be different than the one shown here as mylinuxdb:1521, and may be different each day if using DHCP).

   If you are using the pre-built ODI VM and the Windows is on a different host, then you also need to go into ODI Studio, Topology > Repositories > Work Repositories > WORKREP, click on the connection icon (looks like a plug), then JDBC and change the URL localhost to an actual IP address.

   If the database is running in the VM, then the port is probably 1530 instead of the default of 1521.

   Note that the Oracle Home path must be on the same machine as OEMM, even if you normally use ODI Studio on another platform such as Linux.

4. Select the objects for which you want to capture metadata.

   

   Click Select to continue.

   CAUTION: The check boxes themselves do not indicate whether there is a partial or full sub-selection. For example, by selecting Sales Administration (Project Folder), that causes the sub-items to be selected, and their check boxes no longer display. Compare that behavior to selecting Load TRG_CUSTOMER (Mapping) which causes the hierarchical objects above it (such as Mappings (Mappings) and Sales Administration Demo (Project Folder)) to be selected, but those partial-selection check boxes look no different than the full-selection check boxes.

5. Click Test to verify your parameters.

   

   If the test was successful, click OK, then click Create to continue.

6. There is no version yet, so you do want to import a new one.

   

   Click Yes to continue.

7. The import may take quite a while, depending on the number of objects you are importing. Note the timestamps.

   

   Click Yes to inspect your new metadata. When done experimenting, click Close to continue.

8. This is what was produced by the previous experimentation.

   

   Close the tabs when you are done looking.

You have created a new ODI model.

1. On the main Repository navigation panel, expand "+" Repository > Oracle Tutorial > Load to Dimensional DW, then in the Model Directory panel, expand "+" Models > ODI_DEMO > Orders Application, then in the Metadata Browser panel, expand "+" Orders Application (Model) > SRC_ORDERS (Datastore).

   

   This should look very similar to the one you created, only a subset. Notice it is missing SRC_CITY when compared to the model you did.

You have reviewed the existing ODI models.

1. On the main Repository navigation panel, expand "+" Repository > Oracle Tutorial > BI Reports, then in the Model Directory panel, expand "+" localhost > Reports (folder) > User Folders > My Folders > MyProductsDashboard > page 1 > Products Report, then in the Metadata Browser panel, expand "+" Presentation Layer > A - Sample Sales (Subject Area).

   

   As with all metadata, this is not the report, this is metadata about the report.

This is the metadata for a previous BI report.

Since BI is not installed on this VM, you cannot do a live setup, but you can review the previous setup values. BI requires WLS as a prerequisite.

1. In Oracle Tutorial, right-click BI Reports and select Settings.

   

2. On the Properties tab, note that the bridge is OBIEE.

   

3. On the Import Setup tab, review the possible parameters.

   

   Several of the options have browse () capability. When you are done, close the BI Reports tab.

If OBIEE was present, you would have been able to create a new model; but since OBIEE is not present, all you could do was review the previous setup parameters.

1. In the Repository panel, right-click the My Demo folder, and select New > Model.

   

   You are not going to save anything, so it does not matter what you name it.

2. Review the list of available import bridges, there are about a hundred to choose from!

   

   Scroll down and pick an import bridge, then examine the respective Import Setup parameters.

3. OBE 6. Data Mapping: Bridging the Gap, when Harvesting Bridges aren't available, demonstrates how to use Microsoft Excel as an import bridge to harvest metadata. The Excel spreadsheet is created manually. If you wish, you may do that OBE to see about harvesting using the Excel import bridge.

OEMM can harvest metadata from many different kinds of integration sources from many different vendors.