Managing an Open ETL template

Use an Open ETL template to import and process data that is not directly supported by BMC Helix Capacity Optimization. A CSV file or an SQL query act as the data sources for the template. If the unit of the imported metrics does not directly map with that defined in the product, then you can specify the unit or a multiplier to convert it to the unit defined in the product. 

The Open ETL template wizard enables you to complete the following tasks:

• Create an Open ETL template for integrating a Generic ETL with BMC Helix Capacity Optimization
  
• Edit an existing Open ETL template
  
• Ensure that all fields in the Generic ETL that are required for importing and processing data are extracted
  

After you create an Open ETL template, you can use it to integrate a Generic ETL with BMC Helix Capacity Optimization. For more details, see Generic ETL based on a template.

The following video (3:32) illustrates how to create an Open ETL template, and create an ETL based on the Open ETL template.

 https://youtu.be/sIkEfqOEEUk

You can also delete an Open ETL template. For more details, see Open ETL templates.

To create an Open ETL template using CSV file as a data source

Before you begin

Ensure that the required CSV file is available.

To create an Open ETL template

1. Log in to the Helix Capacity Optimization Console.
2. Select Administration > ETL & SYSTEM TASKS > ETL tasks.
3. On the ETL tasks page, click Add > Add/Edit Open ETL template.
   The Open ETL Template Wizard is displayed.
4. On the Operation page, select Create a new Open ETL Template and click Next.

5. On the Datasource and datatype page, specify the following properties:

   Field	Description
   Select Datasource type	Select Comma Separated Value (CSV) file to import data from a CSV file.
   Select Datatype	Select any one of the following data type for the type of data available in the data source: Performance or Configuration metrics for a set of systems (any type)  Business driver metrics  Catalog of entities and relationships (OBJREL) For more information about the mandatory columns for the selected data type, see Dataset reference.

6. Click Next.

7. On the Entity type identification page, select the required entities from the Available table, and add them to the Selected table using the >> button. In the Selected table, select the required entities, and click << to remove it.

   Entities on this page are listed according to the data type selected on the Datasource and datatype page. If you do not select any entity, all entities are available for mapping data with the columns in the data source.

   The Entity type identification page is not displayed if you selected Catalog of entities and relationships (OBJREL) as the data type.

8. Click Next.

9. In the Upload CSV file page, select the CSV file to be uploaded.

   You can use any one of the following types of CSV file (with or without headers):

   • CSV file parser
   • CSV columnar file parser
   • Object Relationship CSV parser

   
   

    Click here to view sample input file formats...

   1. Sample CSV file with comma as a separator
   

   DS_SYSNM,DURATION,TS,CPU_UTIL,CPU_UTILMHZ,MEM_CONSUMED,MEM_UTIL,TOTAL_FS_UTIL,TOTAL_DSTORE_UTIL,TOTAL_FS_USED
   TestSystem_1,3600,2015-12-01 00:00:00,0.3443145,17.9037,553138520.064,1.94041748046875,18.0821039328303,26.1428634771427,6206517653.01248           
   TestSystem_1,3600,2015-12-01 01:00:00,0.3436665,17.8722,550298976.256,1.7834423828125,18.0821039328303,26.1428634771427,6206517653.01248           
   TestSystem_1,3600,2015-12-01 02:00:00,0.269264,14.3111,551229063.168,1.11126953125,18.0821039328303,26.1428634771427,6206517653.01248           
   TestSystem_1,3600,2015-12-01 03:00:00,0.2992775,15.7389,552812412.928,1.400947265625,18.0821039328303,26.1428634771427,6206517653.01248           
   TestSystem_1,3600,2015-12-01 04:00:00,0.3112775,16.3333,552557608.96,1.7545703125,18.0821039328303,26.1428634771427,6206517653.01248           

   2. Sample CSV file with semi-colon as a separator

   DS_SYSNM;DURATION;TS;CPU_UTIL;CPU_UTILMHZ;MEM_CONSUMED;MEM_UTIL;TOTAL_FS_UTIL;TOTAL_DSTORE_UTIL;TOTAL_FS_USED
   TestSystem_1;3600;2015-12-01 00:00:00;0.3443145;17.9037;553138520.064;1.94041748046875;18.0821039328303;26.1428634771427;6206517653.01248           
   TestSystem_1;3600;2015-12-01 01:00:00;0.3436665;17.8722;550298976.256;1.7834423828125;18.0821039328303;26.1428634771427;6206517653.01248           
   TestSystem_1;3600;2015-12-01 02:00:00;0.269264;14.3111;551229063.168;1.11126953125;18.0821039328303;26.1428634771427;6206517653.01248           
   TestSystem_1;3600;2015-12-01 03:00:00;0.2992775;15.7389;552812412.928;1.400947265625;18.0821039328303;26.1428634771427;6206517653.01248           
   TestSystem_1;3600;2015-12-01 04:00:00;0.3112775;16.3333;552557608.96;1.7545703125;18.0821039328303;26.1428634771427;6206517653.01248           

   Note: Different separators can be used for the CSV file that is used in the Open ETL template and in the Generic ETL that uses this template. The integration of the Open ETL template and ETL works irrespective of the separator. For example, you can create an Open ETL template with semi-colon as a separator for the associated CSV file. In the Generic (CSV) ETL, in which you use this template, you can define comma as the separator. The integration still works.

