Designing Logical Models

Considerations in Logical Model Design

• You must consider not only the current business status, but also the future business development.
• Personnel who are familiar with the businesses must participate in the modeling. In this way, the business requirements can be fully integrated into the models.
• Converting the logical model to the physical model must be efficient.
• You must consider physical features during physical modeling.
• Each entity, attribute, and relationship must be consistent with the information in the actual business.

Creating a Logical Model

1. On the DataArts Studio console, locate an instance and click Access. On the displayed page, locate a workspace and click DataArts Architecture.
   Figure 1 DataArts Architecture
   

2. On the DataArts Architecture page, choose Models > ER Modeling in the left navigation pane.
3. If no ER model is available, the Create Hierarchical Governance Model dialog box is displayed, prompting you to create physical models for the SDI and DWI layers. After creating the physical models, you can click in the Logical Models area to create logical models.
   

   According to the data governance methodology and the ER and dimensional modeling methods, four layers of data warehouse models are available by default:

   • ER modeling consists of the SDI and DWI layers. Physical models belong to one of the two layers.
     • SDI stands for Source Data Integration and is the source data layer. SDI is a simple implementation of source system data.
     • DWI stands for Data Warehouse Integration, also called the data consolidation layer. DWI integrates and cleans data from multiple source systems, and implements entity relationship modeling based on the three normal forms.
   • In dimensional modeling, DWR-layer models are created based on dimensions, and data is aggregated into DM-layer models.
     • Data Warehouse Report (DWR) is based on the multi-dimensional model and its data granularity is the same as that of the DWI layer.
     • Data Mart (DM) is where multiple types of data are summarized. DM is designed to display the summarized data.

   The administrator can rename the models of the four layers by clicking next to the model names. The model name can contain only letters, digits, and underscores (_), and must start with a letter.

   Figure 2 Creating hierarchical governance models
   
   Figure 3 Creating a logical model
   

4. In the dialog box displayed, set the parameters and click OK.
   Figure 4 Configuring the logical model
   

   Table 1 Parameters for creating a logical model

   Parameter	Description
   *Name	Only letters, numbers, and underscores (_) are allowed.
   Prefix	Only letters, numbers, and underscores (_) are allowed. Dimension codes must start with letters. NOTE: When you create, modify, or import a logical entity to a logical model, the system checks whether a prefix is available. If no prefix is available, the verification fails. When you perform a reverse operation, the system checks whether a prefix is available. If no prefix is available, the system automatically adds a prefix.
   Description	A description of the logical model.

Creating and Publishing a Logical Entity

A logical entity is a logical table. After creating a logical model, you can create a logical entity in the model.

