1. Managing Metadata Repositories for Oracle Analytics Server
  2. Build Your Metadata Repository
  3. Import Metadata and Working with Data Sources
  4. Perform Data Source Preconfiguration Tasks

Perform Data Source Preconfiguration Tasks

You might need to perform configuration steps to access the data sources.

These configuration steps are sometimes required before you can import physical objects from your data sources into your repository file, or set up connection pools to your data sources.

For many data sources, you need to install client components. Client components are often installed on the computer hosting the Oracle BI Server for query access, and on the computer hosting the Model Administration Tool (if different) for offline operations such as import. In some cases, you must install client components on the computer where the JavaHost process is located.

This section contains the following topics:

Set Up ODBC Data Source Names (DSNs)

Before you can import from a data source through an ODBC connection, or set up a connection pool to an ODBC data source, you must first create an ODBC Data Source Name (DSN) for that data source on the client computer.

You reference the DSN in the Import Metadata Wizard when you import metadata from the data source.

You can only use ODBC DSNs for import on Windows systems.

  1. In Windows, locate and open the ODBC Data Source Administrator. The ODBC Data Source Administrator dialog appears.
  2. In the ODBC Data Source Administrator dialog, click the System DSN tab, and then click Add.
  3. From the Create New Data Source dialog, select the driver appropriate for your data source, and then click Finish.

    The remaining configuration steps are specific to the data source you want to configure. Refer to the documentation for your data source for more information.

ODBC DSNs on Windows systems are used for both initial import, and for access to the data source during query processing. On Linux systems, ODBC DSNs are only used for data access. See Set Up Data Sources on Linux.

See Set Up Teradata Data Sources.

Set Up Oracle Database Data Sources

When you import metadata from an Oracle Database data source or set up a connection pool, you can include the entire connect string for Data Source Name, or you can use the net service name defined in the tnsnames.ora file.

If you choose to enter only the net service name, you must set up a tnsnames.ora file in the following location within the Oracle Analytics Server environment, so that the Oracle BI Server can locate the entry:

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

You should always use the Oracle Call Interface (OCI) when importing metadata from or connecting to an Oracle Database. Before you can import schemas or set up a connection pool, you must add a TNS names entry to your tnsnames.ora file. See the Oracle Database documentation for more information.

This section contains the following topics:

See Enable NUMERIC Data Type Support for Oracle Database and TimesTen.

Oracle 12c Database In-Memory Data Sources

For all Oracle 12c Database In-Memory data sources, the Oracle BI Server creates tables in memory.

Oracle 12c Database In-Memory is a high-performance in-memory data manager. It uses In-Memory Column Store to store copies of tables and partitions in a special columnar format that exists in memory and provides for rapid scans. See the 12c Release 1 Oracle Database Concepts Guide and Oracle Database Administrator's Guide for more information.

Oracle 12c on Exadata Data Sources

For Oracle 12c Database on Exadata and Oracle 12c Database In-Memory on Exadata data sources, the Oracle BI Server creates tables in memory.

Oracle BI Server uses Exadata Hybrid Columnar Compression (EHCC) by default.

Oracle Exadata Database Machine is the optimal platform for running Oracle Database. Both Oracle 12c Database and Oracle 12c Database In-Memory run on the Oracle Exadata Database Machine. See the documentation included with the Exadata Database Machine for more information.

Advanced Oracle Database Features Supported by Oracle BI Server

The Oracle BI Server supports the compression, Exadata Hybrid Columnar Compression, and In-Memory features to take advantage of native Oracle Database functionality and significantly improve query time.

When you import metadata or specify a database type, the feature set for that database object is automatically populated with default values appropriate for the database type. The Oracle BI Server uses the SQL features with this data source. When a feature is marked as supported (checked) in the Features tab of the Database dialog, the Oracle BI Server pushes the function or calculation to the data source for improved performance. When a function or feature isn't supported in the data source, the calculation or processing is performed in the Oracle BI Server.

