Creating Fact Tables

Creating and Publishing a Fact Table

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 > Dimensional Modeling in the left navigation pane. On the displayed page, click the Fact Tables tab.
3. Select a subject from the subject tree on the left and click Create.
4. On the Create Fact Table page, perform the following operations:
   1. Set the parameters in the Basic Settings area.
      Figure 2 Basic Settings area
      

      Table 1 Parameters in the Basic Settings area

      Parameter	Description
      Subject	Select a subject (business domain group > business domain > business object) where you can place the fact table.
      Name	It must start with letters. Only letters, digits, and the following special characters are allowed: ()-_
      Table Code	Table codes must start with letters. Only letters, numbers, and underscores (_) are allowed.
      Data Connection Type	Select a data connection type from the drop-down list box.
      Data Connection	Select a data connection from the drop-down list box. It is recommended that the same data connection be used for dimension modeling.
      Database	Select a database from the drop-down list box.
      Queue	DLI queue. This parameter is displayed only for DLI data connections.
      Schema	DWS or POSTGRESQL mode. This parameter is displayed only for DWS and POSTGRESQL data connections.
      Table Type	DWS connections support the following tables: DWS_ROW: Tables are stored to disk partitions by row. DWS_COLUMN: Tables are stored to disk partitions by column. MRS_HIVE supports only HIVE_TABLE.
      Distributed By	This parameter is displayed only for DWS data connections. You must add a table field before selecting a table field from the drop-down list as a Distributed By field. Multiple table fields can be selected. Currently, only REPLICATION and HASH are supported. REPLICATION: A full table is stored on each DN. The advantage of this option is that each DN has all the data of a table. During the join operation, data redistribution can be avoided, reducing network overhead. The disadvantage is that each DN retains the complete data of a table, resulting in data redundancy. Generally, this option is recommended for small dimension tables. HASH: If you select this option, you must specify a distribution key for the user table. When a record is inserted, the system performs hash computing based on values in the distribute keys and then stores data on the corresponding DN. In a Hash table, I/O resources on each node can be used during data read/write, which improves the read/write speed of a table. Generally, this option is recommended for large dimension tables (a large dimension table contains over 1 million records).
      Owner	You can enter an owner name or select an existing owner.
      Description	A description of the fact table. Up to 600 characters are supported.

   2. On the Field Settings page, click Create and select Dimension or Measure to add a dimension or measure field.
      • If you select Dimension, select one or multiple dimensions in the displayed dialog box and click OK
      • If you select Measure, set required parameters to add a measure field.
      For details about the field parameters, see Table 2. After adding a field, you can click or to move the field up or down.

      Figure 3 Adding a dimension or measure field
      

      Table 2 Field parameters

      Parameter	Description
      Name	It must start with letters. Only letters, digits, and the following special characters are allowed: ()-_ The surrogate key name of the added dimension field is displayed automatically. Generally, you do not need to change the name.
      Field Code	Table codes must start with letters. Only letters, numbers, and underscores (_) are allowed.
      Data Type	Data type of the created dimension
      Primary Key	If this parameter is selected, the field is a primary key.
      Partition	If this parameter is selected, the field is a partition field.
      Not Null	Whether the parameter value can be left empty.
      Associate Standard	If you have created data standards, click to select one to associate with the field. If Create Data Quality Jobs is selected for Model Design Process on the Function Settings tab page in Configuration Center and a field is associated with a data standard, a quality job is automatically generated after a table is published. A quality rule is generated for each field associated with the data standard. The quality of the field 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.
      Associate Dimension	Only dimension fields need to be associated with dimensions. The name of the associated dimension. Click to replace the associated dimension.
      Role	Roles need to be assigned to dimension fields which are added for multiple times. This is not required for measure fields. If a dimension is added multiple times, set different roles to distinguish the dimensions.
      Description	A description of the dimension.

   3. On the Mapping Settings tab page, click Create Mapping and set mapping parameters.
      Figure 4 Configuring mapping parameters
      

      Table 3 Parameters of mappings

      Parameter	Description
      Mapping	Only letters, numbers, and underscores (_) are allowed.
      Model	Select a created relationship model from the drop-down list box. If no relationship model has been created, create one. See Designing Physical Models.
      Table	Select a table from which data is obtained. If data is obtained from multiple tables, click next to the table name to set the JOIN condition between the table and other tables. Select a JOIN mode. The JOIN mode includes left JOIN, right JOIN, inner JOIN, and outer JOIN from left to right. Set the JOIN condition in the JOIN field. Generally, select the fields with the same meaning in the source table and joined table. Click or to add or delete a JOIN condition. The relationship between JOIN conditions is AND. Click OK. If you want to delete a joined table after setting the JOIN condition, click next to the table name. Figure 5 Join Condition dialog box
      Field Mapping	Select a source field with the same meaning as the current mapping field. If a table field comes from multiple models, you must create multiple mappings. In each mapping, you only need to set the source field for the field that comes from the current mapping. Other fields do not need to be set.

