6 Working with Common Format Designer

This chapter describes how to use Oracle Data Integrator's Common Format Designer feature for creating a data model by assembling elements from other models. It also details how to generate the DDL scripts for creating or updating a model's implementation in your data servers, and how to automatically generate the interfaces to load data from and to a model.

6.1 Introduction to Common Format Designer

Common Format Designer (CFD) is used to quickly design a data model in Oracle Data Integrator. This data model may be designed as an entirely new model or assembled using elements from other data models. CFD can automatically generate the Data Definition Language (DDL) scripts for implementing this model into a data server.

Users can for example use Common Format Designer to create operational datastores, datamarts, or master data canonical format by assembling heterogeneous sources.

CFD enables a user to modify an existing model and automatically generate the DDL scripts for synchronizing differences between a data model described in Oracle Data Integrator and its implementation in the data server.

6.1.1 What is a Diagram?

A diagram is a graphical view of a subset of the datastores contained in a sub-model (or data model). A data model may have several diagrams attached to it.

A diagram is built:

by assembling datastores from models and sub-models.

by creating blank datastores into which you either create new columns or assemble columns from other datastores.

6.1.2 Why assemble datastores and columns from other models?

When assembling datastores and columns from other models or sub-models in a diagram, Oracle Data Integrator keeps track of the origin of the datastore or column that is added to the diagram. These references to the original datastores and columns enable Oracle Data Integrator to automatically generate the integration interfaces to the assembled datastores (Interfaces IN)

Automatic interface generation does not work to load datastores and columns that are not created from other model's datastores and columns. It is still possible to create the integration interfaces manually, or complete generated interface for the columns not automatically mapped.

6.1.3 Graphical Synonyms

In a diagram, a datastore may appear several times as a Graphical Synonym. A synonym is a graphical representation of a datastore. Graphical synonyms are used to make the diagram more readable.

If you delete a datastore from a diagram, Designer prompts you to delete either the synonym (the datastore remains), or the datastore itself (all synonyms for this datastore are deleted).

References in the diagram are attached to a datastore's graphical synonym. It is possible create graphical synonyms at will, and move the references graphical representation to any graphical synonym of the datastores involved in the references.

6.2 Using the Diagram

From a diagram, you can edit all the model elements (datastore, columns, references, filters, etc) visible in this diagram, using their popup menu, directly available from the diagram. Changes performed in the diagram immediately apply to the model.

6.2.1 Creating a New Diagram

To create a new diagram:

In the Models tree in Designer Navigator, expand the data model or sub-model into which you want to create the diagram, then select the Diagrams node.

Right-click, then select New Diagram to open the Diagram Editor.

On the Definition tab of the Diagram Editor enter the diagram's Name and Description.

Select Save from the File main menu.

The new diagram appears under the Diagrams node of the model.

6.2.2 Create Datastores and Columns

To insert an existing datastore in a diagram:

Open the Diagram Editor by double-clicking the diagram under the Diagrams node under the model's node.

In the Diagram Editor, select the Diagram tab.

Select the datastore from the Models tree in Designer Navigator.

Drag this datastore into the diagram. If the datastore comes from a model/sub-model different from the current model/sub-model, Designer will prompt you to create a copy of this datastore in the current model. If the datastore already exists in the diagram, Oracle Data Integrator will prompt you to either create new graphical synonym, or create a duplicate of the datastore.

The new graphical synonym for the datastore appears in the diagram. If you have added a datastore from another model, or chosen to create a duplicate, the new datastore appears in model.

If references (join) existed in the original models between tables inserted to the diagram, these references are also copied.

To create a graphical synonym of a datastore already in the diagram select Create Graphical Synonym in the popup menu of the datastore.

Conditions, filters and references are added to the diagram when you add the datastore which references them into the diagram. It is possible to drag into the diagram these objects if they have been added to the datastore after you have added it to the diagram.

To edit a key on a column:

If a column is part of a key (Primary, Alternate), it is possible to edit the key from this column in the diagram.

In the Diagram tab, select one of the column participating to the key.

Right-click then select the name of the key in the pop-up menu, then select Edit in the sub-menu.

To create a reference between two datastores:

In the Diagram Editor, select the Diagram tab.

In the toolbar click the Add Reference button.

Click the first datastore of the reference, then drag the cursor to the second datastore while keeping the mouse button pressed.

Select the synonyms to be used as the parent and child of the reference.

Click OK. The reference representation appears now on the selected synonyms.

This operation does not change the reference itself. It only alters its representation in the diagram.

6.2.5 Printing a Diagram

Once you have saved your diagram you can save the diagram in PNG format, print it or generate a complete PDF report.

To print or generate a diagram report:

On the Diagram tab of your diagram, select Print Options from the Diagram menu.

In the Data Model Printing editor select according to your needs one of the following options:

Generate the complete PDF report

Save the diagram in PNG

Print your diagram

Click OK.

6.3 Generating DDL scripts