The following is information about Oracle Database features supported by Oracle BI Server:

  • Compression

    Compression reduces the size of the database. Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. Compression is used by default. If you create aggregates on your Oracle databases, then compression is applied to the aggregate tables by default.

    When you create a database object for any of the Oracle databases, the COMPRESSION_SUPPORTED feature is automatically applied to the object.

  • Exadata Hybrid Columnar Compression (EHCC)

    Oracle's EHCC is optimized to use both database and storage capabilities on Exadata and enables the highest level of data compression to provide significant performance improvements. By default, Oracle 11g Database on Exadata, Oracle 12c Database on Exadata, and Oracle 12c Database In-Memory on Exadata use this type of compression.

    When you create a database object for any of the Oracle databases, the EHCC_SUPPORTED feature is automatically applied to the object.

    By default, compression is disabled for objects in the Oracle databases. To enable compression for an object, set the object's PERF_PREFER_COMPRESSION flag to on.

  • In-Memory

    – In memory retrieval eliminates seek time when querying the data, which provides faster and more predictable performance than disk. The in memory feature creates tables in memory for Oracle 12c Database In-Memory and Oracle 12c Database In-Memory on Exadata. If you create aggregates on these databases, then the aggregates are created in memory.

    When you create a database object for any of the above mentioned Oracle databases, the INMEMORY_SUPPORTED feature is automatically applied to the object.

Oracle Database Fast Application Notification and Fast Connection Failover

If Fast Application Notification (FAN) events and Fast Connection Failover (FCF) are enabled on the Oracle Database, the Oracle Call Interface (OCI) uses the FAN events and enables FCF for the Oracle Database data sources.

Fast Application Notification (FAN) events and Fast Connection Failover (FCF) run in the background. When a query initiated by a user fails due to the unavailability of an Oracle database, the query fails quickly and the user can then retry the query rather than wait for the database request to time out.

Additional Oracle Database Configuration for Client Installations

You must install the Oracle Database Client on the computer where you performed the client installation.

After installing the Oracle Database Client, create an environment variable called ORACLE_HOME and set it to the Oracle home for the Oracle Database Client. Create an environment variable called TNS_ADMIN, and set the variable to the tnsnames.ora file location of BI_DOMAIN\config\fmwconfig\bienv\core.

Configure Oracle BI Server When Using a Firewall

The presence of a firewall between the Oracle BI Server and the Oracle Database can result in very long query times.

You could experience long query times when using a simple nqcmd query that could take two to three minutes to return results, or when using Answers, you don't get a response after running or validating a SQL statement initiated in Presentation Services.

To improve query time, go to the sqlnet.ora file in BI_DOMAIN\config\fmwconfig\bienv\core and add the BREAK_POLL_SKIP and DISABLE_OOB parameters as follows: 

BREAK_POLL_SKIP=10000
DISABLE_OOB=ON

You perform this configuration change only on the Oracle BI Server. You don't need to change configuration on the Oracle Database or on user client desktops.

DataDirect Drivers and Oracle Database

You must use ODBC DataDirect drivers to establish connections to ODBC data sources.

ODBC DataDirect drivers are also used by the Oracle Platform Security Services (OPSS) security store implementation to access credentials.

DataDirect ODBC framework, version 8.0.2, and Oracle Wire Protocol, version 8.0.0, support Oracle Database 12c connectivity, and are configured for data source name (DSN) and DNS-less connectivity without additional configuration.

The certified Oracle Database versions include:

  • 12.2.1.2 or higher

  • 11.2.0.4 or higher

You can find additional information about the DataDirect drivers in the Progress DataDirect documentation located in the following installation directories:

  • mwhome\bi\common\ODBC\Merant\7.1.6\help

  • mwhome\bi\common\ODBC\Merant\8.0.0\help

  • mwhome\bi\common\ODBC\Merant\8.0.2\help

About Setting Up Oracle OLAP Data Sources

Before you import from an Oracle OLAP data source, ensure that the data source is a standard form Analytic Workspace.

You must install the Oracle Database Client on the computer where you performed the client installation before you can import from Oracle OLAP sources.

The biadminservlet Java process must be running to import from Oracle OLAP data sources, for both offline and online imports. You can use the Deployments option in Weblogic Console or Fusion Middleware Control to check the status of the biadminservlet Java process.

Use either the Administrator or Runtime client install option.

