Importing 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 ensure that the physical cube columns have the correct aggregation rule and that the hierarchy type is correct. See Working with Physical Hierarchy Objects.

Caution:

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

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

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 will not display in the right-click menu for the database's physical tables.

To learn about the available connection types, see Multidimensional Connection Options.
  1. In the Administration Tool, select File, then select Import Metadata.

    Note:

    If you have already defined an existing database and connection pool, you can right-click the connection pool in the Physical layer and select Import Metadata. The Import Metadata Wizard appears with the information on the Select Data Source screen pre-filled. You can also use this method to perform incremental imports.

  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 (Oracle RPAS data sources only), 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.
  7. A list of warning messages display if some objects were not imported, appears, resolve the issues as needed.

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.

It is also a good practice to visually inspect the imported data in the Physical layer, such as physical columns and hierarchical levels, to ensure 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 Oracle BI 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 Setting 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 Working 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 Business Intelligence 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 Working 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 does not deliver all of the required client driver .jar files. To ensure that you have 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

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 Working 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.

SAP BW Native

The SAP BW Native connection type requires the following information:

  • System IP or Hostname: The host name or IP address of the SAP data server. This field corresponds to the parameter ashost in the SAP/BW connect string.

  • System Number: The SAP system number. This is a two-digit number assigned to an SAP instance, also called Web Application Server, or WAS. This field corresponds to the parameter sysnr in the SAP/BW connect string.

  • Client Number: The SAP client number. This is a three-digit number assigned to the self-contained unit called Client in SAP. A Client can be a training, development, testing, or production client, or it can represent different divisions in a large company. This field corresponds to the parameter client in the SAP/BW connect string.

  • In Language supply the SAP language code used when logging into the data source, for example, EN for English or DE for German. The Language field corresponds to the value in the lang parameter in the SAP/BW connect string.

  • When supplying additional parameters in the connection string user the format param=value. Delimit multiple parameters with a colon.

  • Provide a valid User Name: A valid user name and password for the data source.

The first five fields constitute the elements of the SAP/BW connect string, in the format:

ashost=value:sysnr=value:client=value:lang=value:
additional_param=value

For example:

ashost=10.30.0.19:sysnr=00:client=100:lang=EN

About Importing Metadata from Oracle RPAS Data Sources

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

When using the 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 less appropriate schema model (the star schema) that creates joins between all of the tables, causing drill down to not work correctly. Many of the joins created in this way are unwanted, and would need to be removed manually.

    See Setting Up ODBC Data Source Names (DSNs).

  • When you import RPAS schemas in the 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 have 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.

    Note: If you do not change the Normalize Dimension Tables setting value to No, most queries will 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 System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 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: Database property (located in the Database dialog's General tab) for the following reason.

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 and not as it typically does in the logical table source navigation. Because the table name used in the generated SQL is automatically generated, there will be a mismatch between the generated SQL and the database table name. 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 does not have tables with the same name or if you want to have the standard aggregate navigation within Oracle Business Intelligence, then you must change the data source definition Database field from RPAS to ODBC Basic. See Creating a Database Object Manually in the Physical Layer.