Create a logical model: dimension table

Create a dimension 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. On the Data Modeling page, click Dimensional Modeling in the top menu bar to open the Dimensional Modeling page.

3. Create a dimension table.

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

   2. Configure the basic information for the dimension table.

      Select the warehouse layer, data domain, business category, data mart, and other information for the table. You can later find the table in the corresponding object list. This example creates a public-layer dimension table. The main parameters are described below.

      Parameter	Description
      Data Layer	Select the warehouse layer for the dimension table. Different layers serve different business scenarios: Common Layer: Stores results of shared data processing and integration to build enterprise-wide dimension tables. After selecting it, specify a data domain. For instructions on creating a data domain, see Data domain. Application Layer: Builds business-specific dimension tables for application needs. After selecting it, specify a data mart. For instructions on creating a data mart, see Data mart. Note The default application data layer supports only application tables. To create a dimension table in an application layer, create a new application layer and set its model type to Dimension or Dimension tables and dimensions. For more information, see:Custom layers.
      Business Category	If you select the public layer, you can select the business category of the dimension table here.
      Data Domain	If you select the public layer, you can select the data domain of the dimension table here.
      Category/Mart/Subject	If you select the application layer, select the data mart or subject area under the business category here.
      Storage Policy	Specify the strategy for storing the dimension table's data, including its retention period and data volume range.
      Dimension	Select the dimension associated with the table to define the perspective for analyzing its data. Note For information about creating a dimension, see Create a conceptual model: dimension.
      Naming Rule	Use a configured checker to enforce naming rules for the dimension table. After selecting a checker, the table's Table Name must follow its rules. Note For information about configuring a checker, see Configure data warehouse layer checkers.
      Table Name	Name of the dimension table. If a table naming rule is configured, the name must follow it.
      Table Display Name	Display name of the dimension table.
      Lifecycle	Retention period of the dimension table, up to 36,000 days.
      Owner	Owner of the dimension table. The user who creates the table is selected by default.
      Description	Description of the dimension table.

4. When finished, click Save to create the dimension table.

   Find and manage the table under its data domain or business category in the left navigation tree of Dimensional Modeling.

Add table fields

After creating the model, add table fields.

You can use Shortcut Mode or Script Mode to add fields to the table. The Shortcut Mode option supports Import from Table/View: importing fields from an existing physical table or view in a compute engine. In the Find existing table/view drop-down list, search for and select a physical table or view to import fields.

-- After publication of the physical table starts (approval before publication, publication in progress, or publication succeeded), the table name cannot be changed.
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');

Set table field information

The fields required by the model have now been added. Set their Associated Field, Redundant Field, Associated Granularity/Metric.

1. Set field attributes.

   By default, basic attributes such as Field Name, Type, Field Display Name, Description, Primary Key, Not Null, Measurement Unit, Actions are displayed. Click Field Display Settings in the upper-right corner of the added fields to choose which field attributes to display and modify them as needed.

2. Set the field's Field Standard to Associate, Lookup Table to Associate. Associate an added field with a field standard and standard code to standardize its allowed values and range.

   After configuring fields, you can also click Association in the upper-right corner of the field list to set the associated Atomic Metric, Lookup Table and Field Standard.

   • Field Standard to Associate: Standardize data with the same meaning but different field names, including its allowed values and units of measurement.

   • Lookup Table to Associate: Specify the content and range of values allowed for a field standard.

3. Set the field's Redundant Field.

   In a traditional star schema, dimensions are stored in dimension tables and accessed through foreign keys in fact tables to reduce storage. In DataWorks Intelligent Data Modeling, frequently used fields, such as user IDs and common analysis dimensions, can instead be stored redundantly to improve downstream query efficiency, simplify data access, and reduce joins.

   Example 1: The Order Creation Detail Table can redundantly include the Delivery Address dimension from the Delivery Address Dimension Table, such as recipient address and phone number.

   Example 2: The Product Dimension Table can redundantly include procurement and brand information from the Product Information Table.

   In the Actions column of an added field, click Redundant Field to set its associated field.

   In the Redundant Fields dialog box, select the Associated table/view name (including the data source type and specific table name). Review available fields below, including names, types, primary keys, and partitions. Select the Associated field and click Save to finish.

4. After configuring the settings, click Save.