After installing the Oracle Database Client, create an environment variable called ORACLE_HOME, and set the variable to the Oracle home for the Oracle Database Client. Create an environment variable called TNS_ADMIN, and set the variable to the location of the tnsnames.ora file located in BI_DOMAINbidata/components/core/serviceinstances/ssi/oracledb.

Java Data Sources

If you use the JDBC connection type, then the remote Java data sources must connect to Weblogic Server.

If you aren't using JDBC (Direct Driver) this configuration isn't required.

Before you can include JDBC and JNDI data sources in the repository, you must perform the required set up tasks.

You must configure JDBC in the Oracle WebLogic Server. For information about how to perform this configuration, see Using JDBC Drivers with WebLogic Server in the Oracle WebLogic Server documentation.

You must load data sources for importing into the repository. See Load Java Data Sources.

Load Java Data Sources

To make Java data sources available for import into the repository, you must first connect to the Java Datasource server to load the Java metadata.

  1. In the Model Administration Tool, select File, and select Load Java Datasources.
  2. In the Connect to Java Datasource Server dialog, enter the enter hostname, port, and credentials to access the server and load the Java metadata.
  3. Click OK.
The Java metadata has been loaded from the server and is now available for import into the repository.

About Setting Up Oracle TimesTen In-Memory Database Data Sources

Oracle TimesTen In-Memory Database is a high-performance, in-memory data manager.

These preconfiguration instructions assume that you've already installed Oracle TimesTen, see Oracle Data Integrator for more information.

If you plan to create aggregates on your TimesTen source, you must also ensure that PL/SQL is enabled for the instance, and that the PL/SQL first connection attribute PLSQL is set to 1. You can enable PL/SQL at install time, or run the ttmodinstall utility to enable it post-install. See TimesTen In-Memory Database Reference for more information.

This section contains the following topics:

See Enable NUMERIC Data Type Support for Oracle Database and TimesTen.

Configure TimesTen Data Sources

You must configure TimesTen before you can use it as a data source.

  1. On the computer where TimesTen has been installed, create a Data Manager DSN, as a system DSN.
  2. Perform an initial connection to the data store to load the TimesTen database into memory, and then create users and grant privileges. The default user of the data store is the instance administrator, or in other words, the operating system user who installed the database.
  3. On the computer running the Oracle BI Server, install the TimesTen Client.
  4. On the computer where the TimesTen Client has been installed, create a Client DSN, as a system DSN.

If the TimesTen database is installed on the same computer as the TimesTen client, you can specify either the Data Manager DSN or the Client DSN in the Import Metadata Wizard.

After importing data from your TimesTen source, or when manually setting up a database object and connection pool, ensure that your database type and version are set correctly in the Database field of the General tab of the Database dialog. You must also ensure that the Call interface field in the General tab of the Connection Pool dialog is set correctly. See:

Improve Use of System Memory Resources with TimesTen Data Sources

To improve the use of system memory resources, Oracle recommends that you increase the maximum number of connections for the TimesTen server.

To avoid lock timeouts, you might also want to adjust the LockWait interval for the connection as appropriate for your deployment. See LockWait in TimesTen In-Memory Database Reference Guide for more information.

  1. In your TimesTen environment, open the ttendaemon.options file for editing. You can find this file at:

    install_dir\srv\info

  2. Add the following line:
    -MaxConnsPerServer number_of_connections

    To determine number_of_connections, use the following formula: if there are M connections for each connection pool in the Oracle BI repository, N connection pools in the Oracle BI repository, and P Oracle BI Servers, then the total number of connections required is M * N * P.

  3. Save and close the file.
  4. In the ODBC DSN you're using to connect to the TimesTen server, set the Connections parameter to the same value you entered in Step 2:

    • On Windows, open the TimesTen ODBC Setup wizard from the Windows ODBC Data Source Administrator. The Connections parameter is located in the First Connection tab.

    • On Linux, open the odbc.INI file and add the Connections attribute to the TimesTen DSN entry, as follows: 

      Connections=number_of_connections
  5. Stop all processes connecting to TimesTen, such as the ttisql process and the Oracle BI Server.
  6. Stop the TimesTen process.
  7. After you've verified that the TimesTen process has been stopped, restart the TimesTen process.