5. Click Publish.
6. Wait for the reviewer to approve the fact table.

   After the fact table is approved, it is automatically created in the database.

7. Go back to the fact table list and locate the table just published. View its synchronization status in the Sync Status column.
   • If the synchronization is successful, the fact table is successfully published and created in the database.
   • If the synchronization failed, choose More > View History in the row where the fact table is located. On the page displayed, click the History tab to view logs. Troubleshoot the problem based on the logs. After the error is rectified, choose More > Synchronize above the fact table list to issue the synchronization command again. If the problem persists, contact technical support personnel.

Managing a Fact Table

After a fact table is created, you can access the Fact Tables page of Dimensional Modeling in DataArts Architecture. On the page displayed, you can edit, publish, suspend, and delete the fact table, as well as view the publish logs.

Figure 6 Fact table management

• Editing a fact table
  1. In the fact table list, select a fact table and click Edit to the right of it. The page for editing the fact table is displayed.
  2. Edit the table as required.
  3. Click Save to save the settings, or click Publish to publish the settings.
• Publishing a fact table
  1. In the fact table list, select a fact table and click Publish. The dialog box for publishing the fact table is displayed.
  2. Select a reviewer from the drop-down list box.
  3. Click OK.
• Viewing the publish history
  1. Select a fact table in the list and choose More > View History on the right.
  2. On the page displayed, you can view the publish history and version comparison information of the fact table.

     If the publish log includes error logs, the publishing has failed. You can click Resynchronize to synchronize the table to other DataArts Studio modules.

• Associating a fact table with a quality rule
  1. Select a fact table in the fact table list and click Associate Rule above the list.
  2. In the Associate Quality Rule dialog box, you can add rules to the fields in the fact table in batches and associate the rules with the fields.
  3. Click OK.
• Previewing an SQL statement
  1. Select a fact table in the list and choose More > Preview SQL on the right.
  2. On the page displayed, you can view or copy the SQL statement.
• Suspending a fact table
  1. In the fact table list, select a fact table and click Suspend. The dialog box for suspending a fact table is displayed.
  2. Select a reviewer from the drop-down list box.
  3. Click OK.
     

     • You can suspend or delete a fact table only when it is not referenced. For example, a fact table can be deleted only when it is not used by atomic metrics.
• Deleting a fact table

  If you no longer need a fact table, you can delete it. Fact tables that are to be published, already published, or to be suspended cannot be deleted.

  1. In the fact table list, select a fact table and choose More > Delete above the list.
  2. In the dialog box displayed, click Yes.
• Importing fact tables

  You can import fact tables to the system quickly.

  1. Above the fact table list, choose More > Import.
     Figure 7 Import Table dialog box
     
  2. Download the fact table template, and edit and save it.
  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 edited template to import.
  5. Click Upload. When the template is uploaded, the Last Import page is displayed. You can view the imported data.
  6. Click Close.
