Creating and managing custom ETLs

Download the utility from the Administration tab.

1. Log in to BMC Helix Capacity Optimization as an administrator.
2. In the Home tab, select Tools, and click Download ETL Development kit. 
   The coetl-<version>.zip file is downloaded to your system.
3. Create a directory (for example, coetl) and copy the downloaded file to the directory.
4. Extract the contents of the file.

The project folder contains the following directory structure and files:

The ETL project includes the default properties. Edit the <project folder>/module.properties file to configure the ETL properties, such as name, description, associated datasets, and additional properties. The following table explains how to edit some of these properties:

#ETL module description
etl.module.description=MonitoringDatabase Extractor module

#Mandatory semicolon separated list of datasets IDs that ETL supports
general.dataset.idlist=1;2

#Additional configuration properties supported by ETL module
#
#Property definition syntax:
#       property name|description|type [String,Numeric,Boolean]|default value (optional)|required [true,false]
#
# Example - (adding a mandatory String property 'extract.system.types.list' with default value of '0;16;25;28':
#
#additional.config.properties.number=1
#additional.config.property.1=extract.system.types.list|System types to extract|String|0;16;25;28|true

Import the project that you created to an Integrated Development Environment (IDE) such as Eclipse IDE. The following steps are for the Eclipse IDE.

1. Open the Eclipse IDE.
2. Create a new project and provide a project name.
3. Select the ETL project path that you created in step 2 and customize as needed.
4. Click Finish.

Example: The imported project displays the Java extractor code.

Compile the Java code to create a Java ETL package. You can use the compiler that is embedded with the EDK. When you run the compile command, the generated compiled resources are copied to the bin directory of the project folder.

To use the embedded compiler, run the following command:

• (Windows) .\coetl.cmd --compile <path_to_saved_project>
• (Linux) ./coetl.sh --compile <path_to_saved_project>

Example:

• (Windows) .\coetl.cmd --compile development\MonitoringDatabaseETLProject
• (Linux) ./coetl.sh --compile development/MonitoringDatabaseETLProject

You can also use an external build tool such as Maven to compile the Java code. You need to instrument the build tool (Development IDE or external tool) to create Java compiled classes and add them to the bin directory of the project folder.

To create an ETL package from the ETL project that you created, run the following command:

• (Windows) .\coetl.cmd --package <project_path>
• (Linux) ./coetl.sh --package <project_path>

Example:

• (Windows) .\coetl.cmd --package development\MonitoringDatabaseETLProject
• (Linux) ./coetl.sh --package development/MonitoringDatabaseETLProject

The ETL package (.pkg file) is created in the output folder.

Activate the ETL package to view the custom module in the list of ETL modules that are available to the BMC Helix Capacity Optimization administrators when creating a new ETL instance. Before activating the package, you must generate the API key that is used to authenticate the user when connecting to the Helix Capacity Optimization Console, and copy this key to the EDK root folder. For details on generating the API key, see Generating an API key for programmatic access.

1. Navigate to the <project_path>/output folder. 
2. Run the following command to activate the package:
   • (Windows) .\coetl.cmd --activate <project_path>\output\<project_name>.etl.pkg
   • (Linux) ./coetl.sh --activate <project_path>/output/<project_name>.etl.pkg

Example:

• • (Windows) .\coetl.cmd --activate development\MonitoringDatabaseETLProject\output\ETLProject.etl.pkg
  • (Linux) ./coetl.sh --activate development/MonitoringDatabaseETLProject/output/ETLProject.etl.pkg

The ETL module is named according to this format: "<project_name> <parser|dbextractor|extractor> module". For example, MonitoringDatabase DBExtractor module. If you rename the generated key file or save it in a folder different from the EDK root folder, ensure that you specify the complete path of the key file:

• • (Windows) .\coetl.cmd --api-key <api_key_path>\<api_key>.key --activate <project_path>\output\<project_name>.etl.pkg
  • (Linux) ./coetl.sh --api-key <api_key_path>/<api_key>.key --activate <project_path>/output/<project_name>.etl.pkg

Example:

• • (Windows) .\coetl.cmd --activate development\MonitoringDatabaseETLProject\output\ETLProject.etl.pkg --api-key development\MonitoringDatabaseETLProject\edk_vl-aus-bco-dv02.key
  • (Linux) ./coetl.sh --activate development/MonitoringDatabaseETLProject/output/ETLProject.etl.pkg --api-key development/MonitoringDatabaseETLProject/edk_vl-aus-bco-dv02.key 

After activation, the custom module is displayed in the list of ETL modules that are available to the administrators when creating a new ETL instance.

1. Log in to the BMC Helix Capacity Optimization console.
2. Navigate to Administration > ETL & System Tasks, and select ETL tasks.
   
3. On the ETL tasks page, click Add > Add ETL.

4. On the Run Configuration tab, verify that the custom ETL module is displayed in the ETL Module list. 
5. Select the custom ETL module and configure the ETL properties, and click Save. The ETL tasks page shows the details of the newly configured ETL.
6. Click the ETL and click Run active configuration. A confirmation message about the ETL run job submission is displayed.
7. Verify the ETL logs and data collection in the Workspace.