Configure Oracle BI Server to Access the TimesTen DLL on Windows

If the user that starts Oracle BI Server doesn't have the path to the TimesTen DLL ($TIMESTEN_HOME\lib) in their operating system PATH variable, then you must add the TimesTen DLL path as a variable in the obis.properties file.

  1. Open obis.properties for editing. You can find obis.properties at:

    BI_DOMAIN\config\fmwconfig\bienv\obis

  2. Add the required TimesTen variable TIMESTEN_DLL, and also update the LD_LIBRARY_PATH variable, as shown in the following example. 
    TIMESTEN_DLL=$TIMESTEN_HOME\lib\libttclient.so
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$TIMESTEN_HOME\lib
  3. Save and close the file.
  4. Restart OBIS1.
  5. Repeat these steps on each computer that runs the Oracle BI Server process. If you're running multiple Oracle BI Server instances on the same computer, be sure to update the ias-component tag appropriately for each instance in obis.properties, for example, ias-component id="coreapplication_obis1”, and ias-component id="coreapplication_obis2".

About Setting Up Essbase Data Sources

The Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources.

The Essbase client libraries are installed by default. No additional configuration is required to enable Essbase data source access.

See Configure SSO for Essbase, Hyperion Financial Management, or Hyperion Planning Data Sources for configuration used for authentication using a shared token against Essbase installed with the EPM System Installer.

About Setting up Cloudera Impala Data Sources

These topics provide information about Windows ODBC drivers and Cloudera Impala Metadata.

Use the information in this section to set up Cloudera Impala data sources in the Oracle BI repository.

Obtain Windows ODBC Driver for Cloudera

If you performed a client installation, then you don't have the Windows ODBC driver required for you to import Cloudera Impala metadata.

If you used the Installer to install the Model Administration Tool, then you don't have to perform this procedure.

  1. Go to Cloudera's website.
  2. Click the Downloads link and then click the Impala ODBC Drivers & Connectors link.
  3. In the Download list, locate the required ODBC driver for your Administration Tool platform and click Download Bits to download the installer.
  4. Run the ODBC driver installer to install the driver.

Import Cloudera Impala Metadata Using the Windows ODBC Driver

Cloudera Impala is a massively parallel processing (MPP) SQL query engine that runs natively in Apache Hadoop. Perform this procedure to import Cloudera Impala metadata into the Oracle BI repository.

To perform this procedure, you must have the required Windows ODBC driver. If you've a client installation of the Administration Tool, then you must follow the Obtain Windows ODBC Driver for Cloudera procedure to install the required Windows ODBC driver.

  1. In Windows, locate and open the ODBC Data Source Administrator.
  2. In the ODBC Data Source Administrator dialog, click the System DSN tab, and then click Add.
  3. In the driver list, locate and select a Cloudera Impala driver. Click Finish.
  4. In Cloudera ODBC Driver for Impala DSN Setup, enter the connection details for your Impala instance in these fields:

    • In the Data Source Name field, enter the data source name specified in the connection pool defined in the repository.

    • In the Host field, enter the fully qualified host name or the IP address.

    • In the Port field, enter the port number. The default is 21050.

    • In the Database field, specify the database. This value is usually Default.

  5. If you're setting up a data source for Cloudera Impala driver, then click Test.
  6. If you're setting up a data source for DataDirect Impala driver, then click Test Connect.
  7. In the Administration Tool, select File, then select Import Metadata.
  8. In the Import Metadata wizard, on the Select Data Source screen, confirm that ODBC 3.5 displays in the Connection Type field.
  9. Select the Impala DSN, provide a user name and password, and click Next.
  10. In the Select Metadata Types screen, click Next to accept the default values.
  11. In the Select Metadata Objects screen, go to the Data source view list and select the Impala tables for import and click the > (Import selected) button to move them to the Repository view list.
  12. Click Finish.
  13. In the Physical Layer of the repository, double click the Impala database. The Database dialog appears.
  14. In the Database type field, choose Cloudera Impala, and click OK.
  15. Click Save to save the repository.
  16. Optional: Model the newly imported data as necessary in the Business Model and Mapping layer and the Presentation layer.

About Setting Up Apache Hive Data Sources

