erwin Data Transformation 9.2.6 - User Guide

Custom Transformation

Custom Transformation

If it is required to apply a custom transformation the user must define a specific XSLT Library using the XSLT Library Configuration Tool (see before).

The user may define a “CC Uncompliant” or “CC Compliant” (compliant to CC schema) XSLT Library item.

 “CC compliant” transformations are instead not subject to any constraint (besides adhering to CC internal XML schema) when used in workflow transformations.

A number of “CC uncompliant” transformations are included, producing a formatted workflow:

•CC2CSV: produces a csv output file

•CC2HTML

•CC2EXCEL: produces a MS Excel compatible file via XSLT, so that it can be slightly customized

•CC2MSEXCEL: produces an XLSX file through Excel API, not configurable

•CC2OGFF: Produces an Open Group File Format XML output file. Note that this additionally requires the application of a fixed value field (OGFF_Type) specifying the target object type.

A special third type is “CM Compliant” transformations, which means that the output file is an XML ready to be consumed by the import module CM4Collector (i.e. describing target model, operation, objects metamodel, identity constraints, maps and object instances to be uploaded). Using that one, all the user interface setting for operation will be skipped at workflow execution time.

 It’s possible to insert more than one transformation in the same workflow of the same type or of different types. The transformation defined in a workflow will be displayed in the section “Transformation” in the left middle section of the Workflow window.

It’s possible to select one of these transformations to delete it; if the user deletes a transformation in a workflow, the mapping rules applying to the fields that are no longer required (i.e. created by that transformation) will also be deleted.

Operation

Operation

To define the operation of a Workflow follow the steps in the section “Mapping and Operation”:

•Select the operation in the “with operation” drop-down list.

•Optionally, provide the email configuration parameters

According to the rules of the operation type, the Mapping and Operation section shows the requested parameters as described below.

Load in CM Repository

Load in CM Repository

In the “Model Name” field you can see the model linked to the selected configuration.

The user may proceed with the mapping operation in the mapping table, having the requested parameters described in the following list:

Column Name: shows the columns coming from the data source and from the transformation steps; a sequence number that the system assigns to each data source can be seen as prefix to the column name, so the user can easily recognize the source of the data, in the case of multiple data sources.

Column type and format: choose the type and format of the column of the source (String, numeric, boolean, date). This is important if user wants DT to perform a re-format operation on the target column data type.

In particular, to properly import date fields with DT, it’s important to follow the following rules.

First, it’s helpful to configure the query/file in order to have preformatted source data.

In particular, dates coming from data sources that are mapped onto CM datetime property types have to be one of the following:

•DD/MM/YYYY i.e. 31/08/2016 (EUR FORMAT)

•MM/DD/YYYY i.e. 08/31/2016 (USA FORMAT)

•YYYY-MM-DD i.e. 2016-08-31 (UTC FORMAT)

while time, when provided, has to be mandatory in the format:

•HH24:mm:ss

This input date format has to be set in workflow mapping step:

Model object type: choose the model object type to load the data; it’s possible to select different object types in the same operation

Mapping (prop./assoc.): once you have selected the object type you can map the property or the association between the ones defined in CM for that object type.

ssociation between the ones defined in CM for that object type.

Type: shows the type of the mapped column of the target (String, numeric, boolean, date)

Action: user can now choose what to do for a single attribute/association mapping. This means that:

•For multiline property: the user can choose between APPEND, REPLACE or EMPTY model values

•For other data type properties: the user can choose EMPTY model values (not for Name or ID)

•For association type: user can choose between MERGE, REPLACE or EMPTY model values

•For property types:

oUUID

oCreated By

oCreation Date

oUpdated by

oUpdated Date

user is requested to choose between two Actions: KEEP TARGET (default) and KEEP SOURCE.

KEEP TARGET: target object property is maintained regardless of what is mapped from the user (the property is skipped)

KEEP SOURCE: target object property is overridden with source property

UUID, Creation Date and Created By properties cannot be updated.

KEEP SOURCE action should be used in a federated models environment and is also supported by Synch in CM Repository

Unique Key: users must check, for all the mapped object types, which set of columns must be considered as the unique keyset when performing the upload of the data. Keep in mind that this setting will work according to the usual Corporate Modeler behaviour, where “Name” has to be unique in the object type instances list, while mapping ID or Unique ID allows object name to be updated. For example, the user may check “Name” as key, and all other properties/associations of an existing object with that name will be updated, or a new object with that name will be created by DT if it is not already in use, or will concatenate it with a sequence number. 

Any other mapped column can be included in the keyset, without the “name” – in this case, if an object is identified by the configured keyset, DT will try to update the name with the uniqueness rules described above.