1. On the DataArts Architecture page, choose Models > ER Modeling in the left navigation pane.
2. On the displayed page, click a logical model to access its management page. Then, click Create.
3. On the displayed page, configure parameters as prompted.
   1. Set the basic parameters.
      Figure 5 Basic Settings
      

      Table 2 Parameters on the Basic Settings tab page

      Parameter	Description
      * Subject	Select a subject from the drop-down list box.
      Logical Entity Code	You can select Auto Generate or Custom.
      * Table Name	Logic entity name. The value can contain only letters, digits, brackets, commas (,), and the following special characters: +-#_[]/. It must start with a letter.
      * Table Code	Name of the physical table converted from the logical entity. Table codes cannot start with numbers. Only letters, numbers, and the following special characters are allowed: _${}
      Parent Logical Entity	Set a parent logical entity, which is inherited by child logical entities. Common logical entities and attributes can be logically abstracted as a parent logical entity. After specific attributes are added to the parent logical entity, a child logical entity is generated. The modifications to the attributes in a parent logical entity affect all child logical entities that inherit it.
      Tag	Tags are custom identifiers that help you classify and search for data assets. After adding a tag, you can search for related data assets in the DataArts Catalog module with ease. Click . In the dialog box displayed, select one or more existing tags, or enter a new tag name and press Enter. You can also go to the Tags page of the DataArts Catalog module to add a tag. Then, return to this page and select the newly added tag from the drop-down list box. For details, see Tags. If you want to modify the tag of a table in ER modeling, you must suspend the table first. After modifying the tag, you can publish the table again.
      Owner	You can enter an owner name or select an existing owner.
      * Description	A description of the table to create. It allows 1 to 200 characters.

   2. On the Logical Entity Attributes page, add required attributes. Table 3 lists the parameters for logical entity attributes.
      Figure 6 Adding a logical entity attribute
      

      Table 3 Parameters for logical entity attributes

      Parameter	Description
      *Field Name	The value can contain only letters, digits, brackets, commas (,), and the following special characters: +-#_[]/. It must start with a letter.
      *Field English Name	Only letters, numbers, and underscores (_) are allowed. A field code must start with a letter.
      *Code	Code of the logical attribute. If the logical entity uses a custom code, the code of the logical attribute can be customized or automatically generated.
      Data Type	Data type of the attribute. If you cannot find a desired data type from the drop-down list box, you can add a data type by referring to Field Types.
      Data Standard	If you have created data standards, click to select one to associate with the logical entity attribute. If Create Data Quality Jobs is selected for Model Design Process on the Function Settings tab page in Configuration Center and a logical entity attribute is associated with a data standard, a quality job is automatically generated after a logical entity attribute is published. A quality rule is generated for each logical entity attribute associated with the data standard. The quality of the logical entity attribute is monitored based on the data standard. You can access the Quality Job page of DataArts Quality to view the job details. If no data standard is available, create one. See Creating Data Standards for details. NOTE: After a logical entity is published, if the data standard code is modified, you must synchronize the dimension tables of the data standard to DataArts Catalog. Otherwise, the data standard code in the logical entity details cannot be updated.
      Primary Key	If this parameter is selected, the attribute is a primary key. NOTE: If you want to convert a logical model into a physical model, note the following restrictions for this parameter: If an MRS Spark connection is used to connect to MRS Hudi data sources, data can be written to the database only if fields have primary keys. Otherwise, table synchronization fails.
      Partition	If this parameter is selected, the attribute is a partition field.
      Not Null	Whether the parameter value can be left empty.
      Tag	You can click to add a tag for the logical entity attribute. In the dialog box displayed, select one or more existing tags. If no tag has been added, you can go to the Tags page of the DataArts Catalog module to add a tag. For details, see Tags. In the dialog box displayed, enter a new tag name and press Enter. Tag names can contain letters, numbers, and underscores (_), but cannot start with underscores (_).
      Security Level	You can click to add a security level for the logical entity attribute. If you cannot find the security level you want, click go to to go to the DataArts Security console and create a security level. You can disable this function on the Models tab page on the Configuration Center page.
      Description	A description of the table to create.

   3. On the Relationships tab page, click Add to create a relationship.

      A relationship refers to the association between a parent and a child entity (also called a primary and a secondary entity). It describes how an entity is associated with another entity, or the impact of an entity's behavior on another entity. Relationships between entities in a data model are particularly important and must be accurately defined. Otherwise, the actual business rules cannot be accurately described in the data model, and data consistency is greatly damaged.

      For example, if the student ID attribute of a score table is the primary key for a student table, the relationship between the two tables designed according to the third normal form (3NF) is as follows:

      • Child logical entity: score table
      • Child logical entity attribute FK: student ID
      • Child to parent:
      • Parent logical entity: student table
      • Parent logical entity attribute PK: student ID
      • Parent to child:
      Figure 7 Adding a relationship
      

      Table 4 Parameters on the Relationships tab page

      Parameter	Description
      Name	Name of the relationship
      Child Logical Entity	Select a child logical entity from the drop-down list box. Click to set the current logical entity as a child logical entity. For example, if the student ID attribute of a score table is the primary key for a student table, the child logical entity is the score table, and the corresponding parent logical entity is the student table.
      Child Logical Entity Attribute FK	Foreign key of the child logical entity attribute. The attribute of the child logical entity must be the foreign key of the parent logical entity. For example, if the student ID attribute of a score table is the primary key for a student table, the foreign key of the child logical entity attribute is the student ID in the score table.
      Child to Table	indicates that each piece of data in the child logical entity corresponds to only one piece of data in the parent logical entity. indicates that each piece of data in the child logical entity corresponds to at most one piece of data in the parent logical entity. indicates that one piece of data in the child logical entity corresponds to multiple pieces of data in the parent logical entity. indicates that one piece of data in the child logical entity corresponds to one piece of data in the parent logical entity at least.
      Parent to Child	indicates that the data in the parent logical entity is in one-to-one relationship with the data in the child logical entity. indicates that each piece of data in the parent logical entity corresponds to at most one piece of data in the child logical entity. indicates that one piece of data in the parent logical entity corresponds to multiple pieces of data in the child logical entity. indicates that each piece of data in the parent logical entity corresponds to at least one piece of data in the child logical entity.
      Parent Logical Entity	Select a logical entity that has a logical relationship with the selected child logical entity. For example, if the student ID attribute of a score table is the primary key for a student table, the parent logical entity is the student table, and the corresponding child logical entity is the score table.
      Parent Logical Entity Attribute PK	Primary key of the parent logical entity attribute. The attribute of the parent logical entity must be the primary key of the parent logical entity. For example, if the student ID attribute of a score table is the primary key for a student table, the primary key of the parent logical entity attribute is the student ID in the student table.
      Role	You can customize a role name to identify the relationship.
      Operation	Click to delete a relationship. Click to edit the relationship.

   4. On the Mappings page, click Create to create a mapping. Then click Save. Mapping means setting up a mapping relationship between the source and destination logical entity.
      Figure 8 Creating a mapping
      
      • Mapping is automatically generated when a mapping is created. You can change the value.
      • Source Logical Entity: If data comes from multiple logical entities of a model, you can click next to a logical entity to establish a JOIN relationship between the logical entity and another logical entity.
        Figure 9 Setting the JOIN condition for the source table
        

        Table 5 JOIN conditions

        Parameter	Description
        Joined Logical Entity	Select a logical entity for which you want to establish a JOIN relationship with the source logical entity.
        Joined Mode	Left JOIN, right JOIN, inner JOIN, and outer JOIN are represented from left to right.
        Joined Attribute	Generally, the JOIN attribute in the source logical entity is the same as that in the joined logical entity. You can click or to add or delete a JOIN attribute. The relationship between JOIN attributes is AND.

      • Logical Attribute Mapping: Select a source attribute with the same meaning as the current attribute.