These topics provide information about Windows ODBC drivers and Apache Hive.

This section contains the following topics:

Obtain Windows ODBC Driver for Client Installation

If you've a client install of the Administration Tool, you don't have the Windows ODBC driver required for you to import Apache Hive metadata.

To obtain the Windows driver required to perform the import, log in to the My Oracle Support web site support.oracle.com and access DocID 1520733.1. The technical note associated with this DocID includes the required Windows driver, together with the instructions to install the driver and to perform the metadata import from the Hive data source.

Limitations on the Use of Apache Hive

These topics describes the limitations on the use of Hadoop and Hive with Oracle Analytics Server.

This section contains the following topics:

Hive Limitation on Dates

There are limitations with the DATE type with Hive data sources.

Hive supports the Timestamp data type. Use the DATE or DATETIME data type for timestamp columns in the repository's Physical layer.

Hive Doesn't Support Count (Distinct M) Together with Group By M

Learn the limitations of Hive data sources.

Queries similar to the following could cause Hive to crash. 

  • SELECT M, COUNT(DISTINCT M) ... FROM ... GROUP BY M ...

The situation occurs when the attribute in the COUNT(DISTINCT... definition is queried directly and if that attribute is also part of the table or foreign key or level key. Because COUNT(DISTINCT X) together with GROUP BY X always results in the count value of 1, a significant number of occurrences of this case are unlikely to happen.

To avoid this error when using COUNT(DISTINCT...) on a measure, don't include the exact attribute or any attribute in the same level.

Hive Doesn't Support Differing Case Types

Hive requires a strict check on types of the various parts of the Case statement.

This causes a presentation query such as the following to fail in Hive: 

select supplierid, case supplierid when 10 then 'EQUAL TO TEN' when 20 then 
'EQUAL TO TWENTY' else 'SOME OTHER VALUE' end as c2 from supplier order by c2
asc, 1 desc

The full error message in Hive for this query is:

FAILED: Error in semantic analysis: Line 2:32 Argument type mismatch '10': 
The expressions after WHEN should have the same type with that after CASE: 
"smallint" is expected but "int" is found
Exception Thrown for Locate Function with an Out-of-Bounds Start Position Value

Learn how to use the Locate function’s syntax.

The full syntax of the Locate function is of the form:

LOCATE ( charexp1, charexp2, [, startpos] )

where charexp1 is the string to search for within the string charexp2.

The optional parameter startpos is the character position within charexp2 at which to begin the search.

If startpos has a value that's longer than the length of charexp2, such as in the following example: 

select locate('c', 'abcde', 9) from employee

then Hive throws an exception instead of returning 0.

Hive May Crash on Queries Using Substring

Some queries that use the Substring function with a start position parameter value might cause Hive to crash.

The following might cause Hive to crash: 

select substring(ProductName, 2) from Products
Hive Doesn't Support Create Table

As the Apache Hive ODBC driver doesn't support SQLTransact, which is used for creating tables, CREATE TABLE isn't supported by Hive.

Hive May Fail on Long Queries With Multiple AND and OR Clauses

The examples show conditions that could cause Hive data sources to fail.

The following WHERE clauses are examples of conditions that might cause queries to fail in Hive due to their excessive length:

Example 1

        WHERE (Name = 'A' AND Id in (1))
           OR (Name = 'B' AND Id in (2))
           OR  .......
           OR (Name = 'H' AND Id in (8))

Example 2

        WHERE (Id BETWEEN '01' AND '02')
           OR (Id BETWEEN '02' AND '03')
           OR  .......
           OR (Id BETWEEN '07' AND '08'))

Long queries could fail in Hive especially if the queries have conditions with multiple OR clauses each grouping together combinations of AND and BETWEEN sub-clauses as shown in the preceding examples.

Queries with Subquery Expressions May Fail

Queries with subquery expressions might fail in Hive.

If subquery expressions are used, the physical query that Oracle BI Server generates could include mixed data types in equality conditions. Because of Hive issues in equality operators, you could get an incorrect query result.

For example, for the following query:

select ReorderLevel from Product where ReorderLevel in 
  (select AVG(DISTINCT ReorderLevel) from Product);