No New: when importing data from an external source, it’s possible that the master list of objects involved in the operation is the one contained in the model. In such a case, the user would want existing objects to be updated (only for the properties used in mapping), but no new objects to be created. If so, user may choose to check “No New” option, on the keyset of the master object type, and this will prevent new objects to be created, while existing will be updated within bounds of mapped properties. The same applies when the object list which has not to be extended is the one related to an object type associated with the master, involved in the operation. In such a case, the user may choose to check the “No New” option, on the record related to the association, and this will prevent new associated objects from being created.

As a result for this operation, data coming from the source adapter will be transformed as defined in “Transformation” step and uploaded into specified model according to the defined mapping rules.

Some additional requirements apply to specific kind of operations, as follows.

          1. In case of “All Objects” type of associations, differently from other type of associations, the target object type has to be specified, allowing a proper execution of data import. To do this, after mapping a source column to an association to all object, it is then mandatory to map one more specific column:

•TARGET_OBJTYPE: scriptname of the object type at the other side of the “all object“ association with the same name, which user wants the objects to belong

To help this mapping, the tooltip of Model Object type, when selecting one, is equal to the SCRIPTNAME of the selected.

This can be added in the source with a fixed value field, with scriptname value, to be then used in mapping.

          2. In case of multiple data source, it is implied that the different dataset, alternatively:

Have to be joined, if each of them contains a subset of properties/associations information for the same Object Type. If so, the “Name” property of the object type must be mapped onto each dataset resulting in multiple “Name” mappings – this will be the field used to perform the join operation at runtime on the actual data.

Have to be used to map on different object types; if so, “Name” property must mapped only once for each Object Type.
 

          3. In case of association type, when the user wants, for instance, to import the association’s properties, it is mandatory to map four specific columns:

•CCAboveName: name of the object instance at one side of the association (the one defined as “Source Object Type” at design time)

•CCBelowName: name of the object instance at one side of the association (the one defined as “Target Object Type” at design time)

•CCAboveTableName: scriptname of the object type at one side of the association (the one defined as “Source Object Type” at design time)

•CCBelowTableName: scriptname of the object type at one side of the association (the one defined as “Target Object Type” at design time)

To help the last two mappings, the tooltip of Model Object type, when selecting one, is equal to the SCRIPTNAME of the selected.

          4. In case of Users or User Group import operation, which is allowed using the Admin Model configuration, the operation, will be executed within the following conditions:

•New users can be created, if:

oUser Name and Logon Name are both unique - records which are uncompliant to this Corporate Modeler requirement are skipped at import time, and logged into the operation log file

•Password cannot be provided

•Power Level has to be provided, in a numeric form, and is documented by a tooltip:

oUsers - 1 (System Manager), 2 (Project Manager), 3 (Normal User), 4 (Read Only User)

•No new user groups are going to be created; user can only associate users to existing user groups

•The operation key has to be one and only one of the following fields:

•User Name – in that case, for existing objects, fields can be updated, except for Logon Name, Power Level and Password

•Logon Name – in that case, for existing objects, name and other fields can be updated, except for Power Level and Password

•CW ID - in that case, for existing objects, name and other fields can be updated, except for Logon Name, Power Level and Password

Load in CM Repository - Migrate diagrams between CM Models

Load in CM Repository - Migrate diagrams between CM Models

The “Load in CM Repository” operation can be used to migrate Diagrams between CM Models. To enable this migration, both source and target model configuration must be created, and diagram migration utilities must be located into erwin EA (Corporate Modeler) bin folder, see Installation guide for instructions.

Then, create a workflow selecting the target model configuration, and select as the source model EA Adapter, Diagram object type as data source, selecting at least Name, Id, Unique Id fields:

Other fields, like Category, Parent Object/Parent object name etc can also be selected to be used to filter diagrams of interest, if desired.

Finally, select the “Load in CM Repository” mapping name, Id, Unique Id as mandatory fields, using Unique Id as key for the operation, with Action “Keep SOURCE” or “Keep TARGET” as desired:

All other mappings are unnecessary, since the diagram migration component will take care of moving diagram properties and associations, diagrammed objects and actual diagram image content (shapes, connectors etc)

The migration will actually work under the following conditions:

1.Workflow operation involves the object type DIAGRAM

2.CwImport.exe, CwSingleExport.exe, CwSingleExportAndImport.exe are found into Corporate Modeler bin folder (see Installation Guide)

3.Source and target models share the same design, at least for diagrammed objects

Please note that the diagrams already existing into target model, based on their UUID, are exported in XML (“Diagrams_DT_TARGET_BAK_YYYYMMDD_hh24mmssmillis.xml” into operation folder) for backup and rollback support, before being updated.