4. Click Publish, select a reviewer, and click Submit.
   

   You can choose to publish the logical model to the production or development environment. By default, it is published to the production environment. If you do not choose an environment, the logical model cannot be published.

   Wait for the reviewer to approve the application. After the application is approved, return to the model list and view the created logical entity in the list.

   

   By default, Synchronize logical assets is selected for Model Design Process on the Functions tab page of the Configuration Center page.

   • For new logical models, you can click Publish to synchronize them to the logical assets of the DataArts Catalog module.
   • For historical logical models, you can click More and select Synchronize from the drop-down list box to synchronize them to the logical assets of the DataArts Catalog module.

Converting a Logical Model to a Physical Model

After a logical model is created, you can convert it to a new physical model or an existing physical model.

1. On the DataArts Studio console, locate an instance and click Access. On the displayed page, locate a workspace and click DataArts Architecture.
   Figure 10 DataArts Architecture
   

2. On the DataArts Architecture page, choose Models > ER Modeling in the left navigation pane.
3. Find the required logical model and click the conversion button on the model.
   Figure 11 Logical model conversion
   
4. In the Convert to Physical Model dialog box, set the parameters and click OK.
   Figure 12 Convert to Physical Model dialog box
   
   

   During the conversion of a logical model into a physical model, the system checks whether the logical model has a prefix. If the model does not have a prefix, the system automatically adds a prefix to the model.

   Table 6 Parameters

   Parameter	Description
   *Model Name	The name of the physical model to be converted from a logical model. You can enter a new model name, and then the system creates the model. You can also select an existing model name from the drop-down list box. Only letters, numbers, and underscores (_) are allowed.
   *Update Existing Table	This parameter is displayed when a model name is selected. No Yes If you select Yes, you need to set Physical Table Update Mode. Retain unnecessary fields Delete unnecessary fields
   *Data Connection Type	Select a data connection type from the drop-down list box.
   Data Connection	The name of the data connection. Select the required data connection. You are advised to use the same data connection for an ER model. If no data connection is available, access Management Center to create one. For details, see Managing Data Connections.
   Database	The name of the database. Select a database from the drop-down list box. If no database is available, access DataArts Factory to create one. For details, see Creating a Database.
   Select Logical Entity	All: Convert all logical entities into physical tables. Partial: Convert the selected logical entities into physical tables.
   Queue	DLI queue. This parameter is available only for DLI data connections.
   Schema	Schema of DWS or POSTGRESQL. This parameter is available only for DWS and PostgreSQL data connections.
   Description	A description of the model. Up to 600 characters are supported.

