1. Managing Metadata Repositories for Oracle Analytics Server
  2. Build Your Metadata Repository
  3. Import Metadata and Working with Data Sources
  4. Import Metadata from Multidimensional Data Sources

Import Metadata from Multidimensional Data Sources

You can import metadata from a multidimensional data source to the Physical layer of the Oracle BI repository.

Using multidimensional data sources enables the Oracle BI Server to connect to and extract data from a variety of sources.

During the import process, each cube in a multidimensional data source is created as a single physical cube table. The Oracle BI Server imports the cube metadata, including its metrics, dimensions, and hierarchies. After importing the cubes, you need to verify that the physical cube columns have the correct aggregation rule, and that the hierarchy type is correct. See Work with Physical Hierarchy Objects.

Note:

Manually creating a physical schema from a multidimensional data source is labor-intensive and error prone. Therefore, it's strongly recommended that you use the import method.

Oracle recommends removing hierarchies and columns from the Physical layer if you aren't going to use the hierarchies and columns in the business model. Eliminating unnecessary objects in the Model Administration Tool could result in better performance.

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

See Multidimensional Connection Options.
  1. In the Model Administration Tool, do one of the following:
    • Select File, then select Import Metadata.
    • From an existing database, right-click the connection pool in the Physical layer and select Import Metadata.
  2. In Select Data Source, in the Connection Type field, select the type of connection appropriate for your data source, and click Next.
  3. In Select Metadata Types (only Oracle RPAS data sources), select Tables, Keys, and Foreign Keys and then, click Next.
  4. In Select Metadata Objects, from the Available list, select the objects to import using the Import >, or Import All >>.
  5. Select Import UDAs if you want to import user-defined attributes (UDAs) from an Essbase data source.
  6. Click Finish.

A list of warning messages display if some objects weren't imported. Resolve the issues as needed.

After you import metadata, you should verify 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 hierarchical levels to confirm that the import completed successfully.

For Essbase data sources, all hierarchies are imported as Unbalanced by default. Review the Hierarchy Type property for each physical hierarchy and change the value if necessary. Supported hierarchy types for Essbase are Unbalanced, Fully balanced, and Value.

Multidimensional Data Source Connection Options

In the Model Administration Tool when importing multidimensional data sources into your repository, you can use these connection types in the Import Metadata wizard’s Select Data Source page.

ODBC 3.5

The ODBC 3.5 connection type is used for Oracle RPAS data sources. Select the DSN entry and provide the user name and password for the selected data source. See Set Up ODBC Data Source Names (DSNs).

Essbase 9+

Use Essbase 9+ connection type for Essbase 9 or Essbase 11 data sources. Provide the host name of the computer where the Essbase Server is running in the Essbase Server field, then provide a valid user name and password for the data source. This information should be obtained from your data source administrator.

If the Essbase Server is running on a non-default port or in a cluster, include the port number in the Essbase Server field as hostname:port_number. See Work with Essbase Data Sources.

XMLA

Use the XMLA connection type for Microsoft Analysis Services and SAP/BW. Enter the URL of a data source from which to import the schema. You must specify the Provider Type such as Analysis Services 2000 or SAP/BW 3.5/7.0, and a valid user name and password for the data source.

You can use a new or existing Target Database.

Oracle OLAP

Provide the net service name in the Data Source Name field, and a valid user name and password for the data source. The data source name is the same as the entry you created in the tnsnames.ora file in the Oracle Analytics Server environment. You can also choose to enter a full connect string rather than the net service name.

Provide the URL of the biadminservlet. The servlet name is services, for example:

http://localhost:9704/biadminservlet/services

You must start the biadminservlet before you can use it. Check the status of the servlet in the Administration Console if you receive an import error. You can also check the Administration Server diagnostic log and the Domain log.

See Work with Oracle OLAP Data Sources.

You can use data sources from an Oracle Database data sources and the OLAP connection type. The data source can contain both relational tables and multidimensional tables. You should avoid putting multidimensional and relational tables in the same database object because you might need to specify different database feature sets for the different table types.

For example, Oracle OLAP queries fail if the database feature GROUP_BY_GROUPING_SETS_SUPPORTED is enabled. However, you might need to GROUP_BY_GROUPING_SETS_SUPPORTED enabled for Oracle Database relational tables.

