Jet Analytics Oracle Data Source Provider

The following steps are necessary for the Jet Analytics Ingest Service (TIS) server to successfully connect to an Oracle data source. EZCONNECT and TNSNAMES are the two common methods for configuring an Oracle database connection. EZCONNECT is more straightforward, while TNSNAMES may be more common in enterprise environments. Both methods require the Oracle Database Access Components (ODAC) to be installed and configured on the TIS as a prerequisite.

Installing the Oracle Data Access Components

Due to the discontinuation of direct Oracle support in the Microsoft .NET Framework, the Oracle Data Access Components are required for Jet Analytics Data Integration to connect to an Oracle database. These components must be installed on the system running the TIS service.

1. Create the folder C:\tns and copy the SQLNET.ORA and TNSNAMES.ORA files into it.
2. Download the Oracle Data Access Components from oracle.com/database/technologies/net-downloads.html. Scroll down the page to find the ODAC 21c installer.

   

3. Right-click Command Prompt and select Run as Administrator.
4. Navigate to the folder containing the unzipped installation files and run the following installation command:

   install.bat odp.net4 c:\oracle myhome true true c:\tns

5. Download and install the Microsoft Visual C++ 2015–2022 Redistributable.

   

Once the ODAC components are installed, the Jet Analytics Oracle Data Source provider supports the following two connection methods:

• TNSNAMES — uses a TNSNAMES.ORA file provided by the Oracle database administrator
• EZCONNECT — bypasses the TNSNAMES.ORA file by entering connection details directly in the Jet Analytics Portal

TNSNAMES connection method

Oracle database administrators commonly provide the following two files to users who need to connect to an Oracle database:

• TNSNAMES.ORA
• SQLNET.ORA

Both files must be saved in a folder on the system, and environment variables must be created in the machine context pointing to this folder and to the ODAC installation directory. This can be done manually or by using the Jet Analytics Oracle Data Source ODAC Configuration Utility to expedite the process.

1. Create an environment variable in the machine context named TNS_ADMIN with the value C:\tns.
2. Add the following two entries to the Path environment variable in the machine context:
   1. C:\Oracle
   2. C:\Oracle\bin
3. Reboot the system.

The following diagram illustrates how the Oracle Data Source settings in the Jet Analytics Portal relate to the information in the TNSNAMES.ORA file.

Adding the Jet Analytics Oracle Data Source using TNSNAMES

In the Jet Analytics portal, go to Data Source Connections and click to add a new data source. For the provider, select the most recent version of the Jet Analytics Oracle Data Source provider.

The Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support.

Configure the provider settings as follows:

1. Data Source: Set this to the schema name — for example, GML as defined in the TNSNAMES.ORA file.
2. User ID: Set this to the username — for example, GML as defined in the TNSNAMES.ORA file.
3. Password: Set this to the password for the username above.
4. Out of range date conversion: Set to MS SQL min/max date. This converts dates that exceed the minimum or maximum value supported by SQL Server to the SQL Server minimum or maximum.
5. Round numbers: Set to Yes. This applies the Round() function to numeric and decimal values to ensure they fit SQL Server data types.

Once the Oracle Data Source has been created in the Portal and the ODAC components are installed and configured on the TIS system, Jet Analytics Data Integration can be used to execute the synchronization and transfer tasks for the new Oracle Data Source.

In the example above, the Ingest instance uses the Data Source setting of GML to read the TNSNAMES.ORA file and retrieve the following connection information:

• Host name: #########-4ca944bda8.westus3.cloudapp.azure.com
• Port number: 1521
• Pluggable database: XEPDB1

EZCONNECT method

For the EZCONNECT method, only the SQLNET.ORA file needs to be saved on the system. The TNSNAMES.ORA file is not required. Environment variables must be created in the machine context pointing to the SQLNET.ORA folder and to the ODAC installation directory. This can be done manually or by using the Jet Analytics Oracle Data Source ODAC Configuration Utility.

1. With the Oracle ODAC components installed in the C:\Oracle directory, save the SQLNET.ORA file to C:\Oracle\TNS.
2. Create an environment variable in the machine context named TNS_ADMIN with the value C:\Oracle\TNS.
3. Add the following two entries to the Path environment variable in the machine context:
   1. C:\Oracle
   2. C:\Oracle\bin
4. Reboot the system.

Adding the Jet Analytics Oracle Data Source using EZCONNECT

In the Jet Analytics portal, go to Data Source Connections and click to add a new data source. For the provider, select the most recent version of the Jet Analytics Oracle Data Source provider.

The Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support.

[Insert image (Oracle_EZCONNECT_Provider_Selection) here]

Configure the provider settings as follows:

1. Data Source: Set this to the connection string in the format <HostName>:<PortNumber>/<DatabaseName> — for example:

   #########-4ca944bda8.westus3.cloudapp.azure.com:1521/XEPDB1

2. User ID: Set this to the username — for example, GML.
3. Password: Set this to the password for the username above.
4. Out of range date conversion: Set to MS SQL min/max date. This converts dates that exceed the minimum or maximum value supported by SQL Server to the SQL Server minimum or maximum.
5. Round numbers: Set to Yes. This applies the Round() function to numeric and decimal values to ensure they fit SQL Server data types.

In the example above, the Ingest instance uses the Data Source setting to retrieve the following connection information directly:

• Host name: ########-4ca944bda8.westus3.cloudapp.azure.com
• Port number: 1521
• Pluggable database: XEPDB1

Once the Oracle Data Source has been created in the Portal and the ODAC components are installed and configured on the TIS system, Jet Analytics Data Integration can be used to successfully execute the synchronization and transfer tasks for the new Oracle Data Source.

Default Schemas

Oracle includes a number of built-in schemas that are treated as default schemas by the Jet Analytics Oracle Data Source provider. If your Oracle Data Source schema matches one of the default schemas, you must set Include default schemas to Yes in order for that schema's tables to be included in the data source synchronization task.

The following owners are defined as default schemas. All owners beginning with APEX_, and the following named owners:

ANONYMOUS, APPQOSSYS, AUDSYS, BI, CTXSYS, DBSFWUSER, DBSNMP, DIP, DVF, DVSYS, EXFSYS, FLOWS_FILES, GGSYS, GSMADMIN_INTERNAL, GSMCATUSER, GSMUSER, HR, IX, LBACSYS, MDDATA, MDSYS, MGMT_VIEW, OE, OJVMSYS, OLAPSYS, ORACLE_OCM, ORDDATA, ORDPLUGINS, ORDSYS, OUTLN, OWBSYS, OWBSYS_AUDIT, PM, REMOTE_SCHEDULER_AGENT, SCOTT, SH, SI_INFORMTN_SCHEMA, SPATIAL_CSW_ADMIN_USR, SPATIAL_WFS_ADMIN_USR, SYS, SYSBACKUP, SYSDG, SYSKM, SYSMAN, SYSRAC, SYSTEM, SYS$UMF, WMSYS, XDB, XS$NULL