DA# Macro(4): DA# Macro Function(3)-Reverse

Let's take a look at the DA# Macro Reverse function.

This is a continuation of the previous article.

DA# Macro(3): DA# Macro Function(2)-Attribute Get/Set

2.4. DA# Macro Reverse function

2.4.1. Reasons for creating the DA# Macro Reverse function

The reverse function of the data modeling tool is a function that creates an ERD-type data model from the database using reverse engineering techniques.

The most common method is to access the database directly and execute the reverse function. If it is difficult to access the database directly or it takes a long time to obtain the authority, provide SQL to collect data model information to the person in charge (usually DBA or IT operator) who has access authority, and export the execution result as an Excel file. Reverse function can be executed by receiving it.

DA# provides both the function to reverse from the database and the function to reverse from the Excel file. However, it has the following limitations and requires a lot of manual work when using it at the project site.

• Only one data model can be processed at a time.
  • If there are many data models (tens to hundreds), the reverse function must be repeatedly executed for each model.
• It is created as a single subject area diagram, and all entities are randomly placed.
  • It is necessary to create a subject area and separate the subject area of each entity.
  • In the subject area diagram, it is necessary to move entities, such as grouping entities into business areas or arranging related entities in one place.

In order to overcome the above limitations as much as possible and eliminate manual work as much as possible, a reverse function was created in the DA# Macro tool. The functions below are available.

1. Multiple models can be reversed at once
2. By designating the subject area of each table, it is possible to place it in the corresponding subject area diagram.
3. Designate a group of entities within a subject area diagram and arrange them in one place for each group –> Core function
4. User-defined layout in addition to the layout provided by DA# (planned in a future version. Currently, only the layout provided by DA# is supported)

2.4.2. Summary of how to use the reverse function

• Create an input Excel file, select each file in the 'Reverse' sheet, and set options to execute.
• The input Excel file is created by executing a query in the database or by using the table definition document being managed.

2.4.3. input file

• Model list: A file that creates a reverse target model list (required)
  • Set DBMSType of Model
  • Only objects whose model names in the table list and the models described in this file match match are reversed.
• Table list: The file that created the reverse target table list (required)
• Column list: File that created the reverse target column list (required)
• FK list: A file that creates a list of foreign keys (FKs) to reverse (optional)
• Notes on creating each list file
  • Model, Table, Column, and FK lists can be written in one file or saved in separate files.
  • The template for each list should be prepared using the attached file at the bottom of the DA# Macro “Reverse” sheet or as a file provided separately (must be written in the prescribed format)

2.4.4. Reverse Option

• Input file open mode: select which mode (read-only, writable) to open the input file
• Whether to close the input file after processing: Whether to close after reading all input files and loading them into memory (Y: close after reading, N: keep open)
• Entity Arrangement Method: The arrangement method provided by DA# Menu > Format > Auto Arrangement. Currently, it specifies one of two methods: radial or HTree.
• Group Arrangement Interval: Horizontal, vertical interval between Entity Groups (unit: pixel) (recommended value: 300 or more)
• Number of Group columns in a row: The number of Entity Groups to be placed in a row in the subject area diagram.
  • Example: When there are 10 Entity Groups in total
  • When setting the number of columns to 3: Arranged in 4 rows and 3 columns (4×3)
  • When setting the number of columns to 5: Arranged in 2 rows and 5 columns (2×5)
• Whether to use Reverse DA# model template: Below Whether to use (create a new model without using a template when “N” is selected)
• Reverse DA# model template file: Reverse DA# model template file
  • Designate a file in which the default settings of the model are saved in advance
  • Examples of model default settings: object name view options (view secondary names, view synonyms), page scaling, etc.
  • Not used if the above use option is “N”
  • Example value: D:\Project\My\01.ExcelVBA\02.DA# Macro\test reverse file\template model.damx
• Reverse DA# model storage path: path to save the reversed DA# model file (*.damx) (the file name is created according to the format below)
• Model filename format: A format string applied to model filenames.
  • [Model Name]: Model name specified in the Model list template
  • [DATE]: YYYYMMDD format (e.g. 20201228)
  • [TIME]: HH24MISS format (e.g. 155621)
  • Constant: customer company name, system name, etc.
  • Example value: Encore [model name]_[DATE]_[TIME].damx
• Whether to create a physical model: whether to create a physical model diagram for each subject area
  • “Y”: Reverse takes more time to complete, but creates a physical model from scratch
  • Does not create “N” physics model (reverse can be completed faster)

2.4.5. Template file: Model

• Model name: The name of the model file to be saved. Only the model specified here is reversed.
  • It is used in “Model file name format” of option.
  • Must match the model name of Table, Column, and FK Template.
