erwin Data Transformation 9.2.3 - User Guide

Align External DB with Model Contents

Align External DB with Model Contents

The example describes the following use case: business service data, managed natively into a CE repository, must be loaded consistently in a MS SQL database, hosting a third party Service Catalogue.

Let us suppose that:

•Business service data are into the CM model, in a dedicated object type

•a Service Catalogue manages enterprise services data, stored in a MS SQL database table. The service table identifies the item with a “COD_APPLICATION” field, which is differentiated by the Erwin ID of the model for the same item:

A DT workflow aligning Business Services data from Erwin to the Service Catalogue database can be configured with the following steps:

•CREATE MODEL CONFIGURATION

Log in to DT and create a model configuration for the model involved in the use case

•CREATE DATABASE ADAPTER TO LOAD SERVICE CATALOG DB

Press “DB Adapter” button in Home Page and configure the DB Loader Adapter:

with the proper DB type, server, port, database name, user, password for the target

•CREATE WORKFLOW TO ALIGN SERVICE CATALOG DATABASE

Press the “Workflow configuration” button on the Home Page and configure the following workflow choosing the model configuration according to requirements (please note that the model configuration is mandatory for a given model, only for “Load, Sync or Delete” operation against that model)

SOURCE DEFINITION – Source 1

Choose CM Adapter and the specific CM Model Export adapter from the dropdown list; then pick the “Service” object type from the list and check the desired properties / associations from the “CM Filters” interface:

TRANSFORMATION 1 – Filter services at export time

To optimize the data flow, filter source data in order to export only the more recently updated. Use then a “Data filtering”, “Fixed Value Manager” to manage only services with “Updated Date” up to 3 days before the operation (i.e. running the workflow every 3 days, they will be only needed services, if updated in the last 3 days).

TRANSFORMATION 2/3 – Add Fixed Value fields for target Business Critical fields

The target database stores the Business Criticality information in a bit field, with “0” or ”1” values. In CM there is a checkbox field, exported by CM Adapter as “True” / “False” values.

A text conditional replace is then needed so that two different fixed value fields, with values 0 and 1 respectively, replace “False” and “True” in the following replace transformation (see later):

TRANSFORMATION 4 – Prepare Application name column into Vendor dataset

Vendors are to be imported as Application associated objects, but the Vendor file does not contain the application name, which is needed to execute the operation.

A “Replace Value on Condition” is needed to decode the “True”/”False” value for the source field hosting Business Criticality information in Modeler to the target 1/0 format:

TRANSFORMATION 5 – Add Fixed Value to fill a datetime target field

Let’s suppose that a datetime field in the target table is uploaded with the DT execution datetime, for any created/updated record.

In such a case, a fixed value field, with value equal to parameter “<%SYSDATE_EUR%>” (to get a European “DD/MM/YYYY” format, or “<%SYSDATE_USA%>” for a “MM/DD/YYYY” format or “<%SYSDATE%>” for a standard “DDDD/MM/YY” UTC format):

 OPERATION – Send to DB Loader

In the “Mapping and Operation” step, choose the previously configured DB Loader, the target table, and the target field to be used as a key for the operation. Then map all the relevant source fields to target fields. In case of datetime, boolean or numeric target fields, the source datatype must be specified to ensure a correct data transfer:

Email can be configured with default text (simply filling the “email to” field) or with custom properties (“Conf”). Please refer to the “Email configurations” for a detailed description on the email configuration options.

•TEST THE WORKFLOW AND CHECK RESULT

Test the configured workflow with the “Test” button, and follow its execution through the progress bar or Logs and email notifications. At the end of the workflow execution, check the result in the target database:

Align Federated Models

Align Federated Models

This example describes the following use case: an enterprise organized its EA information in two federated models, one specifically aimed to support IT and the second for more general business analysis purposes. The two models partially share the same metamodel, given to different IT and business teams requirements.

The first model is used to synchronize the second about application data, but only for a specific subset of applications, i.e. applications with a given lifecycle status (“production”) and only for those coming from the enterprise application catalogue (the source of application information for IT models, while the second hosts applications from other sources, relevant for the business).

 Let us suppose that:

•Application data are in the source CM model in a dedicated object type:

•a second CM Model hosts application data, which is needed to execute Business Analysis in a dedicated object type, and a slightly different metamodel from the IT Model:

A DT workflow aligning the database of two CM models can be configured with the following steps:

•CREATE WORKFLOW TO ALIGN FEDERATED MODELS

Press the “Workflow configuration” button on the Home Page and configure the following workflow choosing the model configuration according to requirements (please note that the model configuration is mandatory for a given model, when the “Load, Sync or Delete” operation must be executed on that model)

SOURCE DEFINITION – Source 1

Choose the CM Adapter and specific CM Model Export adapter (i.e. the IT Model one) from the dropdown list; then pick the “Application” object type from the list and check the desired properties/associations from the “Source Filters” interface:

TRANSFORMATION 1 – Filter Application in “Production”

To optimize the data flow, filter source data in order to export only the more recently updated. Use then a “Data filtering”, “Fixed Value Manager” to manage only services with “Status” equal “6 – Production”.

OPERATION – Sync CM Repository

In the “Mapping and Operation” step, choose “Sync CM Repository”. The target model is the one related to the Model Configuration the workflow has been assigned to. Then map all the relevant source fields to the target fields for “Application” object type. Define the desired synchronization action (“Logical”, “Physical” or “Physical Forced” deletion, which will affect redundant objects in the target model in comparison with the exported list from the source model):

 To apply the synchronization within requirements (it must occur only for applications with a specific source system - the Application Catalogue) configure a filter for the synch operation, by double clicking on the filter icon on the “Object Type” column:

•TEST THE WORKFLOW AND CHECK RESULT

Test the configured workflow with the “Test” button and follow its execution through the progress bar, Logs and email notifications. At the end of workflow execution, check the result in the target CM Model:

Deriving values and update consistency data

Deriving values and update consistency data

The example describes the following use case: an enterprise organized its EA information in a model which supports general business analysis purposes with reports and dashboards. One of them is about IT Portfolios, and shows summary information to managers.

Let us suppose that:

•IT Portfolio data are in the source CM model in a dedicated object type:

and the “Total TCO” value must be calculated as the sum of associated application TCO, for the ones of “ERP” category and a number of users greater than 100.

A DT workflow calculating Total TCO for IT Portfolios can be configured with the following steps:

•CREATE WORKFLOW TO DERIVE VALUES AND UPDATE CONSISTENCY DATA

Press the “Workflow configuration” button on the Home Page and configure the following workflow choosing the model configuration according to requirements

 SOURCE DEFINITION – Source 1

Choose CM Adapter, and specific CM Model Export adapter (i.e. the IT Model one) from the dropdown list; the select “Association IT Portfolio-Application” from the Object Type list, and select <Application> Category, <Application> TCO, <Application> Number of Users, <IT Portfolio> Name, <IT Portfolio> Total TCO fields:

TRANSFORMATION 1 – Filter application by “category” and “number of users”

To export the relevant application, set a filter on “category” and “number of users” with “DATA FILTERING”-“Fixed Value Manager” to manage applications having:

•“Category” equal to “ERP”

•“Number of users” greater than “100”.

TRANSFORMATION 2 – Derive IT Portfolio “Total TCO” from associated applications

Choose a “DATA DERIVATION”, “Sum Values in Column” transformation and set:

•The “<Application> TCO” column as “Source Column (to sum values from)”

•The “<IT Portfolio> Name” column as “Key (for data aggregation)”

•The “<IT Portfolio> Total TCO” as “Result” for the transformation

OPERATION – Sync CM Repository

In the “Mapping and Operation” step, choose the “Load CM Repository”. The target model is the one related to the Model Configuration which the workflow has been assigned. Then map all the relevant source fields to target fields:

•TEST THE WORKFLOW AND CHECK RESULT

Test the configured workflow with the “Test” button and follow its execution through the progress bar, Logs and email notifications. At the end of the workflow execution check the result in CM Model IT Portfolios: