A fact table can be used to store a large amount of real data or detailed values that can reflect the status of business activities. A fact table is a result table that stores the data results of a particular dimension after data is aggregated. For example, if you want to analyze product sales, you can create a fact table that stores the data of the product dimension, time dimension, and total sales volume. The product dimension and the time dimension are used as foreign keys. This topic describes how to create a fact table.
Prerequisites
A data layer is created. You can store tables that are used for the same purpose at the same data layer. This helps you easily find and use tables. In most cases, fact tables are stored at the data warehouse detail (DWD) layer. You can also store fact tables at other data layers based on your business requirements. For information about how to create a data layer, see Create a data layer.
A business process is created. A business process determines the type of business activity data that can be stored in a fact table. For information about how to create a business process, see Business process.
Background information
Sort and analyze data that is generated in each business process, and store the data in fact tables as fields. For example, you can create a fact table for the business process of placing an order, and record the following information as fields in the fact table: order ID, order creation time, commodity ID, number of commodities, and sales amount. You can deploy the fact tables in a data warehouse and perform ETL operations to summarize and store data in the format defined in the fact table. This allows business personnel to access the data for subsequent data analysis.
As illustrated in the preceding figure:
When you create a fact table, take note of the following points:
You can specify the business category and business process that are analyzed by using the fact table. You can view the fact tables of a specific business category or business process in the future.
You can specify the data layer that stores the fact table when the fact table is used for data modeling analysis. In most cases, fact tables are stored at the DWD layer.
After you create a fact table, you can add fields for the fact table. You can also associate the fact table with fields of other fact tables, partition the fact table, and use the same field standard to define value ranges for fields in the fact table. This ensures consistent attributes for the data across the entire data domain.
After you create and configure a fact table, you can publish and materialize the fact table to a compute engine. This way, the fact table can be used in the compute engine for data analysis.
Create a fact table
Go to the Data Modeling page.
Log on to the DataWorks console. In the left-side navigation pane, choose . On the page that appears, select the desired workspace from the drop-down list and click Go to Data Modeling.
In the top navigation bar of the Data Modeling page, click Dimensional Modeling to go to the Dimensional Modeling page.
Create a fact table.
On the Dimensional Modeling tab, move the pointer over the
icon and choose . In the Basic Information section of the configuration tab that appears, configure basic information for the fact table.
Parameter
Description
Data Layer
The data layer to which the fact table belongs. DWD that belongs to the common layer category is selected by default. If you use the fact table in subsequent operations, data in the fact table is stored at the DWD layer. For information about how to create a data layer, see Create a data layer.
Business Process
The business process to which the fact table belongs. For information about how to create a business process, see Business process.
Business Category
The business category to which the fact table belongs. For more information, see Business category.
Storage Policy
The policy based on which you want to store data in the fact table. You can select a storage policy such as Daily Incremental Data or Daily Full Data.
Naming Rule
The checker that is used to check whether the name of the fact table conforms to the configured naming conventions. The checker that is used to check whether the name of the fact table conforms to the configured naming conventions. For information about how to configure a checker, see Configure and use a checker at a data layer.
Name
The name of the fact table. If you configure the Naming Rule parameter, the name that you specify must conform to the naming conventions defined in the checker specified by the Naming Rule parameter.
Display Name
The display name of the fact table.
Lifecycle
The lifecycle of the fact table. Unit: day. The maximum value of this parameter is 36000.
Owner
The owner of the fact table. The default owner is the creator of the fact table.
Description
The description of the fact table.
Table Type
The type of the fact table. You can select one of the following table types: Transaction Fact Table, Periodic Snapshot Fact Table, and Accumulating Snapshot Fact Table.
After the parameters are configured, click Save. You can view the fact table in the left-side navigation tree.
Add fields for the fact table
After you configure basic information for the fact table, you must add fields for the fact table.
You can use fast modeling language (FML) statements to add fields for a table, configure attributes for the fields, and configure associations and partitions for the table. For more information, see Use FML statements to configure and manage data tables.
You can add fields for the fact table in shortcut mode or script mode. The shortcut mode supports the following method to add fields for a fact table: Import from Table/View. To use this method to add fields for the fact table, you must import all or specific fields from a physical table or view that is selected from the Search for Existing Table/View drop-down lists to the fact table.
You can import fields only from physical tables or views that are created within MaxCompute, Hologres, and E-MapReduce (EMR) Hive compute engine instances.
Shortcut Mode: Import from Table/View
Click Expand next to Import from Table/View.
In the Search for Existing Table/View drop-down lists, select a compute engine type from the left one, and enter a keyword and select the desired physical table or view from the displayed search results from the right one. Click the following icons next to the name of the selected physical table or view to import all or specific fields from the physical table or view to the fact table.
NoteWhen you search for the desired physical table or view, keyword-based fuzzy match is supported. After you enter a keyword, all physical tables or views whose names contain the keyword are displayed.
You can search for only tables in the production environment. You cannot search for tables in the development environment.
: imports all fields from the selected physical table or view to the fact table. 
: imports specific fields from the selected physical table or view to the fact table.
If you choose to import specific fields, select the fields from the physical table or view in the dialog box that appears. Then, click Import.
NoteIf a field in the fields that are imported has no display name, you can fill the description of the field as the display name of the field as prompted.
Script Mode
The script mode allows you to edit code to manage the fields in the table. After you click Script Mode, the statement that is used to create the table is displayed in the code editor in the Script Mode dialog box. You can modify the statement based on your business requirements. Then, click OK.
Configure attributes and associations for fields in the fact table
After fields are added for the fact table, you can configure Associated Field Standard, Associated Lookup Table, Redundant Field, and Associated Granularity/Metric for the fields.
Configure attributes for fields in the fact table.
By default, the following basic attributes are displayed for fields: Field Name, Type, Field Display Name, Description, Primary Key, Not Empty, Measurement Unit, and Actions. You can click Field Display Settings in the upper-right corner of the list of the fields that are added and select the attributes that you want to display. You can also modify the values of the attributes based on your business requirements.
Configure Associated Field Standard and Associated Lookup Table for fields in the table.
You can associate field standards and lookup tables with the added fields by configuring Associated Field Standard and Associated Lookup Table for fields in the table.
Associated Field Standard: allows you to manage the values of the fields that have the same meaning but different names in a centralized manner. A field standard can also be used to define the value range and measurement unit for the fields.
Associated Lookup Table: allows you to define the value range of the fields that use a field standard.
Configure Redundant Field for fields in the fact table.
You can find the field for which you want to configure redundant fields and click Redundant Field in the Actions column. In the dialog box that appears, select one or more fields that you want to associate with the field and click Save.
Then, click Save in the upper part of the configuration tab of the fact table.
What to do next
After the preceding configuration is complete, you must configure partitions for the table and publish the table to the required environment. For more information, see Publish and materialize a table.