Working with ETLs
Navigate to Helix Capacity Optimization Console> Administration > ETL & System Tasks > ETL tasks to view the list of all configured ETL tasks grouped by task group. You can manage or run , data movers, and task chains.
For more information, refer to the following sections:
Similar to the Maintaining System tasks page, each row in the table represents an ETL task, and displays details including the last execution results. For more information on the structure of the summary table, refer to Managing ETL and System tasks.
Last exit value
The ETL task completed successfully.
The WARNING status is generated by a large number of causes that often depend on the nature of the ETL task itself. For example, an ETL that collects data from files or databases may not find data to extract. In this case, a warning message will be generated: Empty directory [...] for extraction, or, in the case of a database ETL, Loader: Dataset is empty. Another common warning message generated by ETL tasks can be related to the the data extracted being partially corrupted. The ETL process discards that data, continues processing, and displays a warning message.
The FAILED status indicates that the ETL task did not complete processing. Failures can be generated by different events. For example, network failure or a badly configured parameter could prevent an ETL from connecting to a database or mounting a network folder.
Service ETL tasks do not exit, hence they do not have a Last exit value.
For more information about execution issues, analyze the log file. For more information, see Managing tasks.
You can use the Task Commands form to start or reschedule any task displayed in the list. This functionality is described in the Task management section.
Viewing, editing, and deleting an ETL task
The ETL tasks page under Administration > ETL & System Tasks > ETL tasks displays detailed information about all ETLs, summarizing the status of their last run. For more information, see Understanding the ETL task summary table.
You can also edit the properties of an ETL task from this page. For more information, see Editing an ETL task.
To delete a task, select a task and then click Delete. From the list, select Delete. If you want to delete the entities imported by the ETL including details like Entity catalog, Lastcounter, and hierarchy transactions, from the delete drop-down, click Delete imported data..
Click Lastcounter to view the Status detail table. It lists the timestamp, result of the last run, and the value of the lastcounter parameter for each data source. Click Edit Lastcounter to manually change the lastcounter value. The lastcounter and lookup entries are created only when the ETL task is in production mode.
It is strongly recommended to limit the amount of data to import. If you need to recover historical data you should do it in small chunks. For instance, you should import data for a few days at a time, and import data for subsequent days in chunks.
Working with ETL entity catalogs
Click Entity catalog in the ETL Task details page to view information about its lookup tables. You can view the list of systems, business drivers and domains imported by the ETL, including data sources and the name they will have in BMC Helix Capacity Optimization.
Deleting an entity catalog record
Each row displays the mapping between the data sources and BMC Helix Capacity Optimization name.
Click Delete drop-down and select one of the following:
- Delete selected lookup entries of ETL
- Delete selected lookup entries of all ETLs
- Delete selected lookup entries of all ETLs and dismiss entities: Lookup reference and delete the selected resources. If the selected resources are not shared with other ETL tasks, this action will change their status to "dismissed". For more information, see Life cycle and status of entities and domains.
When an ETL task encounters an entity (or domain) in the data source, it checks its own lookup tables to find a configured target. If no target is found, the object is treated as a new object and the ETL task performs the following actions:
- Creates a new entity (or domain) with a name identical to the one found in the data source.
- Adds an entry into the ETL task lookup tables to track the new association.
New entities appear in the lookup tables and in the All Systems and Business Drivers > Newly discovered page of the Workspace section. For details, see Life cycle and status of entities and domains.
Adding a lookup table record
You can manually add a record to the Systems, Business Drivers, or Domains lookup tables. Click Add system lookup, Add business driver lookup, or Add domain lookup, and enter the required details.
|Lookup field||Select the method used by the data source to calls that entity.|
|Lookup value||Type the lookup value|
|System||Select an existing entity from the list.|
Click Add system, Add business driver, or Add domain as applicable.
Sharing Entity catalog
You can also configure an ETL to share the entity catalog of another ETL. To do so, follow these steps:
- Edit the Run configuration of the ETL.
- In the Edit run configuration page, expand Entity catalog.
- Select Shared Entity Catalog and select an entity catalog from Sharing with Entity Catalog..
- Click Save.
When two ETL tasks share the entity catalog, both of them should be able to load the same entity. Whenever a new entity is defined, one of the two ETLs will load it first, in no particular order.
Some issues might be caused if you set up the entity catalog after the first data import. An ETL task could automatically create a new entity and import its data, while it should have appended data to an existing entity. If this happens, you will have to perform an entity catalog reconciliation.
The manual reconciliation of an entity in BMC Helix Capacity Optimization is discouraged. If manual reconciliation is performed incorrectly, it may disrupt the system. Also, the reconciliation process cannot be undone. It is strongly advised that you run an ETL task in simulation mode before executing it for the first time, to facilitate solving lookup duplication issues beforehand. For details, see Entity name resolution and Preventing duplication issues.
Lookup duplication example
The following example depicts a situation in which a lookup reconciliation is necessary.
An ETL task,
ETL_A, which accesses a data source
dsA that collects data for two systems:
ETL_A runs everyday, and has been running for some time.
After its first run, it created two new entities in BMC Helix Capacity Optimization,
sys2. You later renamed these entities as
ny_sys2 to match your BMC Helix Capacity Optimization naming policy.
The lookup table of
ETL_A contains the following mappings, where 301 and 302 are the unique IDs for those BMC Helix Capacity Optimization entities.
In your IT infrastructure there is another data source,
dsB, which stores data for two systems,
sys2 (the same as before) and
sys3, but collects a different set of metrics from
If you create a new ETL task,
ETL_B, which imports
dsB data from
sys3 into BMC Helix Capacity Optimization and let
ETL_B perform an automatic lookup, its lookup table will look like the following:
The BMC Helix Capacity Optimization Data Warehouse now has two new systems. This is a problem, since
sys2 already exists, but
etlB did not know it.
In this case,
ETL_B should share the lookup table of
ETL_A in order to assign data to the correct system in BMC Helix Capacity Optimization, that is
If a lookup duplication problem occurred, you can recover the problem. To learn how, see Lookup reconciliation and splitting in Entity catalogs.
Preventing duplication issues
To avoid these problems, the correct procedure for creating a new ETL task is:
- Create the new ETL task with simulation mode turned on and the maximum log level (10).
- Manually run the ETL task and check its execution log to find out if it created any new entities. You can use this information to understand if the automatic lookup process is safe and if you need to use shared lookup from another ETL.
- If you notice an issue, you can also manually add a line in the lookup table.
- Toggle simulation mode off.
- Run the ETL task to import new data.
This following topics help you work with and understand ETL modules.
- Configuring database connection using Perl or Java ETLs
- Determining how to aggregate data to a given resolution using SQL
- Determining how to append the output of an ETL to an existing file using FileAppenderA
- Determining how to extract compressed files
- Enabling Windows shares mounting
- Exporting the run configuration of an ETL
- Gathering ETL logs
- Handling ETL lookup name
- Importing and exporting ETL packages
- Importing macro-events
- Setting file access for parsers
- Understanding entity identification and lookup