10. Click Next.

11. In the Column mapping page, review the mapping of data available in the data source with the BMC Helix Capacity Optimization entities. For more details about column mapping, see Column mapping for Open ETL template.

    The Messages table provides the following information:

    • Indicates whether all columns are mapped or not.
    • If there is any unmapped column, a Critical or Warning message is displayed.
    • A brief description of the message.
    • A link to view the details of the message.

12. If a Critical or Warning message is displayed in the Messages table, modify the mapping for the column that has the error or warning. 

    Click Explain to review the details of the Critical or Warning message and to view the suggested solution. For Duration or DS_SYSNM columns, you can specify the default values.

13. To modify the default mapping of an entity, see the To modify the default column mapping section. 

14. Click Next.

15. On the Name and description page, type Name and Description for the Open ETL template.

16. Click Finish. The new Open ETL template is saved.

To create an Open ETL template using SQL query as a data source

Before you begin

Ensure that the required database is configured with BMC Helix Capacity Optimization. For more details, see Adding external database connections.

To create an Open ETL template

1. Select Administration > ETL & SYSTEM TASKS > ETL tasks.
2. On the ETL tasks page, click Add > Add/Edit Open ETL template.
   The ETL Template Wizard is displayed.
3. On the Operation page, select Create a new Open ETL Template and click Next.

4. On the Datasource and datatype page, specify the following fields:
   - In the Select Datasource type field, select SQL Query.
   - In the Select datatype field, select one of the following options:

   • Performance or Configuration metrics for a set of systems (any type) 

   • Business driver metrics 

   • Catalog of entities and relationships (OBJREL)

   For more information about the mandatory columns for the selected data type, see Dataset reference.

5. Click Next.

6. For Catalog of entities and relationships (OBJREL) data type, complete the following steps:

   In the Wizard Page	Description and Action
   SQL connection and entity query	In the SQL query box, type the SQL query for extracting entities and then, select the connection parameters from the Datasource and Scheduler lists.
   Entity column mapping	Displays the mapping of data available in the data source with the BMC Helix Capacity Optimization entities. For more details about column mapping, see Column mapping for Open ETL template. If you want to modify the default mapping of an entity, see To modify the default mapping.
   Relationship query	In the SQL query box, type the SQL query for defining the relationship between entities.
   Relationship query mapping	Displays the mapping of data available in the data source with the BMC Helix Capacity Optimization entities. For more details about column mapping, see Column mapping for Open ETL template. If you want to modify the default mapping of an entity, see To modify the default mapping.

