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 sources such as Essbase, Oracle OLAP, Hyperion Financial Management, Hyperion Planning, Microsoft Analysis Services, and SAP/BW (SAP/Business Warehouse).

The primary differences between setting up multidimensional data sources and relational data sources are in the Physical layer. The setup in the Business Model and Presentation layers for multidimensional data sources and relational data sources is almost identical.

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 for more information.

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.

It is recommended that you remove hierarchies and columns from the Physical layer if they will not be used in the business model. This eliminates maintaining unnecessary objects in the Administration Tool and might 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 import metadata from a multidimensional data source:

  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 the Select Data Source screen, in the Connection Type field, select the type of connection appropriate for your data source.

    The remaining fields and options on the Select Data Source screen vary according to the connection type you selected. The table describes the multidimensional connection types.

    Connection Type Description

    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) for more information.

    Essbase 9+

    Use this option for Essbase 9 or Essbase 11. 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 if it is part of an Essbase Cluster, you must include the port number in the Essbase Server field, in the format hostname:port.

    Note the following:

    • You can import metadata from an Essbase Cluster, but you must still specify an individual Essbase Server host name and port number in the Essbase Server field.

    • See Working with Essbase Data Sources for information about how data from Essbase data sources is modeled in the Physical layer.

    XMLA

    The XMLA connection type is used for Microsoft Analysis Services and SAP/BW. Enter the URL of a data source from which to import the schema. Then, enter 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.

    For Target Database, do one of the following:

    • Select New and enter the name you want to use for the new database object.

    • Select Existing and then click Browse to select an existing database object.

    Oracle OLAP

    Provide the name of the data source (net service name) in the Data Source Name field, then provide 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. Make sure that the name you use is from the tnsnames.ora file within the Oracle Business Intelligence environment. You can also choose to enter a full connect string rather than the net service name.

    For URL, provide the URL of the biadminservlet. This servlet is used for Oracle OLAP metadata imports. The name of the servlet is services. For example, enter a string similar to the following in the URL field:

    http://localhost:9704/biadminservlet/services

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

    See Working with Oracle OLAP Data Sources for information about how data from Oracle OLAP data sources is modeled in the Physical layer.

    Note:

    Oracle Database data sources with the OLAP option can contain both relational tables and multidimensional tables. However, you should avoid having both table types in the same database object in the Administration Tool, because you may 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 may need this feature enabled for Oracle Database relational tables.

    As a best practice, create two separate database objects for relational and 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 using the ADM thin client driver, include the driver and application name (cube name), in the following format:

    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 all of the needed .jar files, go to your instance of Hyperion, locate and copy the adm.jar, ap.jar, and HspAdm.jar files and paste them 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]

    You also need to 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 for information about how data from Hyperion Financial Management and Hyperion Planning data sources is modeled in the Physical layer.

    Note:

    Review and complete the preconfiguration steps described in About Setting Up Hyperion Financial Management Data Sources before import.

    SAP BW Native

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

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

    • Additional Parameters: Additional connection string parameters in the format param=value. Delimit multiple parameters with a colon. This field is optional.

    • User Name: A valid user name for the data source.

    • Password: The corresponding user password. The password is case-sensitive.

    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

    Note: Be sure to complete the preconfiguration steps described in Setting Up SAP/BW Data Sources before import.

    Other data source types are described in other sections:

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

  3. For Oracle RPAS data sources only, the Select Metadata Types screen is displayed. For Oracle RPAS, select Tables, Keys, and Foreign Keys. Then, click Next.
  4. In the Select Metadata Objects screen, 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

    For Essbase data sources, select Import UDAs if you want to import UDAs (user-defined attributes).

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

About Importing Metadata from Oracle RPAS Data Sources

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

Whent 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) for more information.

  • 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 more information about 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 for more information.