Create a logical model: Application table

Modeling perspective

Dimensional modeling organizes model tables into three levels: Common layer, Application layer, and Uncategorized. The Common layer is used to build reusable unified metrics, dimensions, and detailed fact data, and supports management from either data domain or business category perspectives. The Application layer addresses business-specific statistical needs and supports only the business category perspective. After selecting a level, you can create and manage model tables in the corresponding directory tree.

Create an application table

1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Modeling in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Modeling.

2. In the top menu bar of the Data Modeling page, click Dimensional Modeling to go to the Dimensional Modeling page.

3. Create an application table.

   1. On the Dimensional Modeling page, hover over the icon and click Logical Model > Create Application Table.

   2. Configure the basic information for the application table.

      Parameter	Description
      Data Layer	The data layer where the application table is stored. By default, the system selects the application data service (ADS) layer under the Application Layer. You can also select another data layer based on your business requirements. For more information about how to create a data layer, see Define data warehouse layers.
      Mart/Subject	Select an existing data mart or subject area. For more information, see Data mart and Subject area.
      Granularity	Select an existing dimension. For more information, see Create a conceptual model: Dimension.
      Period	The time range of the statistical data to aggregate in the application table. For example, last day or last week. You must select from existing periods. If no existing period meets your business needs, you can create a new one. For more information, see Period.
      Modifier	The business scope of the statistical data. You must select from existing modifiers. If no existing modifier meets your business needs, you can create a new one. For more information, see Modifier.
      Naming Rule	Select a checker to validate the table naming rules. You can choose a checker that you previously created for each data layer during data warehouse planning. For more information, see Configure data warehouse layer checkers and Use checkers.
      Table Name	The name of the application table. If a naming rule is configured, the table name must comply with that rule.
      Table Display Name	The display name of the table.
      Lifecycle	The retention period of the table, in days. An application table can be retained for a maximum of 36,000 days.
      Owner	The owner of the application table. By default, this is the user who creates the table.
      Description	The description of the table.

4. Click Save in the upper-left corner.

Add table fields

You can add fields to the table in Shortcut Mode or Script Mode. Shortcut Mode supports the following import methods:

• Import from Table/View: Import fields from an existing physical table or view in the compute engine. In the Search for Existing Table/View drop-down list, find the physical table or view you want and import its fields.

  Note

  Currently, you can import fields only from tables or views in MaxCompute, Hologres, and EMR Hive engines.

• Import from Metrics: Select the derived metrics you want to add as model fields.

-- The table name cannot be modified after the physical table publishing process begins (i.e., when the model is pending approval, being published, or already published).
CREATE DIM TABLE dim_ec_pub_department_df ALIAS 'Department Dimension Table'
(
    id            ALIAS 'id' STRING COMMENT 'id',
    gmt_create    ALIAS 'Creation Time' TIMESTAMP COMMENT 'Creation Time',
    gmt_modified ALIAS 'Modification Time' TIMESTAMP COMMENT 'Modification Time',
    name          ALIAS 'Department Name' STRING COMMENT 'Department Name',
    parent_id     ALIAS 'Parent Department ID' STRING COMMENT 'Parent Department ID',
    `level`       ALIAS 'Level; 0: Group; 1: Subsidiary; 2: Business Unit; 3: Department.' BIGINT COMMENT 'Level; 0: Group; 1: Subsidiary; 2: Business Unit; 3: Department.',
    ds            ALIAS 'Business Date, yyyymmdd' STRING COMMENT 'Business Date, yyyymmdd'
)
COMMENT 'Department Dimension Table'
WITH('life_cycle'='1000');

Configure table field information

After adding the required fields to the model, you can configure the Associated Field, Redundant Field, and Associated Granularity/Metric for each field based on your business requirements.

1. Set field properties.

   By default, the field properties displayed include Field Name, Type, Field Display Name, Description, Primary Key, Not Null, Measurement Unit, and Actions. In the upper-right corner of the added fields list, click Field Display Settings to select which field properties to display and modify them as needed.

2. Set the Field Standard to Associate for the fields.

   Associate a field standard with the added fields to standardize their values and range.

   Field Standard to Associate: Centrally manages data that has the same meaning but different field names. It defines the value range, measurement unit, and other properties for the fields.

3. Set Redundant Field.

   In traditional star schema dimensional modeling, dimension tables store dimensions, and you access them through foreign keys in the fact table to reduce storage consumption. In DataWorks Data Modeling, to improve downstream query efficiency, simplify data access, and reduce the number of table joins, you can add frequently used fields as redundant fields (for example, user ID and common analysis dimensions).

   In the Actions column for an added field, click Redundant Field to configure its association.

   In the Redundant Field dialog box, select the data source type and specific table name from the Associated Table/View Name drop-down list. Confirm the field list in the table below, then select the target field from the Association Field drop-down list. Click Save to complete the configuration.

4. Set the Associated Granularity/Metric for fields.

   For aggregate and application tables, you can specify how each field value is calculated by setting the field's Association Type. Options include Statistical Granularity, Derived Metric, and Atomic Metric.

   • Statistical Granularity: Associates with a dimension table and fields within that dimension table. For example, a product dimension or a seller dimension.

   • Derived Metric: Specifies the derived metric whose statistical value the field will aggregate. For example, the total payment amount for orders placed on the Hema app in the last 7 days.

   • Atomic Metric: Specifies the atomic metric whose statistical value the field will aggregate. For example, the payment amount for an order.

   Note

   Fields imported from a table or added in script mode do not have a default association type. You must manually set the association type for these fields.

   After configuration, you can specify a field's associated object in the Field Association panel, located in the upper-right corner of the field list.

5. After completing the settings, click Save in the upper-left corner.