Setting up an ETL configuration

In the IT Advisor ETL solution, just about any piece of information can be in one of four different states at any point in time:   The states are: NEW, CHANGED, DELETED, SYNC_DELETED and SYNCED.  In the typical life-cycle, information starts in the NEW-state.   IT Advisor will pick up the information as new, interpret the information and add it to the IT Advisor model. Once the information has been read into IT Advisor, it will be moved to the SYNCED-state.   When the information is updated from the external system, the state of the information should be set to CHANGED.  When IT Advisor synchronizes its information, the changes will be reflected in the IT Advisor model. If the external system decides to remove a piece of information it should be moved to the DELETED state. IT Advisor will subsequently handle cleanup of the information inside IT Advisor and report back the state SYNC_DELETED.    The state-system is primarily used to speed up the process of synchronizing data from the staging database to the IT Advisor internal database. Only data/information in the NEW or CHANGED state are expected to contain information that is not already known by IT Advisor, so only those are read and synchronized. Note: Information in the SYNCED, SYNC_DELETED, and DELETED states will not be read by IT Advisor, and subsequently changes to the information will not be updated into IT Advisor.    Tracking Data Information State in Export Data   The export database uses the same strategy for keeping track of the state of data, as the import database, with the one exception, that the data will never be marked as synced.   The red arrows represent state-changes made by the external system, either through direct data manipulation or through a transformation. The green arrows represent state-changes made by IT Advisor as part of the synchronization of information.       

There are three parts to setting up an ETL configuration:   Setting up a database where you can either import data from or export data to. The import and export database can be located either Internal in IT Advisor or in an External database setup. Both configurations are done from the same configuration interface. Setting up the ETL transformation, that moves the data either from the database to IT Advisor (import) or from IT Advisor to the database (export). Setting a schedule for when the configuration should run.   To start setting up an ETL configuration login to web client with a user with the 'System Administration' permission, and navigate to Cogwheel > Manage ETL Integration.   Internal Database   An Internal Database is a PostgreSQL instance running on the same server as IT Advisor. The database is created and deleted by IT Advisor. To create an internal database, you must provide the following:   Configuration Name - the name that will later be shown inside the IT Advisor client to distinguish between different databases, when configuring External Systems. Database Type - databases are used for either Import or export of data Database Name - the unique name used when creating the database. Import databases are always prefixed with 'staging_' and Export databases with 'export_' Username - unique username. The username is prefixed by 'etluser_' Password - password of the user. No special requirements. Allowed IP addresses - When integrating with external systems, it may be necessary to allow direct database access to the database. It is mandatory to enter the specific ip or range of ip addresses allowed to access the database. Information regarding IP ranges can be found here   Note: Double byte characters are not supported in the configuration of the database     Once saved, the database is created and will subsequently be available inside the IT Advisor client.   The internal database connection can be paused/started using the pause/start management button. This can be used in order to pause/start the scheduled communication with the database. Pressing start will not force the communication to run but enable the scheduled communication to run according to schedule.   External Database   An External Database is located outside of the IT Advisor server. We have tested with PostgreSQL, MS Sequel Server and MySql database types and generally the only requirement is the existence of a JDBC4 compliant database driver. The database must be created, deleted and managed outside of IT Advisor. The database must be set up using a UTF8 character encoding.   The same UI is used to create a configuration defining how to access the external database, and must include the following:   Configuration Name - the name that will later be shown inside the IT Advisor client to distinguish between different databases, when configuring External Systems. Database Type - databases are used for either Import or export of data Database Driver - choose what JDBC driver to use when connecting to the database. If the required driver does not appear in the list, you can upload a custom driver for the connection to your external database following the guidelines here. Database Dialect - some databases speak several  different dialects, choose the one the matches your setup. Database Name - the name used when connecting to the database. External databases are never prefixed. Port Number - specify what port is used to communicate with the external database. Please note that it may be necessary to open ports in firewalls between the IT Advisor server, and the server hosting the remote database. Username - unique username. The username is prefixed by 'etluser_' Password - password of the user. No special requirements. Database URL - entered values from the previous fields are combined into a database URL, if extra parameters are needed, they may be inserted into the URL.   Note: Double byte characters are not supported in the configuration of the database.     Once saved, the configuration is created and will subsequently be available as an external database inside the IT Advisor client.   The internal database connection can be paused/started using the pause/start management button. This can be used in order to pause/start the scheduled communication with the database. Pressing start will not force the communication to run but enable the scheduled communication to run according to schedule.   Database Driver Upload   If you need to use a custom driver for the connection to your external database, please follow the guidelines found here.   Configuring the transformation and synchronization schedule   After setting up the database click Next, and choose the wanted transformation or upload one, and select related Transformation Parameter(s) to be used for the ETL integration.   With ETL import databases it is also possible to specify whether name or serial number should be imported from ETL asset to the associated item in ITA. You can import the name in 2 ways:   From the name of ETL asset. The value is imported from the column ci_name in the configuration_item table. From the asset property of the ETL asset. You must specify the name of the property, which is imported from the cip_name column in the configration_item_property table. Only properties of type 4 are imported (meaning the row must have pt_id = 4 = 'ASSETPROPERTY').   Serial numbers are imported from the column ci_serial_number in the table configuration_item.   Click Next to move to configuration of the schedule. The ETL configuration will synchronize data with a given interval, daily, weekly or monthly depending on the schedule defined here.   Scheduling or pausing ETL Integrations is done from Web client > Cogwheel -> Manage ETL integrations.   Example for setting up an import database   Web client > Manage ETL integrations - Import         Example for setting up an export database   ETL Integration Management - External System Configuration - Export            

