This chapter describes how to set up data sources for use with Oracle Business Intelligence when the Oracle BI Server is running on Linux or UNIX.
Most repository development is performed on Windows, because the Administration Tool runs only on Windows. When you move to a production system, however, you can choose to run the Oracle BI Server on a Linux or UNIX platform.
See "System Requirements and Certification" for information about supported Linux and UNIX platforms.
This chapter contains the following topics:
When the Oracle BI Server is running on Linux or UNIX, most data source connections are for query-only access. The Administration Tool is used for importing objects and is a Windows-only tool. Because of this, data source connections for import must be set up on Windows.
Note that some data source connections on Linux and UNIX do support write operations for special functions, like data source connections for write-back, usage tracking, and annotations for Oracle Scorecard and Strategy Management.
When the Oracle BI Server is running on Linux or UNIX and you need to update database object settings (such as the database type) or connection pool settings, you can copy the repository file to a Windows computer, make the changes using the Administration Tool on Windows, and then copy the repository file back to the Linux or UNIX computer.
There are three types of data source connections on Linux and UNIX platforms:
Native data source gateway connections, such as OCI for Oracle Database or DB2 CLI for IBM DB2
ODBC connections using the DataDirect Connect ODBC drivers that are bundled with Oracle Business Intelligence
Native ODBC connections using external drivers, such as for Teradata data sources
Note that you cannot have a single repository that contains both DataDirect Connect ODBC connections and native ODBC connections.
You can connect to both Oracle Database and DB2 using native gateways (OCI and DB2 CLI, respectively).
For Oracle Database, note the following:
The Oracle BI Server uses the Oracle Call Interface (OCI) to connect to the database. OCI is installed by default with Oracle BI Enterprise Edition. You must use the bundled version to connect.
In the tnsnames.ora file, the Oracle Database alias (the defined entry name) must match the Data Source Name used in the repository connection pools of all physical Oracle databases.
When connecting to an Oracle Database data source, you can include the entire connect string, 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:
ORACLE_HOME/network/admin
You must also edit the user.sh file to set environment variables for the database client.
For DB2, you must install the appropriate database client on the computer running the Oracle BI Server, then edit the user.sh file to set environment variables for the database client.
To edit the user.sh file to set environment variables for Oracle Database or DB2:
Open the user.sh file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/user.sh
Include the appropriate environment variable settings for the database client of your choice. Ensure that you point to the appropriate libraries, depending on whether you are using a 32-bit or 64-bit database. See Example 16-1 for sample values.
Save and close the file.
Example 16-1 Sample user.sh Entries for Oracle Database and DB2 (32-Bit)
This example shows sample entries in user.sh for Oracle Database and DB2 on various platforms.
############################################################### # Linux: Oracle BI 32 bit mode ################################################################ #set +u # Oracle Parameters #--------------------------- # Make sure that Oracle DB 32 bit Client is installed #ORACLE_HOME=/export/home/oracle/10g #export ORACLE_HOME #TNS_ADMIN=$ORACLE_HOME/network/admin #export TNS_ADMIN #PATH=$ORACLE_HOME/bin:/opt/bin:$PATH #export PATH #LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH #export LD_LIBRARY_PATH # If you have Linux 64 bit Platform, and would like to run Oracle BI 32 bit # then you must install Oracle DB 64 bit client, and this client comes with # 32 bit libraries under $ORACLE_HOME/lib32. The LD_LIBRARY_PATH in this case # shall be like this: #LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$LD_LIBRARY_PATH #export LD_LIBRARY_PATH # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 32 lib file #. /DB2ISTANCE/sqllib/db2profile #--------------------------- ############################################################### # Solaris: Oracle BI 64 bit mode ############################################################### #set +u # Oracle Parameters #--------------------------- # Make sure to install Oracle DB 64 bit Client #ORACLE_HOME=/export/home/oracle/10g #export ORACLE_HOME #TNS_ADMIN=$ORACLE_HOME/network/admin #export TNS_ADMIN #PATH=$ORACLE_HOME/bin:/opt/bin:$PATH #export PATH #LD_LIBRARY_PATH_64=$ORACLE_HOME/lib:$LD_LIBRARY_PATH_64:/opt/j2se/jre/lib/sparc #export LD_LIBRARY_PATH_64 #--------------------------- # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 64 lib file #. /DB2ISTANCE/sqllib/db2profile #LD_LIBRARY_PATH_64=/DB2ISTANCE/sqllib/lib:$LD_LIBRARY_PATH_64 #export LD_LIBRARY_PATH_64 #--------------------------- ############################################################### # HPUX Itanium: Oracle BI 64 bit mode ############################################################### #set +u # Oracle Parameters #--------------------------- #ORACLE_HOME=/export/home/oracle/10g #export ORACLE_HOME #TNS_ADMIN=$ORACLE_HOME/network/admin #export TNS_ADMIN #PATH=$ORACLE_HOME/bin:/opt/bin:$PATH #export PATH #SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH:/opt/j2se/jre/lib/hp700 #export SHLIB_PATH #--------------------------- # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 64 lib file #. /DB2ISTANCE/sqllib/db2profile #SHLIB_PATH=/DB2ISTANCE/sqllib/lib:$SHLIB_PATH #export SHLIB_PATH #--------------------------- ############################################################### # AIX: Oracle BI 64 bit mode ############################################################### #set +u # Oracle Parameters #--------------------------- #ORACLE_HOME=/export/home/oracle/10g #export ORACLE_HOME #TNS_ADMIN=$ORACLE_HOME/network/admin #export TNS_ADMIN #PATH=$ORACLE_HOME/bin:/opt/bin:$PATH #export PATH #LIBPATH=$ORACLE_HOME/lib:$LIBPATH:/opt/j2se/jre/lib/sparc #export LIBPATH #--------------------------- # DB2 Parameters #--------------------------- #make sure the /DB2ISTANCE/sqllib/lib points to 64 lib file #. /DB2ISTANCE/sqllib/db2profile #---------------------------
Note that the shell script excerpts shown are examples only and are not recommendations for particular software platforms. See "System Requirements and Certification" for information about supported software platforms.
If you are having trouble connecting to an Oracle Database using OCI, check to ensure that the following conditions are true:
The computer running the Oracle BI Server must use Oracle Call Interface (OCI) to connect to the database.
If you choose not to use the entire connect string in the repository connection pool, you must ensure that a valid tnsnames.ora file is set up in the following location within the Oracle Business Intelligence environment, so that the Oracle BI Server can locate the entry:
ORACLE_HOME/network/admin
If you choose not to use the entire connect string in the repository connection pool, ensure that the net service name in the tnsnames.ora file matches the Data Source Name used in the connection pool.
For example, in the following example of a tnsnames.ora entry, the corresponding Oracle BI repository connection pool Data Source Name is ITQA2.
ITQA2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ITQALAB2)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ITQALAB2.corp)
)
)
The following procedure shows how to check repository database and connection pool settings against the Oracle tnsnames.ora settings.
To check that the repository database and connection pool settings are correct:
Open the repository in the Administration Tool.
In the Physical layer, double-click the database you want to check to display the Database dialog.
On the General tab, in the Data source definition: Database field, ensure that the appropriate Oracle Database version is selected. Then, click OK.
Open the Connection Pool dialog for this data source. You might need to expand the database object in the Physical layer to see the connection pool object.
In the Connection Pool dialog, check that the following is true:
The Call interface field displays the appropriate value for the release of the Oracle Database you are using.
The Data source name field displays the Oracle Database net service name that you defined in the tnsnames.ora entry.
The User name and password fields contain the correct values.
Change the values if necessary, then click OK.
In the Oracle Business Intelligence environment, open the tnsnames.ora file located in the following directory:
ORACLE_HOME/network/admin
Check that a valid net service name exists with the following characteristics:
Matches the connection pool settings for the Data Source Name
Specifies the targeted Oracle physical database
This topic applies if both of the following are true:
You are using the Update Rowcount functionality in the Administration Tool in offline mode.
You are running a heterogeneous environment, such as the Oracle BI Server on UNIX, while remote administrators run the Administration Tool on Windows computers.
When using the Update Rowcount functionality in offline mode, the Administration Tool uses local data sources on the client computer, not the server data sources. Therefore, Oracle Database or DB2 clients must be configured on the Windows computer running the Administration Tool so that the following conditions are true:
Data sources point to the same database identified in the Oracle Business Intelligence user.sh file on the UNIX server.
The name of the local data source must also match the name of the data source defined in the Connection Pool object in the physical layer of the Oracle BI repository (.rpd) file.
If these conditions are not true, and if the server and client data sources are pointing at different databases, then erroneous updated row counts or incorrect results appear.
Oracle Business Intelligence provides DataDirect Connect ODBC drivers and driver managers for Linux and UNIX operating systems for connectivity to Microsoft SQL Server, Sybase ASE, and Informix databases.
After Oracle Business Intelligence is installed, the DataDirect Connect ODBC 32-bit drivers are installed in ORACLE_HOME/common/ODBC/Merant/5.3/lib. The 64-bit drivers are installed in ORACLE_HOME/common/ODBC/Merant/5.3/lib64.
Note:
By default, Oracle Business Intelligence uses the DataDirect 5.3 drivers. However, you can configure Oracle Business Intelligence to use the DataDirect 7.0.1 drivers, which are included with your installation or upgrade. See "Configuring Oracle Business Intelligence to Use DataDirect 7.0.1 Drivers" for the steps that you need to follow to configure the system to use the drivers.
Communication between database clients and servers is typically independent of the widths and data paths. In other words, the 32-bit database drivers can communicate with 64-bit database servers, and vice versa.
You do not need to set the ODBCINI environment variable to set up the DataDirect Connect ODBC drivers. This variable is set automatically during installation.
Refer to "System Requirements and Certification" for information about supported operating systems, databases, and driver versions for the DataDirect Connect ODBC drivers.
This section contains the following topics:
Configuring Oracle Business Intelligence to Use DataDirect 7.0.1 Drivers
Configuring the DataDirect Connect ODBC Driver for Microsoft SQL Server Database
Configuring the DataDirect Connect ODBC Driver for Sybase ASE Database
Configuring the DataDirect Connect ODBC Driver for Informix Database
When upgraded or installed, Oracle Business Intelligence puts the DataDirect 7.0.1 drivers in ORACLE_HOME/common/ODBC/Merant/701. By default, Oracle Business Intelligence is configured to use the DataDirect 5.3 drivers. However, you can configure Oracle Business Intelligence to use the DataDirect 7.0.1 drivers.
Note that configuring Oracle Business Intelligence to use the DataDirect 7.0.1 drivers is optional. However, Oracle recommends that you use the DataDirect 7.0.1 drivers because they contain fixes to issues present in the DataDirect 5.3 drivers.
To configure Oracle Business Intelligence to use DataDirect 7.0.1 Drivers:
Open opmn.xml for editing. You can find opmn.xml at:
ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml
Locate the coreapplication_obis1 tag, and within that tag, locate the LD_LIBRARY_PATH variable. For example:
<variable id="LD_LIBRARY_PATH" value="ORACLE_ HOME/common/ODBC/Merant/5.3/lib$:ORACLE_HOME/bifoundation/server/bin$:ORACLE_ HOME/bifoundation/web/bin$:ORACLE_ HOME/clients/epm/Essbase/EssbaseRTC/bin$:ORACLE_ HOME/bifoundation/odbc/lib$:ORACLE_INSTANCE:ORACLE_HOME/lib" append="true"/>
Update the variable to include the DataDirect 7.0.1 driver path. For example:
<variable id="LD_LIBRARY_PATH" value="ORACLE_HOME/common/ODBC/Merant/7.0.1/ lib:ORACLE_HOME/common/ODBC/Merant/5.3/lib$:ORACLE_ HOME/bifoundation/server/bin$:ORACLE_HOME/bifoundation/web/bin$:ORACLE_ HOME/clients/epm/Essbase/EssbaseRTC/bin$:ORACLE_ HOME/bifoundation/odbc/lib$:ORACLE_INSTANCE:ORACLE_HOME/lib" append="true"/>
Save and close the file.
Restart OPMN.
Open the odbc.ini file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
Update the ODBC entry to use the DataDirect 7.0.1 drivers.
[ODBC] #Trace = 0 #TraceFile = ORACLE_INSTANCE/diagnostics/logs/OracleBIApplication/coreapplicat ion/odbctrace.out #TraceDll = ORACLE_HOME/common/ODBC/Merant/5.3/lib/odbctrac.so #InstallDir = ORACLE_HOME/common/ODBC/Merant/5.3 #UseCursorLib = 0 #IANAAppCodePage = 4 IANAAppCodePage=4 InstallDir=ORACLE_HOME/common/ODBC/Merant/7.0.1/install Trace=0 TraceFile=odbctrace.out TraceDll=ORACLE_HOME/common/ODBC/Merant/7.0.1/lib/ARtrc26.so
Update the ODBC Data Sources entry to include the SQL Server data source name.
[ODBC Data Sources] AnalyticsWeb = Oracle BI Server Cluster = Oracle BI Server SSL_Sample = Oracle BI Server SQLSERVER=DataDirect 7.0 SQL Server Wire Protocol
Save and close the odbc.ini file.
Repeat these steps on each computer that runs the Oracle BI Server process. If you are running multiple Oracle BI Server instances on the same computer, then ensure that you update the ias-component tag appropriately for each instance in opmn.xml (for example, ias-component id="coreapplication_obis1", ias-component id="coreapplication_obis2", and so on).
If you are working with a database that is already configured to use the DataDirect 5.3 drivers, then you must modify the configuration's odbc.ini file to use the DataDirect 7.0.1 drivers. See the below procedures for information about how to modify the existing database configuration.
The name of the DataDirect ODBC driver file to connect to a Microsoft SQL Server database is ARmsss23.so. See "System Requirements and Certification" for supported versions of Microsoft SQL Server.
To configure the DataDirect Connect ODBC Driver to connect to Microsoft SQL Server:
Open the user.sh file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/user.sh
In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:
For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.
For HP-UX, the library path variable is SHLIB_PATH.
For AIX, the library path variable is LIBPATH.
For example, to set the library path variable for the 32-bit driver on Linux:
#################################################### # Linux: Oracle BI 32 bit mode #################################################### #SQLServer 2000 Parameters #--------------------------------------- LD_LIBRARY_PATH=/user/local/Oracle_BI1/common/ODBC/Merant/5.3/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH
Save and close the file.
Open the odbc.ini file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
Create an entry for the database, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you set the Driver parameter to the file name and location of the DataDirect Connect driver for Microsoft SQL Server. In the following example, the Driver parameter is set to the 64-bit DataDirect Connect driver, and the data source name is SQLSERVER_DB.
[SQLSERVER_DB] Driver=/usr/Oracle_BI1/common/ODBC/Merant/5.3/lib64/ARmsss23.so Description=DataDirect 5.1 SQL Server Wire Protocol Address=111.111.111.111,1433 AlternateServers= AnsiNPW=Yes ConnectionRetryCount=0 ConnectionRetryDelay=3 Database=dbschema_name LoadBalancing=0 LogonID= Password= QuoteID=No ReportCodePageConversionErrors=0
Save and close the odbc.ini file.
Open the repository in the Administration Tool on a Windows computer.
In the Physical layer, double-click the database object for the Microsoft SQL Server database.
Click OK.
Save and close the repository.
On the Linux or UNIX computer, shut down Oracle Business Intelligence.
Copy the repository from the Windows computer to the Linux or UNIX computer.
Start Oracle Business Intelligence on the Linux or UNIX computer.
The name of the DataDirect ODBC driver file to connect to a Sybase ASE database is ARase23.so. See "System Requirements and Certification" for information about supported versions of Sybase ASE.
To configure the DataDirect Connect ODBC Driver to connect to Sybase ASE Database:
Open the user.sh file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/user.sh
In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:
For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.
For HP-UX, the library path variable is SHLIB_PATH.
For AIX, the library path variable is LIBPATH.
For example, to set the library path variable for the 32-bit driver on Linux:
LD_LIBRARY_PATH=/user/local/Oracle_BI1/common/ODBC/Merant/5.3/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH
Save and close the file.
Open the odbc.ini file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
Create an entry for the database, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you set the Driver parameter to the file name and location of the DataDirect Connect driver for Sybase ASE Database. For NetworkAddress, provide the IP address or fully qualified host name and the port number.
In the following example, the Driver parameter is set to the 64-bit DataDirect Connect driver, and the data source name is SybaseASE_DB.
[SybaseASE_DB] Driver=/usr/Oracle_BI1/common/ODBC/Merant/5.3/lib64/ARase23.so Description=DataDirect 5.3 Sybase Wire Protocol AlternateServers= ApplicationName= ApplicationUsingThreads=1 ArraySize=50 AuthenticationMethod=0 Charset= ConnectionRetryCount=0 ConnectionRetryDelay=3 CursorCacheSize=1 Database=Paint DefaultLongDataBuffLen=1024 EnableDescribeParam=0 EnableQuotedIdentifiers=0 EncryptionMethod=0 GSSClient=native HostNameInCertificate= InitializationString= Language= LoadBalancing=0 LogonID=my_id NetworkAddress=111.111.111.111,5005 OptimizePrepare=1 PacketSize=0 Password= RaiseErrorPositionBehavior=0 ReportCodePageConversionErrors=0 SelectMethod=0 ServicePrincipalName= TruncateTimeTypeFractions=0 TrustStore= TrustStorePassword= ValidateServerCertificate=1 WorkStationID=
Save and close the odbc.ini file.
The name of the DataDirect ODBC driver file to connect to an Informix database is ARifcl23.so. See "System Requirements and Certification" for information about supported versions of Informix.
To configure the DataDirect Connect ODBC Driver to connect to Informix:
Open the user.sh file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/user.sh
In the section for your operating system, include the appropriate library path environment variable for the DataDirect Connect libraries. Ensure that you point to the appropriate library, depending on whether you are using a 32-bit or 64-bit database. Note the following:
For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.
For HP-UX, the library path variable is SHLIB_PATH.
For AIX, the library path variable is LIBPATH.
For example, to set the library path variable for the 32-bit driver on Linux:
LD_LIBRARY_PATH=/user/local/Oracle_BI1/common/ODBC/Merant/5.3/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH
Save and close the file.
Open the odbc.ini file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
Create an entry for the database, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you set the Driver parameter to the file name and location of the DataDirect Connect driver for Informix. Also, you must specify the HostName parameter (you can use the fully qualified host name or the IP address) and the PortNumber parameter.
In the following example, the Driver parameter is set to the 64-bit DataDirect Connect driver, and the data source name is Informix_DB.
[Informix_DB] Driver=/usr/Oracle_BI1/common/ODBC/Merant/5.3/lib64/ARifcl23.so Description=DataDirect Informix Wire Protocol AlternateServers= ApplicationUsingThreads=1 CancelDetectInterval=0 ConnectionRetryCount=0 ConnectionRetryDelay=3 Database= HostName=111.111.111.111 LoadBalancing=0 LogonID=informix Password=mypassword PortNumber=1526 ReportCodePageConversionErrors=0 ServerName= TrimBlankFromIndexName=1
Save and close the odbc.ini file.
Oracle Business Intelligence bundles UNIX ODBC drivers for some data sources, but not all. For these data sources, including Teradata and Oracle TimesTen In-Memory Database, you must install your own ODBC driver, then update the user.sh and odbc.ini files to configure the data source.
If you are using Teradata, see also "Avoiding Spool Space Errors for Queries Against Teradata Data Sources" for related information.
To configure a database connection using a native ODBC driver:
Install the ODBC driver for the data source.
Open the user.sh file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/user.sh
In the section for your operating system, include the appropriate library path environment variable for the native ODBC driver. Ensure that you point to the appropriate library, depending on whether you use a 32-bit or 64-bit database. Note the following:
For Solaris and Linux, the library path variable is LD_LIBRARY_PATH.
For HP-UX, the library path variable is SHLIB_PATH.
For AIX, the library path variable is LIBPATH.
For example, to set the library path variable for Teradata on Linux:
LD_LIBRARY_PATH= tdata_location:libodbc_location:$LD_LIBRARY_PATH export LD_LIBRARY_PATH
Where tdata_location is the location of the tdata.so file and libodbc_location is the location of the libodbc.so file. You can install the Teradata drivers in a location that is different than indicated in the documentation.
Save and close the file.
Open the odbc.ini file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
Create an entry for the database, ensuring that the ODBC connection name is identical to the data source name specified in the connection pool defined in the repository. Ensure that you set the Driver parameter to the file name and location of the native ODBC driver for the database, with the library suffix that is appropriate for the operating system (for example, .so for Solaris and AIX, or .sl for HP-UX).
The following example provides details for a Teradata data source on Solaris, with a data source name of Terav502.
[Terav502]
Driver=/usr/odbc/drivers/tdata.so
Description=NCR 3600 running Teradata V2R5.2
DBCName=172.20.129.42
LastUser=
Username=
Password=
Database=
DefaultDatabase=name_of_target_database_or_user
Note that the DefaultDatabase parameter can be left empty only if you have selected the option Require fully qualified table names in the General tab of the Connection Pool dialog for this data source in the Administration Tool.
Still in the odbc.ini file, add an entry to the section [ODBC Data Sources] with the details appropriate for the data source. The following example provides details for a Teradata data source with a data source name of Terav502.
Terav502=tdata.so
Using the Administration Tool, open the repository and add the new DSN you created as the Connection Pool Data source name for the appropriate physical databases. See "Creating or Changing Connection Pools" for more information.
Restart the Oracle BI Server.
To set up Oracle TimesTen In-Memory Database data sources, first follow the instructions in "Configuring TimesTen Data Sources" to set up the TimesTen data source. Ensure that you go to the section "Configuring Database Connections Using Native ODBC Drivers" in step 4 of the procedure to obtain the correct steps for Linux and UNIX systems.
Next, review the best practices described in "Improving Use of System Memory Resources with TimesTen Data Sources" and implement them as needed.
Finally, if the user that starts OPMN does not have the path to the TimesTen DLL ($TIMESTEN_HOME/lib) in their operating system LD_LIBRARY_PATH variable (or SHLIB_PATH and LIBPATH on HP-UX and AIX, respectively), you must add the TimesTen DLL path as a variable in the opmn.xml file.
To update opmn.xml to include TimesTen variables on Linux and UNIX:
Open opmn.xml for editing. You can find opmn.xml at:
ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml
Locate the ias-component tag for the Oracle BI Server process. For example:
<ias-component id="coreapplication_obis1" inherit-environment="true">
Under the Oracle BI Server ias-component tag, add the required TimesTen variable TIMESTEN_DLL, and also update the LD_LIBRARY_PATH variable (or equivalent), as shown in the following example. Replace the example values with the correct path for the TimesTen client:
<ias-component id="coreapplication_obis1" inherit-environment="true"> <environment> ... <variable id="TIMESTEN_DLL" value="$TIMESTEN_HOME/lib/libttclient.so" /> <variable id="LD_LIBRARY_PATH" value="$TIMESTEN_HOME/lib:$ORACLE_HOME/ bifoundation/server/bin$:$ORACLE_HOME/bifoundation/web/bin$: $ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/bin$:" append="true" /> ... </environment>
Save and close the file.
Restart OPMN.
Repeat these steps on each computer that runs the Oracle BI Server process. If you are running multiple Oracle BI Server instances on the same computer, then ensure that you update the ias-component tag appropriately for each instance in opmn.xml (for example, ias-component id="coreapplication_obis1", ias-component id="coreapplication_obis2", and so on).
You can access Oracle RPAS ODBC data sources when the Oracle BI Server is running on an AIX UNIX platform. To configure this database connection, first update the odbc.ini file to configure the Oracle RPAS ODBC data source, then use the rdaadmin tool to define dimension tables as not normalized at run time.
See "Setting Up Oracle RPAS Data Sources" for information about configuring Oracle RPAS ODBC data sources on Windows.
To configure Oracle RPAS ODBC as a data source on AIX UNIX:
Log on as a separate telnet session.
Open the odbc.ini file. You can find this file at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
In the RPAS data source section, edit the values. For example:
[RPAS Sample]
Data Source Name=RPAS Sample
Driver=[client RPASClient/lib/raix/oaodbc.so
DriverUnicodeType=1
Description=OpenRDA DSN
The Data Source Name you provide must match the value entered for DATABASE: in Step 3 of the following procedure. Also, you must add the line DriverUnicodeType=1 as shown in the preceding example.
To use the rdaadmin client tool to define dimension tables as not normalized at run time:
Locate the rdaadmin client tool in the following location:
/bin/rdaadmin
Run the rdaadmin client tool by typing the following command:
rdaadmin
Enter appropriate text when prompted, as follows:
DATABASE: [Oracle_RPAS_database_name]
The database name must match the name given for the Data Source Name in the previous task (for example, RPAS Sample).
ADDRESS: [ip_address]
PORT: [port_number]
An example port number value is 1707.
CONNECT_STRING: [NORMALIZE_DIM_TABLES=NO]
This value treats dimension tables as not normalized at run time.
TYPE: []
SCHEMA_PATH: []
REMARKS: []
The RPAS environment variable OPENRDA should be declared in the Oracle BI Server session on UNIX. For example, declare the variable as follows using the 64 bit rdaadmin client tool:
OPENRDA_INI=/rpasclient64/config/raix/openrda.ini export OPENRDA_INI
Oracle Business Intelligence provides the ability to create Hadoop data sources on Linux. Hadoop is a framework that enables distributed processing of large data sets across clusters of computers. The integration also relies on Apache Hive, which is data warehouse software for querying and managing large datasets residing in distributed storage. Hive is built on top of Hadoop and provides an ODBC interface to Hadoop, as well as a SQL-like query language called QL for querying the data.
Note:
Integration with Hadoop as a data source is only available when the Oracle BI Server is running on Linux. It is not available on Windows or other UNIX platforms.
This section contains the following topics:
About Using Hadoop Data Sources with Oracle Business Intelligence
Limitations on the Use of Hadoop and Hive with Oracle Business Intelligence
Hadoop is a batch processing infrastructure which has traditionally relied on the coding of MapReduce programs to process and access data. Hive provides an ODBC interface and a SQL-like query language for Hadoop to eliminate the need for manual coding of programs for data access.
As a batch-oriented system, Hadoop is not particularly suited for low latency, concurrent user access. Oracle Exalytics provides a platform to integrate data from Hadoop and other Oracle BI Server supported data sources, and is the recommended solution for highly interactive and predictable performance against high volume data systems.
You can configure Hadoop and Hive to work with Oracle Business Intelligence through the use of either an Oracle-supplied DataDirect 7.0.1 ODBC driver or an Apache Hive ODBC driver available from an Apache web site. Oracle recommends that you follow the process that uses the DataDirect 7.0.1 driver.
This section contains the following topics:
Important:
Before you start the configuration process that uses the DataDirect 7.0.1 ODBC driver, ensure that you have performed the steps described in Configuring Oracle Business Intelligence to Use DataDirect 7.0.1 Drivers.
This section contains the following topics:
Configuration Instructions Using DataDirect 7.0.1 ODBC Driver
Acquiring Windows Driver to Enable Data Import Using Administration Tool
To configure Oracle Business Intelligence to use Hive data sources through the DataDirect 7.0.1 ODBC driver, perform the following steps:
On the Oracle BI Server computer, create an entry in the following odbc.ini file for the Hive datasource name (DSN) using the DataDirect 7.0.1 ODBC driver:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
For example:
[Hive] Driver=/usr/Oracle_BI1/common/ODBC/Merant/7.0.1/lib/ARhive27.so Description=Oracle 7.1 Apache Hive Wire Protocol ArraySize=16384 Database=default DefaultLongDataBuffLen=1024 EnableDescribeParam=0 HostName=localhost LoginTimeout=30 MaxVarcharSize=2000 PortNumber=10000 RemoveColumnQualifiers=0 StringDescribeType=12 TransactionMode=0 UseCurrentSchema=0
Open opmn.xml for editing. You can find opmn.xml at:
ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml
Locate the coreapplication_obis1 tag, and within that tag, locate the LD_LIBRARY_PATH variable.
Ensure that the LD_LIBRARY_PATH variable includes the path to the DataDirect 7.0.1 driver, namely ORACLE_HOME/common/ODBC/Merant/7.0.1/lib.
In the same opmn.xml file, either above or below the LD_LIBRARY_PATH variable, create the HADOOP_DLL variable to point to the DataDirect 7.0.1 driver.
For example:
<variable id="HADOOP_DLL"
value="ORACLE_HOME/common/ODBC/Merant/7.0.1/lib/ARhive27.so"/>
Save and close the file.
Restart OPMN.
Create a soft link libARicu27.so pointing to libARicu26.so.
For example:
cd /usr/Oracle_BI1/common/ODBC/Merant/7.0.1/lib/ ln -s libARicu26.so libARicu27.so
Metadata import using the Administration Tool is supported through the DataDirect 7.0.1 ODBC driver. 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.
This section contains the following topics:
Before you can integrate Hadoop and Hive with Oracle Business Intelligence, you must ensure that the Hive implementation includes the HiveODBC component. You achieve this by performing the following steps:
Install the Apache Hive ODBC driver on the Oracle BI Server computer. Follow the instructions at the following site for installing the Hive ODBC driver:
On the Oracle BI Server computer, create an entry in the following odbc.ini file for the Hive datasource name (DSN) using the Hive ODBC driver:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
For example:
[HiveDSN] Driver = /scratch/mydir/installs/lib/libodbchive.so Description = Hive Driver v1 DATABASE = default HOST = localhost PORT = 10000 FRAMED = 0
You must specify the path to the Hive ODBC driver, the database to connect to in Hive (typically Default), and the hostname and port where Hive resides.
After the Hive DSN has been defined, users can connect to Hive using nqcmd (for example, nqcmd -dHiveDSN).
Set the environment variable HADOOP_DLL to the file name of the Hive ODBC driver, and have the library path set up so that the driver DLL, and any other DLLs that it depends on recursively, are in the library path.
Because the Hive ODBC driver is only supported on Linux, you cannot use the Administration Tool to import data. Instead, you must follow a process that includes running the command line utility nqsimporttool to import metadata into an XML file using the Oracle BI Server XML schema.
You can find nqsimporttool in the following directory:
ORACLE_HOME/bifoundation/server/bin
Before running nqsimporttool, you must first run bi-init to launch a command prompt that is properly initialized. See "Running bi-init to Launch a Shell Window Initialized to Your Oracle Instance" for more information.
To import Hadoop data, perform the following steps:
Run the following command at the command prompt:
nqsimporttool -D datasource_name [-U username] [-P password] [-O output_file_name] -d database_name [-c catalog_name] [-s schema_name] [-h] [-v]
You must provide the datasource name of the Hive DSN (-D parameter), and a name for the database object that will exist in the Physical layer of the RPD (-d parameter).
For the -u and -p parameters:
If you omit the -u parameter, nqsimporttool attempts an anonymous login to Hive (whether you specify the -p parameter or not).
If the -u parameter is specified but the -p parameter is not specified, nqsimporttool will prompt for a password in secure mode.
For the -o parameter:
output_file_name can either be a standalone file name (for a file in the current directory) or a full directory + file name.
If the -o parameter is not specified, XML output is directed to the stderr stream of the terminal. This causes the XML to display on the terminal.
For the -c and -s parameters:
The -c parameter is used to specify the name of the physical catalog to be created as part of the physical database in the RPD. If the -c parameter is not specified, nqsimporttool does not generate XML for the catalog.
The -s parameter is used to specify the name of the schema to be created as part of the physical database in the RPD. If the -s parameter is not specified, nqsimporttool does not generate XML for the schema.
To illustrate how the parameters influence the creation of XML database objects, assume that the Hive database contains a single table TT.
With nqsimporttool options -d DD -c CC -s SS, the following objects will be created in the physical layer of the RPD:
DD => CC => SS => TT (where => depicts containership)
If the import is performed without the -c and -s options, as in -d DD, the objects created are:
DD => TT
-h displays the help information for the nqsimporttool parameters.
-v displays extra information at each step of the import process, including lists of the imported objects, specifically, the tables, and for each table, the columns and their attributes.
After you have generated the XML file that contains the Hadoop metadata, use biserverxmlexec to execute the XML file against the RPD file.
For example:
biserverxmlexec -I my_hadoop_metadata.xml -B rp1.rpd -O rp2.rpd
When prompted, specify the password for the RPD.
You can perform this step either on Linux or on Windows.
Copy the resulting RPD file to Windows and open it in the Administration Tool in offline mode.
You will see the Hadoop Physical layer objects in the repository.
Because the HiveODBC driver does not allow joins to be imported, you must manually re-create Physical layer joins in the metadata.
Drag and drop the Physical layer metadata to the Business Model and Mapping layer and Presentation layer
This section describes the following limitations on the use of Hadoop and Hive with Oracle Business Intelligence, many of which are known Hive limitations:
Hive Does Not Support Count (Distinct M) Together with Group By M
Exception Thrown for Locate Function with an Out-of-Bounds Start Position Value
Hive May Fail on Long Queries With Multiple AND and OR Clauses
Hive does not have Date and Datetime data types. Dates and datetimes are both encoded using the String data type (the equivalent of Varchar in Hive). Hive requires Dates to be in encoded in the format yyyy-mm-dd. Similarly, for Datetime values, the string must be in the format yyyy-mm-dd hh:mm:ss.Although the date columns are typed as String in the Hive back end, they should continue to be typed as Date or Datetime in RPDs, because Oracle Business Intelligence allows calendar operations only on Date and Datetime values.
Queries of the form:
SELECT M, COUNT(DISTINCT M) ... FROM ... GROUP BY M ...
may cause Hive to crash.
The general 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 that, as 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
This issue has been documented at the following site:
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 may cause Hive to crash, for example:
select substring(ProductName, 2) from Products
More information on substring issues appears at the following site:
As the Apache Hive ODBC driver does not support SQLTransact, which is used for creating tables, CREATE TABLE is not supported by Hive.
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 that involve selecting columns with Unicode data will either fail or give unpredictable results. The Apache Hive ODBC driver does not support Unicode. A product enhancement is required for Unicode support by the DataDirect ODBC driver.
Queries with subquery expressions may fail in Hive. If subquery expressions are used, the physical query that Oracle BI Server generates may include mixed datatypes 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);
Queries of the form:
SELECT DISTINCT M, M ... FROM TABX
are not supported by Hive.
More information on this issue is available at the following site:
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 Enterprise Edition. No additional configuration is required to enable Essbase data source access for full installations of Oracle BI Enterprise Edition.
However, for HP-UX Itanium systems, the following additional steps are required:
Define ESSLANG and LANG.
For example:
ESSLANG=English_UnitedStates.UTF-8@Binary export ESSLANG LANG=en_US.utf8 export LANG
Comment out LOCALE, SORT_ORDER_LOCALE, and SORT_TYPE in the NQSConfig.ini file. For example:
[ GENERAL ] // Localization/Internationalization parameters. // LOCALE="English-usa"; // SORT_ORDER_LOCALE="English-usa"; // SORT_TYPE="binary";
IBM DB2 Connect does not support the option of automatically disconnecting when an application using it receives an interrupt request.
When the native database uses DB2 Connect workstation, then you must change the setting of the parameter INTERRUPT_ENABLED. This parameter must be changed on any Oracle Business Intelligence computer if the database or any data source resides on IBM DB2 on a mainframe running z/OS or s/390 platforms.
Note:
If IBM DB2 is used, DB2 Connect must be installed on the Oracle BI Server computer. The version of DB2 Connect must match the most recent DB2 instance that was configured as a data source.
To configure the INTERRUPT_ENABLED parameter:
Configure a database alias to be used as the native CLI Data Source Name. For example, create a new database entry using DB2 Configuration Assistant.
Using the database alias created and the name of the actual target DB2 database, set the INTERRUPT_ENABLED parameter using the following syntax:
uncatalog dcs db local_dcsname catalog dcs db local_dcsname as target_dbname parms \",,INTERRUPT_ENABLED\"
where:
local_dcsname represents the local name of the host or database (database alias name)
target_dbname represents the name of database on the host or database system
Note:
Ensure that you use backslashes to pass the quotation marks as part of the string.
The following example uses an OS390 DB2 instance:
uncatalog dcs db DB2_390 catalog dcs db DB2_390 as Q10B parms \",,INTERRUPT_ENABLED,,,,,\" catalog database DB2_390 as DB2_390 at node NDE1EF20 authentication dcs