Working in Data Modeler

This note is focused on the specific case of engineering to relational model from logical using Oracle SQL Developer Data Modeler.

Logical model

Suppose we are making a data mart constellation schema for our stock.

So far we have this logical model with mostly just entities, attributes, comments and relations:



The color variation comes from choosing Classification Type within Properties for each entity: Fact or Dimension. We would like to preserve this differentiation in entity name prefix as well, so we set up the Short Name: for Date dimension it would be D001, D002 for Time.

With a lot of entities you may find this Custom Transformations Script for logical object useful:

 1 //
 2 //Sets table abbr to D00., F00., depending on Classification type name
 3 //
 4 
 5 max_num_len = 3; //F[...]
 6 var cur_num = []; //array for current value for each type
 7 
 8 //replacement for String.format("%04",value);
 9 function formatCount(len, value){
10     strval = "" + value;
11     ret = "";
12     for ( var i = 0; i < len - strval.length; i++ ){
13         ret = ret + "0";
14     }
15     ret = ret + value;
16     return ret;
17 }
18 
19 function existTableWithAbbr(abbr){
20     tables = model.getEntitySet().toArray();
21     for ( var t = 0; t < tables.length; t++ ){
22         if ( tables[t].getShortName() == abbr ){
23             return true;
24         }
25     }
26     return false;
27 }
28 
29 //Main loop
30 tables = model.getEntitySet().toArray();
31 for ( var t = 0; t < tables.length; t++ ){
32     table = tables[t];
33     name = table.getName();
34     classType = table.getClassificationType();
35     if ( table.getShortName() == "" && classType != null ){
36         typeId = classType.getTypeID();
37 
38         if ( cur_num[typeId] == null ) { // initialization
39             cur_num[typeId] = 1;
40         }
41 
42         do {
43             prefix = classType.getTypeName().substring(0,1)
44                 + formatCount(max_num_len,cur_num[typeId]);
45             cur_num[typeId] ++ ;
46         } while ( existTableWithAbbr(prefix) );
47 
48         table.setShortName(prefix);
49         table.setDirty(true);
50     }
51 
52 }

Transformations menu is under Tools → Design Rules And Transformations.

Noticed many links to same dimension from the Income order fact? Each of them have different business mining and would be further separated on virtual data mart layer. 

To give different names to these future foreign-key columns in logical model you may: manually add Primary UID attribute to dimensions and choose Bachman Notation from right-click canvas menu. Rename appeared attributes.

On the figure above you see that many attributes are of custom Domain type called %_TMPL. They would be spitted depending on this Domain type name and attributes of corresponding entity.

Lets create these entities:

Don’t forget to comment template attributes like ‘Code’, ‘Descriptions’ and to-be-spitted attributes like ‘of the article’.

The last step here is to customize naming templates. Right click on your design → Properties → Templates under Settings/Naming Standard:

Foreign Key to {child abbr}_{parent abbr}_FK 

Column Foreign Key to {ref table}

Primary and Surrogate Keys to {table abbr}_{table}_PK

Surrogate Key Column to {table}_ID

Right click on Logical Model → Engineer to Relational Model. At General Options tab disable Apply naming translation. Click the Engineer button.

Relational model

Now, use Apply Naming Standards on Relational_1 for everything except Column Foreign Key – we would rename them manually.

Next we would like to:

• Uppercase table and constraint names;
• Reorder columns – keys to beginning;
• Split %_TMPL columns;
• Copy comments to Comments in RDBMS;
• Set primary key names to {table name}_ID;
• Set table names to {table abbr}_{table};
• Add appropriate prefix to fact column names.

For the first two and fourth tasks there are preinstalled scripts, and the other ones may be automated like so:

Split %_TMPL columns

 1 //
 2 //For example, would replace column "STATUS", which domain name is "CODE/NAME_TMPL", 
 3 //with a copy of "CODE/NAME_TMPL"(code, name) tables' columns
 4 //so there would be "STATUS_code" and "STATUS_name"
 5 //
 6 
 7 TMPL_POSTFIX = "_TMPL";
 8 
 9 tables = model.getTableSet().toArray();
10 for ( var t = 0; t < tables.length; t++ ){
11     table = tables[t];
12     columns = table.getElements();
13     for ( var c = 0; c < columns.length; c++ ){
14         column = columns[c];
15         domname = column.getDomain().getName(); // template table name
16         if ( domname.indexOf(TMPL_POSTFIX) == domname.length - TMPL_POSTFIX.length ){
17             template = model.getTableSet().getByName(domname);
18             if ( template != null ){
19                 prefix = column.getName();
20                 orig_comment = column.getComment();
21                 column.remove();
22                 tcolumns = template.getElements();
23                 for ( var tc = 0; tc < tcolumns.length; tc++ ){
24                     col = table.createColumn();
25                     tcol = tcolumns[tc];
26                     tcol.copy(col);
27                     col.setName(prefix + "_" + tcol.getName());
28                     col.setComment(col.getComment()+" "+orig_comment);
29                 }
30                 table.setDirty(true);
31             }
32         }
33     }
34 }

Set primary key names to {table name}_ID

 1 tables = model.getTableSet().toArray();
 2 for ( var t = 0; t < tables.length; t++ ){
 3     table = tables[t];
 4     name = table.getName();
 5     columns = table.getElements();
 6     for ( var c = 0; c < columns.length; c++ ){
 7         column = columns[c];
 8         if ( column.isPKElement() ){
 9             column.setName(name+'_ID');
10             table.setDirty(true);
11         }
12     }
13 }

Set table names to {table abbr}_{table}

 1 tables = model.getTableSet().toArray();
 2 for ( var t = 0; t < tables.length; t++ ){
 3     table = tables[t];
 4     name = table.getName();
 5     abbr = table.getAbbreviation();
 6     if ( abbr != "" ) {
 7         table.setName(table.getAbbreviation()+"_"+name);
 8         table.setDirty(true);
 9     }
10 }

Add appropriate prefix to fact column names

 1 //
 2 //Sets Fact tables' column name prefix like F001_, D003_ 
 3 //
 4 
 5 tables = model.getTableSet().toArray();
 6 for ( var t = 0; t < tables.length; t++ ){
 7     table = tables[t];
 8     classType = table.getClassificationType();
 9     if ( classType != null ){
10         if ( classType.getTypeName().equals("Fact") ){
11             prefix = table.getAbbreviation()
12             if ( ! prefix.equals("") ) {
13                 columns = table.getElements();
14                 for ( var c = 0; c < columns.length; c++ ){
15                     column = columns[c];
16                     if ( ! column.getName().substring(0,2).equals("DD") ){
17                         if ( ! column.isFKElement()  ){
18                                     column.setName(prefix+'_'+column.getName());
19                         }
20                         else {
21                             columnRefTableAbbr = column.getFKIndexAssociation().getOriginalContainer().getAbbreviation();
22                             column.setName(columnRefTableAbbr+'_'+column.getName()+'_ID');

23                         }
24                         table.setDirty(true);
25                     }
26                 }
27             }
28         }
29     }
30 }

And the result:

If this would be the real schema you will continue with unique constraints on dimensions, composite keys on facts and so on.
Anyway, now you have some ideas on how to get basic tasks done efficiently!