CMiC Analytics: Catalog Builder - Enterprise Relationship Diagrams (ERDs)

• CMiC Analytics: Catalog Builder - Enterprise Relationship Diagrams (ERDs)
• Building an ERD using Oracle Data Modeler
• Generating ERDs for Data Sources
• Overview
• Relationships Generated in ERDs

Building an ERD using Oracle Data Modeler

Step 1: Download ERD script for your data source.

• Select your data source.

• Press [Download ERD script] button. A pop-up with the suggested file name will appear.

  

  Pop-up window launched from [Download ERD script] button

• Select the ‘Include Column Comments’ checkbox if you wish to generate column comments. When selected, Field Description property will be used as a source for the generated column comments.

• Press [Download] button.

• Once the file is generated, the browser’s Save dialog will appear. Save the file to the desired location.

  

  Example of browser’s pop-up window used for saving DDL script

Step 2: Use Data Modeler to draw ERD.

• Open Data Modeler, then go to “File”=>“Import”=>“DDL File”.

  

  Importing a DDL file in Oracle SQL Developer Data Modeler

• Select the generated DDL script. Make sure Import to: option is set to “New Relational Model” and click [OK].

  

• Select “Oracle Database 11g” and click [OK].

  

• Review the import log and click [Close].

  

• Click [Merge] in the Compare Models dialog window. If you wish, you can explore this dialog and select only those items that you would like to import. CMiC recommends that you use defaults and import everything.

  

• Once the merge process is completed, use Fit Screen or Zoom toolbar buttons to make the diagram visible. If your data source includes a lot of folders, a diagram created by default may be too big, as shown in the screenshot below.

  

 

• To create a new diagram with a smaller number of folders, right-click on the default diagram and select menu item “Diagram”=>“Create SubView”.

  

• Then right-click on the new diagram and select “Object”=>“Add/Remove Objects”.

  

• In the Add/Remove Objects pop-up, select only those objects that you would like to include into your diagram. In example below, we include all JC objects. When finished, click [Apply] and [Close].

  

• If new objects are not visible in the diagram, click Fit Screen toolbar button.

  

• You can also automatically arrange the objects using the Auto Layout function, as shown in the screenshot below.

  

  Auto Layout function in Oracle SQL Developer Data Modeler

• You can also select what information will be visible in the diagram. In the screenshot below, we included “Comments”.

  

  Example of selecting details, such as Comments, to be visible in the diagram

• You can export the diagrams to a PDF or Image file by selecting “File” => “Print Diagram” => “DDL File”.

  

  Exporting a diagram to PDF

Generating ERDs for Data Sources

Overview

Example of Entity-Relationship Diagram

BI Catalog Builder allows users to generate a script for drawing Entity-Relationship Diagrams (ERDs) for any data source.

Users simply download a DDL script for any data source and then import it into any tool that creates entity-relationship diagrams.

CMiC recommends Oracle’s SQL Developer Data Modeler, as it’s free to use and powerful enough to draw quite sophisticated diagrams. The data modeler can be downloaded from Oracle’s website:
https://www.oracle.com/database/technologies/appdev/datamodeler.html.

Relationships Generated in ERDs

Relationships are generated as follows in ERDs:

• The generated ERDs will use “One-To-Many” relationships between entities (folders).

• In order to correctly determine “One” and “Many” sides of the relationship, the Catalog Builder will use “Join Type” information (see screenshot below).

• In case of OUTER joins, the join direction (LEFT or RIGHT) will always point to a folder associated with the “Many” side of the relationship.

• In case of INNER joins, Catalog Builder will always use “Right Folder” as the one to be associated with “ONE” side of the relationship and the “Left Folder” will be associated with the “MANY” side of the relationship.

When building a custom data source, in order to have correct relationships in your ERDs, always use “Lookup” folders on the right side of the joins. The CMiC Default Data Source functions according to this rule.

Example

Join Setup:

Left Folder: GL Transactions

Right Folder: GL Companies

Join Type: Inner

Produced Relationship in ERD:

“GL Transactions”  >------- “GL Companies”

Example of join type to be used for entity relations