Importing Metadata from Relational Data Sources

You can import metadata for supported relational data source types by selecting the appropriate connection type in the Import Metadata Wizard.

To import metadata, you must have all database connections set up on your local computer. You can import metadata in both offline and online modes.

See Importing Metadata from Multidimensional Data Sources and Working with ADF Data Sources for information about importing from other data sources.

When you import physical tables, be careful to limit the import to only those tables that contain data that are likely to be used in the business models you create. You can use the Find feature to locate and select the tables that you want to import. Importing large numbers of extraneous tables and other objects adds unnecessary complexity and increases the size of the repository.

When you import metadata for most data sources, the default is to import tables, primary keys, and foreign keys. It is recommended that you import primary and foreign keys along with your tables so that the keys are automatically created in the Physical layer. If you do not import keys, you must create them manually, which can be a time-consuming process.

You can also import database views, aliases, synonyms, and system tables. Import these objects only if you want the Oracle BI Server to generate queries against them.

If you are importing metadata into an existing database in the Physical layer, then confirm that the COUNT_STAR_SUPPORTED option is selected in the Features tab of the Database properties dialog. If you import metadata without the COUNT_STAR_SUPPORTED option selected, then the Update Row Count option cannot display in the right-click menu for the database's physical tables.

To import metadata from a relational data source:

  1. In the Administration Tool, select File, then select Import Metadata.
  2. In the Select Data Source screen, in the Connection Type field, select the type of connection appropriate for your data source, such as ODBC 3.5.

    Make sure to choose OCI 10/11g if your data source is an Oracle Database. Using OCI as your connection protocol to an Oracle Database ensures better performance and provides access to native database features that are not available through ODBC.

    Note:

    For non-Oracle databases, it is recommended that you use ODBC 3.5 or DB2 CLI (Unicode) for importing schemas with International characters, such as Japanese table and column names.

    The remaining fields and options on the Select Data Source screen vary according to the connection type you selected:

    • For ODBC 2.0 and ODBC 3.5 data sources, in the DSN list, select a data source from which to import the schema. Then, provide a valid user name and password for the data source.

      Note:

      When you import through the Oracle BI Server, the DSN entries are on the Oracle BI Server computer, not on the local computer.

    • For OCI 10/11g and DB2 CLI (Unicode) data sources, provide the name of the data source in the Data Source Name field, then provide a valid user name and password for the data source.

      For Oracle Database data sources, the data source name is either a full connect string or a net service name from the tnsnames.ora file. If you enter a net service name, you must ensure that you have set up a tnsnames.ora file within the Oracle BI EE environment, in:

      BI_DOMAIN\config\fmwconfig\bienv\core

    Other data source types are described in other sections:

    When you have finished providing information in the Select Data Source screen, click Next. The Select Metadata Types screen appears.

  3. Select the options for the types of objects that you want to import (for example, Tables, Keys, and Foreign Keys). Some options are automatically selected. Different types of data sources have different default selections, based on what is typical for that data source.

    If you want to import joins, select both Keys and Foreign Keys. If you want to import system tables, you must have the system privilege for your data source. To import from Customer Relationship Management (CRM) tables, select Metadata from CRM tables.

  4. Click Next. The Select Metadata Objects screen appears.
  5. Select the objects you want to import in the Available list and move them to the Selected list, using the > (Import selected) or >> (Import all) buttons. You can also move objects from the Selected list back to the Available list, using the < (Remove selected) and << (Remove all) buttons.

    To search for a particular item, enter a keyword in the Find box and then click Find Down or Find Up.

    Select Show complete structure to view all objects, including those that have already been imported. Deselecting this option shows only the objects that are available for import. When this option is selected, objects that have already been imported appear grayed out.

  6. Click Finish.

    If some objects could not be imported, a list of warning messages appears. In the dialog displaying the messages, you can perform the following actions:

    • To search for specific terms, click Find and then Find Again.

    • To copy the contents of the window so that you can paste the messages in another file, click Copy.

After you import metadata, you should check to ensure that your database and connection pool settings are correct. In rare cases, the Oracle BI Server cannot determine the exact database type during import and instead assigns an approximate type to the database object. See Setting Up Database Objects and Creating or Changing Connection Pools for more information about working with these objects.

It is also a good practice to visually inspect the imported data in the Physical layer, such as physical columns and tables, to ensure that the import completed successfully.