REWOO Scope manuals

ETL

Description

REWOO Scope provides an interface for importing data from other systems directly into REWOO Scope and mapping it to the structure there. Any new structures that may be required for this can be created both by copying templates and by deriving them from an existing type. The changes from the previous systems are transferred via changesets in CSV files. Groovy scripts can be stored for further transformation of the data into a REWOO-compliant structure.

Existing data in REWOO Scope can also be queried in these scripts.

A unique ID must be defined for each upstream system - e.g. myCRM. Together with an ID for each record that is unique for this system - e.g. REWOO customers - this results in an installation-wide unique external ID for this record: myCRM REWOO customers. So once an item has been made known to the Scope ETL, care must be taken to ensure that both system ID and external ID are identical for subsequent records to identify the item. Also, when using the external ID as the context ID, the system ID is used to determine the context element.

If the external ID has not yet been used and is therefore unknown to REWOO Scope, the record is assigned by name and type if it exists, or derived from type or template if it is new. The Action Type can be used to control how REWOO Scope should react to these situations.

If the external ID is known, the name and type are ignored, the element is identified and the associated data is updated.

Pre-system specific configuration

The following parameters must be defined for each pre-system.
The name of the system:
etlconfig.example.system = Example
An ETL user with this name is created and is entered as the author of all values resulting from an ETL import.
The password of the ETL user
etlconfig.example.password = oElxW3$
Optionally a Groovy script to change or add to the import
etlconfig.example.transformscript = ./transform_example.groovy
The script must be placed in the rewoo-admin/storage/rewoo/etl/conf folder.
The directory where the ETL files are stored
etlconfig.example.inputdir = ./spool
This directory is relative to rewoo-admin/storage/rewoo/etl/spool
The file extensions of the ETL files
etlconfig.example.inputfiles = txt,TXT
One or more comma-separated file extensions can be specified here to identify the import files. Case sensitive, i.e. csv and CSV are two different extensions.

Input format

The changesets have the format: 
(int; int; int; long; String; String; String; String; String; String)
The fields of the changesets have the following meaning:

general

  1. id (int primary key not null): consecutive primary key across all changesets
  2. actionType (int not null): Action that applies to this changeset
    • CREATE_MODIFY(1) Element is created if it does not exist, otherwise changed
    • COPY_MODIFY(3) Element is copied from a template if it does not exist, otherwise modified
    • MATCH(4) Element exists, used to map from the external ID to existing, real elements; templates cannot be made known.
    • ADD_FILE(5) only for file lists: a file is appended to an existing list. If a file with the same name exists in this list, it will be replaced.
    • CHANGE_STATE(6) Element changes its status.
    • CHANGE_LAYOUT(7) element changes its form layout.
    • CHANGE_ROLE_SET(8) Element changes its role set.
  3. rewooType (int not null): one of
    • OBJECT(1)
    • PROCESS(2)
    • ASPECT(3)
    • NODE(4)
    • VALUE(7)
    • CONNECTION(8)
  4. timeStamp (long not null): timestamp of the change; the timestamp must always be greater than or equal to the timestamp of the context (Format: the number of milliseconds since January 1, 1970, 00:00:00 UTC)
  5. systemID (String not null): unique name of the upstream system
  6. externalID (String not null): unique identifier regarding the previous system, defined by the ETL and used for identification in case of changes or removal or for defining the context.

actionType = CREATE_MODIFY and rewooType = OBJECT, PROCESS, ASPECT, NODE

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • for OBJECT and PROCESS the context is always null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. modelType (String not null): Name of the type that will be instantiated
  3. name (String not null): Element name
  4. layoutName (String): Layout name

actionType = COPY_MODIFY and rewooType = OBJECT, PROCESS, ASPECT, NODE

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • for OBJECT and PROCESS the context is always null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. templateName (String not null): Name of the template that will be copied
  3. name (String not null): Element name
  4. copyStrategy (optional, String, default: CONNECTED): Strategy, which connected elements should also be copied; corresponds to the strategy of the COPY function; possible values: SINGLE, CHILDREN, CONNECTED

