5 Importing Metadata and Working with Data Sources
This chapter describes how to create a new Oracle BI repository, set up back-end data sources, and import metadata using the Import Metadata Wizard in the Oracle BI Administration Tool. It also describes how to use a standby database with Oracle Business Intelligence.
This chapter contains the following topics:
About Importing Metadata and Working with Data Sources
If you do not already have an Oracle BI repository file, you must create one. Then, you can import metadata from your data sources into the Physical layer of your repository. The Physical layer of the Administration Tool defines the data sources to which the Oracle BI Server submits queries, and the relationships between physical databases and other data sources that are used to process multiple data source queries.
Metadata imports to an Oracle BI repository must occur through an ODBC or native database connection to the underlying data source. Metadata can also be imported from software such as Microsoft Excel through an ODBC connection.
Importing metadata directly from each data source saves you time and effort by importing the structure for the Physical layer. Data from these sources can be displayed on Oracle BI Interactive Dashboards and other clients. You can only import metadata from supported data sources.
After you import metadata, properties in the associated database object and connection pool are typically set automatically. However, you may want to adjust database or connection pool settings. See Chapter 7, "Setting Up Database Objects and Connection Pools" for more information.
Although you can create the Physical layer manually rather than importing metadata, it is a labor-intensive and error-prone activity. It is strongly recommended that you import metadata.
Creating a New Oracle BI Repository
You can use the Create New Repository Wizard in the Administration Tool to create a new Oracle BI repository, in either binary (RPD) or MDS XML format. You do not need to create a new repository if you already have an existing repository.
If you have an existing repository, you can use the existing data source settings in that file as a template to connect to different data sources. To do this, use the existing data source settings and just change the database type and connection pool information. See "Setting Up Database Objects" and "Creating or Changing Connection Pools" for details.
To create a new Oracle BI repository:
-
In the Administration Tool, select File, then select New Repository. The Create New Repository Wizard appears.
If an existing repository is open, you are prompted to save your changes, and the existing repository is closed.
-
Select Binary if you want to create a repository in RPD format. To create a repository in MDS XML format, select MDS XML Documents.
-
For binary repositories, type a name for the repository. Keep the name to 156 characters or less to avoid problems with the metadata dictionary URL. An RPD file extension is automatically added if you do not explicitly specify it.
-
For Location, follow the steps appropriate for your repository type:
-
For binary repositories, select a location for the RPD file. By default, new binary repositories are stored in the repository subdirectory, located at ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obisn\repository.
-
For MDS XML format repositories, select a root folder location for the set of MDS XML files.
-
-
If you want to import metadata into the repository now, select Yes (the default) for Import Metadata. If you do not want to import metadata, select No.
-
Enter and confirm the password you want to use for this repository. The repository password must be longer than five characters and cannot be empty.
You enter the repository password when you open the repository in online or offline mode. It is used to encrypt the repository contents.
-
If you selected Yes for Import Metadata, click Next.
Refer to the following sections for information about the Import screens, according to your data source type:
Note that you many need to set up your data sources before you import information into the repository. See "Performing Data Source Preconfiguration Tasks" for more information.
-
If you selected No for Import Metadata, click Finish to create an empty repository.
Performing Data Source Preconfiguration Tasks
Before you can import physical objects from your data sources into your repository file, or set up connection pools to your data sources, you might need to perform configuration steps so that the data sources can be accessed by Oracle Business Intelligence.
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.
Note:
See also the following related topics:
-
If the Oracle BI Server is running on a non-Windows platform, see Chapter 16, "Setting Up Data Sources on Linux and UNIX" for additional instructions.
-
See Chapter 6, "Working with ADF Data Sources" for information about setting up ADF data sources.
-
See "System Requirements and Certification" for information about the data source versions supported by Oracle Business Intelligence.
This section contains the following topics:
-
Enabling NUMERIC Data Type Support for Oracle Database and TimesTen
-
Configuring SSO for Essbase and Hyperion Financial Management Data Sources
Setting 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 this 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.
To set up an ODBC DSN on Windows:
-
Open the Windows Control Panel by selecting Start > Settings > Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
-
In the ODBC Data Source Administrator dialog, click the System DSN tab, and then click Add.
-
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 execution. On UNIX systems, ODBC DSNs are only used for data access. For information about setting up ODBC data sources on UNIX, see Chapter 16, "Setting Up Data Sources on Linux and UNIX."
See also "Setting Up Teradata Data Sources" for additional information specific to Teradata.
Setting Up Oracle Database Data Sources
You should always use 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.
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:
ORACLE_BI_HOME\network\admin
This section contains the following topics:
-
Additional Oracle Database Configuration for Client Installations
-
Avoiding Oracle Database Connection Errors in Windows 7 64-bit Environments
See also "Enabling NUMERIC Data Type Support for Oracle Database and TimesTen" for related information.
Additional Oracle Database Configuration for Client Installations
If you install the Administration Tool using the Oracle Business Intelligence Enterprise Edition Plus Client Installer, you must install the Oracle Database Client on the Administration Tool computer before you can import from Oracle Database sources. Use either the Administrator or Runtime client install option.
If you are running a 32-bit Administration Tool, you must install the 32-bit Oracle Database Client. If you are running a 64-bit Administration Tool, you must install the 64-bit Oracle Database Client.
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 (ORACLE_HOME\network\admin).
Avoiding Oracle Database Connection Errors in Windows 7 64-bit Environments
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. Check the sqlnet.ora file in ORACLE_BI_HOME\network\admin to ensure that the AUTHENTICATION_SERVICES parameter appears as follows:
SQLNET.AUTHENTICATION_SERVICES= (NONE)
Otherwise, 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.
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. In addition, 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.
If you have a client installation of the Administration Tool, see also the following section for additional configuration steps.
Additional Oracle OLAP Configuration for Client Installations
If you install the Administration Tool using the Oracle Business Intelligence Enterprise Edition Plus Client Installer, you must install the Oracle Database Client on the Administration Tool computer before you can import from Oracle OLAP sources. Use either the Administrator or Runtime client install option.
If you are running a 32-bit Administration Tool, you must install the 32-bit Oracle Database Client. If you are running a 64-bit Administration Tool, you must install the 64-bit Oracle Database Client.
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 (ORACLE_HOME\network\admin).
Setting Up Oracle TimesTen In-Memory Database Data Sources
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 TimesTen; see Oracle TimesTen In-Memory Database Installation Guide 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 Oracle TimesTen In-Memory Database Reference for more information.
This section contains the following topics:
See also "Enabling NUMERIC Data Type Support for Oracle Database and TimesTen" for related information.
Configuring TimesTen Data Sources
This section describes the TimesTen configuration necessary before you can use TimesTen as a data source for Oracle Business Intelligence.
To set up TimesTen data sources:
-
On the computer where TimesTen has been installed, create a Data Manager DSN (as a system DSN). See "Defining a Data Manager DSN" in Oracle TimesTen In-Memory Database Operations Guide for more information.
-
Perform an initial connection to the data store to load the TimesTen database into memory, and then create users and grant privileges, if you have not done so already. See "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for more information. Note that the default user of the data store is the instance administrator, or in other words, the operating system user who installed the database.
-
On the computer running the Oracle BI Server, install the TimesTen Client. See Oracle TimesTen In-Memory Database Installation Guide for more information.
-
On the computer where the TimesTen Client has been installed, create a Client DSN (as a system DSN). See "Creating Client DSNs" in Oracle TimesTen In-Memory Database Operations Guide for more information.
See "Configuring Database Connections Using Native ODBC Drivers" for information about how to perform this step when the Oracle BI Server is running on Linux or UNIX.
Note that 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 the following resources:
-
Oracle Fusion Middleware Installation and Administration Guide for Oracle Exalytics In-Memory Machine for specific instructions on setting up TimesTen sources on the Oracle Exalytics Machine
See "System Requirements and Certification" for information about supported TimesTen versions for Oracle Business Intelligence.
Improving Use of System Memory Resources with TimesTen Data Sources
To improve the use of system memory resources, it is recommended that you increase the maximum number of connections for the TimesTen server, as follows:
-
In your TimesTen environment, open the ttendaemon.options file for editing. You can find this file at:
install_dir/srv/info
-
-MaxConnsPerServer number_of_connectionsTo 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.
-
Save and close the file.
-
In the ODBC DSN you are 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 UNIX, open the odbc.INI file and add the Connections attribute to the TimesTen DSN entry, as follows:
Connections=number_of_connections
-
-
Stop all processes connecting to TimesTen, such as the ttisql process and the Oracle BI Server.
-
Stop the TimesTen process.
-
After you have verified that the TimesTen process has been stopped, restart the TimesTen process.
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 Oracle TimesTen In-Memory Database Reference for more information.
Ensuring that OPMN Can Access the TimesTen DLL on Windows
If the user that starts OPMN does not have the path to the TimesTen DLL ($TIMESTEN_HOME/lib) in their operating system PATH variable, you must add the TimesTen DLL path as a variable in the opmn.xml file.
To update opmn.xml to include TimesTen variables on Windows:
-
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 thePATHvariable, as shown in the following example. Replace the example values with the correct path for your TimesTen client:<ias-component id="coreapplication_obis1" inherit-environment="true"> <environment> ... <variable id="TIMESTEN_DLL" value="$TIMESTEN_HOME/lib/libttclient.so" /> <variable id="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, be sure to 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).
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 with Oracle BI Enterprise Edition. No additional configuration is required to enable Essbase data source access for full installations of Oracle BI Enterprise Edition.
See also "Configuring SSO for Essbase and Hyperion Financial Management Data Sources" for information about required configuration for authenticating using a shared token against Essbase installed with the EPM System Installer.
Additional Essbase Configuration Steps for Client Installations
If you install the Administration Tool using the Oracle Business Intelligence Enterprise Edition Plus Client Installer, you must perform additional configuration before you can import metadata from Essbase data sources.
For client installations of the Administration Tool, you must ensure that the Essbase client libraries are installed on the client Administration Tool computer before you can set up a connection to or import metadata from Essbase data sources. See "System Requirements and Certification" for information about supported versions of the Essbase Client for use with Oracle Business Intelligence.
After you ensure that the Essbase client libraries are installed on the Administration Tool client computer, you must ensure that the PATH environment variable on each computer includes the location of the Essbase client driver (for example, EPM_ORACLE_HOME/products/Essbase/EssbaseClient).
You also need to ensure that the ESSBASEPATH environment variable is set appropriately for each computer. For more information, see Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide (or the equivalent title for your client version).
In addition, you need to update bi-init.bat on the Administration Tool computer.
To add Essbase environment variables to bi-init.bat:
-
Open the
bi-init.batfile for editing. You can findbi-init.batat:ORACLE_INSTANCE\bifoundation\server\bin\ -
Add a line setting the
ESSBASEPATHenvironment variable, as appropriate for your version and installed location of the Essbase client libraries. For example:set ORACLE_INSTANCE=C:\mw_home\instances\instance1 set ORACLE_BI_APPLICATION=coreapplication set ESSBASEPATH=C:\products\Essbase\EssbaseClient call %ORACLE_INSTANCE%\bifoundation\OracleBIApplication\ %ORACLE_BI_APPLICATION%\setup\user.cmd
-
Locate the line that sets the
PATHenvironment variable, and add the location of the Essbase client binaries. For example:set PATH=%ORACLE_HOME%\bifoundation\server\bin;%ORACLE_HOME%\ bifoundation\web\bin;%ORACLE_HOME%\bin;%ESSSBASEPATH%\bin;%PATH% -
Save and close the file.
Note:
If you encounter connection failures when attempting to import metadata from Essbase data sources using client installations of the Administration Tool, ensure that the following ports are open on the Essbase Server computer:
-
Port 1423
-
Port range 32768 to 33768
Setting Up Hyperion Financial Management Data Sources
This section describes required configuration steps to use Hyperion Financial Management as a data source. See also "Configuring SSO for Essbase and Hyperion Financial Management Data Sources" for information about required configuration for authenticating using a shared token against Hyperion Financial Management.
Before you can import from or set up a connection to Hyperion Financial Management data sources, you must ensure that the Hyperion Financial Management ADM driver is installed on the system that is running the Oracle BI JavaHost process. The installation and configuration of the ADM driver differs based on the release number for Hyperion Financial Management, as described in the following list:
-
For Hyperion Financial Management 11.1.2.1.x:
Install the Hyperion Financial Management client, which includes the Hyperion Financial Management ADM driver. Ensure that you perform the following steps in the Server/Cluster Registration tab of the Client Configuration utility:
-
In the Server and Cluster Registration area, provide the IP address or host name of the Hyperion Financial Management server and select Use One Server Only.
-
In the Enable DCOM area, click Enable DCOM.
-
-
For Hyperion Financial Management 11.1.2.2.x:
Install the Hyperion Financial Management ADM driver. Ensure that you perform the configuration using the Enterprise Performance Management Configurator.
-
In the configuration, provide the details for the Hyperion Shared Services Database to register with the Foundation server and the Hyperion Financial Management server.
-
In the DCOM User Details page, enter a domain user as the user for connecting to the Hyperion Financial Management server.
-
In addition, you must edit the opmn.xml file on each system that is running the Oracle BI JavaHost process to include environment variables that are required by Hyperion Financial Management.
Note that 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 also "Additional Hyperion Configuration for Client Installations" for JavaHost configuration steps.
Note:
You can use Hyperion Financial Management data sources with Oracle BI EE only when Oracle BI EE is running in a Windows-only deployment.
For Hyperion Financial Management 11.1.2:
-
Open the opmn.xml file for editing. You can find opmn.xml at:
ORACLE_INSTANCE/config/OPMN/opmn/opmn.xml -
Locate the ias-component tag for the JavaHost process. For example:
<ias-component id="coreapplication_obijh1">
-
Within the environment tag, add new variables that are required for Hyperion Financial Management, using the following example as a guide. Update the actual values as appropriate for your installation.
<variable id="HYPERION_HOME" value="C:/oracle/Middleware/EPMSystem11R1"/> <variable id="EPM_HOME" value="C:/MW_Home/Oracle_BI1/common/jlib/11.1.2.0"/> <variable id="ADM_HOME" value="C:/MW_Home/Oracle_BI1/common/ADM/11.1.2.0"/> <variable id="HFM_HOME" value="$HYPERION_HOME/products/FinancialManagement/Client"/> <variable id="ADM_ESS_NATIVE_HOME" value="$ADM_HOME/"/> <variable id="CSS_HOME" value="c:/MW_Home/Oracle_BI1/common/CSS/11.1.2.0"/>
-
Update the
PATHvariable to includeADM_HOME/binif your system is 32 bit orADM_HOME/bin-64if your system is 64 bit and theclientandcommonsubdirectories ofHYPERION_HOME. For example:<variable id="PATH" value="C:\oracle\product\10.2.0\db_1\BIN$: $ADM_HOME/bin$:$HYPERION_HOME/products/FinancialManagement/Common$: $HYPERION_HOME/products/FinancialManagement/Client$:$ORACLE_HOME/ common/ODBC/Merant/5.3/lib$:$ORACLE_HOME/bifoundation/server/bin$: $ORACLE_HOME/bifoundation/web/bin$:$ORACLE_HOME/common/ODBC/ Merant/5.3/lib$:$ORACLE_INSTANCE$:$ORACLE_HOME/lib" append="true"/>
-
Locate the data id="start-args" tag and update the classpath attribute to include the location of required jar files. For example:
<data id="start-args" value="-Xmx1024M -Djava.security.manager=default -DXDO_ FONT_DIR=$ORACLE_HOME/common/fonts -Djava.security.policy=$ORACLE_ INSTANCE/config/$COMPONENT_TYPE/$COMPONENT_NAME/java.policy -Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration -Doracle.core.ojdl.logging.config.file=$ORACLE_INSTANCE/config/$COMPONENT_ TYPE/$COMPONENT_NAME/logging-config.xml -Doracle.home=$ORACLE_HOME -Doracle.instance=$ORACLE_INSTANCE -Doracle.component.type=$COMPONENT_TYPE -Doracle.component.name=$COMPONENT_NAME -classpath $COMMON_COMPONENTS_ HOME/modules/oracle.odl_11.1.1/ojdl.jar$:$ORACLE_ HOME/bifoundation/javahost/lib/core/sautils.jar$:$ORACLE_ HOME/bifoundation/javahost/lib/core/mad.jar$:$ORACLE_ HOME/bifoundation/javahost/lib/core/sawconnect.jar$:$ORACLE_ HOME/bifoundation/javahost/lib/core/javahost.jar$:$COMMON_COMPONENTS_ HOME/modules/oracle.xdk_11.1.0/xmlparserv2.jar$:$ORACLE_ HOME/opmn/lib/ons.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.jps_ 11.1.1/jps-manifest.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.jps_ 11.1.1/jps-api.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.jps_ 11.1.1/jps-common.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.jps_ 11.1.1/jps-internal.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.nlsrtl_ 11.1.0/orai18n.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.nlsrtl_ 11.1.0/orai18n-collation.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.nlsrtl_ 11.1.0/orai18n-mapping.jar$:$COMMON_COMPONENTS_HOME/modules/oracle.dms_ 11.1.1/dms.jar$:$EPM_HOME/epm.jar$:$ADM_HOME/lib/adm.jar$:$ADM_HOME/lib/ap.jar$:$CSS_HOME/lib/css.jar$:$CSS_HOME/lib/common-model.jar com.siebel.analytics.javahost.standalone.Main"/> -
Save and close the file.
-
Go to the ORACLE_HOME/bifoundation/javahost/lib/obisintegration/adm directory and delete all jar files except for admintegration.jar and admimport.jar.
-
Restart the JavaHost process.
-
Repeat these steps on each computer that runs the Oracle BI JavaHost process. If you run multiple JavaHost 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_obijh1", ias-component id="coreapplication_obijh2", and so on).
Run more than one JavaHost process to ensure that JavaHost is not a single point of failure for access to Hyperion Financial Management. To do this, scale out the JavaHost process using Fusion Middleware Control. See "Scaling Your Deployment" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for information about scaling out processes.
Additional Hyperion Configuration for Client Installations
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. To import 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.
To point the Administration Tool at a running JavaHost:
-
Close the Administration Tool, if it is open.
-
On the same computer as the Administration Tool, open the local NQSConfig.INI file in a text editor. You can find this file at:
ORACLE_INSTANCE/config/OracleBIServerComponent/coreapplication_obisn
-
Locate the JAVAHOST_HOSTNAME_OR_IP_ADDRESSES parameter, near the bottom of the file. Update this 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"
Note that in a full (non-client) Oracle Business Intelligence installation, you cannot manually edit this setting because it is managed by Oracle Enterprise Manager Fusion Middleware Control.
-
Save and close the file.
Setting Up SAP/BW Data Sources
You can connect to SAP/BW data sources using either the XMLA connection type, or the SAP BW Native connection type (BAPI). SAP BW Native connections might not be available on certain platforms. See "System Requirements and Certification" for more information.
To connect to SAP/BW data sources using the SAP BW Native connection type, you must first download the BAPI Adapter for OBI Package from the Bristlecone Web site at:
http://www.bristleconelabs.com/edel/OBIEE_BAPIAdaptor_download.asp
Then, follow the configuration instructions in the Readme provided with the download.
No preconfiguration steps are required to connect to SAP/BW over XMLA.
Setting 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" for more information.
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.
See "Configuring Oracle RPAS ODBC Data Sources on AIX UNIX" for information about how to enable ODBC access to Oracle RPAS data sources when the Oracle BI Server is running on AIX UNIX.
Setting Up Teradata Data Sources
You can use ODBC to access Teradata data sources. See "Setting Up ODBC Data Source Names (DSNs)" for information about setting up ODBC connections for Teradata.
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:
D:\Program Files\NCR\Common Files\Shared ICU Libraries for Teradata\lib
In addition, you must manually edit opmn.xml on each computer running the Oracle BI Server to include required Teradata variables.
To update opmn.xml to include Teradata variables on Windows:
-
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 Teradata variables
TERA_HOME,TERAGSS_HOME, andTERAICU_HOME, and also update thePATHvariable, as shown in the following example. Replace the example values with the installed location for your Teradata client:<ias-component id="coreapplication_obis1" inherit-environment="true"> <environment> ... <variable id="TERA_HOME" value="C:/Progra~1/Teradata/Client/13.10/ ODBCDR~1" /> <variable id="TERAGSS_HOME" value="C:/Progra~1/Teradata/TERADA~1/nt-i386/ 131000~1.02" /> <variable id="TERAICU_HOME" value="C:/Progra~1/Teradata/Client/13.10/ SHARED~1" /> <variable id="PATH" value="$TERA_HOME/lib$:$TERAICU_HOME/lib$:$TERAGSS_ HOME/bin$:$ORACLE_HOME/bifoundation/server/bin$:$ORACLE_HOME/ bifoundation/web/bin$:$ORACLE_HOME/clients/epm/Essbase/EssbaseRTC/ bin$:" append="true" /> ... </environment>
Important:
If you use the default location when installing the Teradata client, then the
PATHvariable 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
-
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, be sure to 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).
Avoiding Spool Space Errors for Queries Against Teradata Data Sources
In some situations, 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. To do this, ensure 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.
-
Enabling 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 in Oracle Business Intelligence, which provides greater precision. In addition, literals are instantiated as NUMERIC instead of DOUBLE for Oracle Database and TimesTen data sources. See also "Numeric Literals" for related information.
To enable NUMERIC data type support for Oracle Database and TimesTen data sources:
-
Set
ENABLE_NUMERIC_DATA_TYPEtoYESin NQSConfig.INI. For instructions, see "ENABLE_NUMERIC_DATA_TYPE" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition. -
Enable the NUMERIC_SUPPORTED database feature in the Physical layer database object. See "Specifying SQL Features Supported by a Data Source" for more about how to set database features.
After you have performed these steps, data imported into the Oracle BI repository from Oracle Database and TimesTen will have decimal/numeric data set to NUMERIC, and decimal/numeric SQL entered by users will be treated as NUMERIC. Note that decimal/numeric data from other database types is still mapped as DOUBLE, even when this parameter is set to YES.
The data type of physical columns imported prior to changing this setting will remain the same. In other words, a column in Oracle Database or TimesTen that is declared as DOUBLE instead of NUMBER is still imported as DOUBLE in Oracle Business Intelligence, regardless of how this parameter is set. For existing DOUBLE physical columns, manually update the data type to NUMBER as needed.
Note the following:
-
Numeric data types can be cast to other Number data types, and vice versa.
-
Numeric data type support is not available through the Oracle BI Server JDBC driver.
-
There might be a performance overhead of enabling the numeric data type because of the higher number of bits for numeric data.
Configuring SSO for Essbase and Hyperion Financial Management Data Sources
If you want to use Essbase or Hyperion Financial Management installed with the EPM System Installer as a data source for the Oracle BI Server, and you want to authenticate using a shared token rather than configuring shared credentials in the connection pool, then you must first enable compatibility for shared tokens using the steps provided in this section. Note that the Oracle Business Intelligence user and the Enterprise Performance Management user must use the same identity store.
You do not need to perform any additional steps to enable SSO for Essbase data sources installed using the Oracle Business Intelligence installer. For Essbase data sources installed using the Oracle Business Intelligence installer, you only need to select SSO in the data source connection pool.
See "Configuring SSO Between Oracle BI Enterprise Edition and EPM Products" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about performing steps to authenticate using a shared token.
Importing Metadata from Relational Data Sources
You can import metadata for supported relational data source types by selecting the appropriate connection type in the Import Metadata Wizard. To import metadata, you must have all database connections set up on your local computer. You can import metadata in both offline and online modes.
See "Importing Metadata from Multidimensional Data Sources" and Chapter 6, "Working with ADF Data Sources" for information about importing from other data sources.
When you import physical tables, be careful to limit the import to only those tables that contain data that are likely to be used in the business models you create. You can use the Find feature to locate and select the tables that you want to import. Importing large numbers of extraneous tables and other objects adds unnecessary complexity and increases the size of the repository.
When you import metadata for most data sources, the default is to import tables, primary keys, and foreign keys. It is recommended that you import primary and foreign keys along with your tables so that the keys are automatically created in the Physical layer. If you do not import keys, you must create them manually, which can be a time-consuming process.
You can also import database views, aliases, synonyms, and system tables. Import these objects only if you want the Oracle BI Server to generate queries against them.
To import metadata from a relational data source:
-
In the Administration Tool, select File, then select Import Metadata. The Import Metadata Wizard appears.
Note:
If you have already defined an existing database and connection pool, you can right-click the connection pool in the Physical layer and select Import Metadata. The Import Metadata Wizard appears with the information on the Select Data Source screen pre-filled.
Figure 5-1 shows the Import Metadata Wizard.
Figure 5-1 Import Metadata Wizard: Relational Data Source
Description of "Figure 5-1 Import Metadata Wizard: Relational Data Source"
-
In the Select Data Source screen, in the Connection Type field, select the type of connection appropriate for your data source, such as ODBC 3.5.
Make sure to choose OCI 10g/11g if your data source is an Oracle Database. Using OCI as your connection protocol to an Oracle Database ensures better performance and provides access to native database features that are not available through ODBC.
Note:
For non-Oracle databases, it is recommended that you use ODBC 3.5 or DB2 CLI (Unicode) for importing schemas with International characters, such as Japanese table and column names.
The remaining fields and options on the Select Data Source screen vary according to the connection type you selected:
-
For ODBC 2.0 and ODBC 3.5 data sources, in the DSN list, select a data source from which to import the schema. Then, provide a valid user name and password for the data source.
Note that when you import through the Oracle BI Server, the DSN entries are on the Oracle BI Server computer, not on the local computer.
-
For OCI 10g/11g and DB2 CLI (Unicode) data sources, provide the name of the data source in the Data Source Name field, then provide a valid user name and password for the data source.
For Oracle Database data sources, the data source name is either a full connect string or a net service name from the tnsnames.ora file. If you enter a net service name, you must ensure that you have set up a tnsnames.ora file within the Oracle Business Intelligence environment, in ORACLE_HOME/network/admin.
Other data source types are described in other sections:
-
See "Importing Metadata from Multidimensional Data Sources" for Essbase, XMLA, Oracle OLAP, Hyperion ADM, and SAP BW Native. This section also describes importing from Oracle RPAS data sources over ODBC 3.5.
-
See "Importing Metadata from XML Data Sources" for XML.
-
See Chapter 6, "Working with ADF Data Sources" for OracleADF_HTTP.
When you have finished providing information in the Select Data Source screen, click Next. The Select Metadata Types screen appears.
-
-
Select the options for the types of objects that you want to import (for example, Tables, Keys, and Foreign Keys). Some options are automatically selected. Different types of data sources have different default selections, based on what is typical for that data source.
If you want to import joins, select both Keys and Foreign Keys. If you want to import system tables, you must have the system privilege for your data source. To import from Customer Relationship Management (CRM) tables, select Metadata from CRM tables.
-
Click Next. The Select Metadata Objects screen appears.
-
Select the objects you want to import in the Available list and move them to the Selected list, using the > (Import selected) or >> (Import all) buttons. You can also move objects from the Selected list back to the Available list, using the < (Remove selected) and << (Remove all) buttons.
To search for a particular item, enter a keyword in the Find box and then click Find Down or Find Up.
Select Show complete structure to view all objects, including those that have already been imported. Deselecting this option shows only the objects that are available for import. When this option is selected, objects that have already been imported appear grayed out.
-
Click Finish.
If some objects could not be imported, a list of warning messages appears. In the dialog displaying the messages, you can perform the following actions:
-
To search for specific terms, click Find and then Find Again.
-
To copy the contents of the window so that you can paste the messages in another file, click Copy.
-
After you import metadata, you should check to ensure that your database and connection pool settings are correct. In rare cases, the Oracle BI Server cannot determine the exact database type during import and instead assigns an approximate type to the database object. See "Setting Up Database Objects" and "Creating or Changing Connection Pools" for more information about working with these objects.
It is also a good practice to visually inspect the imported data in the Physical layer, such as physical columns and tables, to ensure that the import completed successfully.
About the Map to Logical Model and Publish to Warehouse Screens
The Map to Logical Model and Publish to Warehouse screens are available only for ADF data sources. For more information about the Map to Logical Model screen, see "Automatically Mapping Flex Object Changes to the Logical Model".
Importing Metadata from Multidimensional Data Sources
You can use the Administration Tool to import metadata from a multidimensional data source to the Physical layer of the Oracle BI repository. The ability to use multidimensional data sources enables the Oracle BI Server to connect to sources such as Essbase, Oracle OLAP, Hyperion Financial Management, Microsoft Analysis Services, and SAP/BW (SAP/Business Warehouse) to extract data. Data from these sources can be displayed on dashboards.
The primary differences between setting up multidimensional data sources and relational data sources are in the Physical layer. The setup in the Business Model and Presentation layers for multidimensional data sources and relational data sources is almost identical.
During the import process, each cube in a multidimensional data source is created as a single physical cube table. The Oracle BI Server imports the cube metadata, including its metrics, dimensions, and hierarchies. After importing the cubes, you need to ensure that the physical cube columns have the correct aggregation rule and that the hierarchy type is correct. See "Working with Physical Hierarchy Objects" for more information.
Caution:
Manually creating a physical schema from a multidimensional data source is labor-intensive and error prone. Therefore, it is strongly recommended that you use the import method.
It is recommended that you remove hierarchies and columns from the Physical layer if they will not be used in the business model. This eliminates maintaining unnecessary objects in the Administration Tool and might result in better performance.
To import metadata from a multidimensional data source:
-
In the Administration Tool, select File, then select Import Metadata. The Import Metadata Wizard appears.
Note:
If you have already defined an existing database and connection pool, you can right-click the connection pool in the Physical layer and select Import Metadata. The Import Metadata Wizard appears with the information on the Select Data Source screen pre-filled. You can also use this method to perform incremental imports.
Figure 5-2 shows the Import Metadata Wizard.
Figure 5-2 Import Metadata Wizard: Multidimensional Data Source
Description of "Figure 5-2 Import Metadata Wizard: Multidimensional Data Source"
Note that the Map to Logical Model and Publish to Warehouse screens are available only for ADF data sources. "About the Map to Logical Model and Publish to Warehouse Screens" for more information.
-
In the Select Data Source screen, in the Connection Type field, select the type of connection appropriate for your data source.
The remaining fields and options on the Select Data Source screen vary according to the connection type you selected. Table 5-1 describes the multidimensional connection types.
Table 5-1 Multidimensional Connection Options
Connection Type Description ODBC 3.5
The ODBC 3.5 connection type is used for Oracle RPAS data sources. Select the DSN entry and provide the user name and password for the selected data source. See "Setting Up ODBC Data Source Names (DSNs)" for more information.
Provide the host name of the computer where the Essbase Server is running in the Essbase Server field, then provide a valid user name and password for the data source. This information should be obtained from your data source administrator.
If the Essbase Server is running on a non-default port, or if it is part of an Essbase Cluster, you must include the port number in the Essbase Server field, in the format
hostname:port.Note the following:
-
You can import metadata from an Essbase Cluster, but you must still specify an individual Essbase Server host name and port number in the Essbase Server field.
-
You must ensure that Essbase client libraries are installed on the computer running the Oracle BI Server before you can import metadata from Essbase data sources. See "Setting Up Essbase Data Sources" for more information.
-
See "Working with Essbase Data Sources" for information about how data from Essbase data sources is modeled in the Physical layer.
The XMLA connection type is used for Microsoft Analysis Services and SAP/BW. Enter the URL of a data source from which to import the schema. Then, enter the Provider Type (such as Analysis Services 2000 or SAP/BW 3.5/7.0) and a valid user name and password for the data source.
For Target Database, do one of the following:
-
Select New and enter the name you want to use for the new database object.
-
Select Existing and then click Browse to select an existing database object.
Provide the name of the data source (net service name) in the Data Source Name field, then provide a valid user name and password for the data source. The data source name is the same as the entry you created in the tnsnames.ora file. Make sure that the name you use is from the tnsnames.ora file within the Oracle Business Intelligence environment. You can also choose to enter a full connect string rather than the net service name.
For URL, provide the URL of the biadminservlet. This servlet is used for Oracle OLAP metadata imports. The name of the servlet is
services. For example, enter a string similar to the following in the URL field:http://localhost:9704/biadminservlet/services
Note that the servlet must be up and running in order to use it. If you receive an import error, then check the status of the servlet in the Administration Console. You can also refer to the Administration Server diagnostic log and the Domain log.
See "Working with Oracle OLAP Data Sources" for information about how data from Oracle OLAP data sources is modeled in the Physical layer.
Note: Oracle Database data sources with the OLAP option can contain both relational tables and multidimensional tables. However, you should avoid having both table types in the same database object in the Administration Tool, because you may need to specify different database feature sets for the different table types.
For example, Oracle OLAP queries fail if the database feature
GROUP_BY_GROUPING_SETS_SUPPORTEDis enabled. However, you may need this feature enabled for Oracle Database relational tables.As a best practice, create two separate database objects for relational and multidimensional tables.
Provide the URL for the Hyperion Financial Management server, including the driver and application name (cube name), in the following format:
adm:native:HsvADMDriver:ip_or_host:application_name
For example:
adm:native:HsvADMDriver:130.35.40.80:UCFHFM
You also need to enter a valid user name and password for your data source.
Note that the JavaHost process must be running to import from Hyperion Financial Management data sources, for both offline and online imports.
See "Working with Hyperion Financial Management Data Sources" for information about how data from Hyperion Financial Management data sources is modeled in the Physical layer.
Note: Be sure to complete the preconfiguration steps described in "Setting Up Hyperion Financial Management Data Sources" before import.
Provide the following information:
-
System IP or Hostname: The host name or IP address of the SAP data server. This field corresponds to the parameter
ashostin the SAP/BW connect string. -
System Number: The SAP system number. This is a two-digit number assigned to an SAP instance, also called Web Application Server, or WAS. This field corresponds to the parameter
sysnrin the SAP/BW connect string. -
Client Number: The SAP client number. This is a three-digit number assigned to the self-contained unit called Client in SAP. A Client can be a training, development, testing, or production client, or it can represent different divisions in a large company. This field corresponds to the parameter
clientin the SAP/BW connect string. -
Language: The SAP language code used when logging in to the data source (for example, EN for English or DE for German). This field corresponds to the parameter
langin the SAP/BW connect string. -
Additional Parameters: Additional connection string parameters in the format param=value. Delimit multiple parameters with a colon. This field is optional.
-
User Name: A valid user name for the data source.
-
Password: The corresponding user password. The password is case-sensitive.
The first five fields constitute the elements of the SAP/BW connect string, in the format:
ashost=value:sysnr=value:client=value:lang=value:additional_param=value
For example:
ashost=10.30.0.19:sysnr=00:client=100:lang=EN
Note: Be sure to complete the preconfiguration steps described in "Setting Up SAP/BW Data Sources" before import.
Other data source types are described in other sections:
-
See "Importing Metadata from XML Data Sources" for XML.
-
See "Importing Metadata from Relational Data Sources" for ODBC 2.0, OCI 10g/11g, and DB2 CLI (Unicode).
-
See Chapter 6, "Working with ADF Data Sources" for OracleADF_HTTP.
When you have finished providing information in the Select Data Source screen, click Next.
-
-
For Oracle RPAS data sources only, the Select Metadata Types screen is displayed. For Oracle RPAS, select Tables, Keys, and Foreign Keys. Then, click Next.
See "About Importing Metadata from Oracle RPAS Data Sources" for more information.
-
In the Select Metadata Objects screen, select the objects you want to import in the Available list and move them to the Selected list, using the > (Import selected) or >> (Import all) buttons. You can also move objects from the Selected list back to the Available list, using the < (Remove selected) and << (Remove all) buttons.
To search for a particular item, enter a keyword in the Find box and then click Find Down or Find Up.
Select Show complete structure to view all objects, including those that have already been imported. Deselecting this option shows only the objects that are available for import. When this option is selected, objects that have already been imported appear grayed out
For Essbase data sources, select Import UDAs if you want to import UDAs (user-defined attributes).
-
Click Finish.
If some objects could not be imported, a list of warning messages appears. In the dialog displaying the messages, you can perform the following actions:
-
To search for specific terms, click Find and then Find Again.
-
To copy the contents of the window so that you can paste the messages in another file, click Copy.
-
After you import metadata, you should check to ensure that your database and connection pool settings are correct. In rare cases, the Oracle BI Server cannot determine the exact database type during import and instead assigns an approximate type to the database object. See "Setting Up Database Objects" and "Creating or Changing Connection Pools" for more information about working with these objects.
It is also a good practice to visually inspect the imported data in the Physical layer, such as physical columns and hierarchical levels, to ensure that the import completed successfully.
For Essbase data sources, all hierarchies are imported as Unbalanced by default. Review the Hierarchy Type property for each physical hierarchy and change the value if necessary. Supported hierarchy types for Essbase are Unbalanced, Fully balanced, and Value.
About Importing Metadata from Oracle RPAS Data Sources
This section provides important information about using the Administration Tool to import metadata from Oracle RPAS, as follows:
-
Oracle RPAS schemas can only be imported on Windows.
-
Before you import RPAS schemas, you must set the Normalize Dimension Tables field value in the ODBC DSN Setup page to Yes for the following reasons:
-
Setting this value to Yes uses an appropriate schema model (the snowflake schema) that creates joins correctly and enables drill down in the data.
-
Setting this value to No uses a less appropriate schema model (the star schema) that creates joins between all of the tables, causing drill down to not work correctly. Many of the joins created in this way are unwanted, and would need to be removed manually.
See "Setting Up ODBC Data Source Names (DSNs)" for more information.
-
-
When you import RPAS schemas in the Administration Tool, you must import the data with joins. To do this, select the metadata types Keys and Foreign Keys in the Import Metadata Wizard.
-
After you have imported RPAS schemas, you must change the Normalize Dimension Tables field value in the ODBC DSN Setup page back to No. You need to revert this setting back to No after import to enable the Oracle BI Server to correctly generate optimized SQL against the RPAS driver.
Note: If you do not change the Normalize Dimension Tables setting value to No, most queries will fail with an error message similar to the following:
[nQSError: 16001] ODBC error state: S0022 code: 0 message: [Oracle Retail][RPAS ODBC]Column:YEAR_LABEL not found..[nQSError: 16014] SQL statement preparation failed. Statement execute failed.
-
If Oracle RPAS is the only data source, you must set the value of
NULL_VALUES_SORT_FIRSTtoONin the NQSConfig.INI file. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about setting values in NQSConfig.INI.
After you import metadata from an Oracle RPAS data source, a database object for the schema is automatically created. Depending on your version of RPAS, you might need to adjust the data source definition: Database property (located in the Database dialog's General tab) for the following reason.
If RPAS is specified in the data source definition Database field and the version of RPAS is prior to 1.2.2, then the BI Server performs aggregate navigation when the SQL is generated and sent to the database and not as it typically does in the logical table source navigation. Because the table name used in the generated SQL is automatically generated, there will be a mismatch between the generated SQL and the database table name. To enable the SQL to run, you must:
-
Change the names of tables listed in the metadata so that the generated names are correct.
-
Create tables in the database with the same names as the generated names.
If the database does not have tables with the same name or if you want to have the standard aggregate navigation within Oracle Business Intelligence, then you must change the data source definition: Database field from RPAS to ODBC Basic. See "Creating a Database Object Manually in the Physical Layer" for more information.
Importing Metadata from XML Data Sources
This section describes how to import metadata from Extensible Markup Language (XML) documents. XML is the universal format for structured documents and data on the Web. It can also be used as a database to store structured data.
The Oracle BI Server supports two XML access modes: access through the Oracle BI Server XML Gateway, and access through an XML ODBC driver.
This section contains the following topics:
About Using XML as a Data Source
The Oracle BI Server supports the use of XML data as a data source for the Physical layer in the repository. Depending on the method used to access XML data sources, a data source may be represented by a URL pointing to one of the following sources:
-
A static XML file or HTML file that contains XML data islands on the Internet (including intranet or extranet). For example:
tap://216.217.17.176/[DE0A48DE-1C3E-11D4-97C9-00105AA70303].XML
-
Dynamic XML generated from a server site. For example:
tap://www.aspserver.com/example.asp
-
An XML file or HTML file that contains XML data islands on a local or network drive. For example:
d:/xmldir/example.xml
d:/htmldir/island.htm
You can also specify a directory path for local or network XML files, or you can use the asterisk ( * ) as a wildcard with the filenames. If you specify a directory path without a filename specification (like d:/xmldir), all files with the XML suffix are imported. For example:
d:/xmldir/
d:/xmldir/exam*.xml
d:/htmldir/exam*.htm
d:/htmldir/exam*.html
-
An HTML file that contains tables, defined by a pair of <table> and </table> tags. The HTML file may reside on the Internet (including intranet or extranet), or on a local or network drive. See "Accessing HTML Tables" for more information.
URLs can include repository or session variables, providing support for HTTP data sources that accept user IDs and passwords embedded in the URL. For example:
http://somewebserver/cgi.pl?userid=valueof(session_variable1)&password= valueof(session_variable2)
This functionality also lets you create an XML data source with a location that is dynamically determined by some run-time parameters. For more information about variables, see Chapter 19.
If the Oracle BI Server needs to access any nonlocal files (network files or files on the Internet, for example), you must run the Oracle BI Server using a valid user ID and password with sufficient network privileges to access these remote files.
Importing Metadata from XML Data Sources Using the XML Gateway
Using the Oracle BI Server XML Gateway, the metadata import process flattens the XML document to a tabular form using the stem of the XML filename (that is, the filename without the suffix) as the table name and the second level element in the XML document as the row delimiter. All leaf nodes are imported as columns belonging to the table. The hierarchical access path to leaf nodes is also imported.
The Oracle BI Server XML Gateway uses the metadata information contained in an XML schema. The XML schema is contained within the XML document, or is referenced within the root element of the XML document.
Where there is no schema available, all XML data is imported as text data. In building the repository, you can alter the data types of the columns in the Physical layer, overriding the data types for the corresponding columns defined in the schema. The gateway converts the incoming data to the desired type as specified in the Physical layer. You can also map the text data type to other data types in the Business Model and Mapping layer of the Administration Tool, using the CAST operator.
The Oracle BI Server XML Gateway does not support:
-
Resolution of external references contained in an XML document (other than a reference to an external XML schema, as demonstrated in the example file in "Examples of XML Documents Generated by the Oracle BI Server XML Gateway").
-
Element and attribute inheritance contained within the Microsoft XML schema.
-
Element types of a mixed content model (such as XML elements that contain a mixture of elements and CDATA, such as <p> hello <b>Joe</b>, how are you doing?</p>).
To import XML data using the Oracle BI Server XML Gateway:
-
In the Administration Tool, select File, then select Import Metadata. The Import Metadata Wizard appears.
Note:
If you have already defined an existing database and connection pool, you can right-click the connection pool in the Physical layer and select Import Metadata. The Import Metadata Wizard appears with the information on the Select Data Source screen pre-filled.
Figure 5-3 shows the Import Metadata Wizard.
Figure 5-3 Import Metadata Wizard: XML Data Source
Description of "Figure 5-3 Import Metadata Wizard: XML Data Source"
Note that the Map to Logical Model and Publish to Warehouse screens are available only for ADF data sources. "About the Map to Logical Model and Publish to Warehouse Screens" for more information.
-
In the Select Data Source screen, select XML for Connection Type. Then, provide the following values:
-
For URL, specify the XML data source URL. The Oracle BI Server XML Gateway supports all data sources described in the previous section.
URLs can include repository or session variables. For more information about variables, see Chapter 19.
If you click Browse, the Select XML File dialog appears, from which you can select a single file. For XML documents, the file name in the URL that you specify must have the suffix
.xml. Otherwise, the documents are treated as HTML documents. -
Type an optional user name and password in the appropriate fields for connections to HTTP sites that employ the HTTP Basic Authentication security mode.
In addition to HTTP Basic Authentication security mode, the Oracle BI Server XML Gateway also supports Secure HTTP protocol and Integrated Windows Authentication (for Windows 2000), formerly called NTLM or Windows NT Challenge/Response authentication.
When you have finished providing information in the Select Data Source screen, click Next. The Select Metadata Types screen appears.
-
-
Select the options for the types of objects that you want to import (for example, Tables, Keys, and Foreign Keys). The most typical options are automatically selected.
If you want to import joins, select both Keys and Foreign Keys. If you want to import system tables, you must have the system privilege for your data source.
-
Click Next. The Select Metadata Objects screen appears.
-
Select the objects you want to import in the Available list and move them to the Selected list, using the > (Import selected) or >> (Import all) buttons. You can also move objects from the Selected list back to the Available list, using the < (Remove selected) and << (Remove all) buttons.
To search for a particular item, enter a keyword in the Find box and then click Find Down or Find Up.
Select Show complete structure to view all objects, including those that have already been imported. Deselecting this option shows only the objects that are available for import. When this option is selected, objects that have already been imported appear grayed out.
-
Click Finish.
After you import XML data, you must adjust connection pool settings. See "Creating or Changing Connection Pools" for complete information. Minimally, you can do the following:
-
In the Connection Pool dialog, type a name and optional description for the connection on the General tab.
-
Click the XML tab to set additional connection properties, including the URL refresh interval and the length of time to wait for a URL to load before timing out.
Because XML data sources are typically updated frequently and in real time, you can specify a refresh interval for Oracle BI Server XML Gateway data sources. The default timeout interval for queries (URL loading time-out) is 15 minutes. For more information, see "About the Refresh Interval for XML Data Sources" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
Examples of XML Documents Generated by the Oracle BI Server XML Gateway
Example 5-1 and Example 5-2 show sample XML documents and the corresponding columns that are generated by the Oracle BI Server XML Gateway.
Example 5-1 XML Schema Contained in an External File
The following sample XML data document (mytest.xml) references an XML schema contained in an external file. The schema file is shown following the data document. The generated XML schema information available for import to the repository is shown at the end.
<?xml version="1.0"?>
<test xmlns="x-schema:mytest_sch.xml">
<row>
<p1>0</p1>
<p2 width="5">
<p3>hi</p3>
<p4>
<p6>xx0</p6>
<p7>yy0</p7>
</p4>
<p5>zz0</p5>
</p2>
</row>
<row>
<p1>1</p1>
<p2 width="6">
<p3>how are you</p3>
<p4>
<p6>xx1</p6>
<p7>yy1</p7>
</p4>
<p5>zz1</p5>
</p2>
</row>
<row>
<p1>a</p1>
<p2 width="7">
<p3>hi</p3>
<p4>
<p6>xx2</p6>
<p7>yy2</p7>
</p4>
<p5>zz2</p5>
</p2>
</row>
<row>
<p1>b</p1>
<p2 width="8">
<p3>how are they</p3>
<p4>
<p6>xx3</p6>
<p7>yy3</p7>
</p4>
<p5>zz2</p5>
</p2>
</row>
</test>
The corresponding schema file follows:
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="test" content="eltOnly" order="many">
<element type="row"/>
</ElementType>
<ElementType name="row" content="eltOnly" order="many">
<element type="p1"/>
<element type="p2"/>
</ElementType>
<ElementType name="p2" content="eltOnly" order="many">
<AttributeType name="width" dt:type="int" />
<attribute type="width" />
<element type="p3"/>
<element type="p4"/>
<element type="p5"/>
</ElementType>
<ElementType name="p4" content="eltOnly" order="many">
<element type="p6"/>
<element type="p7"/>
</ElementType>
<ElementType name="p1" content="textOnly" dt:type="string"/>
<ElementType name="p3" content="textOnly" dt:type="string"/>
<ElementType name="p5" content="textOnly" dt:type="string"/>
<ElementType name="p6" content="textOnly" dt:type="string"/>
<ElementType name="p7" content="textOnly" dt:type="string"/>
</Schema>
The name of the table generated from the preceding XML data document (mytest.xml) would be mytest and the column names would be p1, p3, p6, p7, p5, and width.
In addition, to preserve the context in which each column occurs in the document and to distinguish between columns derived from XML elements with identical names but appearing in different contexts, a list of fully qualified column names is generated, based on the XPath proposal of the World Wide Web Consortium, as follows:
//test/row/p1 //test/row/p2/p3 //test/row/p2/p4/p6 //test/row/p2/p4/p7 //test/row/p2/p5 //test/row/p2@width
Example 5-2 Nested Table Structures in an XML Document
The following example is a more complex example that demonstrates the use of nested table structures in an XML document. You can optionally omit references to an external schema file, in which case all elements are treated as being of the Varchar character type.
===Invoice.xml===
<INVOICE>
<CUSTOMER>
<CUST_ID>1</CUST_ID>
<FIRST_NAME>Nancy</FIRST_NAME>
<LAST_NAME>Fuller</LAST_NAME>
<ADDRESS>
<ADD1>507 - 20th Ave. E.,</ADD1>
<ADD2>Apt. 2A</ADD2>
<CITY>Seattle</CITY>
<STATE>WA</STATE>
<ZIP>98122</ZIP>
</ADDRESS>
<PRODUCTS>
<CATEGORY>
<CATEGORY_ID>CAT1</CATEGORY_ID>
<CATEGORY_NAME>NAME1</CATEGORY_NAME>
<ITEMS>
<ITEM>
<ITEM_ID>1</ITEM_ID>
<NAME></NAME>
<PRICE>0.50</PRICE>
<QTY>2000</QTY>
</ITEM>
<ITEM>
<ITEM_ID>2</ITEM_ID>
<NAME>SPRITE</NAME>
<PRICE>0.30</PRICE>
<QTY></QTY>
</ITEM>
</ITEMS>
</CATEGORY>
<CATEGORY>
<CATEGORY_ID>CAT2</CATEGORY_ID>
<CATEGORY_NAME>NAME2</CATEGORY_NAME>
<ITEMS>
<ITEM>
<ITEM_ID>11</ITEM_ID>
<NAME>ACOKE</NAME>
<PRICE>1.50</PRICE>
<QTY>3000</QTY>
</ITEM>
<ITEM>
<ITEM_ID>12</ITEM_ID>
<NAME>SOME SPRITE</NAME>
<PRICE>3.30</PRICE>
<QTY>2000</QTY>
</ITEM>
</ITEMS>
</CATEGORY>
</PRODUCTS>
</CUSTOMER>
<CUSTOMER>
<CUST_ID>2</CUST_ID>
<FIRST_NAME>Andrew</FIRST_NAME>
<LAST_NAME>Carnegie</LAST_NAME>
<ADDRESS>
<ADD1>2955 Campus Dr.</ADD1>
<ADD2>Ste. 300</ADD2>
<CITY>San Mateo</CITY>
<STATE>CA</STATE>
<ZIP>94403</ZIP>
</ADDRESS>
<PRODUCTS>
<CATEGORY>
<CATEGORY_ID>CAT22</CATEGORY_ID>
<CATEGORY_NAME>NAMEA1</CATEGORY_NAME>
<ITEMS>
<ITEM>
<ITEM_ID>122</ITEM_ID>
<NAME>DDDCOKE</NAME>
<PRICE>11.50</PRICE>
<QTY>2</QTY>
</ITEM>
<ITEM>
<ITEM_ID>22</ITEM_ID>
<NAME>PSPRITE</NAME>
<PRICE>9.30</PRICE>
<QTY>1978</QTY>
</ITEM>
</ITEMS>
</CATEGORY>
<CATEGORY>
<CATEGORY_ID>CAT24</CATEGORY_ID>
<CATEGORY_NAME>NAMEA2</CATEGORY_NAME>
<ITEMS>
<ITEM>
<ITEM_ID>19</ITEM_ID>
<NAME>SOME COKE</NAME>
<PRICE>1.58</PRICE>
<QTY>3</QTY>
</ITEM>
<ITEM>
<ITEM_ID>15</ITEM_ID>
<NAME>DIET SPRITE</NAME>
<PRICE>9.30</PRICE>
<QTY>12000</QTY>
</ITEM>
</ITEMS>
</CATEGORY>
</PRODUCTS>
</CUSTOMER>
<CUSTOMER>
<CUST_ID>3</CUST_ID>
<FIRST_NAME>Margaret</FIRST_NAME>
<LAST_NAME>Leverling</LAST_NAME>
<ADDRESS>
<ADD1>722 Moss Bay Blvd.</ADD1>
<ADD2> </ADD2>
<CITY>Kirkland</CITY>
<STATE>WA</STATE>
<ZIP>98033</ZIP>
</ADDRESS>
<PRODUCTS>
<CATEGORY>
<CATEGORY_ID>CAT31</CATEGORY_ID>
<CATEGORY_NAME>NAMEA3</CATEGORY_NAME>
<ITEMS>
<ITEM>
<ITEM_ID>13</ITEM_ID>
<NAME>COKE33</NAME>
<PRICE>30.50</PRICE>
<QTY>20033</QTY>
</ITEM>
<ITEM>
<ITEM_ID>23</ITEM_ID>
<NAME>SPRITE33</NAME>
<PRICE>0.38</PRICE>
<QTY>20099</QTY>
</ITEM>
</ITEMS>
</CATEGORY>
<CATEGORY>
<CATEGORY_ID>CAT288</CATEGORY_ID>
<CATEGORY_NAME>NAME H</CATEGORY_NAME>
<ITEMS>
<ITEM>
<ITEM_ID>19</ITEM_ID>
<NAME>COLA</NAME>
<PRICE>1.0</PRICE>
<QTY>3</QTY>
</ITEM>
<ITEM>
<ITEM_ID>18</ITEM_ID>
<NAME>MY SPRITE</NAME>
<PRICE>8.30</PRICE>
<QTY>123</QTY>
</ITEM>
</ITEMS>
</CATEGORY>
</PRODUCTS>
</CUSTOMER>
</INVOICE>
The generated XML schema consists of one table (INVOICE) with the following column names and their corresponding fully qualified names.
| Column | Fully Qualified Name |
|---|---|
|
ADD1 |
//INVOICE/CUSTOMER/ADDRESS/ADD1 |
|
ADD2 |
//INVOICE/CUSTOMER/ADDRESS/ADD2 |
|
CITY |
//INVOICE/CUSTOMER/ADDRESS/CITY |
|
STATE |
//INVOICE/CUSTOMER/ADDRESS/STATE |
|
ZIP |
//INVOICE/CUSTOMER/ADDRESS/ZIP |
|
CUST_ID |
//INVOICE/CUSTOMER/CUST_ID |
|
FIRST_NAME |
//INVOICE/CUSTOMER/FIRST_NAME |
|
LAST_NAME |
//INVOICE/CUSTOMER/LAST_NAME |
|
CATEGORY_ID |
//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/CATEGORY_ID |
|
CATEGORY_NAME |
//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/CATEGORY_NAME |
|
ITEM_ID |
//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/ITEM_ID |
|
NAME |
//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/NAME |
|
PRICE |
//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/PRICE |
|
QTY |
//INVOICE/CUSTOMER/PRODUCTS/CATEGORY/ITEMS/ITEM/QTY |
Only tags with values are extracted as columns. An XML query generates fully qualified tag names, to help ensure appropriate columns are retrieved.
The following shows the results of a sample query against the INVOICE table:
SELECT first_name, last_name, price, qty, name FROM invoice ------------------------------------------------------------ FIRST_NAME LAST_NAME PRICE QTY NAME ------------------------------------------------------------ Andrew Carnegie 1.58 3 SOME COKE Andrew Carnegie 11.50 2 DDDCOKE Andrew Carnegie 9.30 12000 DIET SPRITE Andrew Carnegie 9.30 1978 PSPRITE Margar Leverling 0.38 20099 SPRITE33 Margar Leverling 1.0 3 COLA Margar Leverling 30.50 20033 COKE33 Margar Leverling 8.30 123 MY SPRITE Nancy Fuller 0.30 SPRITE Nancy Fuller 0.50 2000 Nancy Fuller 1.50 3000 ACOKE Nancy Fuller 3.30 2000 SOME SPRITE ------------------------------------------------------------ Row count: 12
Accessing HTML Tables
The Oracle BI Server XML Gateway also supports the use of tables in HTML files as a data source. The HTML file can be identified as a URL pointing to a file on the internet (including intranet or extranet) or as a file on a local or network drive.
Even though tables, defined by the <table> and </table> tag pair, are native constructs of the HTML 4.0 specification, they are often used by Web designers as a general formatting device to achieve specific visual effects rather than as a data structure. The Oracle BI Server XML Gateway is currently the most effective in extracting tables that include specific column headers, defined by <th> and </th> tag pairs.
For tables that do not contain specific column headers, the Oracle BI Server XML Gateway employs some simple heuristics to make a best effort to determine the portions of an HTML file that appear to be genuine data tables.
The following is a sample HTML file with one table.
<html>
<body>
<table border=1 cellpadding=2 cellspacing=0>
<tr>
<th colspan=1>Transaction</th>
<th colspan=2>Measurements</th>
</tr>
<tr>
<th>Quality</th>
<th>Count</th>
<th>Percent</th>
</tr>
<tr>
<td>Failed</td>
<td>66,672</td>
<td>4.1%</td>
</tr>
<tr>
<td>Poor</td>
<td>126,304</td>
<td>7.7%</td>
</tr>
<tr>
<td>Warning</td>
<td>355,728</td>
<td>21.6%</td>
</tr>
<tr>
<td>OK</td>
<td>1,095,056</td>
<td>66.6%</td>
</tr>
<tr>
<td colspan=1>Grand Total</td>
<td>1,643,760</td>
<td>100.0%</td>
</tr>
</table>
</body>
</html>
The table name is derived from the HTML filename, and the column names are formed by concatenating the headings (defined by the <th> and </th> tag pairs) for the corresponding columns, separated by an underscore.
Assuming that our sample file is named 18.htm, the table name would be 18_0 (because it is the first table in that HTML file), with the following column names and their corresponding fully qualified names:
| Column | Fully Qualified Name |
|---|---|
|
Transaction_Quality |
\\18_0\Transaction_Quality |
|
Measurements_Count |
\\18_0\Measurements_Count |
|
Measurements_Percent |
\\18_0\Measurements_Percent |
If the table column headings appear in more than one row, the column names are formed by concatenating the corresponding field contents of those header rows.
For tables without any heading tag pairs, the Oracle BI Server XML Gateway assumes the field values (as delimited by the <td> and </td> tag pairs) in the first row to be the column names. The columns are named by the order in which they appear (c0, c1, and so on).
See "Importing Metadata from XML Data Sources Using XML ODBC" and "Examples of XML Documents" for additional XML examples.
Importing Metadata from XML Data Sources Using XML ODBC
Using the XML ODBC database type, you can access XML data sources through an ODBC interface. The data types of the XML elements representing physical columns in physical tables are derived from the data types of the XML elements as defined in the XML schema. In the absence of a proper XML schema, the default data type of string is used. Data Type settings in the Physical layer do not override those defined in the XML data sources. When accessing XML data without XML schema, use the CAST operator to perform data type conversions in the Business Model and Mapping layer of the Administration Tool.
To import XML data using ODBC:
-
To access XML data sources through ODBC, you first need to license and install an XML ODBC driver.
-
Create ODBC DSNs that point to the XML data sources you want to access, making sure you select the XML ODBC database type.
-
In the Administration Tool, select File, then select Import Metadata.
-
Follow the instructions in the dialogs to import the ODBC DSNs into the repository. See "Importing Metadata from Relational Data Sources" for more information.
Caution:
Due to XML ODBC limitations, you must select the Synonyms option in the Select Metadata Types screen, or no tables are imported.
Example of an XML ODBC Data Source
Example 5-3 shows an example of an XML ODBC data source in the Microsoft ADO persisted file format. Both the data and the schema could be contained inside the same document.
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'
rs:updatable='true'>
<s:AttributeType name='ShipperID' rs:number='1' rs:writeunknown='true'
rs:basecatalog='Paint' rs:basetable='Shippers' rs:basecolumn='ShipperID'>
<s:datatype dt:type='i2' dt:maxLength='2' rs:precision='5'
rs:fixedlength='true' rs:benull='false'/>
</s:AttributeType>
<s:AttributeType name='CompanyName' rs:number='2' rs:writeunknown='true'
rs:basecatalog='Paint' rs:basetable='Shippers' rs:basecolumn='CompanyName'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='40'
rs:benull='false'/>
</s:AttributeType>
<s:AttributeType name='Phone' rs:number='3' rs:nullable='true'
rs:writeunknown='true' rs:basecatalog='Paint' rs:basetable='Shippers'
rs:basecolumn='Phone'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='24'
rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row ShipperID='1' CompanyName='Speedy Express' Phone='(503)
555-9831 '/>
<z:row ShipperID='2' CompanyName='United Package' Phone='(503)
555-3199 '/>
<z:row ShipperID='3' CompanyName='Federal Shipping' Phone='(503)
555-9931 '/>
</rs:data>
</xml>
Examples of XML Documents
The following XML documents provide examples of several different situations and explain how the Oracle BI Server XML access method handles those situations.
-
The XML documents 83.xml and 8_sch.xml (shown in Example 5-4 and Example 5-5) demonstrate the use of the same element declarations in different scope. For example, <p3> could appear within <p2> as well as within <p4>.
Because the element <p3> in the preceding examples appears in two different scopes, each element is given a distinct column name by appending an index number to the second occurrence of the element during the import process. In this case, the second occurrence becomes p3_1. If <p3> occurs in additional contexts, they become p3_2, p3_3.
-
The XML documents 83.xml and 84.xml (shown in Example 5-4 and Example 5-6) demonstrate that multiple XML files can share the same schema (8_sch.xml).
-
Internet Explorer version 5 and higher supports HTML documents containing embedded XML fragments called XML islands. The XML document island2.htm (shown in Example 5-7) demonstrates a simple situation where multiple XML data islands, and therefore multiple tables, could be generated from one document. One table is generated for each instance of an XML island. Tables are distinguished by appending an appropriate index to the document name. For island2.htm, the two XML tables generated would be island2_0 and island2_1.
===83.xml===
<?xml version="1.0"?>
<test xmlns="x-schema:8_sch.xml">|
<row>
<p1>0</p1>
<p2 width="5" height="2">
<p3>hi</p3>
<p4>
<p3>hi</p3>
<p6>xx0</p6>
<p7>yy0</p7>
</p4>
<p5>zz0</p5>
</p2>
</row>
<row>
<p1>1</p1>
<p2 width="6" height="3">
<p3>how are you</p3>
<p4>
<p3>hi</p3>
<p6>xx1</p6>
<p7>yy1</p7>
</p4>
<p5>zz1</p5>
</p2>
</row>
</test>
===8_sch.xml===
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<AttributeType name="height" dt:type="int" />
<ElementType name="test" content="eltOnly" order="many">
<AttributeType name="height" dt:type="int" />
<element type="row"/>
</ElementType>
<ElementType name="row" content="eltOnly" order="many">
<element type="p1"/>
<element type="p2"/>
</ElementType>
<ElementType name="p2" content="eltOnly" order="many">
<AttributeType name="width" dt:type="int" />
<AttributeType name="height" dt:type="int" />
<attribute type="width" />
<attribute type="height" />
<element type="p3"/>
<element type="p4"/>
<element type="p5"/>
</ElementType>
<ElementType name="p4" content="eltOnly" order="many">
<element type="p3"/>
<element type="p6"/>
<element type="p7"/>
</ElementType>
<ElementType name="test0" content="eltOnly" order="many">
<element type="row"/>
</ElementType>
<ElementType name="p1" content="textOnly" dt:type="string"/>
<ElementType name="p3" content="textOnly" dt:type="string"/>
<ElementType name="p5" content="textOnly" dt:type="string"/>
<ElementType name="p6" content="textOnly" dt:type="string"/>
<ElementType name="p7" content="textOnly" dt:type="string"/>
</Schema>
===84.xml===
<?xml version="1.0"?>
<test0 xmlns="x-schema:8_sch.xml">
<row>
<p1>0</p1>
<p2 width="5" height="2">
<p3>hi</p3>
<p4>
<p3>hi</p3>
<p6>xx0</p6>
<p7>yy0</p7>
</p4>
<p5>zz0</p5>
</p2>
</row>
<row>
<p1>1</p1>
<p2 width="6" height="3">
<p3>how are you</p3>
<p4>
<p3>hi</p3>
<p6>xx1</p6>
<p7>yy1</p7>
</p4>
<p5>zz1</p5>
</p2>
</row>
</test0>
===island2.htm===
<HTML>
<HEAD>
<TITLE>HTML Document with Data Island</TITLE>
</HEAD>
<BODY>
<p>This is an example of an XML data island in I.E. 5</p>
<XML ID="12345">
test>
<row>
<field1>00</field1>
<field2>01</field2>
</row>
<row>
<field1>10</field1>
<field2>11</field2>
</row>
<row>
<field1>20</field1>
<field2>21</field2>
</row>
</test>
</XML>
<p>End of first example.</p>
<XML ID="12346">
<test>
<row>
<field11>00</field11>
<field12>01</field12>
</row>
<row>
<field11>10</field11>
<field12>11</field12>
</row>
<row>
<field11>20</field11>
<field12>21</field12>
</row>
</test>
</XML>
<p>End of second example.</p>
</BODY>
</HTML>
Using a Standby Database with Oracle Business Intelligence
A standby database is used mainly for its high availability and failover functions as a backup for the primary database. In a standby database configuration, there is regularly scheduled replication from the primary database to the secondary database. The latency of this replication must be short enough that writing to the primary database while reading from the secondary database does not cause any synchronization or data integrity problems.
Because a standby database is essentially a read-only database, it can be used as a business intelligence query server, relieving the workload of the primary database and improving query performance.
The following sections explain how to use a standby database with Oracle Business Intelligence:
-
About Using a Standby Database with Oracle Business Intelligence
-
Creating the Database Object for the Standby Database Configuration
-
Creating Connection Pools for the Standby Database Configuration
-
Updating Write-Back Scripts in a Standby Database Configuration
-
Setting Up Usage Tracking in a Standby Database Configuration
-
Setting Up Event Polling in a Standby Database Configuration
-
Setting Up Oracle BI Scheduler in a Standby Database Configuration
About Using a Standby Database with Oracle Business Intelligence
In a standby database configuration, you have two databases: a primary database that handles all write operations and is the source of truth for data integrity, and a secondary database that is exposed as a read-only source. When you use a standby database configuration with Oracle Business Intelligence, all write operations are offloaded to the primary database, and read operations are sent to the standby database.
Write operations that need to be routed to the primary source may include the following:
-
Oracle BI Scheduler job and instance data
-
Temporary tables for performance enhancements
-
Writeback scripts for aggregate persistence
-
Usage tracking data, if usage tracking has been enabled
-
Event polling table data, if event polling tables are being used
The following list provides an overview of how to configure the Oracle BI Server to use a standby database:
-
Create a single database object for the standby database configuration, with temporary table creation disabled.
-
Configure two connection pools for the database object:
-
A read-only connection pool that points to the standby database
-
A second connection pool that points to the primary database for write operations
-
-
Update any connection scripts that write to the database so that they explicitly specify the primary database connection pool.
-
If usage tracking has been enabled, update the usage tracking configuration to use the primary connection.
-
If event polling tables are being used, update the event polling database configuration to use the primary connection.
-
Ensure that Oracle BI Scheduler is not configured to use any standby sources.
Even though there are two separate physical data sources for the standby database configuration, you create only one database object in the Physical layer. Figure 5-4 shows the database object and connection pools for the standby database configuration in the Physical layer.
Figure 5-4 Standby Database Configuration in the Physical Layer
Description of "Figure 5-4 Standby Database Configuration in the Physical Layer"
Creating the Database Object for the Standby Database Configuration
Use the Administration Tool to create a database object in the repository for the standby database configuration. When you create the database object, make sure that the persist connection pool is not assigned, to prevent the Oracle BI Server from creating temporary tables in the standby database.
To create a database object for the standby database configuration:
-
In the Administration Tool, right-click the Physical layer and select New Database.
-
Provide a name for the database, and then select the type of database in the Database list.
-
Ensure that the Persist connection pool is "not assigned."
Figure 5-5 shows the Database dialog for a standby database configuration.
Figure 5-5 Database Dialog for Standby Database Configuration
Description of "Figure 5-5 Database Dialog for Standby Database Configuration"
Creating Connection Pools for the Standby Database Configuration
After you have created a database object in the repository for the standby database configuration, use the Administration Tool to create two connection pools: one that points to the standby database, and another that points to the primary database.
Because the standby connection pool is used for the majority of connections, make sure that the standby connection pool is listed first.
Note:
Connection pools are used in the order listed, until the maximum number of connections is achieved. Ensure that the maximum number of connections is set in accordance with the standby database tuning.
See "Creating or Changing Connection Pools" for more information about setting the maximum number of connections.
To create a standby connection pool for the standby database configuration:
-
In the Administration Tool, in the Physical layer, right-click the database object for the standby database configuration and select New Object, then select Connection Pool.
-
Provide a name for the connection pool, and ensure that the call interface is appropriate for the standby database type.
-
Provide the Data source name for the standby database.
-
Enter a user name and password for the standby database.
-
Click OK.
Figure 5-6 shows the Connection Pool dialog for the standby connection pool.
Figure 5-6 Connection Pool Dialog for Standby Connection Pool
Description of "Figure 5-6 Connection Pool Dialog for Standby Connection Pool"
To create the primary connection pool for the standby database configuration:
-
In the Administration Tool, in the Physical layer, right-click the database object for the standby database configuration and select New Object, then select Connection Pool.
-
Provide a name for the connection pool, and ensure that the call interface is appropriate for the primary database type.
-
Provide the Data source name for the primary database.
-
Enter a user name and password for the primary database.
-
Click OK.
Figure 5-7 shows the Connection Pool dialog for the primary connection pool.
Figure 5-7 Connection Pool Dialog for Primary Connection Pool
Description of "Figure 5-7 Connection Pool Dialog for Primary Connection Pool"
Updating Write-Back Scripts in a Standby Database Configuration
If you use scripts that write to the database, such as scripts for aggregate persistence, you must update the scripts to explicitly refer to the primary connection pool. Information written through the primary connection will be automatically transferred to the standby database (through the regularly scheduled replication between the primary and secondary databases), and will become available through the standby connection pool.
The following example shows a writeback script for aggregate persistence that explicitly specifies the primary connection pool:
create aggregates sc_rev_qty_yr_cat for "DimSnowflakeSales"."SalesFacts"
("Revenue", "QtySold") at levels ("DimSnowflakeSales"."Time"."Year",
"DimSnowflakeSales"."Product"."Category") using connection pool
"StandbyDemo"."Primary Connection" in "StandbyDemo"."My_Schema"
Setting Up Usage Tracking in a Standby Database Configuration
The Oracle BI Server supports the collection of usage tracking data. When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query and writes statistics to a usage tracking log file or inserts them directly to a database table.
If you want to enable usage tracking on a standby database configuration using direct insertion, you must create the table used to store the usage tracking data (typically S_NQ_ACCT) on the primary database. Then, import the table into the physical layer of the repository using the Administration Tool.
You must ensure that the database object for the usage tracking table is configured with both the standby connection pool and the primary connection pool. Then, ensure that the CONNECTION_POOL parameter for usage tracking points to the primary database. For example, in NQSConfig.ini:
CONNECTION_POOL = "StandbyDatabaseConfiguration"."Primary Connection";
See "Managing Usage Tracking" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for full information about usage tracking, including how to create tables for usage tracking data and how to set parameters for usage tracking.
Setting Up Event Polling in a Standby Database Configuration
You can use an Oracle BI Server event polling table (event table) as a way to notify the Oracle BI Server that one or more physical tables have been updated. The event table is a physical table that resides on a database accessible to the Oracle BI Server. It is normally exposed only in the Physical layer of the Administration Tool, where it is identified in the Physical Table dialog as an Oracle BI Server event table.
The Oracle BI Server requires write access to the event polling table. Because of this, if you are using event polling in a standby database configuration, you must ensure that the database object for the event table only references the primary connection pool.
See "Cache Event Processing with an Event Polling Table" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for full information about event polling, including how to set up, activate, and populate event tables.
Setting Up Oracle BI Scheduler in a Standby Database Configuration
Oracle BI Scheduler is an extensible application and server that manages and schedules jobs, both scripted and unscripted. To use Oracle BI Scheduler in a standby database configuration, you must ensure that the database object for Oracle BI Scheduler only references the primary connection pool.
See "Configuration Tasks for Oracle BI Scheduler" in Oracle Fusion Middleware Scheduling Jobs Guide for Oracle Business Intelligence Enterprise Edition for full information about setting up and using Oracle BI Scheduler.