ETL integration examples

[JLehr]

These example templates can be uploaded to the server to import example data, typically from an Excel sheet. They provide a good starting point for developing your own transformations.

Network Ports and Network Connections Import Example

The files below show how port configurations and network connections can be imported with ETL.

To use the template:

1. Upload the files below to the /data/pentaho/import folder.
2. Create a new staging database.
3. Setup a new ETL Integration in the ITA client, and choose the "Network Import" job/transformation.
4. After the initial synchronisation has been done, associate the new devices to equipment.
5. Force the synchronisation to run again, or wait for the next scheduled synchronisation to run. The network ports and connections should be imported and added to the associated equipment.

The ETL integration examples.zip file attached below contains:

• Network Import.kjb 
• PortConfiguration.ktr 
• Network.ktr 
• ConfigurationItems.ktr 
• Connections.xls 

Import Examples

This section focuses on how to model real-life "scenarios" in the import database, and where the information is used/displayed inside EcoStruxure IT Advisor. 

Entity

To add a simple entity with only a few properties, only the CONFIGURATION_ITEM table is needed (Figure 1). The references to CONFIGURATION_ITEM_TYPE is used to identify the type of the items.

Figure 1

Simple Measurements

In slightly more complicated cases where simple measurements are associated with the ConfigurationItems, the following general data structure is needed. See Figure 2. This associates an average powerdraw of 193.5W to Odin (server_01). 

Figure 2

Advanced Power Measurements

More advanced power measurements can be modeled by using the cim_subid field. See Figure 3. The field cim_subid is used to group measurements – in this case phase power measurements from a breaker-module in a pdu.

The id used in cim_subid is used inside IT Advisor to enable branch circuit monitoring by matching the id to breaker names set up in IT Advisor.

Please note that you have to provide both an average and a peak power measurement (the example in Figure 3 is for the peak measurement). Also note that that you have to specify the start and end time for the measurement. The time is given in ms since January 1st 1970.

Figure 3

Temperature Sensors

To make it possible to use temperature measurements from external systems in the Cooling Simulations, they need to be modeled as shown in Figure 4. It is not necessary to provide all three different types of temperatures.
Each temperature measurement will result in one sensor inside IT Advisor, with the assigned temperature reading. The name of the sensor will be set to "temperatureSensor "+cim_subid. 

Figure 4

Alarm

Alarms are associated with Configuration Items, therefore both the CONFIGURATION_ITEM and ALARM table are needed. Because of the database-constraints on these tables, the Configuration Item needs to be updated before the Alarm. Alarms will show up in IT Advisor even if the external device is not associated with a DataCenterItem in the IT Advisor model. 

Figure 5

Tag

The data structure needed to map tags is shown in the diagram in Figure 6. 
This associates the tag "sales" to the server Odin (server_01). Once the external device (Odin) has been associated with an item in the IT Advisor model, the tag will be associated with that item. If the tag does not already exist, it is created with the cip_value as a description.

If the tag is marked deleted (state_flag set to "deleted"), the tag will be unassociated from the associated DataCenterItem. The tag itself will NOT be deleted from the IT Advisor model.

If the external item (Odin) is unassociated, the DataCenterItem will retain all tags, including those created through ETL. Subsequent changes in ETL to the tag are NOT reflected to the IT Advisor model. 

Figure 6

Custom Property

Custom properties are mapped in the same way as tags, only the classification (property type) differ. In this example (Figure 7) the property "owner=sales" is associated with Odin. Once the external item Odin is associated with a DataCenterItem, the CustomProperty "owner" will be created with the value "sales", and assigned to the DataCenterItem. If the property is marked deleted, it will be removed from the IT Advisor model.

If external item is unassociated, the DataCenterItem will retain all its CustomProperties including those created through ETL. 

Figure 7

Network Connection

Modeling of network connections is done by using CONFIGURATION_ITEM_RELATION and its related tables – most importantly the RELATION_ENDPOINT_PROPERTY table that is used to model information about network-ports.

The requested data-structure can be seen in the diagram (Figure 8). The properties related to the network port, is used inside IT Advisor to create PortConfiguration objects.

Among other things the PortConfigurations maintain a range of ports. The ETL synchronization job will try to make the smallest number of PortConfigurations for a piece of network equipment, by using port-ranges. To aid in this, the module-name is used for grouping. 

Whenever module_name and connector_type match, the range of existing ports will be extended to include new ports (otherwise outside of range) -> you can't have the same module split in two different ranges eg. 1-15 and 16-32

Network connections and port configurations are only added to the IT Advisor model if both ends of the relation are associated with data center items. 

The communication speed that a network-port can handle should be provided with one of the units "Kbps", "Mbps", "Gbps" or "Tbps". 

The port type must be one of the following: FIBER_REGULAR, FIBER_UPLINK, RJ45_REGULAR, RJ45_UPLINK, APC_LAN_REGULAR, APC_LAN_UPLINK, IP_KVM_UPLINK, IP_KVM_REGULAR, FIBER_SC_REGULAR, FIBER_SC_UPLINK, FIBER_LC_REGULAR, FIBER_LC_UPLINK, FIBER_ST_REGULAR, FIBER_ST_UPLINK, FIBER_MTRJ_REGULAR, FIBER_MTRJ_UPLINK, FIBER_FC_REGULAR, FIBER_FC_UPLINK, FIBER_MPO_MTP_REGULAR, FIBER_MPO_MTP_UPLINK, OTHER_REGULAR, OTHER_UPLINK. 

Figure 8

Note the following when importing network connection data:

1. The external items must be associated with equipment, and the association must be saved to the server, before the network connections can be synchronized.
2. If adding or updating information on a RELATION_ENDPOINT_PROPERTY row the corresponding CONFIGURATION_ITEM_RELATION must be marked 'new' or 'changed' in the staging-database.
3. Data for each of the five specific endpoint property types must specified for each relation endpoint property mentioned in the RELATION_END_PROPERTY table.