Skip Headers
Oracle® Fusion Middleware Developer's Guide for Oracle Data Integrator
11g Release 1 (11.1.1)

Part Number E12643-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

7 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.

This chapter includes the following sections:

7.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.

7.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.

7.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.

7.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.

7.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.

7.2.1 Creating a New Diagram

To create a new diagram:

  1. 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.

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

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

  4. Select Save from the File main menu.

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

7.2.2 Create Datastores and Columns

To insert an existing datastore in a diagram:

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

  2. In the Diagram Editor, select the Diagram tab.

  3. Select the datastore from the Models tree in Designer Navigator.

  4. 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.

To create a new datastore in a diagram:

  1. In the Diagram Editor, select the Diagram tab.

  2. In the Diagram Editor toolbar, click Add Datastore.

  3. Click into the diagram workbench.

  4. An Editor appears for this new datastore. Follow the process described in Chapter 5, "Creating and Reverse-Engineering a Model" for creating your datastore.

To add columns from another datastore:

  1. In the Diagram Editor, select the Diagram tab.

  2. Select a column under a datastore from the Models tree of the Designer Navigator.

  3. Drag this column into the datastore in the diagram to which you want to append this column. The Column Editor appears to edit this new column. Edit the column according to your needs.

  4. Select Save from the File main menu. The new column is added to the datastore.

7.2.3 Creating Graphical Synonyms

To create a graphical synonym for a datastore:

  1. In the Diagram tab, select the datastore.

  2. Right-click, then select Create Graphical Synonym.

The new graphical synonym appears in the diagram.

This operation does not add a new datastore. It creates only a new representation for the datastore in the diagram.

7.2.4 Creating and Editing Constraints and Filters

To add a new condition, filter, key to a datastore:

  1. In the Diagram tab, select the datastore.

  2. Right-click then select the appropriate option: Add Key, Add Filter, etc.

  3. A new Editor appears for the new condition, filter, key, etc. Follow the process described in Chapter 5, "Creating and Reverse-Engineering a Model" for creating this element.

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.

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

  2. 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:

  1. In the Diagram Editor, select the Diagram tab.

  2. In the toolbar click the Add Reference button.

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

  4. Release the mouse button. The Reference Editor appears.

  5. Set this reference's parameters according to the process described in Chapter 5, "Creating and Reverse-Engineering a Model".

To move a reference to another graphical synonym:

  1. In the Diagram Editor, select the Diagram tab.

  2. In the Diagram tab, select the reference you wish to modify.

  3. Right-click and select Display Options.

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

  5. 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.

7.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:

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

  2. 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

  3. Click OK.

7.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.

The DDL generation supports two types of datastores: tables and system tables.

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.

Before generating DDL Scripts

In certain cases, constraints that are defined in the data model but not in the database, are not displayed in the Generate DDL editor. To ensure that these conditions appear in the Generate DDL editor, perform the following tasks:

Generating DDL Scripts

To generate the DDL scripts:

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

  2. Right-click, then select Generate DDL. The Generate DDL for Oracle Data Integrator Model dialog is displayed.

  3. In the Generate DDL dialog, click Yes if you want to process only tables that are in the Oracle Data Integrator model, otherwise click No and tables that are not in the model will be also included.

    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.

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

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

  6. Select the folder and click OK.

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

  8. 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).

  9. 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.

7.4 Generating Interface IN/OUT

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

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:

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

  2. 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.

  3. 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.

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

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

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

  7. 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:

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

  2. 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.

  3. 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.

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

  5. 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.

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

  7. 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.