Multi Data Source (MDS)
Multi Data Source (MDS) allows data retrieval via Reports Wand from data sources other than the Oracle e-Business Suite system that Wands for Oracle is installed on.
MDS consists of two major components:
- Connections – create, edit and delete database connections for the new data source.
- Report Definitions – create, edit and delete report definitions, defined with SQL for the new data source.
MDS Checklist
| # | Step | Comment |
|---|---|---|
| 1 | Database table install. |
The XXE4A_RW_MDS_CONNECTIONS database table should be available from the APPS schema. It can be installed by running the server installation.
|
| 2 | Obtain relevant JDBC driver. | Often available from database vendor or your database administrator. |
| 3 | Obtain connection string, and authentication credentials. | Your database administrator should be able to assist. |
| 4 | Create the JDK_JAVA_OPTIONS system environment variable. | Only required if you have installed the Wands launcher with the Automatic Client Update option. Confirm JarLoader.jar is in the correct directory. |
| 5 | Create connection. | Must be a Reports Wand Administrator. |
| 6 | Create report definition. | Must be a Reports Wand Administrator. |
System Environment Variable
If the Wands Launcher has been installed with the Automatic Client Update, then the following system environment variable will be required for MDS to work correctly. The JarLoader.jar file is put down in the Wands for Oracle installation directory when the client is updated by the automatic process or by the Wands Launcher. The environment variable should match the location of the JarLoader.jar file. Please confirm the file does exist before creating this system environment variable.
| Variable Name | JDK_JAVA_OPTIONS |
|---|---|
| Variable Value |
-javaagent: “C:\Users\< username >\AppData\Roaming\insightsoftware\Wands\Oracle\JdbcDrivers\JarLoader.jar”
|
Requirements and Setup
This feature requires a custom database table and sequence to be installed. This can be done as part of the Wands for Oracle installation or upgrade on the application server, or by using the database table script below.
The following changes can be made to the script:
- You can modify the script to change the ownership of the table. If you choose to do so, ensure the APPS database user has SELECT, UPDATE and DELETE privileges on the table and create the required synonym to allow the APPS user to access the table without having to fully qualify the table name using the table owner.
- The tablespace (APPS_TS_MEDIA) used in the script can be modified to suit your system.
APPS.XXE4A_RW_TABLE_SECURITY
CREATE TABLE apps.xxe4a_rw_mds_connections(
data_source_id VARCHAR2(64 BYTE) NOT NULL ENABLE,
description VARCHAR2(256 BYTE) NOT NULL ENABLE,
connection_string VARCHAR2(512 BYTE) NOT NULL ENABLE,
user_name VARCHAR2(256 BYTE),
password VARCHAR2(128 BYTE),
database_type VARCHAR2(64 BYTE) NOT NULL ENABLE,
updated_by VARCHAR2(128 BYTE) NOT NULL ENABLE,
updated_date DATE NOT NULL ENABLE,
connection_owner VARCHAR2(128 BYTE) NOT NULL ENABLE,
public_flag CHAR NOT NULL ENABLE,
driver_folder VARCHAR2(512 BYTE),
driver_class VARCHAR2(512 BYTE),
CONSTRAINT xxe4a_rw_mds_connections_pk PRIMARY KEY(data_source_id, connection_owner) ENABLE
)
LOGGING
TABLESPACE APPS_TS_MEDIA