| Oracle® Business Intelligence Applications Data Warehouse Administration Console Guide Version 7.9.4 E10759-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Data Warehouse Administration Console Guide Version 7.9.4 E10759-01 |
|
Previous |
Next |
This chapter describes the functionality available in the Data Warehouse Administration Console (DAC). It contains the following topics:
Some of the DAC interface tabs have common elements, such as columns or subtabs. The common elements are described below.
Name
The Name column in a tab specifies the name of the database object.
Inactive
The Inactive column indicates whether a database object is inactive. Inactive objects do not participate in the ETL process.
Owner
The Owner column specifies the source system container in which the database object was created.
Edit
The Edit subtab enables you to edit an object that is selected in the top window.
Description
The Description subtab displays and enables you to edit a description of the object selected in the top window.
The Design view provides access to functionality related to creating and managing subject areas. The tabs in this view are listed in alphabetical order.
A configuration tag is an object that controls the inclusion of tasks in subject areas. When a task is tagged, it is not eligible to be included in the collection of tasks for any subject area, unless the tag is part of the subject area definition "Include Task" property.
A configuration tag can function in one of the following ways:
Remove tasks from all subject areas
When you assign a task to a configuration tag, the task will not be eligible to participate in any subject area.
Reassign autogenerated tasks to a specific subject area
An autogenerated task is a task that the DAC automatically assigns to a subject area when the subject area is assembled.
For autogenerated tasks that were removed from participating in a subject area, you can set up the configuration tag to reassign a task to participate in specific subject areas. You do this by associating the configuration tag with the desired subject area. This method only applies to tasks that are autogenerated tasks of a subject area.
Add non-autogenerated tasks to a subject area
You can set up a configuration tag to add non-autogenerated tasks to a subject area. The non-autogenerated tasks will participate in the subject area along with the subject area's autogenerated tasks.
Assign only configuration tag tasks to a subject area (excludes the subject area's autogenerated tasks)
You can also set up a configuration tag so that only tasks that were assigned to the configuration tag participate in a specific subject area. In this case, the subject area's autogenerated tasks do not participate.
For instructions on creating configuration tags, see "Working with Configuration Tags".
Include Tasks
If this check box is selected, the tasks that are assigned to a configuration tag will participate in the ETL process for the subject area to which this configuration tag is assigned.
For example, suppose Configuration Tag 1 is made up of Task 1 and Task 2, and Configuration Tag 1 is assigned to Subject Area 1. Task 1 and Task 2 will be executed when the execution plan for Subject Area 1 is executed, whether or not Task 1 and Task 2 relate to the tables that make up the subject area.
Use this subtab to view the subject areas that belong to a configuration tag or to add subject areas to a configuration tag.
Configuration Tag Tasks Only
This read-only field indicates whether configuration tag tasks are the only tasks associated with this subject area that will participate in the ETL process. If this check box is selected, only the tasks associated with the configuration tag will be chosen by the DAC when the subject area is assembled.
Use this subtab to add or remove tasks from the configuration tab selected in the top window.
For instructions, see "Working with Configuration Tags".
The Indices tab lists all the indexes associated with the selected source system container. It is recommended that you do not register any indexes for source tables. During the ETL process, when a table is going to be truncated, all the indexes as defined in the repository will be dropped before the data is loaded and will be created after the data is loaded automatically. While this improves the ETL performance, the preconfigured workflows have the bulk load option turned on. The bulk load will fail if there are indexes on the table. Therefore, it is important to keep the index definitions in sync with the database. For example, if you create an index on the database, and it is not registered in the repository, the index will not be dropped and the load will fail.
For Teradata databases, only secondary indexes should be registered in the DAC. You should not register primary indexes or the more complex indexes, such as single- and multi-table indexes, because they cannot be dropped and recreated. You can use SQL commands to drop and create such tasks in the DAC.
Table Name
The table for which an index is created.
Index Usage
Specifies the index usage: ETL or Query. An ETL index is typically used during the ETL process. A Query index is an index used only during the reporting process. It is recommended that you have a clear understanding of when and where the index will be used at the time of registering the index in the DAC.
# Unique Columns
For unique indexes, the number of columns that will be unique.
Is Unique
Indicates whether the index is unique.
Is Clustered
Indicates whether the index is clustered. There can be only one clustered index per table.
Is Bitmap
Indicates whether the index is of the bitmap type.
Allow Reverse Scan
Applicable only for DB2-UDB databases. The index will be created with the Allow Reverse Scan option.
Always Drop & Create
Indicates whether the index will be dropped and created regardless of whether the table is being loaded using a full load or incremental load.
Database Filter
The database filter appears in the Indices tab to the right of the toolbar in the top window. 
Click on the words "Database Type" to open the Index Filtering dialog. It enables you to filter the set of displayed indexes based on the database type. To display all indexes regardless of database type, select the option All.
The DAC enables you to drop and recreate indexes during the load process, which reduces the overall load time during a full load. The DAC drops and recreates indexes based on the index definitions stored in the DAC metadata.
More advanced index management needs to be handled outside of the DAC, such as creating single-table and multi-table join indexes on Teradata databases. In such cases you can use DAC SQL tasks placed appropriately in the task dependencies, or you can use a pre-session or post-session script in Informatica.
If you need to modify the preconfigured indexes with extra options or syntax, you must inactivate them in the DAC metadata so that the DAC server does not try to drop and recreate them. You can then manage these indexes in the same manner as the advanced index management described above.
The Columns subtab displays a list of columns the index is made of.
Position
The position of the column in the index.
Sort Order
Indicates whether the sort order is ascending or descending.
The Databases subtab lists the database types that apply to the selected index. If no database type is indicated, the index will not be created.
Database Type
The type of database.
Index Override
Specifies an index space that overrides the default index space for the default database connection, which is specified in the Edit subtab of the Physical Data Sources tab in the Setup View.
The Source System Folders tab lists the Informatica folders associated with the selected source system container. It enables you to view existing folders and to create new ones.
Logical Folder
The name of the logical Informatica folder. This name is used in the task definition (in the Tasks tab) so that task definitions do not have to be cloned.
Physical Folder
The name of the physical Informatica folder. The physical Informatica folder corresponds to the actual folder in the Informatica repository. This name is used in the Ordered Tasks subtab of the Execution Plans tab.
The Source Systems Parameters tab holds the source system parameters that apply to all tasks under a source system container. This tab enables you to view and edit existing parameters and to define new parameters.
For more information about managing parameters in the DAC, see:
Data Type
Parameter data type. For more information, see "Overview of Parameters".
Possible values are the following:
| Data Type Option | Description |
|---|---|
| Text | The value for the parameter is defined as text. |
| DB Specific Text | Enables you to add database specific hints in Informatica mappings.
When you select this option, in the Value field, you specify the logical connection where the parameter applies, and you specify a default value for the parameter. The DAC evaluates the parameter to this default value for all databases. If you enter text that is applicable to a particular database, you can specify a value for the database type, and the DAC will evaluate the parameter to this value at runtime as long as the logical connection matches the specified database. |
| Timestamp | The value for the parameter is a timestamp and can be static, runtime or SQL. |
| SQL | The value for the parameter is a SQL statement. |
Value
The parameter value.
The Subject Areas tab lists all the subject areas associated with the selected source system container. It enables you to view and edit existing subjects areas and to create new ones.
Configuration Tag Tasks Only
This column indicates whether configuration tag tasks are the only tasks associated with this subject area that will participate in the ETL process. If this check box is selected, only the tasks associated with the configuration tag will be chosen by the DAC when the subject area is assembled.
For more information, see "Working with Configuration Tags" and "Configuration Tags Tab".
Last Designed
Date and time the subject area was last assembled.
The Configuration Tags subtab lists the configuration tags that are associated with this subject area.
For more information, see "Working with Configuration Tags" and "Configuration Tags Tab".
Include Tasks
This read-only field indicates whether the configuration tag tasks will be executed.
Context Disabled
When this read-only check box is selected, the configuration tag is globally disabled.
The Tables subtab lists the tables that are associated with the selected subject area. It enables you to add tables to subject areas or to remove them.
The Tasks subtab lists the tasks associated with the selected subject area, and enables you to add tasks to a subject area, inactivate tasks, and remove tasks from a subject area.
When you inactivate a task, it remains inactive even if you reassemble the subject area. When you remove a task from a subject area, it will be added back to the subject area upon reassembly.
Task Name
Name of the task.
Parent Group
If the task belongs to a task group, this column displays the task group name.
Phase
Task phase of the ETL process.
Autogenerated
Indicates whether the task was automatically generated by the DAC's task generation process.
Is Group
Indicates whether the task is a task group.
The Task Source Tables (RO) subtab opens in Query mode and is read only. It enables you to query by task name, table name, table type or data source for the source tables for the tasks associated with the selected subject area.
The Task Target Tables (RO) subtab opens in Query mode and is read only. It enables you to query by task name, table name, table type or data source for the target tables for the tasks associated with the selected subject area.
The Tables tab lists all the tables associated with the selected source system container. It enables you to view and edit existing tables and to create new ones.
Table Type
Indicates the type of table.
Warehouse
Indicates whether the table is a warehouse table. If this option is not selected, the schema creation process will not include this table.
Image Suffix
Suffix for image tables. Applicable only to Siebel source tables. For more information about image tables, see the description of the Change Capture Scripts command in the section "Design View Right-Click Menu Commands".
Is MultiSet
Indicates whether the table is a MultiSet table. Applicable only to Teradata databases.
Has Unique Primary Index
Indicates whether the table has a Unique Primary Index. Applicable only to Teradata databases.
The Conditional for Tasks (RO) subtab displays a read-only list of tasks that are optional tasks for the selected table.
Build Image
Applicable for Siebel transactional sources only. Indicates the change capture for the primary/auxiliary source tables will be executed.
The Indices (RO) subtab displays a read-only list of indexes that belong to the selected table.
Index Usage
Specifies the index usage: ETL or Query. An ETL index is typically used during the ETL process. A Query index is an index used only during the reporting process. It is recommended that you have a clear understanding of when and where the index will be used at the time of registering the index.
# Unique Columns
For unique indexes, the number of columns that will be unique.
Is Unique
Indicates whether the index is unique.
Is Clustered
Indicates whether the index is clustered. There can be only one clustered index per table.
Is Bitmap
Indicates whether the index is of the bitmap type.
Allow Reverse Scan
Applicable only for DB2-UDB databases. The index will be created with the Allow Reverse Scan option.
Table Space Name
Name of the table space.
Applicable to Teradata databases only.
Lists tables that are related to the selected table. Related tables participate in the ETL process in addition to the tables that are associated with this table.
Table Type
Type of table.
The Source for Tasks (RO) subtab displays a read-only list of tasks that use the selected table as a source.
Build Image
Applicable for Siebel transactional sources only. Indicates the changecapture for the primary/auxiliary source tables will be executed
Type
Type of table.
The Target for Tasks (RO) subtab displays a read-only list of tasks that use the selected table as a target.
Build Image
Applicable for Siebel transactional sources only. Indicates the changecapture for the primary/auxiliary source tables will be executed.
Type
Type of table.
The Task Groups tab lists all the task groups associated with the selected source system container. A task can belong to only one group.
Restart All on Failure
Indicates the tasks in this task group will be restarted if one or more tasks fails during an ETL process.
Execute Serially
Indicates the tasks in this task group will be executed sequentially. This property overrides the execution order.
Truncate Always
Indicates the target tables are truncated regardless of whether a full or incremental load is occurring. Any indexes registered for the target table are dropped before the command is executed and are recreated after the command completes successfully. When indexes are dropped and created, the table is analyzed so that the index statistics are up-to-date.
Make sure if you select this option that all the tasks write to the same data source.
Truncate for Full Load
Indicates the target tables will be truncated only when a full load is occurring. Any indexes registered for the target table are dropped before the command is executed and are recreated after the command completes successfully. When indexes are dropped and created, the table is analyzed so that the index statistics are up-to-date.
The Child Tasks subtab lists the tasks that belong to the selected task group.
Execution Order
Order among the tasks in the task group in which this task will be executed. If two or more tasks have the same execution order and the Execute Serially flag is not checked, the DAC will run the tasks in parallel.
The Source Tables (RO) subtab lists the tables used for getting data by the selected task group.
Table
Name of source table.
Task
Task that extracts data from the table.
Type
Source table type.
If a table is marked as Primary or Auxiliary and the Build Image property of the task is selected, the change capture process is invoked. There are special tasks that force the base table data to be extracted when data in auxiliary tables changes. A table can be neither Primary nor Auxiliary but still be used for getting some attributes to populate a dimension or fact table. The changes in these kinds of source tables are not reflected in the dimension or fact table once the data is populated.
The Target Tables (RO) subtab is a read-only tab that lists the tables into which the task group loads data.
Table
Name of the target table.
Task
Task that loads data into the target table.
Type
Type of target table.
The Tasks tab lists all the tasks associated with the selected source system container.
Parent Group
If the task is a member of a group, this field lists the task group name.
Command for Incremental Load
A table can be loaded in Full Mode or Incremental Mode. Full Mode refers to data loaded for the first time or data that is truncated and then loaded. Incremental Mode refers to new or changed data being added to the existing data.
The DAC maintains a last refresh timestamp whenever a table is changed during the ETL process. (You can view this timestamp by selecting Setup, then selecting Physical Data Sources, and then selecting Refresh Dates.) If a table has a timestamp, the command appearing in this column is executed. If a table does not have a timestamp, the command for a full load is executed. If the execution type is Informatica, the workflow name is used as the command
Command for Full Load
If a table has no last refresh timestamp, this command is executed.
Folder Name
Only for execution type of Informatica. The folder in which the workflow resides. Note: The name cannot contain spaces.
Primary Source
Logical database connection for the primary source database.
Primary Target
Logical database connection for the primary target database.
Task Phase
Task phase of the ETL process. This information is primarily used for dependency generation. Certain phases, such as Change Capture and Query Index Creation, are not available for you to assign to a task. The DAC server uses the task phase to prioritize tasks and to generate a summary of the time taken for each of the phases.
Execution Type
Tasks are executed based on their execution type. The following types are supported:
Informatica
Task is invoked on an Informatica Server using pmcmd.
External Program
Task is an operable program on the operating system where the DAC server is running. This program can be a batch file, shell script, or any other program that can be run like a bulk loader.
SQL File
Task is a SQL script in .xml or .sql format.
Stored Procedures
Task is a stored procedure that has been defined on the databases.
In addition, there are several internal execution types that you will not be able to select when creating new tasks. These tasks are categorized as either internal change capture tasks or internal data warehouse tasks; all of these tasks are color-coded in pink in the Tasks tab.
IMG_BUILD
Used for internal change capture. If you are using multiple Siebel transactional sources, you cannot change the behavior of the change capture process. This task requires change capture tables to be created on the other sources also. When adding additional Siebel sources, go to Design > Tables, right-click and select Change Capture Tasks. This action generates change capture tasks. Use this same action to disable or delete change capture tasks.
IMG_SYNC
Used for internal change capture. If you are using multiple Siebel transactional sources, you can create this task for the additional tasks for doing similar change capture sync processes. You cannot change the behavior of the change capture sync process. This task requires change capture tables to be created on the other sources also. This task should be used with discretion for Siebel sources only.
QUERY_INDEX
Used for internal data warehouse. This task enables you to alter when the Query indexes are created. The DAC server drops all indexes before loading when the CreateQueyIndexesAtTheEnd setting is set to True. When this setting is set to False, all the indexes, regardless of the index type, get created as part of the task that does the loading.
UPDATE_ETL_PARAM
Used for internal data warehouse. This task is used only to update W_PARAM_G from the DAC server. This task populates the system properties to the W_PARAM_G table in the data warehouse by querying values defined in the DAC repository. Because only one data warehouse per DAC repository is supported, this execution type should not be chosen for any task.
Priority
Indicates the order in which the task is executed. If two or more tasks have the same priority, the order occurs randomly.
Pre-SQL for Full Load
The SQL script (derived from a SQL or XML file) that is executed before the specified task when the task is participating in a full load.
Pre-SQL for Incremental Load
The SQL script (derived from a SQL or XML file) that is executed before the specified task when the task is participating in an incremental load.
Post-SQL for Full Load
The SQL script (derived from a SQL or XML file) that is executed after the specified task when the specified task is participating in a full load.
Post-SQL for Incremental Load
The SQL script (derived from a SQL or XML file) that is executed after the specified task when the specified task is participating in an incremental load.
Build Image
Applicable for Siebel transactional sources only. Indicates the change capture for the primary/auxiliary source tables will be executed.
Analyze Tables
The DAC automatically analyzes tables when tasks truncate tables. By selecting this check box, however, you can force the DAC to analyze tables even when they are not truncated.
Continue on Error
When this check box is selected, if the command fails, the dependent tasks are not stopped. However, if any autogenerated tasks fail, the dependent tasks are stopped.
The Conditional Tables subtab lists the tables that, if included in an execution plan, cause the optional task selected in the top window to be executed.For example, the Order Item fact table is a conditional table associated with the optional task called UpdateRecencyCat in Person Dimension. The UpdateRecencyCat in Person Dimension task is executed only when the Order Item fact table is included in an execution plan.
The Configuration Tags subtab lists the configuration tags to which the selected task belongs. It also enables you to associate the selected task with a configuration tag.
Include Tasks
This read-only field indicates whether the configuration tag tasks will be executed.
Context Disabled
If this check box is selected, the configuration tag is globally disabled.
The Parameters subtab lists the parameters associated with the selected task. It enables you to configure task level parameters. This parameter takes precedence over source system parameters when the name is the same.
For more information about managing parameters in the DAC, see:
Defining a Text Type Parameter
Defining a Database Specific Text Type Parameter
Defining a Timestamp Type Parameter
Name
Name of the parameter.
Data Type
Parameter data type. For more information, see "Overview of Parameters".
Possible values are the following:
Text
The value for the parameter is defined as text.
DB Specific Text
Enables you to add database specific hints in Informatica mappings.
When you select this option, in the Value field, you specify the logical connection where the parameter applies, and you specify a default value for the parameter. The DAC evaluates the parameter to this default value for all databases. If you enter text that is applicable to a particular database, you can specify a value for the database type, and the DAC will evaluate the parameter to this value at runtime as long as the logical connection matches the specified database.
Timestamp
The value for the parameter is a timestamp and can be static, runtime or SQL.
SQL
The value for the parameter is a SQL statement.
Data Type
The parameter data type. Possible values are Text, Timestamp, and SQL.
Value
The parameter value.
The DAC server uses the ETL phase property to prioritize tasks. By changing the phase property of a task, you change the task's execution order.
Action
The action to be taken in relation to the phase dependency. Possible values are the following:
Wait
Indicates the selected task will wait to be executed until the tasks of a specified phase have been executed.
Block
Indicates the selected task will block all tasks of the specified phase from being executed until is has been executed.
Grain
Applicable only for blocks. Enables you to specify whether the action you choose affects all tasks of a specified phase or related tasks. Possible values are the following:
All
Indicates the action will affect all tasks.
Related
Indicates the action will affect only related tasks. You can view a task's related tasks by navigating to the Execution Plans tab, All Dependencies subtab and viewing the specified task's predecessor tasks.
Phase
The ETL phase that will apply to the Action and Grain properties.
The Source Tables subtab lists the tables from which the selected task extracts data.
Type
Table type. Possible values are the following:
Primary
Indicates the table is a primary source of data.
Auxiliary
Indicates the table is a secondary source of data.
Lookup
Indicates the table is a lookup table.
|
Note: If a table is marked as Primary or Auxiliary and the Build Image property of the task is selected, the change capture process is invoked. There are special tasks that force the base table data to be extracted when data in auxiliary tables change.A table can be neither Primary nor Auxiliary but still be used for getting some attributes to populate a dimension or fact table. The changes in these kinds of source tables are not reflected in the dimension or fact table once the data is populated. |
Data Source
Data source for the table. When a data source is not specified, the default is the task's primary source.
The Target Tables subtab lists the tables into which the selected task loads data.
Type
Table type.
Data Source
Data source for the target table. If no data source is specified, this value defaults to the task's primary target.
Truncate Always
Indicates the target tables will be truncated regardless of whether a full or incremental load is occurring. Any indexes registered for this table are dropped before the command is executed and are recreated after the command completes successfully. When indexes are dropped and created, the table is analyzed so that the index statistics are up-to-date.
Truncate for Full Load
Indicates the target tables will be truncated only when a full load is occurring. Any indexes registered for this table are dropped before the command is executed and are recreated after the command completes successfully. When indexes are dropped and created, the table is analyzed so that the index statistics are up-to-date. When the Truncate Always option is selected, this option is unnecessary.
The Setup View provides access to functionality related to setting up DAC system properties, Informatica servers, database connections, and email notification. The tabs in this view are listed in alphabetical order.
The DAC System Properties tab enables you to configure various properties that determine the behavior of the DAC server.
Analyze Frequency (in days)
For DAC metadata tables, the frequency (in days) the DAC client automatically updates the table and index statistics for the DAC repository. The value must be numerical.
Analyze Tables
The DAC automatically analyzes tables when tasks truncate tables. By selecting this check box, however, you can force the DAC to analyze tables even when they are not truncated.
Auto Restart ETL
Possible values are True and False.
When set to True: An ETL that is running when the DAC server abnormally terminates will continue running when the DAC server is restarted.
When set to False: An ETL that is running when the DAC server abnormally terminates will not automatically restart when the DAC server restarts. The ETL status will be updated to Failed. An administrator will have to manually restart the ETL.
CreateQueryIndexesAtTheEnd
Possible values are True and False.
During the ETL process, the DAC server automatically drops and creates indexes. When set to True, this property groups all indexes of the Query type and creates them after the ETL is complete.
The DropAndCreateIndexes property takes precedence over this property. Therefore, if the DropAndCreateIndexes property is set to False, you cannot set the property CreateQueryIndexesAtTheEnd to True to have indexes of the Query type created at the end of the ETL process.
Also, be aware that when this property is set to True, tables will be analyzed twice. If any indexes are marked as Query type indexes, and are used by ETL processes, it can adversely affect the performance of the ETL process.
DAC Alternate Server Hosts
Host name of the machine where the alternate DAC server resides. The alternate DAC server is used for failover purposes. The DAC client cannot talk to the alternate server unless the main DAC server is not running.
DAC Server Host
Host name of the machine where the DAC server resides. You cannot use an IP address for this property.
The DAC server and a given DAC repository have a one-to-one mapping. That is, you can only run one DAC server against any given DAC repository. Thus, in the repository you must specify the network host name of the machine where the DAC sever is to be run.
This property also takes the value localhost. However, this value is provided for development and testing purposes and should not be used in a production environment.
DAC Server OS
Operating system of the machine where the DAC server resides. Possible values are Windows, Solaris, HP, or AIX.
If you move the DAC server from another operating system to AIX, you need to do the following: change the DAC server host to the appropriate value; restart the DAC client; reenter all the password fields for the Informatica servers and database connections; and reconfigure the DAC server on the AIX machine by running serverSetupPrompt.sh.
DAC Server Port
Network port to which the DAC server binds in order to listen to client requests. The default value is 3141. If this port has been assigned to another process, you can enter any numerical port value greater than 1024.
Drop and Create Change Capture Views Always
Possible values are True and False.
When set to True (the default value), the DAC server drops and creates change capture views every time it performs a change capture process, including for both full and incremental loads.
Setting this property to True can create system catalog lock up for DB2-UDB and DB2-390 databases. Therefore, by setting the property to False, the DAC server will drop and create views selectively, using the following rules:
In full mode:
During the change capture phase, views will be dropped and created as full views.
During the change capture sync process, incremental views will be generated.
In incremental mode:
If the view exists, it will not be dropped and created.
If the view does not exist, the incremental view will be created.
DropAndCreateIndexes
Possible values are True and False. Indicates whether, during the ETL process, the DAC server automatically drops and creates indexes.
This property takes precedence over the CreateQueryIndexesAtTheEnd. Therefore, if the DropAndCreateIndexes property is set to False, you cannot set the property CreateQueryIndexesAtTheEnd to True to have indexes of the Query type created at the end of the ETL process.
Dryrun
Possible values are True and False.
Indicates whether tasks are executed without invoking Informatica workflows. The following processes are executed: change capture, truncation of tables, drop and creation of indexes, and analyze statements.
This option should be used for debugging purposes only and not used in a production environment.
Generic Task Concurrency Limit
Determines how many tasks with execution types other than Informatica can be run concurrently. The value must be numerical.
To set this value, you should consider what the external tasks do. For example, if the tasks open connections to a database, you should consider how this would affect the preconfigured tasks.
HeartBeatInterval
Frequency (in seconds) the DAC server checks on the health of the database connections. The value must be numerical. For example, a value of 300 (the default value) indicates the system will perform subsystem diagnostics and recovery procedures every 300 seconds.
InformaticaFileParameterLocation
Directory where the Informatica parameter file is stored.
Output Redirect
Indicates whether logging information and standard output and errors are redirected to files in the log directory (when property is set to True). The file containing standard output starts with out_ and ends with the .log extension. The standard error messages are in the file starting with err_ and ending with the .log extension.
If this property is set to False, the logging information is directed to the machine's standard output and error files, which typically defaults to the console from which the DAC server was launched if the server was launched in a visible console mode. If the server is launched as a Windows service, the logging information is directed to the service log. If the server is launched with the command shell not visible, all logging information is deleted
Repository DB Pool Size
Indicates the maximum number of connections to the DAC repository that the server will maintain.
Repository Name
Unique name for the DAC repository.
Scheduler.Poll.Interval
Frequency (in seconds) the DAC server polls for changes in the schedule configuration.
Script After Every ETL
The name of the script or executable to be run after every execution plan.
For more information, see the description of the property Script Before Every ETL.
Script Before Every ETL
The name of the script or executable to be run before every execution plan.
For example, before running an execution plan, you might want to run a process or perform certain tasks. These can be contained in a script or executable. This file should be placed in the scripts subdirectory of the DAC server.
The execution plan runs only after the external process has finished. Therefore, it is important that the script or executable does not fail.
Server Log Level
Output logging level. Possible values are Finest, Finer, Fine, Config, Info, Warning, and Severe. The Severe value produces minimal log details, and Finest produces the most extensive amount of reporting.
SQL Trace
Possible values are True and False.
Indicates whether the SQL statements to the DAC repository and database connections are added to the log file. Possible values are True and False. The True value sends a hint to the database connectivity layer of the DAC server to enable SQL tracing; thus, every SQL statement that is run by the DAC server is spooled to the appropriate output log file.
It is recommended that you set this property to False.
Test Run
Possible values are True and False.
When set to True, the execution plan will not stop on errors.
Verify and Create Non-Existing Indices
Possible values are True and False.
Indicates whether indexes defined in the DAC repository will be automatically created in the data warehouse database during an incremental load.
When this system property is set to True, the DAC server verifies whether indexes defined in the DAC repository are also defined in the data warehouse database. This verification process can delay the execution of an execution plan.
This tab enables you to set up a list of email addresses that will be notified about the status of the ETL process.
Name
Logical name of the user to be notified.
Email Address
Email address where the notification is sent.
Notification Level
The notification levels are as follows:
10
Notifies recipient of success or failure of each task.
5
Notifies recipient of success of failure of the entire ETL process.
1
Notifies recipient that ETL completed successfully.
The Informatica Servers tab enables you to register one or more Informatica servers and one Informatica Repository server and to specify how many workflows can be executed in parallel on each server. The DAC server automatically load balances across the servers.
|
Note: You can install multiple Informatica servers and point them to a single Informatica Repository. You need to register each Informatica Server in the DAC and specify a unique machine name and server name. For instructions on registering an Informatica Server in the DAC, see the Oracle Business Intelligence Applications Installation and Configuration Guide. |
Name
Name of Informatica Server or Informatica Repository Server.
Type
Type of server.
Informatica
Specifies the Informatica Server.
Repository
Specifies the Informatica Repository Server.
Server Hostname
The host machine name where the Informatica Server or Informatica Repository Server is installed.
Server Port
Port number used by the Informatica Server or Informatica Repository Server to listen to requests.
Login
Informatica Repository user login.
Password
Informatica Repository password.
Maximum Sessions
The maximum number of workflows that can be executed in parallel on the Informatica Server.
Repository Name
Informatica Repository name.You deploy only one Informatica Repository Server, but you can deploy multiple Informatica Servers.
The Physical Data Sources tab provides access to the connection properties for the physical data sources. In this tab, you can view and edit existing physical data source connections and create new ones.
Name
Logical name for the physical data source.
Type
Physical data source type. Possible values are the following:
Source
Warehouse
Informatica Repository
DAC Repository
Other
Connection Type
Type of database connection. Possible values are the following:
Oracle (OCI8)
Connects to Oracle using the tnsnames entry.
Oracle (Thin)
Connects to Oracle using thin driver.
DB2
DB2 UDB database.
DB2-390
DB2 390 database.
MSSQL
Microsoft SQL Server database.
Teradata
Teradata database.
Flat File
Connection String
If you are using:
Oracle (OCI8), use the tnsnames entry.
Oracle (Thin), use the instance name.
SQL Server, use the database name.
DB2-UDB/DB2-390, use the connect string as defined in the DB2 configuration.
Teradata, use the database name.
Table Owner
Name of the table owner.
Max Num Connections
Maximum number of database connections this connection pool can contain.
DBHost
Host machine where the database resides. This field is mandatory if you are using Oracle (Thin), MSSQL, or Teradata, but is not required if you are using Oracle (OCI8), DB2, or DB2-390.
Port
Port where the database receives requests. Required for Oracle (Thin) and MSSQL databases. Not required for Oracle (OCI8), DB2, or DB2-390, or Teradata databases.
Priority
User-defined priority of the data source.
Data Source Number
User-defined number of the data source.
Default Index Space
Applicable to Oracle databases only. The default index space for the physical data source. When indexes are dropped and created, they are created in this index space.
Parallel Index Creation
Use this field to specify how many indexes are to be created in parallel. For example, if a table with thirty indexes is the only task running in an execution plan at a given time, and you specify that 10 indexes are to be created in parallel, 10 of the 30 indexes will be created in parallel.
The number of indexes that can be created in parallel is limited by the value you set in the Max Num Connections property.
You must select the Parallel Tables Indexes check box in order to specify a number in the Parallel Index Creation field.
Parallel Table Indexes
Use this check box to indicate that indexes within a table are to be created in parallel.
|
Note: All indexes are dropped in serial order. |
The Index Spaces subtab allows you to specify tablespaces for indexes by table type. For instructions, see "Specifying Tablespaces for Indexes by Table Type".
Table Type
Table type for which you want to specify a tablespace.
Index Space
Specifies the name of the index space.
|
Note: You must create the index space on the database before you specify an index space in the DAC. |
During an ETL process, this date is captured for all target tables and source tables of the type primary and auxiliary. The DAC uses this date in the change capture process, during parameter generation, when choosing between full and incremental loads, and when deciding whether to truncate a table. (Does not apply to micro ETL processes.)
|
Note: Refresh dates for micro ETL processes are captured in the Refresh Dates subtab of the Execution Plans tab. |
Name
Name of source or target table.
Execution Plan
The name of the execution plan to which the source or target table belongs.
Refresh Date
The refresh date for the source or target table.
Number of Rows
Valid for target tables only. Indicates the total number of rows in the table after the table has been loaded.
The Execute View provides access to functionality that enables you to run, schedule, and monitor execution plans. The tabs in this view are listed in alphabetical order.
The Current Run tab displays a list of queued, running, and failed current ETL processes in the top window. This list includes comprehensive information about each process. Once an ETL process completes, it is accessible from the Run History tab.
Execution Plan Name
The execution plan whose runtime instance is this record. This field is read only.
Run Status
The status of the run. The possible values are the following.
| Value | Description |
|---|---|
| Queued | Tasks for which the Depends On tasks are not yet completed. Displayed in yellow in the Current Run list. |
| Runnable | Tasks for which the Depends On tasks have completed and are ready to be run but are waiting for an Informatica slot to be available. |
| Running | Tasks for which the Depends On tasks have been completed, have gotten an Informatica slot, and are being executed. Displayed in blue. |
| Paused | Task group members that are waiting for the other tasks in the group to be executed. |
| Failed | Tasks that were executed but encountered a problem. Displayed in red. |
| Stopped | Tasks for which one or more Depends On tasks have failed. |
| Completed | All tasks have completed without errors. Displayed in green. |
Start Timestamp
Start time of the ETL process. Reflects the start time of every ETL attempt. For example, if the ETL fails and is run again, it gets a new start timestamp. The history of attempted runs is maintained in the audit trail for the run. This field is read only.
End Timestamp
End time of the ETL process. Reflects the end time of every ETL attempt. For example, if the ETL fails and is run again, it gets a new start timestamp. The history of attempted runs is maintained in the audit trail for the run. This field is read only.
Duration
A calculated field that shows the difference between start and end time stamps.
Status Description
Displays messages generated during run time. You can add notes to this field for Completed runs.
Process ID
ID for the process. This value is an integer that is incremented by 1 for every run. This value is stored as ETL_PROC_WID in all the data warehouse tables. This field is read-only.
Total Number of Tasks
The total number of tasks for this run. This field is read only.
Number of Failed Tasks
The sum total of tasks that have failed and that have stopped. This field is read only.
Number of Successful Tasks
The number of tasks whose status is Completed. This field is read only.
Number of Tasks Still in Queue
The number of tasks whose prerequisite tasks have not completed, and the number of tasks whose prerequisite tasks are completed and are waiting for resources. This field is read only.
Schedule Name
The name of the scheduled ETL process.
The Audit Trail (RO) subtab is a read-only tab that provides the history of the selected run.
Last Updated
The date the selected run was last updated.
Start Timestamp
Start time of the selected run.
End Timestamp
End time of the selected run.
Duration
The difference between the start timestamp and the end timestamp of the selected run.
Status
Status of the selected run.
The Summary (RO) subtab provides a summary (based on dynamic SQL) of the selected ETL run.
Task Phase
The task phase of the selected ETL run.
Start Time
Start time of the selected ETL run.
End Time
End time of the selected ETL run.
Duration
The difference between the start timestamp and the end timestamp of the selected ETL run.
Source System
The name of the source system container associated with the selected ETL run.
The Tasks subtab displays runtime instances of the tasks. As the execution proceeds, the tasks are executed based on the dependency rules and some prioritization.
As tasks complete, the tasks that depend on the completed tasks are notified and once their dependencies are completed, they become eligible to run. If a task fails, the administrator can address the failure and then requeue the task or mark it as completed. The DAC server polls for any changes in the failed task's detail status. If a failed task detail is queued, the task itself gets back into the ready-to-run queue and all its dependent tasks get into the queued status.
The rules of the prioritization are as follows:
Tasks with no dependencies are executed first.
If a task has failed and has been requeued, it gets the maximum priority.
Tasks with greater phase priorities are executed next. When several tasks of the same phase are eligible to run, the tasks with greater task priorities are executed next.
The Task Details subtab opens in Query mode. It enables you to query for tasks associated with the selected ETL run in order to view execution details.
The Execution Plans tab enables you to view and edit existing execution plans and to create new ones.
Full Load Always
Indicates the specified ETL process will always execute a full load.
Keep Separate Refresh Dates
Used for micro ETL processes. Indicates refresh dates are kept separate for each ETL run of the execution plan.
Prune Days
When the source system is Oracle's Siebel CRM applications, the LAST_UPD column in the transactional database tables is used for incremental change capture. This timestamp reflects the actual event time. It is therefore possible for a data row to be committed to the transactional database with a LAST_UPD date that is older than the date on which the last refresh was executed. This will cause the data row to be missed in the subsequent extract (if based purely on LAST_UPD date).
However, the LAST_UPD date column still provides an opportunity to improve the change capture process by overlapping the extraction date window by the number of days set in this parameter. The records extracted in the overlapped window are filtered by comparing this information with information in the Image table.The Prune Days setting ensures that the rows that had values in LAST_UPD older than values in LAST_REFRESH_DATE are not missed. This is a parameter that can be set based on experience with processes, such as remote sync, that potentially can cause records to be missed. This parameter cannot be less than 1.
For example: Assume the table W_PERSON_D was refreshed on January 15th by querying the table S_CONTACT. And, the Prune Days setting was set to 5. The next time S_CONTACT is queried to load W_PERSON_D, the records that have a LAST_UPD value since January 10 are compared with the ROW_ID of the Image table to cover for any missing records between January 15 and January 10 (the overlap period).
For source systems other than Siebel, the Prune Days setting is used in the same way except that the DAC subtracts the number of prune days from the LAST_REFRESH_DATE of a given source and supplies this as the value for the $$LAST_EXTRACT_DATE parameter.
Last Designed
Date this execution plan was last designed.
Analyze
Indicates the tables associated with this execution plan will be analyzed.
Analyze Truncated Tables Only
Indicates only truncated tables will be analyzed.
Drop/Create Indices
Indicates indexes of the tables associated with this execution plan will be dropped and created.
Run Now Button
The Run Now button submits a request to the DAC server to execute the execution plan.
Build Button
Builds the execution plan, by assembling subject areas, tasks, task phases, indices, tags, parameters, and source system folders.
The All Dependencies subtab opens in Query mode. It enables you to query for tasks that have a dependent relationship. The columns in this tab are the same as those in the Immediate Dependencies subtab. For a description of the columns, see "Execution Plans Tab: Immediate Dependencies Subtab".
The Following Tasks subtab lists the tasks that must be completed after an ETL is executed. Also enables you to add tasks.It includes the same properties as the Preceding Tasks subtab.
The Immediate Dependencies subtab opens in Query mode. It enables you to query for tasks that have an immediate dependent relationship between tasks that are generated during the automatic task generation process.
Task (Calculated)
Shows the source and target of the named task.
Predecessor Name
Predecessor task for the named task.
Predecessor (Calculated)
Shows the source and target of the predecessor task.
The Ordered Tasks subtab lists tasks associated with the selected execution plan and the order in which they can be executed.
Primary Source
Primary source table from which the task extracts data.
Primary Target
Primary target table into which data is loaded.
Folder Name
Name of the Informatica folder in which the task resides.
Task Phase
Task phase of the ETL process. The DAC server uses the task phase to prioritize tasks and to generate a summary of the time taken for each of the phases.
Command
Command associated with the task.
Source System
Source system container from which the task extracts data.
Details Button
The Details button in the subtab toolbar opens a dialog that lists the following details about a selected task:
All Predecessors
All Successors
Immediate Predecessors
Immediate Successors
Source Tables
Target Tables
Conditional Tables
The Parameters subtab lists the parameters of the selected execution plan for database connections and Informatica folders.
Type
Possible values are the following:
Folder
Indicates an Informatica folder.
Datasource
Indicates a database connection parameter.
Name
Logical name of the folder or database connection.
Value
Physical name of the folder or database connection.
Source System
Name of the source system associated with the parameter.
The Preceding Tasks subtab lists the tasks that must be completed before an ETL process is executed. It also enables you to add preceding tasks.
Name
Name of task.
Priority
Indicates the order in which the task is executed. If two or more tasks have the same priority, the DAC will execute them in parallel.
Command
Command associated with the task.
Source System
Source system container from which the task extracts data.
Applies to micro ETL execution plans (indicated by selecting the Keep Separate Refresh Dates check box in the Execution Plans tab).
Connection
Logical name for the database connection.
Refresh Dates
Last refresh time of the execution plan. This applies only when separate refresh dates are maintained. Used for micro ETL processing.
The Subject Areas subtab lists the subject areas associated with the selected execution plan. You can also add subject areas to the selected execution plan.
Subject Area
Name of the subject area associated with the execution plan.
Source System
The source system container associated with the subject area.
The Run History tab displays information about completed ETL processes. The information displayed in the top and bottom windows is the same as that in the Current Run tab. For a description of the information in the Run History tab, see "Current Run Tab".
The Scheduler tab enables you to schedule ETL processes to be executed either once at a later time or periodically. When you schedule an ETL or make changes to a schedule, the DAC server picks up the information from the DAC client. The DAC server polls the DAC repository for changes periodically at a frequency set in the DAC system properties.
The top window of the Scheduler tab lists ETL runs that have been scheduled. The bottom window enables you to schedule an ETL run.
Execution Plan
The name of the scheduled execution plan.
Last Schedule Status
The last run status of the scheduled ETL process. Possible values are Running, Completed or Stopped.
Next Trigger
Time the scheduled ETL run will next be executed.
Status Description
Description of the last ETL run. Possible values are Running, Completed, or the reason the process stopped.
Recurrence
Indicates how often the schedule will be executed.