actionType = MATCH and rewooType = OBJECT, PROCESS, ASPECT, NODE

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • for OBJECT and PROCESS the context is always null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. name (String not null): Element name

actionType = CHANGE_STATE and rewooType = OBJECT, PROCESS, ASPECT, NODE, CONNECTION

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • for OBJECT and PROCESS the context is always null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. state (String): Name of the status, the element should have:
    • ACTIVE
    • ON_HOLD
    • CLOSED
    • ARCHIVED
    • REJECTED

actionType = CHANGE_LAYOUT and rewooType = OBJECT, PROCESS, ASPECT, NODE, CONNECTION

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • for OBJECT and PROCESS the context is always null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. layout (String): Name of the new layout

actionType = CHANGE_ROLE_SET and rewooType = ASPECT, NODE

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. role set (String): Name of the new roll set or empty if the roll set is to be removed.

actionType = CREATE_MODIFY and rewooType = VALUE

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • for OBJECT and PROCESS the context is always null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. valueType (String not null): String, Number, Date, Image, ExternalLink (http://...), Email (...@mail.net), Choice (Selection, is stored as a string, the selection options are in the layout), Boolean (true or false), Condition
  3. name (String not null): Name of the field on the form
  4. value (String not null): the actual value

actionType = ADD_FILE and rewooType = VALUE

  1. externalContextID (String): the external ID of the context if known, otherwise null
    • for OBJECT and PROCESS the context is always null
    • in ASPECT the context is the object or the process
    • in NODE the context is the parent node or the aspect
  2. valueType (String not null): FileLinks
  3. name (String not null): Name of the file field on the form
  4. value (String not null): the absolute path to the file (if a file with identical name exists in the FileLinks field, it will be replaced; otherwise the file will be added to the list)

actionType = CREATE_MODIFY and rewooType = CONNECTION

(other ActionTypes are not possible for relationships)
  1. externalID Node A (String not null): the external ID of the node where the relationship begins
  2. correlationType (String not null): Name of the connection type
  3. rights (String not null): two characters for the rights from the start node to the destination node and vice versa
    • O (Null) no rights
    • R read rights
    • W read and write rights
    • A Rights for model changes
  4. externalID Node B (String not null): the external ID of the node where the relation ends

Restrictions

Changes to REWOO types, templates and messages are not possible for the time being.

Example

Note: Comments cannot be inserted in the CSV files. In the example, the comments are inserted afterwards to explain the content.
1. an already existing object is selected for the ETL
"1";"4";"1";"0";"myCRM";"REWOO";"";"REWOO";"";""
2. the aspects of REWOO are selected for the ETL to be able to use them as context.
"2";"4";"3";"0";"myCRM";"REWOO Customern";"REWOO";"Customern";"";""
"3";"4";"3";"0";"myCRM";"REWOO Employee";"REWOO";"Employee";"";""
3. a node for a customer is inserted or updated in the "Customers" aspect (line 2).
"4";"1";"4";"0";"myCRM";"Customer 983";"REWOO Customern";"Customern";"Customer 983";""
"5";"1";"7";"0";"myCRM";"Customer_KU_NR";"Customer 983";"String";"AG-No.";"983"
"6";"1";"7";"0";"myCRM";"Customer_KU_NAME";"Customer 983";"String";"AG-Name";"VisionHolz AG"
"7";"1";"7";"0";"myCRM";"Customer_KU_COUNTRY";"Customer 983";"String";"Country";"D"
"8";"1";"7";"0";"myCRM";"Customer_KU_ZIP";"Customer 983";"String";"ZIP";"76149"
"9";"1";"7";"0";"myCRM";"Customer_KU_CITY";"Customer 983";"String";"City";"Karlsruhe"
"10";"1";"7";"0";"myCRM";"Customer_KU_STREET";"Customer 983";"String";"Street";"Am Sandfeld 16"
"11";"1";"7";"0";"myCRM";"Customer_KU_DATE";"Customer 983";"Date";"Creation date";"17.12.2008"
"12";"1";"7";"0";"myCRM";"Customer_KU_CONTACT";"Customer 983";"String";"Contact";"Herr Dres"
"13";"1";"7";"0";"myCRM";"Customer_KU_PHONE";"Customer 983";"String";"Phone";"07215686656"
"14";"1";"7";"0";"myCRM";"Customer_KU_CURRENCY";"Customer 983";"String";"Currency";"Euro"
4. a new object is created with the help of a "Project" template
"15";"3";"1";"0";"myCRM";"Projekt 125";"";"Projekt";"Projekt 125";""
"16";"1";"7";"0";"myCRM";"Projekt_PJ_NR";"Projekt 125";"String";"Projectno.";"125"
"17";"1";"7";"0";"myCRM";"Projekt_PJ_NAME";"Projekt 125";"String";"Projectname";"Sitzgruppe Gutholz"
"18";"1";"7";"0";"myCRM";"Projekt_PJ_BEGIN";"Projekt 125";"Date";"Project begin";"01.02.2011"
"19";"1";"7";"0";"myCRM";"Projekt_PJ_END";"Projekt 125";"Date";"Project ende";"25.04.2011"
"20";"1";"7";"0";"myCRM";"Projekt_PJ_MANAGER";"Projekt 125";"String";"Project manager";"Dres"
5. the aspects of this project are selected for the ETL to be able to use them as context
"21";"3";"3";"0";"myCRM";"Project management 125";"Projekt 125";"Project management";"Project management";"Max Neumann"
"22";"3";"3";"0";"myCRM";"Project customer 125";"Projekt 125";"Customer";"Customer";""
6. a node for a project customer is introduced or updated in the aspect "Project customers
"23";"1";"4";"0";"myCRM";"Client 125 983";"Project customer 125";"";"VisionHolz AG";""
7. a connection is created between the project customer (line 23) and the customer under REWOO (line 4) 
"24";"1";"8";"0";"myCRM";"Customer project 125 983";"Order 125 983";"Customer project";"R";"Customer 983"
8. the node of the project manager is made known to the ETL (MATCH)
"25";"4";"4";"0";"myCRM";"Project management 193";"REWOO Employee";"Neumann, Max";"";""
9. a relationship is created between the employee under REWOO (line 25) and the project management (line 21) 
"26";"1";"8";"0";"myCRM";"Project management 193 125";"Project management 193";"Project management";"W";"Project management 125"
10. a new process is created using the template "Order templateFurniture construction
"27";"3";"2";"0";"myCRM";"Order 471";"";"OrdersvorlageMöbelbau";"Order 471";""
"28";"1";"7";"0";"myCRM";"Order_AB_ID";"Order 471";"String";"Order id";"471"
"29";"1";"7";"0";"myCRM";"Order_AB_VERSIONSNO";"Order 471";"String";"Order version";"1"
"30";"1";"7";"0";"myCRM";"Order_AB_DATE";"Order 471";"Date";"Order date";"01.02.2011"
"31";"1";"7";"0";"myCRM";"Order_AB_DONE";"Order 471";"Boolean";"done";"true"
"32";"1";"7";"0";"myCRM";"Order_AB_DONEAT";"Order 471";"Date";"done at";"20.02.2011"
"33";"1";"7";"0";"myCRM";"Order_AB_PJ_NO";"Order 471";"String";"Project No.";"125"
"34";"1";"7";"0";"myCRM";"Order_AB_AMOUNT";"Order 471";"Number";"Order amount";"8937.50EUR"
11. the aspects of the order are made known to the ETL in order to be able to use them as context
"35";"3";"3";"0";"myCRM";"Controlling 471";"Order 471";"Controlling";"Controlling";""
"36";"3";"3";"0";"myCRM";"Client 471";"Order 471";"Client";"Client";""
12. a relationship is created between project management (line 21) and controlling (line 28)
"37";"1";"8";"0";"myCRM";"Project controlling 125 471";"Project management 125";"P-Controlling";"W";"Controlling 471"
13. a relationship is created between the project customer (line 23) and the client (line 29)
"38";"1";"8";"0";"myCRM";"Order 983 471";"Client 125 983";"Order";"W";"Client 471"
14. the status of the node of the project manager is changed (CHANGE_STATE)
"39";"6";"4";"0";"myCRM";"Project management 193";"REWOO Employee";"";"";"ON_HOLD"
15. the role set of the node of the project manager is changed (CHANGE_ROLE_SET)
"40";"8";"4";"0";"myCRM";"Project management 193";"REWOO Employee";"";"";"Project roles"

Bulk Operations - Manual Bulk Operations

Bulk operations provide a tool in the Admin Panel that allows manual changes to a large set of records without having to edit each record individually.

Possible operations

The following operations are available:

  • Change the value of a specific field (set value)
  • change state (change state)
  • Change layout (change layout)
  • change role set (change role set)
  • Copy a template (copy sketch)
Changing the value of a specific field (set value).

On all data sheets the specified field will be set with the specified value.

  • valueType - The field type of the field to be changed. Not all field types are supported. This field must be present on all records to which this operation is to be applied.
  • name - The name of the field to be edited. The name must be exact.
  • value - The new value to be written to this field.
Change state

The status of all selected records will be changed.

  • state - The desired destination state. Please note that logical inconsistencies do not occur. For example, no active element (ACTIVE) can occur under a template (IMAGINARY).
Change layout)