Oracle BI Server generates the following physical query that includes 'ReorderLevel = 15.0' where ReorderLevel is of type Int and 15.0 is treated as Float: 

Select T3120.ReorderLevel as c1 from Products T3120 
 where (T3120.ReorderLevel = 15.0)

You can correct the mixed data types issue using the following command:

select ReorderLevel from Product where ReorderLevel in 
  (select cast(AVG(DISTINCT ReorderLevel) as integer) from Product);
Hive Doesn't Support Distinct M and M in Same Select List

Learn about the limitations for using Select with Hive data sources.

Queries of the following form aren't supported by Hive: 

  • SELECT DISTINCT M, M  ... FROM TABX

About Setting Up Hyperion Financial Management Data Sources

Use these required steps to configure the Hyperion Financial Management data source.

Hyperion Financial Management 11.1.2.3.x or 11.1.2.4.x can use the ADM native driver or the ADM thin client driver. You can install and configure the ADM thin client driver on Linux operating system.

You can also use the Hyperion Financial Management 11.1.2.3.x and 11.1.2.4.x data sources with Oracle Analytics Server running in a Windows or Linux deployment.

Hyperion Financial Management ADM driver includes the ADM native driver and ADM thin client driver. For both Windows and Linux deployments, ensure that you perform the configuration using the Enterprise Performance Management Configurator.

  • In the Windows and Linux configurations, provide the details for the Hyperion Shared Services Database to register with the Foundation server and the Hyperion Financial Management server.

  • During configuration, make sure to enable DCOM configuration.

  • If you're configuring for Windows, then in the DCOM User Details page, enter a domain user as the user for connecting to the Hyperion Financial Management server. If you're configuring the ADM thin client driver for Linux, then you don't need to perform this step.

In addition, you must edit the obijh.properties file on each system that's running the JavaHost process to include environment variables that are required by Hyperion Financial Management. The JavaHost process must be running to import from Hyperion Financial Management data sources, for both offline and online imports. If you've a client installation of the Model Administration Tool, then see Performing Additional Hyperion Configuration for Client Installations for JavaHost configuration steps.

Important:

You should always use forward slashes (/) instead of backslashes (\) when configuring the EPM paths in the obijh.properties file.

Forward slashes are required in the EPM paths on Windows. Backslashes don't work when configuring the EPM paths in the obijh.properties file.

  1. Locate the obijh.properties at:

    ORACLE_HOME/bi/modules/oracle.bi.cam.obijh/env/obijh.properties

  2. Open the obijh.properties file for editing.

  3. Append the following to the OBIJH_ARGS variable: 

    DEPM_ORACLE_HOME=C:/Oracle/Middleware/EPMSystem11R1 
-DEPM_ORACLE_INSTANCE=C:/Oracle/Middleware/user_projects/epmsystem1 
-DHFM_ADM_TRACE=2

  4. Add the following variables to the end of the obijh.properties file:

    EPM_ORACLE_HOME=C:/Oracle/Middleware/EPMSystem11R1

    EPM_ORACLE_INSTANCE=C:/Oracle/Middleware/user_projects/epmsystem1

  5. Locate the loaders.xml file in:

    ORACLE_HOME/bi/bifoundation/javahost/config/loaders.xml

  6. In the loaders.xml file, locate <!-- BI Server integration code -->.

  7. In the <ClassPath>, add the fm-adm-driver.jar, fm-web-objectmodel.jar, epm_j2se.jar, and epm_hfm_web.jar files using the format shown in the following: 

    <ClassPath>
{%EPM_ORACLE_HOME%}/common/hfm/11.1.2.0/lib/fm-adm-driver.jar;
{%EPM_ORACLE_HOME%}/common/hfm/11.1.2.0/lib/fm-web-objectmodel.jar;
{%EPM_ORACLE_HOME%}/common/jlib/11.1.2.0/epm_j2se.jar;
{%EPM_ORACLE_HOME%}/common/jlib/11.1.2.0/epm_hfm_web.jar;
</ClassPath>

  8. Save and close the file.

  9. Go to the ORACLE_HOME/bi/bifoundation/javahost/lib/obisintegration/adm directory and delete all jar files except for admintegration.jar and admimport.jar.

  10. Restart OBIS1.

  11. Repeat these steps on each computer that runs the JavaHost process.

