DataWorks:Logical model: Fact table

Prerequisites

Before you begin, ensure that you have:

• A Data Warehouse Layer defined. Fact tables are placed in the Data Warehouse Detail (DWD) layer of the Public Layer. For more information, see Define a Data Warehouse Layer

• A Business Process created that represents the business activity the fact table will store. For more information, see Business Process

How it works

When you create a fact table, you specify the Data Warehouse Layer where its data is stored, and associate it with a Business Category and Business Process for easier discovery.

After the table is created, you add fields and configure associations, partitions, and data standards to ensure consistency across your data domain. Once configured, publish and materialize the fact table to a computing engine to make the data available for analysis.

Create a fact table

1. Go to the Data Modeling page. Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Development and O\&M \> Data Modeling. Select the desired workspace from the drop-down list and click Go to Data Modeling.

2. In the top menu bar, click Dimensional Modeling.

3. Create the fact table.

   1. Hover over the icon and click Logical Model \> Create Fact Table.

   2. Configure the basic parameters.

      

      Parameter	Description
      Data layer	Select an existing Data Warehouse Layer. Only the DWD of the Public Layer is supported. For more information, see Define a Data Warehouse Layer.
      Business category	Select an existing business category. For more information, see Business Category.
      Business process	Select an existing business process. For more information, see Business Process.
      Storage policy	Specifies the storage policy for the fact table. Options include Daily Incremental Data and Daily Full Data.
      Naming rule	Select a pre-configured checker to verify that the table name complies with your naming conventions. For more information, see Configure Data Warehouse Layer checkers and Use checkers.
      Name	The internal name of the table. If a Naming rule checker is configured, the name must comply with those rules.
      Display name	The display name shown in the console.
      Lifecycle	The retention period for data in the fact table, up to 36,000 days.
      Owner	The person responsible for the table. Defaults to the user who creates it.
      Description	A description of the fact table.

4. Click Save. The new table appears in the directory tree on the left.

Add fields to the fact table

Add fields using Shortcut Mode or Script Mode.

Shortcut mode

Shortcut Mode lets you import fields from an existing physical table or view in your computing engine.

Field import in Shortcut Mode is only supported for tables and views in MaxCompute, Hologres, and EMR Hive.

1. In Shortcut Mode, click Expand next to Import from Table/View.

2. In the Search For Existing Table/View input box, enter a name to search. Select the table or view from the results, then import all or a selection of its fields.

   - Fuzzy search is supported. Enter a keyword to find all tables or views whose names contain that keyword. - Only tables in the production environment are searchable. Development environment tables are not supported. - Click to import all fields. - Click to import a selection of fields.

3. If you choose to import a selection of fields, select the fields from the dialog box and click Import.

4. If any imported fields have an empty Field Display Name, follow the on-screen prompts to set the display name from the field's description.

Script mode

Script Mode lets you define the model with FML statements. Click Script Mode to open a dialog box with modeling language auto-generated from the current configuration. Edit the FML and click OK.

For a full reference on FML syntax, see Model in Script Mode.

Configure field properties

After adding fields, configure their properties to standardize field content and define associations.

1. View and edit field properties. The field list displays the following properties by default: Field Name, Data Type, Field Display Name, Description, Primary Key, Not Null, and Actions. To show or modify additional properties, click Field Display Settings in the upper-right corner of the field list.

2. Set Field Standard to Associate and Lookup Table to Associate for fields that require standardization.

   • Field Standard to Associate: Standardizes fields that share the same meaning but have different names, by defining consistent value ranges, units of measure, and more.

   • Lookup Table to Associate: Defines the set of valid values for a specific field.

3. Configure redundant fields. In a star schema, dimension attributes are stored in separate dimension tables and accessed through foreign keys in the fact table. To improve query performance and reduce joins, you can add frequently used dimension attributes directly to the fact table as redundant fields. For example, an order fact table might include the shipping address dimension from the shipping address dimension table — redundantly storing attributes like recipient address and recipient phone number — so downstream queries can retrieve them without joining the dimension table. In the Actions column for a field, click Redundant Field to configure its associated fields.

   

4. Click Save in the upper-left corner.

What's next

After creating and configuring the fact table, publish and materialize it to a computing engine to make it available for data analysis. For more information, see Materialize a logical model.