You might need to perform configuration steps so that Oracle Business Intelligence can 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 typically installed on the computer hosting the Oracle BI Server for query access, and on the computer hosting the Administration Tool (if different) for offline operations such as import. In some cases, client components must be installed on the computer where the JavaHost process is located.
This section contains the following topics:
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.
ODBC DSNs on Windows systems are used for both initial import, and for access to the data source during query execution. On UNIX systems, ODBC DSNs are only used for data access. See Setting Up Data Sources on Linux and UNIX.
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 Business Intelligence environment, so that the Oracle BI Server can locate the entry:
BI_DOMAIN\config\fmwconfig\bienv\core
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:
Advanced Oracle Database Features Supported by Oracle BI Server
Oracle Database Fast Application Notification and Fast Connection Failover
Additional Oracle Database Configuration for Client Installations
Oracle Database Connection Errors in Windows 7 64-bit Environments
See Enabling NUMERIC Data Type Support for Oracle Database and TimesTen.
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, which stores 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.
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.
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 is not 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 above mentioned 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 above mentioned Oracle databases, the EHCC_SUPPORTED feature is automatically applied to the object.
By default, compression is disabled for objects in the above mentioned Oracle databases. To turn compression on 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.
If Fast Application Notification (FAN) events and Fast Connection Failover (FCF) are enabled on the Oracle Database, then the Oracle Call Interface (OCI) loaded by the BI Server utilizes the FAN events and enable FCF for the Oracle Database data sources.
This functionality runs in the background. When an Oracle Business Intelligence query initiated by an analysis user fails due to the Oracle database being unavailable, the query fails quickly and the user can then retry the query rather than waiting for the database request to time out.
You must install the Oracle Database Client on the computer where you performed the client installation.
You must perform the Oracle Database Client install before you can import from Oracle Database sources. Use either the Administrator or Runtime client install option.
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. Then, create an environment variable called TNS_ADMIN and set it to the location of the tnsnames.ora file, which is BI_DOMAIN\config\fmwconfig\bienv\core.
The presence of a firewall between the Oracle BI Server and the Oracle Database can result in very long query times.
For example, you could experience long query times when using a simple nqcmd query and it takes two to three minutes to return results, or when using Answers, you do not get a response after executing 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 do not need to change configuration on the Oracle Database or on user client desktops.
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.
In Oracle BI Enterprise Edition 12.2.1.2.0 DataDirect ODBC framework version 8.0.1 and Oracle Wire Protocol version 7.1.5 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
To support Oracle BI Enterprise Edition 12.2.1.2.0, you must configure the 12.2.0.1 version of Oracle Database to allow older client versions to connect. Set the minimum authentication protocol allowed when connecting to Oracle Database instances to version 11 by adding the following setting to your Oracle Database server sqlnet.ora file:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
You can find additional information about the DataDirect drivers in the Progress DataDirect documentation located in the following Oracle BI EE 12c installation directories:
mwhome\bi\common\ODBC\Merant\7.1.5\help
mwhome\bi\common\ODBC\Merant\8.0.1\help
If you are running Oracle BI EE on a Windows 7 64-bit computer, you must ensure that the default authentication service is not set to use Windows domain credentials.
You might receive a connection error when importing from an Oracle Database because the Administration Tool will attempt to log in using your Windows domain credentials.
Check the sqlnet.ora file in BI_DOMAIN\config\fmwconfig\bienv\core to ensure that the AUTHENTICATION_SERVICES parameter appears as follows:
SQLNET.AUTHENTICATION_SERVICES= (NONE)
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 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_DOMAIN\config\fmwconfig\bienv\core.
If you use the JDBC connection type, then the remote Java data sources must connect to Weblogic Server.
If you are not using JDBC (Direct Driver) this configuration is not 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 Loading 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.
Oracle TimesTen In-Memory Database is a high-performance, in-memory data manager that supports both ODBC and JDBC interfaces.
These preconfiguration instructions assume that you have already installed Oracle TimesTen, see Oracle Data Integrator for more information.
Note:
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 Enabling NUMERIC Data Type Support for Oracle Database and TimesTen.
You must configure TimesTen before you can use it as a data source for Oracle Business Intelligence.
See TimesTen In-Memory Database Installation Guide, “Defining a Data Manager DSN” and “Managing Access Control” in TimesTen In-Memory Database Operations Guide.
See Configuring Database Connections Using Native ODBC Drivers.
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:
Oracle Exalytics In-Memory Machine for specific instructions on setting up TimesTen sources on the Oracle Exalytics Machine
See System Requirements and Certification for supported TimesTen versions for Oracle Business Intelligence.
To improve the use of system memory resources, Oracle recommends that you increase the maximum number of connections for the TimesTen server.
Note:
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.
The Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources.
The Essbase client libraries are installed by default with Oracle BI EE. No additional configuration is required to enable Essbase data source access for full installations of Oracle BI EE.
See Configuring 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.
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. This topic contains the following sections:
If you performed a client installation, then you do not have the Windows ODBC driver required for you to import Cloudera Impala metadata.
If you used the Oracle Business Intelligence Installer to install the Oracle BI Administration Tool, then you do not have to perform this procedure.
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 have a client installation of the Administration Tool, then you must follow the Obtaining Windows ODBC Driver for Client Installation procedure to install the required Windows ODBC driver.
These topics provide information about Windows ODBC drivers and Apache Hive.
This topic contains the following sections:
If you have a client install of the Administration Tool, then you will not 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.
These topics describes the limitations on the use of Hadoop and Hive with Oracle Business Intelligence.
This section contains the following topics:
When Hive data sources, there are limitations with the DATE type.
Hive supports the Timestamp data type. Timestamp columns should be given the type DATE or DATETIME in the repository's Physical layer.
Learn the limitations of Hive data sources.
Queries of the form:
SELECT M, COUNT(DISTINCT M) ... FROM ... GROUP BY M ...
may cause Hive to crash.
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.
Note:
Because COUNT(DISTINCT X) together with GROUP BY X always results in the count being 1, a significant number of occurrences of this case are unlikely to happen.
To avoid such errors, when using COUNT(DISTINCT...) on a measure, do not include the exact attribute or any attribute in the same level.
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
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 is 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.
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
As the Apache Hive ODBC driver does not support SQLTransact, which is used for creating tables, CREATE TABLE is not supported by Hive.
Shows examples of conditions that could cause Hive data sources to fail.
The following WHERE clauses are examples of conditions that may 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'))
In general, long queries may fail in Hive, but particularly if they have conditions with many OR clauses, each grouping together combinations of AND and BETWEEN sub-clauses, as shown in the preceding examples.
Queries with subquery expressions might fail in Hive.
If subquery expressions are used, the physical query that Oracle BI Server generates may include mixed data types in equality conditions. Because of Hive issues in equality operators, the query result may not be correct.
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)
This can be corrected by using the following command:
select ReorderLevel from Product where ReorderLevel in (select cast(AVG(DISTINCT ReorderLevel) as integer) from Product);
These topics describes required configuration steps to use Hyperion Financial Management as a 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. The ADM thin client driver can be installed and configured on Linux.
Note:
You can use the Hyperion Financial Management 11.1.2.3.x and 11.1.2.4.x data sources with Oracle BI EE running in a Windows or Linux deployment.
Installing the Hyperion Financial Management ADM driver includes the drivers, 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 are 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 are configuring the ADM thin client driver for Linux, then you do not need to perform this step.
In addition, you must edit the obijh.properties file on each system that is running the Oracle Business Intelligence JavaHost process to include environment variables that are required by Hyperion Financial Management.
Note:
The JavaHost process must be running to import from Hyperion Financial Management data sources, for both offline and online imports. If you have a client installation of the 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 do not work when configuring the EPM paths in the obijh.properties file.
Locate the obijh.properties at:
ORACLE_HOME/bi/modules/oracle.bi.cam.obijh/env/obijh.properties
Open the obijh.properties file for editing.
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
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
Locate the loaders.xml file in:
ORACLE_HOME/bi/bifoundation/javahost/config/loaders.xml
In the loaders.xml file, locate <!-- BI Server integration code -->.
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>
Save and close the file.
Go to the ORACLE_HOME/bi/bifoundation/javahost/lib/obisintegration/adm directory and delete all jar files except for admintegration.jar and admimport.jar.
Restart OBIS1.
Repeat these steps on each computer that runs the Oracle Business Intelligence JavaHost process.
If you install the Administration Tool using the Oracle Business Intelligence Enterprise Edition 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 Administration Tool must point to the location of a running JavaHost.
The steps in this section are only required for client installations of the Administration Tool.
You can connect to SAP/BW data sources using either the XMLA connection type, or the SAP BW Native connection type (BAPI).
You should verify that SAP BW Native connections are available for your platform.
See System Requirements and Certification.
To connect to SAP/BW data sources using the SAP BW Native connection type, you must first download the OBIEE BAPI Adapter for SAP .
Follow the configuration instructions in the documentation provided with the download.
No preconfiguration steps are required to connect to SAP/BW over XMLA.
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 execution. On UNIX systems, you can only connect to Oracle RPAS data sources for data access.
You can use ODBC to access Teradata data sources.
See Setting Up ODBC Data Source Names (DSNs).
After you have 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.
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 is a target column rather than a combined expression.
The original query from Answers is requesting DISTINCT, and does not 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 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.
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 in Oracle Business Intelligence to provide greater precision. In addition, literals are instantiated as NUMERIC instead of DOUBLE for Oracle Database and TimesTen data sources.
See Numeric Literals in the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.
ENABLE_NUMERIC_DATA_TYPE to YES in NQSConfig.INI file located in BI_DOMAIN/config/fmwconfig/biconfig/OBIS.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 is not 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.
Shared logon is required and enabled by default for all Essbase connection pools.
You cannot disable the Shared logon setting in the General tab of the Connection Pool Properties dialog.
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 you need to authenticate using a shared token.
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 you need to authenticate using a shared token. For Hyperion Financial Management or Hyperion Planning, you can use either a CSS token or an SSO token.
The Oracle Business Intelligence 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.