In an ETL configuration, allowed IP addresses that can access the database can be configured as:   List   10.1.1.1,10.1.1.2,10.1.1.3 (comma separated)   Range   10.1.1.1-10.1.1.30 (all IP addresses between 10.1.1.1 and 10.1.1.30)   Mask   10.1.1.0/24 (all IPs in the sub network 10.1.1.x) 10.1.0.0/16 (all IPs in the sub network 10.1.x.x) 10.0.0.0/8 (all IPs in the sub network 10.x.x.x) 0.0.0.0/0 (all IPs - no restriction)      See Subnet mask cheatsheet

The import and export database can be located either Internal on the EcoStruxure IT Advisor or in an External database setup (server, etc.). Both configurations are done from the same configuration interface.   Configuration and management of ETL databases is done using the server configuration interface.   Internal Database   An Internal Database is a PostgreSQL instance running on the same server as IT Advisor. The database is created and deleted by IT Advisor. To create an internal database, you must provide the following:   Configuration Name: The name that will later be shown inside the IT Advisor client to distinguish between different databases, when configuring External Systems. Database Type: Databases are used for either Import or export of data Database Name: The unique name used when creating the database. Import databases are always prefixed with 'staging_' and Export databases with 'export_' Username: Unique username. The username is prefixed by 'etluser_' Password: Password of the user. No special requirements. Allowed IP addresses: When integrating with external systems, it may be necessary to allow direct database access to the database. It is mandatory to enter the specific IP or range of IP addresses allowed to access the database. Information regarding IP ranges can be found here   Note: Double byte characters are not supported in the configuration of the database     Once saved, the database is created and will subsequently be available inside the IT Advisor client.   The internal database connection can be paused/started using the pause/start management button. This can be used in order to pause/start the scheduled communication with the database. Pressing start will not force the communication to run but enable the scheduled communication to run according to schedule.   External Database   An External Database is located outside of the IT Advisor server. We have tested with PostgreSQL, MS Sequel Server and MySql database types and generally the only requirement is the existence of a JDBC4 compliant database driver. The database must be created, deleted and managed outside of IT Advisor. The database must be set up using a UTF8 character encoding.   Webmin is used to create a configuration defining how to access the external database, and must include the following:   Configuration Name - the name that will later be shown inside the IT Advisor client to distinguish between different databases, when configuring External Systems. Database Type - databases are used for either Import or export of data Database Driver - choose what JDBC driver to use when connecting to the database. If the required driver does not appear in the list, it can be uploaded using the Upload database driver functionality Database Dialect - some databases speak several  different dialects, choose the one the matches your setup. Database Name - the name used when connecting to the database. External databases are never prefixed. Port Number - specify what port is used to communicate with the external database. Please note that it may be necessary to open ports in firewalls between the IT Advisor server, and the server hosting the remote database. Username - unique username. The username is prefixed by 'etluser_' Password - password of the user. No special requirements. Database URL - entered values from the previous fields are combined into a database URL, if extra parameters are needed, they may be inserted into the URL.   Note: Double byte characters are not supported in the configuration of the database.     Once saved, the configuration is created and will subsequently be available as an external database inside the IT Advisor client.   The internal database connection can be paused/started using the pause/start management button. This can be used in order to pause/start the scheduled communication with the database. Pressing start will not force the communication to run but enable the scheduled communication to run according to schedule.   Database Driver Upload   When using external databases of other types than postgreSQL, it may be necessary to upload a JDBC driver to the IT Advisor server. Note: The JDBC driver must be Java 6 compatible.   This is done from the server configuration interface EcoStruxure IT Advisor->ETL Integration Management in the tab Upload JDBC Driver.   The required JDBC driver should be downloaded from the database vendors support site (or other trusted support site), and stored locally on the client computer.   After upload completes, the IT Advisor server must be restarted.   See an example using MS SQL Server here.

Create a database in MS SQL   Create a database in MS SQL with a case sensitive collation. Collations in MS SQL Server includes ‘cs’ in their name if they are case sensitive; and ‘ci’ if case insensitive.   This is important because IT Advisor internally keys are allowed that only differ in upper case/lower case, for example, ‘MyProp’ is different from ‘myprop’. Such keys would result in primary key duplication errors if the MS SQL Server database is not using a case sensitive collation.   Set up the ETL export database   Set up the ETL export database in the web client, Configuration > Manage ETL Configurations.     Make sure you have not defined any tags or custom properties that only differs in a trailing space, for example, “my key” and “my key “. Note the trailing space in the last key.   This is important, because internally such keys are seen as being different. MS SQL Server sees them as being equal, which will result in a duplicate key error. For more information on how MS SQL Server compares strings with trailing spaces see http://support2.microsoft.com/kb/316626 .   If you have two tags or two custom properties with the same name except for trailing spaces, you can merge them from the client. Since trailing spaces are invisible in the user interface, you might want to start the merge by renaming the key you want to remove. For example, rename “my key “to “my key_”.