Perform Additional Hyperion Configuration for Client Installations

If you install the Administration Tool using the Plus Client Installer, you must perform additional configuration before you can perform offline imports from Hyperion Financial Management data sources.

When importing from Hyperion Financial Management data sources in offline mode, the Model Administration Tool must point to the location of a running JavaHost.

The steps in this section are only required for client installations of the Model Administration Tool.

  1. Close the Model Administration Tool.
  2. On the same computer as the Model Administration Tool, open the local, use a text editor to open the NQSConfig.INI file located in:

    BI_DOMAIN\config\fmwconfig\biconfig\OBIS

  3. Locate the JAVAHOST_HOSTNAME_OR_IP_ADDRESSES parameter.
  4. Update the JAVAHOST_HOSTNAME_OR_IP_ADDRESSES parameter to point to a running JavaHost, using a fully-qualified host name or IP address and port number. For example:

    JAVAHOST_HOSTNAME_OR_IP_ADDRESSES = "myhost.example.com:9810"

    In a full (non-client) Oracle Analytics Server installation, you can't manually edit the JAVAHOST_HOSTNAME_OR_IP_ADDRESSES setting because it's managed by Fusion Middleware Control.

  5. Save and close the file.

Set Up Oracle RPAS Data Sources

Oracle BI Server can connect to Oracle RPAS (Retail Predictive Application Server) data sources through ODBC DSNs.

To set up Oracle RPAS data sources, you must first install the Oracle RPAS ODBC driver. During set up of the ODBC DSN, you must select the SQLExtendedFetch option, select DBMS from the Authentication Method list, and select No from the Normalize Dimension Tables list. See About Importing Metadata from Oracle RPAS Data Sources.

On Windows systems, you can connect to Oracle RPAS data sources for both initial import and for access to the data source during query processing. On Linux systems, you can only connect to Oracle RPAS data sources for data access.

Set Up Teradata Data Sources

You can use ODBC to access Teradata data sources.

See Set Up ODBC Data Source Names (DSNs).

After you've installed the latest Teradata ODBC driver and set up an ODBC DSN, you must add the lib directory for your Teradata data source to your Windows system Path environment variable. For example:

C:\Program Files\Teradata\Client\15.00\ODBC Driver for Teradata nt-x8664\Lib

You must edit obis.properties on each computer running the Oracle BI Server to include required Teradata variables.

  1. Open obis.properties located in:

    BI_DOMAIN\config\fmwconfig\bienv\obis

  2. In PATH, LD_LIBRARY_PATH, and LIBPATH enter the required variable information as shown in the following example.
    PATH=C:\Program Files\Teradata\Client\15.00\ODBC Driver for Teradatant-x8664\Lib;
LD_LIBRARY_PATH=C:\Program Files\Teradata\Client\15.00\ODBC Driver forTeradata nt-x8664\Lib;
LIBPATH=C:\Program Files\Teradata\Client\15.00\ODBC Driver for Teradatant-x8664\Lib;

    Note:

    If you use the default location when installing the Teradata client, then the PATH variable might exceed the 1024 character limit imposed by Windows. To avoid this issue, install the Teradata client in a directory with a shortened path name such as C:\TD, or use shortened 8.3 file names such as C:\PROGRA~1\Teradata\Client\13.10\ODBCDR~1\Bin instead of C:\Program Files\Teradata\Client\13.10\ODBC Driver for Teradata\Bin.

    To determine the correct 8.3 file names, run dir /x from the appropriate directory. For example: 

    C:\>dir /x
 Volume in drive C has no label.
 Volume Serial Number is 0000-XXXX
 Directory of C:\