5. After the model is converted to a physical model, you can set layers for the physical model. You can select the SDI or DWI layer. As shown in Figure 13, move the cursor to the card of the physical model and click the edit button of the model.
   Figure 13 Setting layers for the physical model
   

   In the displayed dialog box, select SDI or DWI for Data Warehouse Layer.

   • SDI stands for Source Data Integration and is the source data layer. SDI is a simple implementation of source system data.
   • DWI stands for Data Warehouse Integration, also called the data consolidation layer. DWI integrates and cleans data from multiple source systems, and implements entity relationship modeling based on the three normal forms.
   Figure 14 Editing the physical model
   

Importing Logical Entities by Reversing a Database

By reversing databases, you can import one or more created database tables from other data sources into a logical entity directory to turn them into logical entities.

1. On the DataArts Architecture console, choose ER Modeling in the navigation pane on the left. Click a logical entity to access it.
2. Above the logical entity list, click Reverse Database.
3. In the displayed dialog box, set required parameters and click OK.

   Table 7 Parameters for reversing the database

   Parameter	Description
   *Subject	Select a subject from the drop-down list.
   *Data Connection Type	The data connection types supported by the reverse database are displayed in the drop-down list box. Select the required data connection type.
   *Data Connection Name	Select a data connection. If you want to reverse a database from other data sources to a logical entity directory, you must create a data connection in Management Center to connect to the data source. For details on how to create data connections, see Managing Data Connections.
   *Database	Select a database.
   *Schema	Select a value from the drop-down list box. This parameter is available only for DWS and PostgreSQL tables.
   Queue	DLI queue. This parameter is available only when Data Connection Type is set to DLI.
   Update Table	When Yes is selected, if the name of the reversed table is the same as that of an existing table in the logical entity list, the logical entity is updated.
   *Data Table	You can select All or Partial.

   Figure 15 Reverse Database dialog box
   

4. You can view the result on the Last Reverse tab page. If the reverse operation is successful, click Close. If the reverse operation fails, you can view the failure cause. After the fault is rectified, select the table again and click Reverse to retry.
   Figure 16 Last Reverse tab page
   

Importing Logical Entities

Importing an Excel file

1. Above the logical entity list, click Import and select Import EXCEL. In the Import Table dialog box, click the Import tab and then ER Modeling Template.
   Figure 17 Importing an Excel file
   

2. Edit and save the downloaded template.
3. Choose whether to update existing data.
   

   If a code in the template already exists in the system, the data is considered duplicate.

   • No: If the data to be imported already exists in the system, the existing data in the system will not be replaced.
   • Yes: If the data to be imported already exists in the system:
     • If the existing data in the system is in draft state, the data will be replaced and new draft data will be generated.
     • If the existing data in the system is in published state, expanded data will be generated.
4. Click Select File and select the template you have edited and saved.
5. Click Upload. When the template is uploaded, the Last Import page is displayed. You can view the imported data.
6. Click Close.

Importing an LDM model

• Before importing an LDM model, select a theme. Otherwise, the model cannot be imported.
• Only logical models can be imported.
• Prepare an .ldm logical model exported from the third-party system Powet Designer in advance.
• LDM models of version 16.x can be imported.

1. Above the logical entity list, click Import and select Import LDM. In the Import Table dialog box, click the Import tab.
   Figure 18 Importing an LDM model
   

2. Choose whether to update existing data.
   • No: If the data to be imported already exists in the system, the existing data in the system will not be replaced.
   • Yes: If the data to be imported already exists in the system:
     • If the existing data in the system is in draft state, the data will be replaced and new draft data will be generated.
     • If the existing data in the system is in published state, expanded data will be generated.
3. Click Select File and select the prepared .ldm logical model.
4. Click Upload. When the template is uploaded, the Last Import page is displayed. You can view the imported data.
5. Click Close.

Exporting Models

1. Above the logical entity list, click Export.
2. Select the object to be exported.

   Select Table or DDL.

   If you select DDL, select ALL or Partial for Scope. If you select Partial, select the tables to be exported.

3. Click OK.