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 go to the Dimensional Modeling page.

3. Create the dimension table.

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

   2. Configure the basic properties of the dimension table.

      You can specify properties such as the data warehouse layer, Data Domain, Business Category, and Data Mart for the dimension table. After the table is created, you can find it listed under these entities. This topic uses the creation of a Common Layer dimension table as an example. The key parameters are described below.

      Parameter	Description
      Data Layer	Select the data warehouse layer for the dimension table. Different layers serve different purposes: Common Layer: Stores processed and integrated shared data to build unified, enterprise-level dimension tables. If you select this layer, you must also specify a Data Domain. For more information about how to create a Data Domain, see Data Domain. Application Layer: Builds business-specific dimension tables based on application requirements. If you select this layer, you must also specify a Data Mart. For more information about how to create a Data Mart, see Data Mart. Note By default, the Application Layer only allows the creation of application tables. To create a dimension table in the Application Layer, you need to create a new application layer and set its model type to Dimension or Dimension Table, Dimension. For more information, see Custom layers.
      Business Category	If the data warehouse layer is set to Common Layer, you can select the Business Category for the dimension table.
      Data Domain	If you select Common Layer for the data warehouse layer, you can select the Data Domain for the dimension table.
      Category/Mart/Subject	If you select Application Layer for the data warehouse layer, you must select a mart or theme under Business Category.
      Storage Policy	The policy that defines how the dimension table stores data, including the retention period and data volume.
      Dimension	The dimension to associate with the dimension table. This defines the perspective for data analysis. Note To create a dimension, see Create a conceptual model: Dimension.
      Naming Rule	A checker that enforces naming conventions for the dimension table. If you select a checker, the Table Name must conform to its rules. Note To configure a checker, see Configure data warehouse layer checkers.
      Table Name	The name of the dimension table. If you configured a naming rule, the name must comply with the rule.
      Table Display Name	The display name of the dimension table.
      Lifecycle	The retention period for the dimension table, in days. The maximum value is 36,000.
      Owner	The owner of the dimension table. Defaults to the user who creates the table.
      Description	A description of the dimension table.

4. After you finish the configuration, click Save.

   You can find and manage the dimension table under its corresponding Data Domain or Business Category in the left-side navigation tree.

Add table fields

After the logical model is created, you must add fields to the table.

You can add fields in either Shortcut Mode or Script Mode. In Shortcut Mode, you can use the Import from Table/View feature to import fields from existing physical tables or views in a compute engine. Search for and select a table or view from the Find Existing Table/View drop-down list.

-- After the model is published, the table name cannot be modified, regardless of its publishing status (pre-approval, in progress, or successful).
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' BIGINT COMMENT 'The department 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 field information

After you add 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 list displays basic attributes such as Field Name, Type, Field Display Name, Description, Primary Key, Not Null, Measurement Unit, and Actions. In the upper-right corner of the field list, click Field Display Settings to select which properties to display and modify them.

2. Set the Field Standard to Associate and Lookup Table to Associate for the fields. This allows you to associate the added fields with a column standard and a lookup table, which standardizes their content and value ranges.

   Alternatively, after configuring the fields, you can click Associate in the upper-right corner of the field list to uniformly set the associated Atomic Metric, Lookup Table, and Field Standard.

   • Field Standard to Associate: Governs fields that have the same meaning but different names. It defines properties such as value range and unit of measurement.

   • Lookup Table to Associate: Defines the valid content and range of values for a specific column standard.

3. Set the Redundant Field for the fields.

   In a traditional star schema, dimensions are stored in dimension tables and accessed through foreign keys in the fact table to reduce storage. In contrast, DataWorks' intelligent dimensional modeling encourages adding frequently used fields, such as a user ID or common analytical dimensions, as redundant fields. This practice improves downstream query performance, simplifies data retrieval, and reduces the number of table joins.

   Example 1: An "order creation detail table" can include the "shipping address" dimension from a "shipping address dimension table" as a redundant field, which contains attributes such as the recipient's address and phone number.

   Example 2: A "product dimension table" can include attributes such as "procurement information" and "brand information" from a "product information table" as redundant fields.

   In the Actions column for a specific field, click Redundant Field to configure its associated fields.

   In the Redundant field configuration dialog box, select the Associated table/view name, including the data source type and the specific table name. The field list below shows the available fields for association, with properties such as name, type, primary key, and partition. Select the associated field and click Save.

4. After you finish the configuration, click Save in the upper-left corner.