| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
Previous |
Next |
This chapter provides information about preparing to install and deploy Oracle Business Intelligence Applications. You should review this information before you begin the installation and deployment process. For example, as a minimum you should read the general guidelines for setting up the Oracle Business Analytics Warehouse, and read the appropriate database-specific guidelines for the source OLTP databases that you are using.
|
Note: To install the server components, the computers need to meet the conditions specified in System Requirements and Supported Platforms for Oracle Business Intelligence Applications. |
This chapter contains the following topics:
Section 3.1, "Oracle Business Analytics Warehouse Deployment Configuration"
Section 3.3, "General Guidelines for Setting Up Oracle Business Analytics Warehouse"
Section 3.4, "IBM DB2 UDB-Specific Database Guidelines for Oracle Business Analytics Warehouse"
Section 3.6, "Oracle-Specific Database Guidelines for Oracle Business Analytics Warehouse"
Section 3.8, "SQL Server-Specific Database Guidelines for Oracle Business Analytics Warehouse"
Section 3.9, "Teradata-Specific Database Guidelines for Oracle Business Analytics Warehouse"
The figure below shows the recommended Oracle Business Analytics Warehouse deployment configuration.
In the figure above:
Component 1 hosts the ETL servers (that is, the Informatica Server, the Informatica Repository Server, and the DAC server).
|
Note: You can install the Informatica Server on other machines as well to increase performance. The other ETL servers can also be hosted on other machines. |
Component 2 hosts all the ETL clients (that is, the Informatica client tools, and the DAC client).
Components 3 (OLTP database) and 4 (Data Warehouse (OLAP) database) are database instances that can be hosted on one or more machines. The hardware requirements are entirely dependent on your usage and performance requirements. It is highly recommended that each of these components be defined in their own database instance to allow for instantiating parameters that are optimized for each component's usage.
|
Note: For more information about hardware and software requirements, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications. |
Table 3-1 provides the operating system, driver, and connectivity software requirements for the Oracle Business Analytics Warehouse components.
|
Note: For information about the supported versions of the components listed in Table 3-1, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications. |
Table 3-1 OS, Driver and Connectivity Requirements for Oracle Business Analytics Warehouse
| Component | Operating System | Software | Connectivity and Driver |
|---|---|---|---|
|
1 ETL Servers |
|
|
|
|
2 ETL Clients |
Windows |
|
|
|
3 (A) Oracle Business Analytics Warehouse |
|
Database software with Oracle Business Analytics Warehouse |
Not applicable |
|
3 (B) ETL Repositories |
|
Database software with ETL repositories |
Not applicable |
The Oracle Business Analytics Warehouse is a database that contains dimensional schemas. Although it is technically possible to put the Oracle Business Analytics Warehouse in the same database as the transactional database, it is not recommended for performance reasons. The transactional database is structured as an online transaction processing (OLTP) database, whereas the Oracle Business Analytics Warehouse is structured as an online analytical processing (OLAP) database, each optimized for its own purpose. The reasons for not combining the two databases are:
The analytical queries interfere with normal use of the transactional database, which is entering and managing individual transactions.
The data in a transactional database is normalized for update efficiency. Transactional queries join several normalized tables and will be slow (as opposed to pre-joined, de-normalized analytical tables).
Historical data cannot be purged from a transactional database, even if not required for current transaction processing, because you need it for analysis. (By contrast, the analytical database is the warehouse for historical as well as current data.) This causes the transactional database to further slow down.
Transactional databases are tuned for one specific application, and it is not productive to use these separate transactional databases for analytical queries that usually span more than one functional application.
The analytical database can be specifically tuned for the analytical queries and Extract-Transform-Load (ETL) processing. These are quite different from transactional database requirements.
On the transactional database, you should place the S_ETL tables in a separate tablespace. These ETL tables are used by the Oracle Business Analytics Warehouse and should not be part of the routine backup processes.
A complete listing of these tables is available in Oracle Business Analytics Warehouse Data Model Reference.
To maximize ETL performance on Siebel OLTP databases, create three indices on the Siebel OLTP database, using the following SQL commands:
CREATE INDEX S_AUDIT_ITEM_M100 ON S_AUDIT_ITEM (FIELD_NAME ASC, BUSCOMP_NAME DESC) PCTFREE 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
CREATE INDEX S_AUDIT_ITEM_M101 ON S_AUDIT_ITEM (RECORD_ID ASC, FIELD_NAME DESC) PCTFREE 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
CREATE INDEX S_OPTY_M102 ON S_OPTY (ROW_ID ASC, PR_POSTN_ID DESC) PCTFREE 10 ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS;
The Informatica repository stores all of the Informatica object definitions for the ETL mappings that populate the Oracle Business Analytics Warehouse. It is a series of repository tables that are stored in a database, which can be transactional, analytical, or a separate database.
The Oracle Business Analytics Warehouse works with relational database management systems. In addition to the general requirements, there are additional database management systems (DBMS)-specific requirements depending on the DBMS you are using.
The following general guidelines will help you set up the data warehouse physical database for performance and growth:
At a minimum, separate the data and index tablespaces. Create more tablespaces to separate heavily used tables and their indices.
Use the maximum block and page size available for tablespaces ((for example, 32K), because it provides good overall performance and also does not impose low limits to the maximum size to which the tablespace can grow, as compared to 4K, 8K,16K sizes.
If you are using multiple disk storage systems, stripe the tablespace containers and files across as many disks as possible.
Raw devices for tablespaces provide better performance as compared to cooked file systems.
RAID-5 is known to give a good balance of performance and availability.
Size the buffer pools based on content and size (number of tables and their sizes) of tablespaces.
Allocate about 75 percent of the total available server memory to the database, assuming no other application is running on the same server.
During the Oracle Business Analytics Warehouse configuration process, when you create the data warehouse tables using the procedure Section 4.12.2, "How to Create Data Warehouse Tables", you can create tables in one tablespace and indices in another tablespace. However, for performance reasons, it is recommended that you create tablespaces as described in Table 3-2.
Table 3-2 Recommended Tablespace Configuration
| Tablespace Name | List of Tables |
|---|---|
|
DIM_STG |
W_*DS |
|
FACT_STG |
W_*FS |
|
DIM |
W_*D and W_*MD |
|
FACT |
W_*F |
|
AGG |
W_*A |
|
OTHER |
Remaining W* tables |
|
DIM_INDX |
Indices of W_*D tables (for example, other tables would include W*G and W*GS tables) |
|
FACT_INDX |
Indices of W_*F tables |
|
OTHER_INDX |
Remaining Indices of W* tables |
|
Note: To avoid fatal deadlocks during the ETL, make sure that you select the 'Session Level Retry on Deadlock' option in Informatica. |
Table 3-3 provides guidelines for parameter settings for DB2 relational database management system (RDBMS) usage. Use these guidelines as a starting point. You will need to make changes based on your specific database sizes, data shape, server size (CPU and memory), and type of storage. The database administrator should make changes to the settings based on performance monitoring and tuning considerations.
Table 3-3 Recommended DB2 Parameter Settings
| Parameter | DB2 UDB V7 | DB2 UDB V8 | Notes |
|---|---|---|---|
|
SHEAPTHRES |
400000 |
400000 |
|
|
ASLHEAPSZ |
15 |
15 |
|
|
RQRIOBLK |
65535 |
65535 |
|
|
QUERY_HEAP_SZ |
16384 |
16384 |
|
|
JAVA_HEAP_SZ |
2048 |
2048 |
|
|
MAXAGENTS |
400 |
400 |
|
|
NUM_INITAGENTS |
10 |
10 |
|
|
NUM_POOLAGENTS |
200 |
200 |
|
|
INTRA_PARALLEL |
YES |
YES |
|
|
FCM_NUM_BUFFERS |
12288 |
12288 |
|
|
SHEAPTHRES_SHR |
N/A |
=SHEAPTHRES |
|
|
DBHEAP |
16384 |
16384 |
|
|
CATALOGCACHE_SZ |
5558 |
5558 |
|
|
LOGBUFSZ |
2048 |
2048 |
|
|
UTIL_HEAP_SZ |
10000 |
10000 |
|
|
NUM_ESTORE_SEGS |
16 |
NIL |
Restore is not needed in DB2 V8 64-bit because the V7 limit of 1.75 GB addressable memory has been lifted. |
|
ESTORE_SEG_SZ |
65536 |
NIL |
|
|
LOCKLIST |
25000 |
25000 |
|
|
APP_CTL_HEAP_SZ |
5000 |
5000 |
|
|
SORTHEAP |
4000 |
4000 |
|
|
STMTHEAP |
40960 |
40960 |
|
|
APPLHEAPSZ |
2560 |
2560 |
|
|
PCKCACHESZ |
2560 |
2560 |
|
|
STAT_HEAP_SZ |
20000 |
20000 |
|
|
DLCHKTIME |
10000 |
10000 |
|
|
MAXLOCKS |
50 |
50 |
|
|
LOCKTIMEOUT |
1200 |
1200 |
|
|
MAXAPPLS |
500 |
500 |
|
|
AVG_APPLS |
10 |
10 |
|
|
MAXFILOP |
500 |
500 |
|
|
GROUPHEAP_RATIO |
N/A |
70 |
New in V8 |
|
APPGROUP_MEM_SZ |
N/A |
30000 |
New in V8 |
|
DATABASE_MEMORY |
N/A |
AUTOMATIC |
New in V8 |
|
Note: To avoid fatal deadlocks during the ETL, make sure that you select the 'Session Level Retry on Deadlock' option in Informatica. |
The following requirements apply to IBM DB2 RDBMS usage for zOS and OS/390:
The Analytics applications communicate with IBM DB2 UDB for z/OS and OS/390 (running on zSeries servers) through IBM DB2 Connect middleware.
The following editions of DB2 Connect are supported:
DB2 Connect Enterprise Edition (EE). This edition is installed on a mid tier server such as an Informatica Server/Client, DAC, and Oracle Business Intelligence.
DB2 Connect Unlimited Edition (UE). This edition provides the functionality of DB2 Connect Enterprise Edition but is priced differently.
The ODBC driver for all connections must use the IBM DB2 ODBC Driver.
Make the appropriate connections using the DB2 Client Configuration Assistant.
Use the variable settings shown in Table 3-4.
Table 3-4 Variable Settings for IBM DB2 UDB zOS and OS/390 Databases
| Parameter | Recommended Setting | Notes |
|---|---|---|
|
IDTHTOIN |
1800 |
|
|
CDSSRDEF |
Any |
|
|
STARJOIN |
1 |
This setting indicates that star join is enabled. The one table with the largest cardinality is the fact table. However, if there is more than one table with this cardinality, star join is not enabled. |
Note: Oracle Business Intelligence Applications Version 7.9.4 does not support Oracle 11g source systems.
To configure Business Analytics Data warehouses on Oracle databases more easily, refer to the following init.ora parameter template files, located in <DRIVE>:\<BI Apps install directory>\dwrep\Documentation\. For example, C:\OracleBI\dwrep\Documentation\.
init9iR2.ora - init.ora template for Oracle RDBMS 9iR2
init10gR2.ora - init.ora template for Oracle RDBMS 10gR2
The init.ora parameter template files provide parameter guidelines based on the rule-based optimizer for Oracle 8i and the cost-based optimizer for Oracle 9i and Oracle 10g. Use these guidelines as a starting point. You will need to make changes based on your specific database sizes, data shape, server size (CPU and memory), and type of storage. The database administrator should make changes to the settings based on performance monitoring and tuning.
Copy the template file corresponding to your database version into your $ORACLE_HOME/dbs directory, review the recommendations in the template file, and make the changes based on your specific database sizes, data shape, server size (CPU and memory), and type of storage. The database administrator should make changes to the settings based on performance monitoring and tuning considerations.
This section contains additional suggestions for optimizing performance.
Oracle Business Intelligence Applications under Oracle support only binary sorting. If you are running an Oracle client, do one of the following:
Set the NLS_SORT parameter to BINARY.
Choose a NLS_LANG setting that includes binary.
These settings are required for adequate performance from the dedicated Web client.
Make sure that cost-based optimization is enabled in the Oracle development, test, and production databases and that statistics are kept up to date. Otherwise, the rule-based optimizer may be used.
Create foreign keys in the Oracle database, but configure Oracle to not enforce the foreign key relationship. The existence of foreign keys will allow Oracle to better optimize certain queries. By turning off enforcement, the database load should not be negatively affected.
Analyze application for occurrences of highly skewed data that is indexed. Create histogram statistics for these indices to enable the optimizer to better perform queries.
To increase data throughput between Oracle BI Server and Oracle, change SDU and TDU settings in listener.ora. The default is 2 KB and can be increased to 8 KB.
On the server side, edit the listener.ora file. Under the particular SID_LIST entry, modify SID_DESC as follows:
SID_LIST_LISTENER =
SID_LIST =
SID_DESC = (SDU=16384)(TDU=16384)
ORACLE_HOME = /.....)
SID_NAME = SOLAP)
)
)
Set the tablespace to at least the same as the transactional database size. Make sure the temporary tablespace has adequate space.
Set the number of log file groups to 4.
Set the size of each log file to 10 MB.
The sga_max_size to 700 MB.
On the client side, edit the tnsnames.ora file. Modify the TNS alias by adding SDU= and TDU= as follows:
myhost_orcl.world= DESCRIPTION=(SDU=16384)(TDU=16384) ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521)) CONNECT_DATA=(SID=ORCL))
This section provides guidelines for SQL Server database usage.
|
Note: The SQL Server database must be created with a collation sequence that supports binary sort order or case-sensitive dictionary sort order. Case-insensitive dictionary sort order is not supported. For example, for binary sort order with the U.S English character set, use the collation "Latin1_General_BIN". If you use the default collation setting of "SQL_Latin1_General_CP1_CI_AS", the database is set to case-insensitive, which is not supported, and causes index creation failures. |
This section includes the following topics:
Oracle Business Intelligence Applications requires that SQL Server databases be created with the ANSI NULL option selected.
To set the ANSI NULL option
In a SQL Server 2000 environment, when loading Analytics tables with international data, or loading more than one language, you need to modify the DB Library Options setting.
To modify the DB Library Options setting
From the Microsoft SQL Server program menu, select Client Network Utility.
Select the DB Library Options tab.
Clear the option Automatic ANSI to OEM.
|
Note: SQL Server 2000 automatically tunes many of the server configuration options; therefore, an administrator is required to do little, if any, tuning. Although these configuration options can be modified, the general recommendation is that these options be left at their default values, allowing SQL Server to automatically tune itself based on run-time conditions. |
If necessary, SQL Server components can be configured to optimize performance, as shown in Table 3-5.
Table 3-5 Recommended Variable Settings for SQL Server Databases
| Parameter | Recommended Setting | Notes |
|---|---|---|
|
Affinity mask |
0 |
|
|
Allow updates |
0 |
|
|
Awe enabled |
0 |
|
|
C2 audit mode |
0 |
|
|
Cost threshold for parallelism |
5 |
|
|
Cursor threshold |
–1 |
|
|
Default full-text language |
1033 |
|
|
Default language |
0 |
|
|
Fill factor |
95% |
For insert-intensive transactions, set Fill Factor between 90 and 95%. For better query performance, set Fill factor to 95 or even 100%. |
|
Index create memory |
1024 KB |
Default is 0. |
|
Lightweight pooling |
0 |
|
|
Locks |
0 |
|
|
Max degree of parallelism |
0 |
Default is 0. This turns off parallelism. Max degree of parallelism should be left at 0, which means use parallel plan generation. It should be set to 1 (use only 1 process) if you run multi threaded components (for example, several EIM threads). |
|
Max server memory |
2000 MB |
Default is 2147483647. |
|
Max text repl size |
65536 B |
|
|
Max worker threads |
100 |
Default is 255. |
|
Media retention |
0 |
|
|
Min memory per query |
1024 KB |
|
|
Min server memory |
500 MB |
Default is 0. |
|
Nested triggers |
1 |
|
|
Network packet size |
8192 B |
Default is 4096. |
|
Open objects |
0 |
|
|
Priority boost |
0 |
|
|
Query governor cost limit |
0 |
Modify to 60 only if CPU is high. |
|
Query wait |
–1 sec |
|
|
Recovery interval |
0 min |
|
|
Remote access |
1 |
|
|
Remote login timeout |
20 sec |
|
|
Remote proc trans |
0 |
|
|
Remote query timeout |
600 sec |
|
|
Scan for startup procs |
0 |
|
|
Set working set size |
0 |
|
|
Two-digit year cutoff |
2049 |
|
|
User connections |
0 |
|
|
User options |
0 |
SQL Server memory: Make sure adequate memory is available.
Transaction logs and TempDB: Reside on a separate disk from those used by database data.
Full load: Full Recovery model for the database.
Incremental (Refresh) load: Change from Full to Bulk-Logged Recovery model.
This sections contains the recommended best practices and guidelines for maximizing performance in Teradata deployments, and contains the following topics:
The following requirements apply to Teradata database usage:
Install the FastLoad and TPump Teradata utilities on the machine on which the Informatica Server is installed.
When installing Informatica PowerCenter, make sure there are no spaces in the Informatica Server directory name or the directory path. The default directory contains spaces that you must remove manually.
When you install Oracle BI Applications in a Teradata environment, you must load the pre-built Informatica repository file \dwrep\Oracle_BI_DW_Teradata.rep. For more information, see Section 4.14.3, "How to Load the Pre-Built Repository Into Informatica".
In the ODBC configuration for Teradata, set the session Mode to ANSI and DateFormat to AAA, before you create the schema. Tables must be created as case specific. If you do the ODBC configuration after the schema is created, tables might not be created as case specific.
To avoid overflows if the arithmetic calculation involves any decimal data type more than 18,3 (precision, scale), add the following static Source System Parameters:
"as decimal(18,3))" for $$Hint_Tera_Post_Cast
"Cast (" for $$Hint_Tera_Pre_Cast $$Hint_Tera_Post_Cast $$Hint_Tera_Pre_Cast)
For more information about setting Source System Parameters in the DAC, refer to Section 4.18.3, "How to Set DAC Source System Parameters".
|
Note: To avoid fatal deadlocks during the ETL, make sure that you select the 'Session Level Retry on Deadlock' option in Informatica. |
This sections contains the recommended best practises for maximizing performance in Teradata deployments, and contains the following topics:
Section 3.9.2.7, "Loader Configurations"
|
Note: The following best practices should be treated as customizations. You should follow standard customization methodology, such as copying the mapping to a Custom directory. You should never make these changes directly in the OTB objects. |
Teradata redistributes the OUTER table according to the join key, unless the INNER table is tiny. If the INNER table is tiny, the INNER table could be simply copied to all AMPs and the OUTER table would not be redistributed.
Many fact SIL mappings need to get the dimensional ROW_WID from ROW_ID/INTEGRATION_ID. For example, W_PER_RANK_FS.ACCNT_ID needs to be converted to ACCNT_WID before loading into W_PER_RANK_F table. Since ACCT_ID is nullable, the join between W_PER_RANK_FS and W_ORG_D is defined as a LEFT OUTER JOIN.
However, the percentage of NULLs in ACCT_ID column can be as high as 50% or more depending on the data set. When redistributing the W_PER_RANK_FS according to ACCT_ID, all rows with ACCT_ID = NULL are put onto a single AMP.
Although a Teradata database usually has hundreds of gigabytes of spool space, the spool space is allocated across hundreds of AMPs. The spool space for each AMP is limited (for example, to two gigabytes).
When a large percentage of W_PER_RANK_FS is distributed to a single AMP, this can result in insufficient spool space being available. This happens when too much data is spooled onto one AMP, not because the spool space is too small.
To work with Teradata's mechanism of parallel processing, and resolving the LEFT OUTER JOIN, SQL must be re-written.
As an example, refer to the following original SQL:
SELECT ... FROM W_PER_RANK_FS FS LEFT OUTER JOIN W_ORG_D ORG ON FS.ACCNT_ID = ORG.INTEGRATION_ID AND FS.DATASOURCE_NUM_ID = ORG.DATASOURCE_NUM_ID
The above SQL should be re-coded as follows:
SELECT ... FROM
W_PER_RANK_FS FS LEFT OUTER JOIN
(SELECT
FS.INTEGRATION_ID, FS.DATASOURCE_NUM_ID, ORG.ROW_WID, ORG.GEO_WID
FROM
W_PER_RANK_FS FS, W_ORG_D ORG
WHERE
FS.ACCNT_ID = ORG.INTEGRATION_ID AND FS.DATASOURCE_NUM_ID =
ORG.DATASOURCE_NUM_ID AND FS.ACCNT_ID IS NOT NULL)
ORG ON
FS.DATASOURCE_NUM_ID = ORG.DATASOURCE_NUM_I AND
FS.INTEGRATION_ID = ORG.INTEGRATION_ID
The same SQL re-coding method can be used for other Source Qualifiers that run into spool space problems.
In addition, there is another solution to the lack of spool space issue. This solution is to convert the NULLs to some evenly distributed but non-matched values, as shown in the following SQL example:
SELECT ... FROM W_PER_RANK_FS FS LEFT OUTER JOIN W_ORG_D ORG ON CASE WHEN FS.ACCNT_ID IS NOT NULL THEN FS.ACCNT_ID ELSE '#' || FS.INTEGRATION_ID END = ORG.INTEGRATION_ID AND FS.DATASOURCE_NUM_ID = ORG.DATASOURCE_NUM_ID
|
Note: The re-coded SQL is shown in bold type. |
This section describes database statistics.
Oracle Business Intelligence provides a list of column and index statistics collection statements. These are generally applicable for all situations, but should be evaluated for each site and situation. Additional statistics might be required, depending on your requirements. For example, some workflows need more statistics to avoid an out of spool space error on Teradata server.
Once the tables have been created in the staging and target databases, you must run the supplied statistics collection. Failure to do so can affect ETL performance, and possibly result in a spool space error (error number 2646)
The DAC does the re-collection of statistics as part of the ETL processes. However, the DAC issues the collect statistics statement at the table level only (for example, collect statistics on w_org_d), and only for existing statistics.
When there is a low number of distinct values, it is more efficient to use the GROUP BY phrase. Do not use the DISTINCT phrase, unless the number of distinct values is high.
If you do not use all of the OTB fields supplied, you can improve performance by stripping the extraneous fields from the mapping and the tables.
This section explains the loaders that are available in Teradata, and how they are used in Oracle BI Applications.
Teradata has 3 different types of Teradata loader processes, as follows:
Fastload
Mload
Tpump
Each loader process can be used in two different modes, as follows:
Staged Mode: The Informatica process does the following in this order:
Reads from the source data.
Creates a data file.
Invokes the loader process to load the table using the data file created.
Advantages: In the event of failures, you can recover using the Teradata recovery process.
Disadvantages: Staged mode is slower than Piped mode, and you need more disk space, as it can create large data files.
Piped Mode: The Informatica process reads from the source and simultaneously pipes that data to the loader to start loading the target table.
Advantages: Quicker than Staged mode, and you do not require large amounts of disk space because no data files are created.
Disadvantages: In the event of failures, you cannot recover using the Teradata recovery process (because tpump does row commits unlike fastload and mload).
You use the Fastload process on empty tables, such as loading staging tables and in initial loads where the tables are empty.
When the Fastload process starts loading, it locks the target table, which means that processes (for example, lookups) cannot access that table. One solution to this problem is to specify dummy SQL for the look up overrides at the session level.
|
Tip: If a session fails during a Fastlload process, use SQL Assistant to run a simple SQL command (for example, count(*)), to determine whether the table is locked by a Fastload process. |
If a table is locked (for example, (for W_ORG_DS), use the following script to release the lock:
LOGON DATABASEALIAS/USER,PASSWORD BEGIN LOADING USER.W_ORG_DS ERRORFILES USER.ET_W_ORG_DS,USER.UV_W_ORG_DS; END LOADING;
If you save the above text in a file called test.ctl, you would run this process by entering the following command at a command prompt:
C:\fastload\test.ctl
|
Tip: To create a load script for a table, edit the test.ctl script above to change the login information, and replace all occurrences of W_ORG_DS with the required target table name. |
After a load process script runs successfully, you should be able to run the command 'select count(*)' on the target table. If you are not able release the lock, you might need to drop and re-create the table to remove the lock. If you do so, you must re-create the statistics.
|
Tip: Fastload is typically used in piped mode to load staging tables and initial loads. In the event of errors, reload the entire data. |
The Mload process is slower than Fastload but quicker than Tpump. The Mload process can work on both empty tables as well as on tables with data. In the event of errors when running in piped mode, you cannot recover the data.
The Tpump process is slower than Mload but faster than ODBC. The Tpump process does row commits, which enables you to recover processed operations, even if you use piping mode. In other words, if you re-start the process, Tpump starts loading data from the last committed data.
Tpump can be used in the following modes:
Tpump_Insert: Use to do inserts.
Tpump_Update: Use to do updates (this mode requires you to define the primary key in the Informatica target table definition).
Tpump_Upsert: Use to do update otherwise insert (this mode requires you to define the primary key in the Informatica target table definition).
Tpump_Delete: Use to do deletes (this mode requires you to define the primary key in the Informatica target table definition).
Informatica uses the actual target table name to generate the error table and log tables to be used as part of its control file generation. If you have two instances of Tpump loading into same target table at the same time, you need to modify the session to use a different error table and log table name.
The Tpump load process in piped mode is useful for incremental loads, and where the table is not empty. In the event of errors, restart the process and it starts re-loading from the last committed data.
Refer to Informatica documentation for information about configuring a session to use Teradata loaders.
This section describes the different settings for Informatica servers and databases when deploying the Oracle Business Analytics Warehouse in Latin-1 General, Unicode, and non-English environments. When you configure Informatica, the Informatica repository, and the databases, you will need to refer to this section.
|
Note: If you are installing the Oracle Business Analytics Warehouse in a UNIX environment, you must use the Unicode character data mode. |
The Oracle Business Analytics Warehouse can be deployed in various code page environments to support global deployments. The following source and data warehouse configurations are supported:
Latin-1 General (7-bit ASCII) to Latin-1 General (7-bit ASCII). 7-bit ASCII is the character set used for English. See Section 3.10.2, "Source and Data Warehouse Code Pages for Latin-1 General (7-bit ASCII) to Latin-1 General (7-bit ASCII)".
Latin-1 General (8-bit ASCII) to Latin-1 General (8-bit ASCII). 8-bit ASCII is the character set used for Western European languages that use accented Roman characters. See Section 3.10.3, "Source and Data Warehouse Code Pages for Latin-1 General (8-bit ASCII) to Latin-1 General (8-bit ASCII)".
|
Note: You must run the Informatica Server in Unicode mode if your source data contains multibyte or ISO 8859-1 (8-bit ASCII) data. For instructions on setting the data movement mode to Unicode, see Section 4.5.3, "How to Set Up The Informatica Server". |
Unicode to Unicode. See Section 3.10.3, "Source and Data Warehouse Code Pages for Latin-1 General (8-bit ASCII) to Latin-1 General (8-bit ASCII)".
Code page (multi- or single-byte) to Unicode. See Section 3.10.5, "Source and Data Warehouse Code Pages for Code Page to Unicode".
Code page to code page (where the code pages are the same). See Section 3.10.6, "Source and Data Warehouse Code Pages for Code Page to Code Page".
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variable needs to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
A code page contains the encoding to specify characters in a set of one or more languages. An encoding is the assignment of a number to a character in the character set. You use code pages to identify data that might be in different languages. For example, if you are importing Japanese data into a mapping, you must select a Japanese code page for the source data.
When you set a code page, the application or program for which you set the code page refers to a specific set of data that describes the characters the application recognizes. This influences the way that application stores, receives, and sends character data.
Choose code pages based on the character data you are using in mappings. Character data can be represented by character modes based on the character size.
Character size is measured by the amount of storage space a character requires in the database. Database character can be one of the following: single byte, double byte or multibyte.
Compatibility between code pages is essential for accurate data movement when the Informatica Server runs in the Unicode data movement mode. When two code pages are compatible, the characters encoded in the two code pages are virtually identical.
For accurate data movement, the data warehouse code page must be a superset of the source code page. If the source code page is a superset of the data warehouse code page, the Informatica Server cannot process the characters because they cannot be encoded in the data warehouse code page. The data warehouse then results in incorrect or missing data.
This section provides the code pages for Latin-1 General (7-bit ASCII) to Latin-1 General (7-bit ASCII) configurations. 7-bit ASCII is the character set used for English.
Table 3-6 provides the code pages for the Informatica Server and Repository Server running on Windows with OS ENU.
Table 3-6 Code Pages for Informatica Server and Repository Server on Windows with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Data warehouse code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Repository code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Server code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variable needs to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
Table 3-7 provides the code pages for the Informatica Server and Repository Server running on UNIX with OS ENU.
Table 3-7 Code Pages for Informatica Server and Repository Server on UNIX with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Data warehouse code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Repository code page |
ISO 8859-1 Western European |
|
Informatica Server code page |
ISO 8859-1 Western European |
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variable needs to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
Table 3-8 provides the code pages for the Informatica Server running on UNIX, and the Repository Server on Windows, both with OS ENU.
Table 3-8 Code Pages for Informatica Server on UNIX, Repository Server on Windows with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Data warehouse code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Repository code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Server code page |
ISO 8859-1 Western European |
This section provides the code pages for Latin-1 General (8-bit ASCII) to Latin-1 General (8-bit ASCII) configurations. 8-bit ASCII is the character set used for Western European languages that use accented Roman characters.
|
Note: You must run the Informatica Server in UNICODE mode if your source data contains multibyte or ISO 8859-1 (8-bit ASCII) data. For instructions on setting the data movement mode to UNICODE, see Section 4.5.3, "How to Set Up The Informatica Server". |
Table 3-9 provides the code pages for the Informatica Server and Repository Server running on Windows with OS ENU.
Table 3-9 Code Pages for Informatica Server and Repository Server on Windows with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
ISO 8859-1 Western European |
|
Data warehouse code page |
ISO 8859-1 Western European |
|
Informatica Repository code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Server code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variable needs to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
Table 3-10 provides the code pages for the Informatica Server and Repository Server running on UNIX with OS ENU.
Table 3-10 Code Pages for Informatica Server and Repository Server on UNIX with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
ISO 8859-1 Western European |
|
Data warehouse code page |
ISO 8859-1 Western European |
|
Informatica Repository code page |
ISO 8859-1 Western European |
|
Informatica Server code page |
ISO 8859-1 Western European |
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variable needs to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
Table 3-11 provides the code pages for the Informatica Server running on UNIX, and the Repository Server on Windows, both with OS ENU.
Table 3-11 Code Pages for Informatica Server on UNIX, Repository Server on Windows with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
ISO 8859-1 Western European |
|
Data warehouse code page |
ISO 8859-1 Western European |
|
Informatica Repository code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Server code page |
ISO 8859-1 Western European |
For a list of the code pages that are supported for source and data warehouse databases, see System Requirements and Supported Platforms on Oracle's Siebel SupportWeb.
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variables need to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
SiebelUnicodeDB. For instructions on Windows, see Section 4.6.2, "How to Set Siebel UnicodeDB Environment Variables on Windows". For instructions on UNIX, see Section 5.9, "How to Set Environment Variables for the Informatica Server on UNIX".
Table 3-12 provides the code pages for the Informatica Server and Repository Server running on Windows with OS ENU.
Table 3-12 Code Pages for Informatica Server and Repository Server on Windows with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
UTF-8 encoding of Unicode |
|
Data warehouse code page |
UTF-8 encoding of Unicode |
|
Informatica Repository code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Server code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
Table 3-13 provides the code pages for the Informatica Server and Repository Server running on UNIX with OS ENU.
Table 3-13 Code Pages for Informatica Server and Repository Server on UNIX with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
UTF-8 encoding of Unicode |
|
Data warehouse code page |
UTF-8 encoding of Unicode |
|
Informatica Repository code page |
ISO 8859-1 Western European |
|
Informatica Server code page |
ISO 8859-1 Western European |
Table 3-14 provides the code pages for the Informatica Server running on UNIX, the Repository Server on Windows with OS ENU.
Table 3-14 Code Pages for Informatica Server on UNIX, Repository Server on Windows with OS ENU
| Component Code Page | Code Page |
|---|---|
|
Source code page |
UTF-8 encoding of Unicode |
|
Data warehouse code page |
UTF-8 encoding of Unicode |
|
Informatica Repository code page |
MS Windows Latin 1 (ANSI), superset of Latin 1 |
|
Informatica Server code page |
ISO 8859-1 Western European |
For a list of the code pages that are supported for source and data warehouse databases, see System Requirements and Supported Platforms on Oracle's Siebel SupportWeb.
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variables need to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
SiebelUnicodeDB. For instructions on Windows, see Section 4.6.2, "How to Set Siebel UnicodeDB Environment Variables on Windows". For instructions on UNIX, see Section 5.9, "How to Set Environment Variables for the Informatica Server on UNIX".
If the Informatica Server is running on Unix, then you also need to set the PMREPCODEPAGE environment variable appropriately. For example: PMREPCODEPAGE=MS932.
|
Note: Informatica Server can only run <LANG> OS based on the source code page. For example, if the source code page is Japanese then the Informatica Server should be running on JPN OS. |
The following information uses <LANG> = JPN as an example. If you are using a language other than Japanese, replace the code page with the appropriate language.
Table 3-15 provides the code pages for the Informatica Server and Repository Server running on Windows with OS <LANG>.
Table 3-15 Informatica Server and Repository Server on Windows with OS <LANG>
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Japanese, superset of shift JIS |
|
Data warehouse code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Repository code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Server code page |
MS Windows Japanese, superset of shift JIS |
Table 3-16 provides the code pages for the Informatica Server and Repository Server running on UNIX with OS <LANG>.
Table 3-16 Code Pages for Informatica Server and Repository Server on UNIX with OS <LANG>
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Japanese, superset of shift JIS |
|
Data warehouse code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Repository code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Server code page |
MS Windows Japanese, superset of shift JIS |
Table 3-17 provides the code pages for the Informatica Server running on UNIX and the Repository Server running on Windows, both with OS <LANG>.
Table 3-17 Code Pages for Informatica Server on UNIX, Repository Server on Windows, OS <LANG>
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Japanese, superset of shift JIS |
|
Data warehouse code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Repository code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Server code page |
MS Windows Japanese, superset of shift JIS |
For a list of the code pages that are supported for source and data warehouse databases, see System Requirements and Supported Platforms on Oracle's Siebel SupportWeb.
The following environment variables need to be set before you begin the installation process:
NLS_LANG (for Oracle). For instructions, see Section 3.10.7, "Setting the NLS_LANG Environment Variable for Oracle Databases".
DB2CODEPAGE (for DB2). For instructions, see Section 3.10.8, "Setting the DB2CODEPAGE Environment Variable for DB2 Databases".
The following environment variables need to be set during the configuration process:
Informatica Server Data Movement. For instructions, see Section 4.5.3, "How to Set Up The Informatica Server".
SiebelUnicodeDB. For instructions on Windows, see Section 4.6.2, "How to Set Siebel UnicodeDB Environment Variables on Windows". For instructions on UNIX, see Section 5.9, "How to Set Environment Variables for the Informatica Server on UNIX".
If the Informatica Server is running on UNIX, then you also need to set the PMREPCODEPAGE environment variable appropriately. For example: PMREPCODEPAGE=MS932.
The following information uses <LANG> = JPN as an example. If you are using a language other than Japanese, replace the code page with the appropriate language.
Table 3-18 provides the code pages for the Informatica Server and the Repository Server running on Windows, both with OS <LANG>.
Table 3-18 Code Pages for Informatica Server and Repository Server on Windows, OS <LANG>
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Japanese, superset of shift JIS |
|
Data warehouse code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Repository code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Server code page |
MS Windows Japanese, superset of shift JIS |
Table 3-19 provides the code pages for the Informatica Server and the Repository Server running on UNIX, both with OS <LANG>.
Table 3-19 Code Pages for Informatica Server and Repository Server on Windows, OS <LANG>
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Japanese, superset of shift JIS |
|
Data warehouse code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Repository code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Server code page |
MS Windows Japanese, superset of shift JIS |
Table 3-20 provides the code pages for the Informatica Server running on UNIX and the Repository Server running on Windows, both with OS <LANG>.
Table 3-20 Code Pages for Informatica Server on UNIX, Repository Server on Windows, OS <LANG>
| Component Code Page | Code Page |
|---|---|
|
Source code page |
MS Windows Japanese, superset of shift JIS |
|
Data warehouse code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Repository code page |
MS Windows Japanese, superset of shift JIS |
|
Informatica Server code page |
MS Windows Japanese, superset of shift JIS |
Follow this procedure to set the NLS_LANG environment variable for Oracle databases.
|
Note: You need to set the NLS_LANG environment variable on the machine that runs the Informatica server, and on the machine that runs the Oracle client. |
To set the NLS_LANG environment variable for Oracle databases
Determine the NLS_LANG value.
In the data warehouse database, run the command
SELECT * FROM V$NLS_PARAMETERS
Make a note of the NLS_LANG value, which is in the format [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8
For Windows:
Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.
In System variables section, click New.
In the Variable Name field, enter NLS_LANG.
In the Variable Value field, enter the NLS_LANG value that was returned in Step 0.
The format for the NLS_LANG value should be [NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET].
For example: American_America.UTF8.
|
Note: The NLS_LANG character set should reflect the setting of the operating system character set of the client. For example, if the database character set is AL32UTF8 and the client is running on a Windows operating system, then you should not set AL32UTF8 as the client character set in the NLS_LANG parameter because there are no UTF-8 WIN32 clients. Instead, the NLS_LANG setting should reflect the code page of the client. For example, on an English Windows client, the code page is 1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.Setting NLS_LANG correctly allows proper conversion from the client operating system character set to the database character set. When these settings are the same, Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so character set validation or conversion may not be performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary |
For UNIX, set the variable as shown below:
setenv NLS_LANG <NLS_LANG>
For example: setenv NLS_LANG American_America.UTF8.
If your data is 7-bit or 8-bit ASCII and the Informatica Server is running on UNIX, then set NLS_LANG <NLS_LANGUAGE>_<NLS_TERRITORY>.WE8ISO8859P1
|
Caution: Make sure you set the NLS_LANG variable correctly, as stated in this procedure, or your data will not display correctly. |
Reboot the machine after creating the variable.
Follow this procedure to set the DB2CODEPAGE environment variable for DB2 databases.
To set the DB2CODEPAGE environment variable for DB2 databases
Determine the DB2CODEPAGE value.
Connect to the Source database, using the following command:
SELECT CODEPAGE FROM SYSCAT.DATATYPES WHERE TYPENAME = 'VARCHAR'
Make a note of the result.
For example: 1208
For Windows:
Navigate to Control Panel > System and click the Advanced tab. Click Environment Variables.
In System variables section, click New.
In the Variable Name field, DB2CODEPAGE.
In the Variable Value field, enter the value that was returned in Step 0.
For UNIX, set the variable as shown below:
setenv DB2CODEPAGE <DB2CODEPAGE value>
For example: setenv 1208.
Reboot the machine after creating the variable.
This section contains the following topics:
Section 3.11.1, "Preconfigured Mapping for the SA System Subject Area"
Section 3.11.3, "Performance Improvements for Change Capture for Incremental ETL Run"
Section 3.11.4, "Running an ETL with a Non-English OLTP Data Source"
For Oracle's Siebel Applications customers, Table 3-21 describes the preconfigured mappings for the SA system subject area. Fields that are not available in Oracle's Siebel transactional database will default to values shown in the table.
Overriding Defaults. You can add user-specific values for these fields, by creating an extension table to the S_USER table. to store the user-specific defaults for these fields. Additionally, you can change any of the default values. The metadata for the following logical table can be modified to include any physical extension table.
SA User.(User)
For instructions, refer to the documentation about configuring tables and columns for Oracle's Siebel Business Applications.
Setting Provider Information. Typically, the cell phone and the fax numbers in the Oracle Business Analytics Warehouse do not contain a provider name. Therefore, the Pager will typically be a numeric value such as 555-483-3843. To append a provider to this address, use the following guidelines:
If the entire company has the same provider, then you can append the provider in the column mappings.
If users can have different providers, you need to create an extension table. For instructions, refer to the documentation about configuring tables and columns for Oracle's Siebel business applications.
Table 3-21 Preconfigured Mappings for the User Table in the SA System Subject Area
| Logical Column | Physical Table | Expression | Comments |
|---|---|---|---|
|
Cell Phone |
'' |
It might be mapped to S_CONTACT.CELL_PH_NUM if this field contains SMTP address. |
|
|
Cell Phone Priority |
'' |
Defaults to N |
|
|
Display Name |
S_CONTACT |
"Real Time OLTP"."".SIEBEL.S_CONTACT_User.FST_NAME || ' ' || "Real Time OLTP"."".SIEBEL.S_CONTACT_User.LAST_NAME |
First Name concatenated with Last Name |
|
|
S_CONTACT |
EMAIL_ADDR |
|
|
Email Priority |
'HNL' |
Defaults to N |
|
|
Email Type |
'html' |
Defaults to HTML |
|
|
Group Name |
S_RESP |
NAME |
|
|
Handheld |
'' |
Defaults to an empty string |
|
|
Handheld Priority |
'' |
Defaults to an empty string |
|
|
Language |
'en' |
Defaults to 'en' |
|
|
Locale |
'en' |
Defaults to 'en' |
|
|
Logon |
S_USER |
LOGIN |
|
|
Pager |
'' |
It could be mapped to S_CONTACT.PAGER_PH_NUM if this field contains SMTP address |
|
|
Pager Priority |
'' |
Defaults to N |
|
|
Time Zone |
S_TIMEZONE |
NAME |
Table 3-22 lists some of the initialization blocks common to all Oracle Business Intelligence applications and their purposes. Initialization blocks that are specific to each Oracle BI applications area are not listed here.
Table 3-22 Initialization Blocks and Their Purposes
| Initialization Block | Purpose |
|---|---|
|
Authorization |
Calculates user's responsibilities from the database. |
|
Authentication |
Authenticates against the database and verifies the user exists as a operational application user. |
|
External Metadata Strings |
Gets the translated value of metadata strings for the user's locale. This initialization block is critical to Intelligence Dashboards in international deployment. |
|
LOCALE |
Sets the user's locale specification in the Oracle BI Server. |
|
Login Properties |
Gets user's login properties, such as full name, from the database. This initialization block also sets the logging level of the users. By default, the log level is 0 for all users. If you wish to generate Oracle BI query logs for all users, this initialization block should be updated by changing the default value as well as the value in the initialization SQL. |
|
Default System Language ID |
Sets the variable OLTP_LANG_ID by querying the operational application database. |
|
Organizations for Org-based Security |
Queries the operational application database to get the organization membership for each user. It sets the variable ORGANIZATION. |
|
Primary Owner ID |
Sets the primary owner ID based on the user login ID. |
|
Primary Position ID |
Queries the operational application database to set the variable PRIMARY_POSTN_ID. |
|
Warehouse Refresh Date |
Sets several time-based variables such as CURRENT_YEAR. |
|
ETL Run Date |
Retrieves the ETL run date. |
|
ETL Default Currency |
Retrieves the default currency. |
To maximize performance for Siebel (CRM) and Oracle E-Business Suite OLTP Systems, you need to implement the indexes specified in this section.
To implement indexes, you use SQL files that are available in the \OracleBI\dwrep directory. Table 3-23 describes the SQL files that are appropriate for the specified applications.
Table 3-23 SQL files for Siebel transactional databases
| Application Name | SQL File Name |
|---|---|
|
Horizontal Application |
|
|
Industry Application |
The SQL files generate indexes on all S_.* tables that are being used by the out-of-the-box applications.
|
Note: If you move from a test to a production environment, you need to drop the indexes and re-create them in the production environment. |
Change capture SQL generates the following SQL:
Insert into S_ETL_I_IMG_XX (ROW_ID, LAST_UPD) AS SELECT ROW_ID, LAST_UPD, MODIFICATION_NUM From S_XXX WHERE LAST_UPD > 'LAST REFRESH_DATE – PRUNE DAYS' AND NOT EXISTS ( SELECT 'X' FROM S_ETL_R_IMAGE WHERE S_ETL_R_IMAGE.ROW_ID = S_XXX.ROW_ID AND S_ETL_R_IMG_XX.MODIFICATION_NUM = S_XXX.MODIFICATION_NUM AND S_ETL_R_IMG_XX.LAST_UPD = S_XXX.LAST_UPD )
Table 3-24 shows the indexes that, based on the SQL above, are created on the S_CONTACT table by the SQL generation scripts.
To run an ETL with a non-English OLTP Data Source, you must make a copy of an appropriate source system container, and configure the language, country, and continent parameters.
To run an ETL with a non-English OLTP Data Source:
In Data Warehouse Administration Console, choose File, then New Source System to display the New Source System Container dialog.
Select the Create as a Copy of Existing Container radio button.
From the Existing Containers drop down list, select the container that you want to copy, then click OK.
Display the Design view.
Ensure that you have selected the correct container from the containers drop down list.
Select the Source System Parameters tab.
Use the Edit tab below the list of Source System Parameters to change the value of the following parameters in the list:
$$DFLT_LANG (Default Language) – For example, for a Japanese data source, change this value to JPN.
(optional) $$DFLT_COUNTRY (Default Country)
(optional) $$DFLT_CONTINENT (Default Continent)
Tip: To find out the value to specify for the $$DFLT_LANG parameter, issue the query 'select VAL from S_SYS_PREF where SYS_PREF_CD=<ETL value>' against the OLTP database. For example, to find out the default ETL language, issue the following command:
select VAL from S_SYS_PREF where SYS_PREF_CD='ETL Default Language';
Create a new ETL plan for the new source system container, and edit its parameters, as follows:
Display the Execute tab.
Click the Execution Plans sub-tab.
Click New to create a new blank execution tab and use the sub-tabs below (for example, Subject Areas, Parameters, Ordered Tasks) to specify the execution plan details.
Click Save.
Click Run Now to run the new ETL plan (or use the Schedule tab to specify when you want to run the new execution plan).