7. For Performance or Configuration metrics for a set of systems or Business driver metrics data type, complete the following steps:

   1. On the Entity type identification page, select the required entities from the Available table, and add them to the Selected table using the >> button. In the Selected table, select the required entities, and click << to remove it.

      Entities on this page are listed according to the data type that you selected on the Datasource and datatype page. If you do not select any entity, all entities are available for mapping data with the columns in the data source.

   2. Click Next.

   3. In the SQL connection and query page, specify the following information:

      Field	Description
      SQL query	Edit the sample query available in the box or clear the sample query and type the required query to select the data from the required database. Note: Follow the guidelines in the UI to create the required query. Additionally, you can perform the following tasks: Extract the minimum and maximum values of a metric by populating MINVALUE and MAXVALUE columns Specify the lookup information in the DS_SYSNM field for system entities or in the DS_WKLDNM field for business driver metrics
      Datasource	Select the required data source from the list.
      Scheduler	Select the required scheduler from the list. For more details about schedulers, see Administering schedulers.

   4. Click Next.

   5. In the Column mapping page, review the mapping of data available in the data source with the BMC Helix Capacity Optimization entities. For more details about column mapping, see  Column mapping for Open ETL template.

      The Messages table provides the following information:

      • Indicates whether all columns are mapped or not.
      • If there is any unmapped column, a Critical or Warning message is displayed.
      • A brief description of the message.
      • A link to view the details of the message.

   6. If a Critical or Warning message is displayed in the Messages table, modify the mapping for the column that has the error or warning.

      Click Explain to review the details of the Critical or Warning message and to view the suggested solution. For Duration or DS_SYSNM columns, you can specify the default values.

   7. To modify the default mapping of an entity, complete the steps given in To modify the default column mapping.

8. Click Next.
9. On the Name and description page, type Name and Description for the Open ETL template.

10. Click Finish. The new Open ETL template is saved.

To edit an Open ETL template

1. Select Administration >  ETL & SYSTEM TASKS > ETL tasks.
2. On the ETL tasks page, click Add > Add/Edit Open ETL Template.
   The Open ETL Template Wizard is displayed.
3. On the Operation page, select Edit an Open ETL Template and select the required template from the list.
4. Click Next.

5. On the Datasource and datatype page, review the information and click Next. You cannot modify the options selected on the page.

6. On the Entity type identification page, select the required entities from the Available table, and add them to the Selected table using the >> button. In the Selected table, select the required entities, and click << to remove it.

   Note

   • If you do not select any entity, all entities are available for mapping data with the columns in the data source.
   • If you add additional entities or remove entities that are selected, column mapping is updated in the Column mapping page.

7. Click Next.
   Depending on the selected data source, the Upload CSV file page or the SQL Connection and Query page is displayed. You cannot modify the options selected on this page.

8. Click Next.

9. On the Column mapping page, review the mapping of data available in the data source with the BMC Helix Capacity Optimization entities. For more details about column mapping, see Column mapping for Open ETL template.

10. To modify the default mapping of entities, click Edit for the required column and modify the properties.

11. Click Next.
12. On the Name and description page, modify the Name and Description, if required.

13. Click Finish. The Open ETL template is modified and saved.

To modify the default column mapping

You can modify the default mapping of an entity in the Column mapping page of the Open ETL wizard.
For the entity, click Edit in the column and complete the following steps in the Edit column mapping dialog box:.

1. Ensure that the Select type field displays the type of the entity that you chose to edit.

2. If the value in Select type is METRIC- Metric, specify the following properties:
   

   Property	Description
   Select metric	Displays the metric that you want to map to the original or imported column
   Selected metric unit	Displays the unit of the selected metric as defined in BMC Helix Capacity Optimization. For example, PCT for percentage.
   Format	Enables you to specify the format of the selected metric unit. - For percentage metric: Specify the range: 0-1 or 0-100. By default, the format is 0-1. You can apply the selected range to all percentage metrics.  - For metric with unit mapping: Select the metric unit of the data source from the Datasource Unit list. If the metric unit is not available in the list, select Other and then, provide a value in the Multiplier field. The multiplier value is used to convert the data source metric to the selected metric unit.  If you want to apply the same metric unit conversion to other datasource metrics with the same unit, select Apply conversion to all the metrics in '<Selected metric unit>'. - For metric without unit mapping: Specify the multiplier value for converting the data source metric to the selected metric unit. If you want to apply the same multiplier value to other datasource metrics with the same unit, select Apply conversion to all the metrics in '<Selected metric unit>'.

3. If the value in Select type is TS - Timestamp, in the Format field, specify the required date format. You can choose to automatically recognize the format or specify a custom format string.

4. Click Apply.

Where to go from here

Generic ETL based on a template

Related topic

 Open ETL templates