Import 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 Import Metadata from Multidimensional Data Sources and Work with ADF 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's 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 don't 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're 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 can't display in the right-click menu for the database's physical tables.

Other data source types are described in other sections:

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.

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

  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 aren't available through ODBC.

    For non-Oracle databases, it's recommended that you use ODBC 3.5 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 to import the schema. Then, provide a valid user name and password for the data source.

    • For OCI 10/11g 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've set up a tnsnames.ora file within the Oracle Analytics Server environment, in:

      BI_DOMAIN/bidata/components/core/serviceinstances/ssi/oracledb

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

    If some objects couldn't 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.

  3. Select the objects types to import such as Tables, Keys, and Foreign Keys.
  4. Click Next. The Select Metadata Objects screen appears.
  5. Select the objects to import in the Available list and move them to the Selected list, using the > (Import selected) or >> (Import all) buttons.
  6. Optional: Select Show complete structure to view all objects.

    Deselecting this option shows only the objects that are available for import.

  7. Click Finish.

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 can't determine the exact database type during import and instead assigns an approximate type to the database object. See Set Up Database Objects and Create or Change Connection Pools.

Visually inspect the imported data in the Physical layer such as physical columns and tables to ensure that the import completed successfully.