When data structure changes have been performed in a data server, you usually perform an incremental reverse-engineering in Oracle Data Integrator to retrieve the new metadata from the data server.

When a diagram or data model is designed or modified in Oracle Data Integrator, it is necessary to implement the data model or the changes in the data server containing the model implementation. This operation is performed with DDL scripts. The DDL scripts are generated in the form of Oracle Data Integrator procedures containing DDL commands (create table, alter table, etc). This procedure may be executed on the data server to apply the changes.

Note:

The templates for the DDL scripts are defined as Action Groups. Check in the Topology Navigator that you have the appropriate action group for the technology of the model before starting DDL scripts generation. For more information on action groups, please refer to the Knowledge Module Developer's Guide for Oracle Data Integrator.

To generate the DDL scripts:

In the Models tree of Designer Navigator, select the data model for which you want to generate the DDL scripts.

Click Yes if you want to process tables that are not in the Oracle Data Integrator model, otherwise click No.

Oracle Data Integrator retrieves current state of the data structure from the data server, and compares it to the model definition. The progression is displayed in the status bar. The Generate DDL Editor appears, with the differences detected.

Select the Action Group to use for the DDL script generation.

Click the ... button to select the Generation Folder into which the procedure will be created.

Select the folder and click OK.

Filter the type of changes you want to display using the Filters check boxes.

Select the changes to apply by checking the Synchronization option. The following icons indicate the type of changes:

- : Element existing in the data model but not in the data server.

+ : Element existing in the data server but not in the data model.

= : Element existing in both the data model and the data server, but with differences in its properties (example: a column resized) or attached elements (example: a table including new columns).

Click OK to generate the DDL script.

Oracle Data Integrator generates the DDL scripts in a procedure and opens the Procedure Editor for this procedure.

6.4 Generating Interface IN/OUT

For a given model or datastore assembled using Common Format Designer, Oracle Data Integrator is able to generate:

Interfaces IN: These integration interfaces are used to load the model's datastores assembled from other datastores/columns. They are the integration process merging data from the original datastores into the composite datastores.

Interfaces OUT: These integration interfaces are used to extract data from the model's datastores. They are generated using the interfaces (including the interfaces IN) already loading the model's datastore. They reverse the integration process to propagate the data from the composite datastore to the original datastores.

For example, an Active Integration Hub (AIH) assembles information coming from several other applications. It is made up of composite datastores built from several data models, assembled in a diagram. The AIH is loaded using the Interfaces IN, and is able to send the data it contains to the original systems using the Interfaces OUT.

To generate the Interfaces IN:

In the Models tree of Designer Navigator, select the data model or datastore for which you want to generate the interfaces.

Right-click, then select Generate Interfaces IN. Oracle Data Integrator looks for the original datastores and columns used to build the current model or datastore. The Generate Interfaces IN Editor appears with a list of datastores for which Interfaces IN may be generated.

Select an Optimization Context for your interfaces. This context will define how the flow for the generated interfaces will look like, and will condition the automated selection of KMs.

Click the ... button to select the Generation Folder into which the interfaces will be generated.

In the Candidate Datastores table, check the Generate Interface option for the datastores to load.

Edit the content of the Interface Name column to rename the integration interfaces.

Click OK. Interface generation starts.

The generated interfaces appear in the specified folder.

Note:

Interfaces automatically generated are built using predefined rules based on repository metadata. These interfaces can not be executed immediately. They must be carefully reviewed and modified before execution

Note:

If no candidate datastore is found when generating the interfaces IN, then it is likely that the datastores you are trying to load are not built from other datastores or columns. Automatic interface generation does not work to load datastores and columns that are not created from other model's datastores and columns.

To generate the Interface OUT:

In the Models tree of Designer Navigator, select the data model or datastore for which you want to generate the interfaces.

Right-click, then select Generate Interfaces OUT. Oracle Data Integrator looks for the existing Interfaces loading these the datastores. The Generate Interfaces OUT Editor appears with a list of datastores for which Interfaces OUT may be generated.

Select an Optimization Context for your interfaces. This context will define how the flow for the generated interfaces will look like, and will condition the automated selection of KMs.

Click the ... button to select the Generation Folder into which the interfaces will be generated.

In the Candidate Datastores, check the Generation and Generate Interface checkboxes to select either all or some of the candidate datastore to load from the target datastore of the existing interfaces.

Edit the content of the Interface Name column to rename the integration interfaces.

Click OK. Interface generation starts.

The generated interfaces appear in the specified folder.

Note:

Interfaces automatically generated are built using the available metadata and do not always render the expected rules. These interfaces must be carefully reviewed and modified before execution.

Note:

If no candidate datastore is found when generating the interfaces OUT, then it is likely that no interface loads the datastores you have selected to generate the interfaces OUT. The interfaces OUT from a datastore are generated from the interfaces loading this datastore. Without any valid interface loading a datastore, not propagation interface from this datastore can be generated.

Scripting on this page enhances content navigation, but does not change the content in any way.