• Exporting fact tables

  Above the fact table list, choose More > Export to export fact tables.

Associating a Fact Table with a Quality Rule

1. On the DataArts Architecture page, choose Models > Dimensional Modeling in the left navigation pane.
2. Click the Fact Tables tab.
3. In the fact table list, click the name of the target fact table. Click Associate Rule.
   Figure 8 Associating a fact table with a quality rule
   
4. On the page displayed, set the parameters. After the configuration is complete, click OK.
   • Update Existing Rule: If this option is selected, the newly added rule will overwrite the old rule.
   • Table Field: This parameter applies to all fields by default. You can enter a regular expression to filter fields as required.
   • WHERE Clause: This parameter can be used to filter fields.
   • Generate Anomaly Data: If this option is enabled, anomaly data is stored in the specified database based on the configured parameters.
   • Database/Schema: database or schema that stores anomaly data. This parameter is displayed when Generate Anomaly Data is enabled.
   • Table Prefix: prefix of the table that stores anomaly data. This parameter is displayed when Generate Anomaly Data is enabled.
   • Table Prefix: prefix of the table that stores anomaly data. This parameter is displayed when Generate Anomaly Data is enabled.
   • Add Rule: You can click Add Rule to add a rule. For example, add a rule named Unique value, select the rule, click OK, enter an alarm condition expression in the Alarm Condition text box, add other rules in the same way, and click OK.
   • An alarm condition expression consists of alarm parameters and logical operators. When a quality job is running, the system calculates the result of the alarm condition expression and determines whether to trigger the alarm based on the result of the expression. If the expression result is true, the alarm will be triggered. Otherwise, no quality alarm will be triggered. In the Associate Quality Rule dialog box, the alarm parameters of each quality rule are displayed as buttons.
   Figure 9 Associating a fact table with a quality rule
   

Creating a Field in the Fact Table

1. On the DataArts Architecture page, choose Models > Dimensional Modeling in the left navigation pane.
2. Click the Fact Tables tab.
3. In the fact table list, click the name of the target table and click Edit in the Operation column.
4. Click Create in the Table Fields area, select a new field type from the drop-down list, and set the related parameters.
   Figure 10 Creating a field
   
5. After the configuration is complete, click OK.

Associating a Fact Table Field with a Data Standard

1. On the DataArts Architecture page, choose Models > Dimensional Modeling in the left navigation pane.
2. Click the Fact Tables tab.
3. Click the name of the target fact table in the list.
4. In the table field list on the details page of the fact table, search for the target field, click corresponding to the field to configure the association between the field and the data standard. For details on the sources of data standards, see Creating a Data Standard.
   Figure 11 Associating a fact table field with a data standard
   
5. After the configuration is complete, click OK.
   Figure 12 Associating a data standard
   

Associating a Fact Table Field with a Quality Rule

1. On the DataArts Architecture page, choose Models > Dimensional Modeling in the left navigation pane.
2. Click the Fact Tables tab.
3. In the fact table list, click the name of the target fact table.
4. In the table field list on the fact table details page, locate the target field and click to associate the field with a quality rule.
   Figure 13 Associating a fact table field with a quality rule
   
5. After the configuration is complete, click OK.
   Figure 14 Adding a rule
   

Associating Fact Table Fields with a Quality Rule in Batches

1. On the DataArts Architecture page, choose Models > Dimensional Modeling in the left navigation pane.
2. Click the Fact Tables tab.
3. In the fact table list, click the name of the target fact table.
4. In the table field list on the fact table details page, select the target table fields and click Associate Rule.
   Figure 15 Associating fact table fields with a quality rule
   
5. On the page displayed, add a rule and set the rule parameters.
   Figure 16 Adding a rule
   
6. After the configuration is complete, click OK.