A specific existing layout is set for all selected records.

  • layout - The name of the layout that the elements should use. The name must be exact. The layout must be created for all selected elements in the type.
Change role set

A specific existing role set is set for all selected records.

  • roleSet - The name of the role set that the elements should use. The name must be exact. The role set must be created for all selected elements in the type.
Copy a template (copy sketch)

A new element from the specified template is created under all selected elements.

  • sketchName - Name of the template in the world. The location of the template in the world is irrelevant. For this reason, it is necessary that the template has a unique name.
  • elementName - Name that the created elements should have.

Selection of the records to be modified

In order to be able to perform an operation, a query must first be created that selects all elements to which the operation is to be applied. Both very simple and quite complex nested queries can be created to be able to select elements very specifically.

The following selectors are available:

  • Name - Selects all elements with a specific name
  • Status - Selects all elements with a certain status
  • Type - Selects all elements of a certain type
  • NodeType - Selects all elements of a certain level of a type
  • Value - Selects all elements on which a certain field has a certain value

Queries can be nested so that, for example, you can select all elements that are of type "Order", have status "ACTIVE" and on which the "Order value" field is greater than 0.

Restrictions for "Value"

Programmatically, certain rules must be followed for "Value" to find the elements correctly. These are as follows:

  • Boolean - The condition in English: true or false.
  • Condition - The condition in English: true or false.
  • Date - Must be entered in the syntax of the defaultServerLocale. For example for de_DE this corresponds to dd.mm.yyyy, but for en_US it corresponds to mm/dd/yyyy.
  • Number - The number must be exactly as it is stored in the database. Without thousand separator and unit, with decimal point instead of decimal point and with exactly the same number of decimal places. Therefore this request is only conditionally suitable for currency amounts.
  • TrafficLight - The color of the traffic light in English: green, yellow or red.
Execute selection

A click on "Search" executes the query and shows all matched elements. This allows you to quickly see whether the query selects all and only the desired elements, or whether further adjustments are necessary.

Making a change

1. create correct query

The first step is to create a query that selects exactly the elements to which the operation must be applied. For more information, see "Selecting the records to be modified".

2. Set action

The second step is to describe the action to be performed. For more information, see "Possible operations".

  • Pre-system ID - ETL user or user login to which this change should be attributed. It will appear in REWOO Scope afterwards as the author of the datasheet or the new datasheet version, respectively.
3. Generate CSV

After the elements have been selected and the action has been defined, the actual CSV must now be generated, which converts the operation into ETL syntax.

Alternatively, a CSV file stored on the computer can be loaded in via the "Load CSV" button.

4. Execute CSV

The action is executed by clicking on "Execute CSV". If the action is to be saved for later reuse or editing, this can be done by clicking the "Save CSV" button.

© 2024 REWOO Software GmbH
Terms & Conditions Privacy Imprint