You should create two separate database objects, one for relational tables, and one for multidimensional tables.

Hyperion ADM

Provide the URL for the Hyperion Financial Management or Hyperion Planning server.

For Hyperion Financial Management 11.1.2.1 and 11.1.2.2 using the ADM native driver, include the driver and application name (cube name), in the following format:

adm:native:HsvADMDriver:ip_or_host:application_name

For example:

adm:native:HsvADMDriver:192.0.2.254:UCFHFM

For Hyperion Financial Management 11.1.2.3 and 11.1.2.4 use the ADM thin client driver, and include the driver and application name (cube name) as follows:

adm:thin:com.hyperion.ap.hsp.HspAdmDriver:ip_or_host:port:application_name

For example:

adm:thin:com.hyperion.ap.hsp.HspAdmDriver:192.0.2.254:8300:UCFHP

For Hyperion Planning 11.1.2.4 or later, the installer doesn't deliver all of the required client driver .jar files. To ensure that you've the required .jar files, go to your instance of Hyperion, locate and copy the adm.jar, ap.jar, and HspAdm.jar files, and paste the files into MIDDLEWARE_HOME\oracle_common\modules.

For Hyperion Planning 11.1.2.4 or later using the ADM thin client driver, include the driver and application name (cube name), in the following format:

adm:thin:com.oracle.hfm.HsvADMDriver:server:application_name?locale=en_US

Select the provider type and enter a valid user name and password for your data source.

Before importing metadata, start the JavaHost process for both offline and online imports.

See Work with Hyperion Financial Management and Hyperion Planning Data Sources.

Review and complete the pre-configuration steps in About Setting Up Hyperion Financial Management Data Sources before importing.

About Importing Metadata from Oracle RPAS Data Sources

Learn about using the Model Administration Tool to import metadata from Oracle RPAS.

When using the Model Administration Tool to import metadata from Oracle RPAS:

  • Oracle RPAS schemas can only be imported on Windows.

  • Before you import RPAS schemas, you must set the Normalize Dimension Tables field value in the ODBC DSN Setup page to Yes for the following reasons:

    • Setting this value to Yes uses an appropriate schema model (the snowflake schema) that creates joins correctly and enables drill down in the data.

    • Setting this value to No uses a star schema model that creates joins between all of the tables, causing an incorrect drill down. Many of the joins created in the star schema more are unnecessary. You should remove the unnecessary joins manually.

    See Set Up ODBC Data Source Names (DSNs).

  • When you import RPAS schemas in the Model Administration Tool, you must import the data with joins. To do this, select the metadata types Keys and Foreign Keys in the Import Metadata Wizard.

  • After you've imported RPAS schemas, you must change the Normalize Dimension Tables field value in the ODBC DSN Setup page back to No. You need to revert this setting back to No after import to enable the Oracle BI Server to correctly generate optimized SQL against the RPAS driver.

    If you don't change the Normalize Dimension Tables setting value to No, most queries fail with an error message similar to the following: 

    [nQSError: 16001] ODBC error state: S0022 code: 0 message: [Oracle Retail][RPAS 
ODBC]Column:YEAR_LABEL not found..[nQSError: 16014] SQL statement preparation 
failed. Statement execute failed.

  • If Oracle RPAS is the only data source, you must set the value of NULL_VALUES_SORT_FIRST to ON in the NQSConfig.INI file. See Administering Oracle Analytics Server for setting values in NQSConfig.INI.

After you import metadata from an Oracle RPAS data source, a database object for the schema is automatically created. Depending on your version of RPAS, you might need to adjust the data source definition in the Database property.

If RPAS is specified in the data source definition Database field and the version of RPAS is prior to 1.2.2, then the Oracle BI Server performs aggregate navigation when the SQL is generated and sent to the database. Because the table name used in the generated SQL is automatically generated, a mismatch between the generated SQL and the database table name could result. To enable the SQL to run, you must:

  • Change the names of tables listed in the metadata so that the generated names are correct.

  • Create tables in the database with the same names as the generated names.

If the database doesn't have tables with the same name or if you want to have the standard aggregate navigation, then you must change the data source definition Database field from RPAS to ODBC Basic. See Create a Database Object Manually in the Physical Layer.