| Oracle® Business Intelligence Data Warehouse Administration Console User's Guide Version 10.1.3.4 Part Number E12652-02 |
|
|
View PDF |
| Oracle® Business Intelligence Data Warehouse Administration Console User's Guide Version 10.1.3.4 Part Number E12652-02 |
|
|
View PDF |
This chapter describes the functionality available in the Data Warehouse Administration Console (DAC) tabs.
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.
The Name column in a tab specifies the name of the database object.
The Inactive column indicates whether a database object is inactive. Inactive objects do not participate in the ETL process.
The Owner column specifies the source system container in which the database object was created.
The Edit subtab enables you to edit an object that is selected in the top window.
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".
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.
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.
For information using the Index Action feature to override default index behavior, see "About Index, Table and Task Actions".
The table for which an index is created.
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.
Lists the databases associated with the selected index.
For unique indexes, the number of columns that will be unique.
Indicates whether the index is unique.
Indicates whether the index is clustered. There can be only one clustered index per table.
Indicates whether the index is of the bitmap type.
Applicable only for DB2-UDB databases. The index will be created with the Allow Reverse Scan option.
Indicates whether the index will be dropped and created regardless of whether the table is being loaded using a full load or incremental load.
Indicates whether indexes of the bitmap type will be dropped and created regardless of whether the table is being loaded using a full load or incremental load.
The Actions subtab lists the actions that have been set up for the selected index. For a description of index actions, see the following:
The Columns subtab displays a list of columns the index is made of.
The position of the column in the index.
Indicates whether the sort order is ascending or descending.
The Source System Folders tab lists the Informatica folders associated with the selected source system container.
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.
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:
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. |
The parameter value.
A subject area is a logical grouping of tables related to a particular subject or application context. It also includes the tasks that are associated with the tables, as well as the tasks required to load the tables.
Subject areas are assigned to execution plans, which can be scheduled for full or incremental loads.
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.
For more information, see "Customizing DAC Objects and Designing Subject Areas" and "Creating a Subject Area".
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".
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".
This read-only field indicates whether the configuration tag tasks will be executed.
When this read-only check box is selected, the configuration tag is globally disabled.
The Extended Tables (RO) subtab is a read-only tab that lists the extended tables associated with the selected subject area.
Indicates whether the extended table is included in the selected subject area, indicating your choice about inclusion or exclusion of the tables in the assembly process.
For more information about subject areas, see "Customizing DAC Objects and Designing Subject Areas".
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.
For information about adding tables to a subject area, see"Customizing DAC Objects and Designing Subject Areas" and "Creating a Subject Area".
The name of the table associated with the selected subject area.
The Tasks subtab lists the tasks associated with the selected subject area. It enables you to add tasks to and remove tasks from a subject area and to inactivate tasks.
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.
For more information about subject areas, see "Customizing DAC Objects and Designing Subject Areas".
If the task belongs to a task group, this column displays the task group name.
The task phase of the ETL process.
Indicates whether the task was automatically generated by the DAC's task generation process.
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 the physical database tables defined in the database schema that are associated with the selected source system container. It enables you to view and edit existing tables and to create new ones.
For information about adding new tables, see "Adding a New Table and Columns to the Data Warehouse".
The table type.
Indicates whether the table is a warehouse table. If this option is not selected, the schema creation process will not include this table.
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".
Indicates whether the table is a MultiSet table. Applicable only to Teradata databases.
Indicates whether the table has a Unique Primary Index. Applicable only to Teradata databases.
The Actions subtab lists the actions that have been set up for the selected table. For a description of task actions, see the following:
The table action types enable you to trigger SQL scripts to analyze or truncate tables. Table actions for analyzing or truncating tables override all other table properties.
The default actions on a table during ETL execution are truncating a table and analyzing a table. If you want to override any of these syntaxes, you can define actions per mode (full or incremental). Once an action is defined, it overrides the default behavior.
Note: You can associate an action to multiple objects at the same time. First, identify the objects that you want to associate an action with by using the query functionality. Then, right-click on the results displayed, and select Associate Actions. Next, select an action and the mode you want to associate it with.
The table action types are the following:
Analyze Table
Use this type to analyze tables.
Truncate Table
Use this type to truncate tables.
The load type specifies whether the SQL script is to be called for incremental runs, full runs, or both.
Actions are the SQL statement or stored procedure that you define. You can define one or more SQL statements or stored procedures for each action.
The Conditional for Tasks (RO) subtab displays a read-only list of tasks that have defined this table as one of the conditional tables.
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.
The Columns subtab displays the columns that belong to the selected table.
The Indices (RO) subtab displays a read-only list of indexes that belong to the selected table.
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.
For unique indexes, the number of columns that will be unique.
Indicates whether the index is unique.
Indicates whether the index is clustered. There can be only one clustered index per table.
Indicates whether the index is of the bitmap type.
Applicable only for DB2-UDB databases. The index will be created with the Allow Reverse Scan option.
Name of the table space.
Applicable to Teradata databases only.
The Related Tables subtab 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.
The Source for Tasks (RO) subtab displays a read-only list of tasks that use the selected table as a source.
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.
Applicable for Siebel transactional sources only. Indicates change capture for the primary/auxiliary source tables will be executed.
Table type.
The Target for Tasks (RO) subtab displays a read-only list of tasks that use the selected table as a target.
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.
The Task Groups tab lists all the task groups associated with the selected source system container. A task can belong to only one group.
Indicates the tasks in this task group will be restarted if one or more tasks fails during an ETL process.
Indicates the tasks in this task group will be executed sequentially. This property overrides the execution order.
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.
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. It also enables you to add child tasks to a task group selected in the top window.
Logical database connection for the primary source database.
Logical database connection for the primary target database.
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 extracting data by the selected task group.
Name of source table.
Task that extracts data from the table.
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.
Name of the target table.
Task that loads data into the target table.
Type of target table.
The Tasks tab lists all the tasks associated with the selected source system container.
If the task is a member of a group, this field lists the task group name.
The order in which the task is defined to execute in a certain group.
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
If a table has no last refresh timestamp, this command is executed.
Only for execution type of Informatica. The folder in which the workflow resides. Note: The name cannot contain spaces.
Logical database connection for the primary source database.
Logical database connection for the primary target database.
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.
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 manually on the other sources also.
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.
Indicates the order in which the task is executed. If two or more similar tasks (tasks having same phase, similar truncate properties, same number of successors, same number of source tables) have the same priority, the order occurs randomly.
Applicable for Siebel transactional sources only. Indicates the change capture for the primary/auxiliary source tables will be executed.
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 Actions subtab lists the actions that have been set up for the selected task. For a description of task actions, see the following:
Action types are predefined categories of task behaviors that trigger the execution of a SQL script. The following types are available:
Preceding Action
Use this type to execute a SQL script before a task runs.
Success Action
Use this type to execute a SQL script after a task runs successfully.
Failure Action
Use this type to execute a SQL script if a task fails during its execution.
Restart Action
Use this type to execute a SQL script when a task that previously failed is restarted.
Upon Failure Restart Action
Use this type to execute a SQL script to restart a task that fails.
The load type specifies whether the SQL script is to be called for incremental runs, full runs, or both.
You define the task action in the form of a SQL statement or stored procedure. You can define one or more SQL statements for each action. Double-click in the Action field to open the Choose Action dialog box, where you can select the appropriate action.
You define the SQL statement or stored procedure for an action in the Task Actions dialog box, which you access by selecting Tools, then Seed Data, then Actions, and then Task Actions. For instructions, see "Defining a SQL Script for an Action".
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.
This read-only field indicates whether the configuration tag tasks will be executed.
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 of the parameter.
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.
The parameter data type. Possible values are Text, Timestamp, and SQL.
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.
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.
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.
For multi-source execution plans only. Specifies how the Block action of the phase dependency behaves in relation to multi-source execution plans. Possible values are the following:
Both
Indicates the blocking action is active for tasks that have the same source and target physical data source connections.
Source
Indicates the blocking action is active for tasks that have the same source physical data source connection.
Target
Indicates the blocking action is active for tasks that have the same target physical data source connection.
None
Indicates the blocking action is active for all tasks regardless of the source and target physical data source connections.
The ETL phase that will apply to the Action and Grain properties.
When a task gets executed, DAC determines the read and write mode based on the refresh dates that the DAC stores for the database connection and the source/target table name combination of that task. However, if you want to determine the mode of both read and write operations based on a table which is not a source/target table, you can define it in the Refresh Date Tables subtab. For example, suppose have an aggregate table based on W_REVENUE_F and its dimensions, there are two ways of populating the aggregate table. In Full mode, the table gets truncated, and all the aggregate values get recomputed. In the Incremental mode, the delta aggregate values are computed based on the new/updated records in the base fact table. The incremental strategy is efficient for a small subset of rows you would expect in an incremental run. The primary transactional table on the OLTP side for this table is S_REVN. Suppose the data is being incrementally extracted from one source, and at a subsequent time a new data source is added to the execution plan. The big number of incoming rows make the incremental load inefficient, but recomputing of the aggregates more efficient. Hence, the decision to load the table in Full mode should depend on the ultimate source table, which is qualified as OLTP.S_REVN, rather than based on the immediate source table Datawarehouse.W_REVENUE_F.
The Source Tables subtab lists the tables from which the selected task extracts data.
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 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.
Table type.
Data source for the target table. If no data source is specified, this value defaults to the task's primary target.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Generates tasks in the Task Details subtab of the Current Run tab but does not execute them.
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
Indicates the maximum number of connections to the DAC repository that the server will maintain.
Unique name for the DAC repository.
Frequency (in seconds) the DAC server polls for changes in the schedule configuration.
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.
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.
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.
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.
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.
The worker pool size is the number of worker threads that perform operations such as drop/create indexes, truncate/analyze tables, and ETL jobs like SQL and Informatica workflows.
The property's value corresponds to the number of task details that are anticipated to run in parallel. The default size is 50, which assumes that 10 tasks run in parallel and each task has five detail sub-tasks (for example, truncate, drop index, create index, analyze table) running in parallel.
It is prudent to increase this number when running more ETL tasks and/or task details in parallel. For example the Worker Pool Size can be set to 100 if the number of parallel indexes is set to two per table.
Running multiple threads can be resource intensive; therefore, you should set this value as low as possible.
This tab enables you to set up a list of email addresses that will be notified about the status of the ETL process.
Logical name of the user to be notified.
Email address where the notification is sent.
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 of Informatica Server or Informatica Repository Server.
Type of server.
Informatica
Specifies the Informatica Server.
Repository
Specifies the Informatica Repository Server.
For Informatica 7.x installations, indicates the host machine name where the Informatica Server is installed. For Informatica 8.x installations, indicates the Integration Service name.
Port number used by the Informatica Server or Informatica Repository Server to listen to requests.
For Informatica 8.x installations only. Indicates the domain file location.
Informatica Repository user login.
Informatica Repository password.
The maximum number of workflows that can be executed in parallel on the Informatica Server.
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.
Logical name for the physical data source.
Physical data source type. Possible values are the following:
Source
Warehouse
Informatica Repository
DAC Repository
Other
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
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.
Name of the table owner.
Maximum number of database connections this connection pool can contain.
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 where the database receives requests. Required for Oracle (Thin) and MSSQL databases. Not required for Oracle (OCI8), DB2, or DB2-390, or Teradata databases.
User-defined priority of the data source.
User-defined number of the data source.
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.
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 Index Creation check box in order to specify a number in the Num Parallel Indexes field.
The Index Spaces subtab enables you to specify tablespaces for indexes by table type. For instructions, see "Specifying Tablespaces for Indexes by Table Type".
Table type for which you want to specify a tablespace.
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.
Enables you to specify how many indexes can be created in parallel for a given table.
Note: Use this property to specify the number of parallel indexes DAC will create for a specific table. Use the Num Parallel Indexes property in the Physical Data Sources tab to specify the number of parallel indexes DAC will create for all tables associated with a specified physical data source connection.
Name of the table on which the indexes will be created.
Number of Parallel Indexes Column
Number of indexes that can be created in parallel for the specified table.
Analyze frequencies in days by table type.
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 of source or target table.
The name of the execution plan to which the source or target table belongs.
The refresh date for the source or target table.
Indicates when the table was analyzed.
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.
The execution plan whose runtime instance is this record. This field is read only.
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 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 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.
A calculated field that shows the difference between start and end time stamps.
Displays messages generated during run time. You can add notes to this field for Completed runs.
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.
The total number of tasks for this run. This field is read only.
The sum total of tasks that have failed and that have stopped. This field is read only.
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.
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.
The date the selected run was last updated.
Start time of the selected run.
End time of the selected run.
The difference between the start timestamp and the end timestamp of the selected run.
Status of the selected run.
The Summary (RO) subtab provides a summary (based on dynamic SQL) of the selected ETL run.
The task phase of the selected ETL run.
Start time of the selected phase of the run.
End time of the selected phase of the run.
The difference between the start timestamp and the end timestamp of the selected phase of the run.
The Run Type Summary (RO) subtab is a read-only tab that indicates the number of task details by the execution type.
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 prioritization is also based on the number of dependent tasks, the number of source tables, and the average time taken by a task.
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.
For more information, see "Building, Running and Monitoring Execution Plans".
Indicates the specified ETL process will always execute a full load.
Used for micro ETL processes. Indicates refresh dates are kept separate for each ETL run of the execution plan.
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.
The Prune Days property can be set at the execution plan level for the entire plan or in the execution plan parameters for specific source connections. Prune Days value set at the source level override the Prune Days value set at the execution plan level.
Date this execution plan was last designed.
Indicates the tables associated with this execution plan will be analyzed.
Indicates only truncated tables will be analyzed.
Indicates indexes of the tables associated with this execution plan will be dropped and created.
The Run Now button submits a request to the DAC server to execute the execution plan.
The Build button does the following:
Collects all the tasks from all the subject areas.
Substitutes the logical values with the physical values as defined in the parameters subtab (primary source/target connection and folder information).
Removes any redundancies based on the task name and primary source/target connection information.
Creates multiple instances of the same task to accommodate the number of copies of the parameters.
Comes up with the ordered list of tasks with the dependencies computed among them.
For more information about building an execution plan, see "Building and Running Single-Source and Multi-Source Execution Plans".
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".
Note: The All Dependencies information is not subject to export and, therefore, not importable elsewhere. However, if you want to compute all dependencies, you can do so by right-clicking and selecting Compute All Dependencies.
The Following Tasks subtab lists the tasks with the phase Post-ETL.
Indicates the order among the following tasks in which this task is executed.
Command associated with the task.
Source system container from which the task extracts data.
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.
The level of the task's dependency. Tasks that have no dependencies are depth 0. Tasks that depend on other tasks of depth 0 are depth 1, and so on.
Name of immediate dependent task.
Source table from which the task extracts data.
Target table into which the task loads data.
The Ordered Tasks subtab lists tasks associated with the selected execution plan and the order in which they can be executed.
Depth of the task
Indicates the name of the group, if the task belongs to one.
Primary source table from which the task extracts data.
Primary target table into which data is loaded.
Name of the Informatica folder in which the task resides.
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.
Source system container from which the task extracts data.
The Preview Run Details Gives a summary of the details of what gets executed when the particular task runs part of the execution plan. It includes the following:
Pre-ETL actions
Upon Failure Restart actions
Truncate table information
List of indexes to be dropped and created
Full or incremental command based on the current situation
Tables to be analyzed
Upon Success action
Upon Failure action
The Parameters subtab lists the parameters of the selected execution plan for database connections and Informatica folders.
For more information about managing parameters in the DAC, see:
Number of the data source.
Possible values are the following:
Folder
Indicates an Informatica folder.
Datasource
Indicates a database connection parameter.
Logical name of the folder or database connection.
Physical name of the folder or database connection.
Indicates how many minutes an extract of a data source will be delayed after the start of a multiple source ETL.
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 of task.
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 associated with the task.
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).
Logical name for the database connection.
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.
Name of the subject area associated with the execution plan.
The source system container associated with the subject area.
All the tables that are touched by the execution plan by database connection, type (source/target), and subtype (primary/auxiliary/lookup.)
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".
See "Current Run Tab: Audit Trail (RO) Subtab".
See "Current Run Tab: Phase Summary (RO) Subtab".
See "Current Run Tab: Run Type Summary (RO)".
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.
The name of the scheduled execution plan.
The last run status of the scheduled ETL process. Possible values are Running, Completed or Stopped.
Time the scheduled ETL run will next be executed.
Description of the last ETL run. Possible values are Running, Completed, or the reason the process stopped.
Indicates how often the schedule will be executed.
|
 Copyright © 2008, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
