4 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 6, "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.
This chapter contains the following topics:
Creating New Oracle BI Repository Files
You can use the Create New Repository Wizard in the Administration Tool to create new Oracle BI repository files (.rpds). You do not need to create a new repository file if you already have an existing repository.
If you have an existing repository file, 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 repository file:
-
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.
-
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.
-
Select a location for the new repository. By default, new repositories are stored in the repository subdirectory, located at ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication_obisn\repository.
-
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 is located.
Note:
See also the following related topics:-
If the Oracle BI Server is running on a non-Windows platform, see Chapter 15, "Setting Up Data Sources on Linux and UNIX" for additional instructions.
-
See Chapter 5, "Working with ADF Business Component Data Sources" for information about setting up ADF Business Component 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:
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 15, "Setting Up Data Sources on Linux and UNIX."
Note:
For Teradata data sources, 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
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_HOME\network\admin
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 JavaHost process must be running to import from Oracle OLAP data sources, for both offline and online imports.
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.
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.
Avoiding Query Failures Due to Limited Connections with TimesTen Data Sources
In some cases, queries to TimesTen data sources might fail with errors similar to the following:
[10058][State: S1000] [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 16023] The ODBC function has returned an error. The database may not be available, or the network may be down. Statement execute failed
To avoid these failures, 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 RPD, N connection pools in the RPD, 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.
Setting Up Essbase Data Sources
The Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources. You must ensure that the Essbase client libraries are installed on the computer running the Oracle BI Server before you can set up a connection to or import metadata from Essbase data sources. You also need to ensure that the Essbase client libraries are installed on any computer where you want to run the Administration Tool or the nqcmd utility. See "System Requirements and Certification" for information about supported versions of the Essbase Client for use with Oracle Business Intelligence.
After you verify that the Essbase client libraries are installed on the appropriate computers, 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). See "Configuring Essbase Data Sources on Linux and UNIX" for information about setting the PATH variable on Linux.
You also need to ensure that an additional environment variable is set appropriately for each computer (either ESSBASEPATH or ARBORPATH, depending on your client version). For more information, see Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide (or the equivalent title for your client version).
Other system processes need the information contained in the environment variables set for the Essbase client libraries. The following sections provide additional information:
Updating Essbase Information in opmn.xml
To enable connectivity, on each computer running the Oracle BI Server, you need to verify that information in opmn.xml is correct for your Essbase client version and installed location.
To include the location of the Essbase client libraries in opmn.xml:
-
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, locate the line that identifies the variable
ESSBASEPATH. For example:<variable id="ESSBASEPATH" value="$ORACLE_HOME/products/Essbase/EssbaseServer" />
-
If necessary, update the value to point to the appropriate location for your installed client libraries. Change the variable name to
ARBORPATHif necessary, depending on your client version. -
Locate the line that identifies the variable
PATH. For example:<variable id="PATH" value="$ORACLE_HOME/bifoundation/server/bin$:$ORACLE_ HOME/bifoundation/web/bin$:$ORACLE_HOME/bifoundation/odbc/lib$:$ORACLE_ INSTANCE$:$ORACLE_HOME/lib$:$ESSBASEPATH/bin$:$PATH$:$/bin" append="true"/> -
Locate the portion of the entry that shows the location of the Essbase client libraries (shown in bold in the preceding example), and update the location if appropriate for your installation. Change the variable used in the path from
ESSBASEPATHtoARBORPATHif necessary, depending on your client version. -
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).
Adding Essbase Information to bi-init.cmd
On Windows systems, to enable connectivity, you need to update bi-init.cmd on each computer running the Administration Tool.
See "Configuring Essbase Data Sources on Linux and UNIX" for information about setting up the environment on Linux systems.
To add Essbase environment variables to bi-init.cmd:
-
Open the bi-init.cmd file for editing. You can find bi-init.cmd at:
ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup -
Add a line setting the
ESSBASEPATHorARBORPATHenvironment 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.
Setting Up Hyperion Financial Management Data Sources
Before you can import from or set up a connection to Hyperion Financial Management data sources, you must ensure that the Hyperion Financial Management win32 client is installed on the computer running the Oracle BI JavaHost process. If you are using Hyperion Financial Management version 9.3.1, you must also install the HFM Application Builder version 9.2.0.1 component on the JavaHost computer.
When you install the Hyperion Financial Management win32 client, make sure to perform the following steps in the Server/Cluster Registration tab of the client configuration utility:
-
Provide the IP address or host name of the Hyperion Financial Management server.
-
Click Enable DCOM.
In addition, you must edit the file opmn.xml on each computer running the Oracle BI JavaHost process to include environment variables 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.
Note:
You can only use Hyperion Financial Management data sources with Oracle BI EE when Oracle BI EE is running in a Windows-only deployment.For Hyperion Financial Management 9.3.1:
-
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 JavaHost process. For example:
<ias-component id="coreapplication_obijh1">
-
Within the environment tag, add new variables 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:/Hyperion"/> <variable id="ADM_HOME" value="$HYPERION_HOME/common/ADM/9.3.1"/> <variable id="HFM_HOME" value="$HYPERION_HOME/FinancialManagement/Client"/> <variable id="ADM_ESS_NATIVE_HOME" value="$ADM_HOME/Essbase/9.3.1"/>
-
Update the
PATHvariable to includeADM_ESS_NATIVE_HOME/binandHFM_HOME. For example:<variable id="PATH" value="$ADM_ESS_NATIVE_HOME/bin$:$HFM_HOME$:$ORACLE_ HOME/bifoundation/server/bin$:$ORACLE_HOME/bifoundation/web/bin$:$ORACLE_ HOME/bifoundation/odbc/lib$:$ORACLE_INSTANCE$:$ORACLE_HOME/lib" append="true"/> -
Save and close the file.
-
Restart OPMN.
-
Repeat these steps on each computer that runs the Oracle BI JavaHost process. If you are running multiple JavaHost 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_obijh1", ias-component id="coreapplication_obijh2", and so on).
You should run more than one JavaHost process to ensure that the JavaHost is not a single point of failure for HFM access. 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 more information about scaling out processes.
For Hyperion Financial Management 11.1.2:
-
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 JavaHost process. For example:
<ias-component id="coreapplication_obijh1">
-
Within the environment tag, add new variables 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/binand 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/bifoundation/odbc/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 directory ORACLE_HOME/bifoundation/javahost/lib/obisintegration/adm 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 are running multiple JavaHost 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_obijh1", ias-component id="coreapplication_obijh2", and so on).
You should run more than one JavaHost process to ensure that the JavaHost is not a single point of failure for HFM access. 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 more information about scaling out processes.
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.
Importing Metadata from Relational Data Sources
You can import metadata for supported relational data source types by selecting one of the following import options and the appropriate connection type:
-
Local Machine. Available in both offline and online modes. Use this option when you have all database connections set up on your computer. Note the following:
-
Most metadata imports are performed using an ODBC connection type.
-
Native database gateways for metadata import are supported for Oracle Database (using OCI), DB2 (using DB2 CLI Unicode), and XML connection types. See "Importing Metadata from XML Data Sources" for information about importing XML data using the Oracle BI Server XML gateway.
-
You can use the Find button in the Select Metadata Objects screen of the Import Metadata Wizard to find a specific table you want to import.
-
-
Through the Oracle BI Server. Available in online mode. Use this option when you want to use Oracle BI Server connections to import metadata. This option lets you use the Data Source Name (DSN) of the Oracle BI Server computer to import metadata, rather than duplicating the DSNs and connectivity information on your local computer. Available connection types are ODBC, OCI, DB2 CLI (Unicode), and XML.
You must create a DSN for the Oracle BI Server before you can import metadata through the Oracle BI Server. In addition, the Oracle BI Server must be running. For more information about the Oracle BI Server DSN, see "Integrating Other Clients with Oracle Business Intelligence" in Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition.
Note:
When it is running on a UNIX platform, the Oracle BI Server does not support importing metadata using an ODBC connection type.See "Importing Metadata from Multidimensional Data Sources" and Chapter 5, "Working with ADF Business Component 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 4-1 shows the Import Metadata Wizard.
Figure 4-1 Import Metadata Wizard: Relational Data Source
Description of "Figure 4-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, 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 5, "Working with ADF Business Component 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 in the Import Metadata Wizard are reserved for a future release. Check the Oracle Business Intelligence chapter in Oracle Fusion Middleware Release Notes for updates about the functionality in these two screens.
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 4-2 shows the Import Metadata Wizard.
Figure 4-2 Import Metadata Wizard: Multidimensional Data Source
Description of "Figure 4-2 Import Metadata Wizard: Multidimensional Data Source"
Note that the Map to Logical Model and Publish to Warehouse screens in the Import Metadata Wizard are reserved for a future release. Check the Oracle Business Intelligence chapter in Oracle Fusion Middleware Release Notes for updates about the functionality in these two screens.
-
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 4-1 describes the multidimensional connection types.
Table 4-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.
See "Working with Essbase Data Sources" for information about how data from Essbase data sources is modeled in the Physical layer.
Note: 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.
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 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.
The JavaHost process must be running to import from Oracle OLAP data sources, for both offline and online imports.
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 5, "Working with ADF Business Component 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.
Importing Metadata from XML Data Sources
This section describes the use of the Extensible Markup Language (XML) as a data source. 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 various XML access modes, including access through the Oracle BI Server XML Gateway, as well as 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 18.
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 4-3 shows the Import Metadata Wizard.
Figure 4-3 Import Metadata Wizard: XML Data Source
Description of "Figure 4-3 Import Metadata Wizard: XML Data Source"
Note that the Map to Logical Model and Publish to Warehouse screens in the Import Metadata Wizard are reserved for a future release. Check the Oracle Business Intelligence chapter in Oracle Fusion Middleware Release Notes for updates about the functionality in these two screens.
-
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 18.
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 4-1 and Example 4-2 show sample XML documents and the corresponding columns that are generated by the Oracle BI Server XML Gateway.
Example 4-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 4-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 4-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 4-4 and Example 4-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 4-4 and Example 4-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 4-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 4-4 shows the database object and connection pools for the standby database configuration in the Physical layer.
Figure 4-4 Standby Database Configuration in the Physical Layer
Description of "Figure 4-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 4-5 shows the Database dialog for a standby database configuration.
Figure 4-5 Database Dialog for Standby Database Configuration
Description of "Figure 4-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 4-6 shows the Connection Pool dialog for the standby connection pool.
Figure 4-6 Connection Pool Dialog for Standby Connection Pool
Description of "Figure 4-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 4-7 shows the Connection Pool dialog for the primary connection pool.
Figure 4-7 Connection Pool Dialog for Primary Connection Pool
Description of "Figure 4-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.