• DBMSType: Specifies the DBMS Type to be assigned to the physical model of the model. Use one of the following values. (Note: As DA# is upgraded, this value may be added or removed)
  • ALTIBASE
  • BIGQUERY
  • CUBRID
  • DB2UDB
  • GREENPLUM
  • HANADB
  • HIVE
  • IMPALA
  • INFORMIX
  • MARIA
  • MySQL
  • NETEZZA
  • OCEANBASE
  • ORACLE
  • POSTGRESQL
  • REDSHIFT
  • SQLSERVER
  • SYBASEASE
  • SYBASEIQ
  • TERADATA
  • Tibero
  • VERTICA
  • Other values are specified as ORACLE
• Definition: A description of the model

2.4.6. Template file: Table

• Model name: The name of the model file to be saved. Only the model specified here is reversed.
• Subject area name: Model sub-subject area to place each table
• Entity group name: Entities with the same group name are placed in the same position on the subject area diagram.
  • Before executing Reverse, the business area of each table (order, payment, etc.) or the nature of the table (object (basic), detail, code, history, temporary, relationship, etc.) is analyzed in advance, and the group name is designated in advance, and the groups are This is useful if you want to place them in one place.
  • Make the entity group name into a text box and gather and arrange the entities below it. (see image below)

• Others are the attribute values assigned to the entity object of DA# as follows.
  • entity name
  • table name
  • synonym
  • auxiliary name
  • DB Owner
  • Classification: One of Key, Main, Action, None. If no value is specified, the default value is 'None'.
  • Level
  • Levels: either essential or practical. If no value is specified, the default value is 'practical'.
  • Type: One of Normal, Additional, Drop, External, or Pseudo. If no value is specified, the default value is 'Normal'.
  • Normalization: One of inherited, target, or non-target. If no value is specified, the default value is 'inherit'.
  • situation
  • cycle of occurrence
  • monthly amount
  • Retention period (months)
  • total number of cases
  • Justice
  • data processing form
  • Uniqueness
  • Note
  • Tag
• Note: At the time of this writing, setting UDP values is not supported.

2.4.7. Template File: Column

• Model name: The name of the model file to be saved. Only the model specified here is reversed.
• Entity Name: Refer to the contents of the table name below. Not required.
• Table name: A value that connects which entity the property belongs to. This is a required value. Entity names are difficult to guarantee uniqueness and are often empty, so do not use them as connecting values. When extracting reverse information from the database through query, the table name can be guaranteed to be unique and not empty, so the table name is used as the connection value.
• The following are the attribute values assigned to the Attribute object of DA#.
  • attribute name
  • column name
  • Justice
  • auxiliary name
  • synonym
  • Reverse table
  • Reverse Column
  • Reverse Type
  • Reverse Length
  • PK: Whether the property is PKed (Y or null)
  • NotNull: NotNull or not (Y or null)
  • Type: One of Normal, Additional, Drop, System, Pseudo
  • data type
  • length
  • decimal point
  • default
  • Core property status: (Y or null)
  • Essential identifier: (Y or null)
  • Secondary identifier: (Y or null)
  • Standard Synchronization: (Y or null)
  • Non-inheritance: (Y or null)
  • standardization
  • Whether information is protected: (Y or null)
  • Information protection level
  • Encryption: (Y or null)
  • scramble
• Note: At the time of this writing, setting UDP values is not supported.

2.4.8. Template file: FK

• FK is not a required input, but if the relationship between entities is known in advance, it is good to write and enter it as much as possible.
• Model name: The name of the model file to be saved. Only the model specified here is reversed.
• parent entity name, child entity name
  • It must exactly match the entity name specified in the table template.
  • Among the DA# APIs, there is no API that creates an FK in a physical model, so an API (Model.LinkRelation) that creates a relationship in a logical model is used.
• Below are the attribute values assigned to the Relationship object of DA#.
  • relationship name
  • Justice
  • Relationship Type: One of Normal, Pseudo. If no value is specified or neither is specified, the default value is 'Normal'.
    • It is recommended to designate Normal if the relationship between two entities is certain, and Pseudo if not certain or to create a relationship for the purpose of data model analysis.
    • In the case of Normal, the identifier of the parent entity is inherited by the child entity, but in the actual data model, the real property of the identifier may be excluded or the name may be different, which can damage the data model.
  • Ordinality (parent:child): One of 1:1, 1:M, or M:M. If no value is specified, the default value is '1:M'.
  • Selectivity (Parent:Child): Specify as one of the following. If no value is specified, the default value is 'O:M'. (O: Optional, M: Mandatory)
    • O:M -> default
    • O:O
    • M:O -> Unsuitable but provided
    • M:M
  • Identification: One of identification or non-identification. If no value is specified, the default value is 'non-identified'.
    • Identification: parent identifier inherits child identifier
    • Non-identifying: parent identifier inherits as child general property
  • Parent Entity Relational Verb, Child Entity Relational Verb: Usually, a relation name is sufficient, but if additional explanation is needed, a relation verb is specified. See the image below for concept.

---

So far, we have looked at the Reverse function among the DA# Macro functions. Next, we will take a look at the precautions and reference points when using the DA# Macro.

---

<< List of related articles >>

• DA# Macro(1): DA#, DA# API, DA# Macro Overview
• DA# Macro(2): DA# Macro Function(1)-Common Function, Entity Get/Set
• DA# Macro(3): DA# Macro Function(2)-Attribute Get/Set
• DA# Macro(4): DA# Macro Function(3)-Reverse
• DA# Macro(5): Usage notes/notes, downloads, features to be added in the future, notes
• DA# Macro(6): DA# Modeler API
• DA# Macro function demonstration video (YouTube)
• DA# Macro Description Contents , Download