08/25/2008  03:36 PM   <DIR>    DATAEX~1    DataExplorer
04/20/2007  01:38 PM   <DIR>                dell
08/28/2010  10:49 AM   <DIR>    DOCUME~1    Documents and Settings
07/28/2008  04:50 PM   <DIR>    ECLIPS~2    EclipseWorkspace
09/07/2007  11:50 AM   <DIR>                Ora92
09/07/2007  11:50 AM   <DIR>                oracle
05/21/2009  05:15 PM   <DIR>                OracleBI
05/21/2009  05:12 PM   <DIR>    ORACLE~1    OracleBIData
03/02/2011  04:51 PM   <DIR>    PROGRA~1    Program Files
  3. Save and close the file.
  4. Restart OBIS1.
  5. Repeat these steps on each computer that runs the Oracle BI Server process. If you're running multiple Oracle BI Server instances on the same computer, be sure to update the ias-component tag appropriately for each instance in obis.properties, for example, ias-component id="coreapplication_obis1" and ias-component id="coreapplication_obis2".

Avoid Spool Space Errors for Queries Against Teradata Data Sources

Some queries against Teradata might get a No more spool space error from the data source.

This error can occur for DISTINCT queries resulting from selecting All Choices in the Filters pane in Answers.

To avoid this error, you can ensure that the Oracle BI Server rewrites the query to use GROUP BY rather than DISTINCT for these queries by ensuring that the following conditions are met:

  • There is only one dimension column in the projection list, and it's a target column rather than a combined expression.

  • The original query from Answers is requesting DISTINCT, and doesn't include a GROUP BY clause

  • The FROM table is a real physical table rather than an opaque view.

  • The FROM table is an atomic table, not a derived table.

  • The following ratio must be less than the threshold:

    (the distinct number of the projected column) / (number of rows of FROM table)

    Both values used in this ratio come from the repository metadata. To populate these values, click Update Row Count in the Model Administration Tool for both of the following objects:

    • The FROM physical table

    • The physical column for the projected column

    By default, the threshold for this ratio is 0.15. To change the threshold, create an environment variable on the Oracle BI Server computer called SA_CHOICES_CNT_SPARSITY and set it to the new threshold.

Enable NUMERIC Data Type Support for Oracle Database and TimesTen

You can enable NUMERIC data type support for Oracle Database and TimesTen data sources.

When NUMERIC data type support is enabled, NUMBER columns in Oracle Database and TimesTen data sources are treated as NUMERIC to provide greater precision. In addition, literals are instantiated as NUMERIC instead of DOUBLE for Oracle Database and TimesTen data sources.

See Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.

  1. Set ENABLE_NUMERIC_DATA_TYPE to YES in NQSConfig.INI file located in BI_DOMAIN/config/fmwconfig/biconfig/OBIS.
  2. Enable the NUMERIC_SUPPORTED database feature in the Physical layer database object. See SQL Features Supported by a Data Source for more about how to set database features.

The decimal/numeric data from other database types is mapped as DOUBLE when the ENABLE_NUMERIC_DATA_TYPE parameter is set to YES.

The data type of physical columns imported prior to changing the ENABLE_NUMERIC_DATA_TYPE setting remain unchanged. For existing DOUBLE physical columns, you must manually update the data type to NUMBER as needed.

Cast numeric data types to other number data types, and cast other number data types to numeric data types.

Numeric data type support isn't available when using the Oracle BI Server JDBC driver.

Your performance overhead could increase when numeric data types are enabled resulting from the higher number of bits for numeric data.

Configure Essbase to Use a Shared Logon

Shared logon is required and enabled by default for all Essbase connection pools.

You can't disable the Shared logon setting in the General tab of the Connection Pool Properties dialog.

Configure SSO for Essbase, Hyperion Financial Management, or Hyperion Planning Data Sources

Configure SSO and shared logon to use Hyperion Financial Management, or Hyperion Planning installed with the EPM System Installer as a data source.

If you use Hyperion Financial Management, or Hyperion Planning installed with the EPM System Installer as a data source for the Oracle BI Server, then use the SSO token option with shared logon. In this case, Oracle BI Server uses impersonation to connect to Hyperion Planning. The user details provided in the shared logon is used to connect to the data source, and the processing user is the impersonated user. The impersonated users should exist in the identity store used by Hyperion Financial Management or Hyperion Planning.

The user and the Enterprise Performance Management user must use the same identity store.

Note:

Essbase no longer supports CSS token based authentication. As a result, you must update the connection pools to use EssLoginAs authentication. EssLoginAS authentication provides reliable and better performance than CSS token based authentication, and provides the shared logon credentials of the Essbase administrator in the connection pool.