A fact table can be used to store a large amount of real data and detailed values that reflect the status of business activities. This table is a result table that presents the data results of a particular dimension after data aggregation. For example, if you want to analyze product sales, you can create a fact table that stores data of the product dimension, time dimension, and total sales volume. The product dimension and time dimension are used as foreign keys. This topic describes how to create a fact table.
- A data layer is created. Each data layer stores tables that serve the same purpose. 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 more 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 more information about how to create a business process, see Business process.
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.
- 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. This way, 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 specify each field against a uniform data standard. 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.
- Go to the Data Modeling page.
- Go to the Dimensional Modeling page. In the top navigation bar of the Data Modeling page, click Dimensional Modeling.
- Create a fact table. On the Dimensional Modeling page, click the icon, select Create Fact Table, and then specify the parameters that are described in the following table.
Parameter Description Data Layer Set the parameter to the created data layer. The fact table data is stored at the data layer when the fact table is used in the future. For more information, see Create a data layer. Business Process Set the parameter to the created business process. For more information, see Business process. Business Category Set the parameter to the created business category. For more information, see Business category. Storage Policy The policy that is used by a fact table to store data. Valid values include Daily Increment (di) and Daily Full (df). Naming Rule Set the parameter to the configured checker. The parameter checks whether the names of tables at a data layer conform to the naming conventions that are specified in the checker. For more information about how to configure a checker, see Configure a data layer checker. Name The internal name of the table. The name can contain lowercase letters, digits, and underscores (_), and must start with a lowercase letter. Display Name The display name of the table. The display name can contain letters, digits, underscores (_), ampersands (&), and parentheses (). It must start with a letter or a digit. Lifecycle The lifecycle of the fact table. Table Type Set the parameter to Transaction Fact Table, Periodic Snapshot Fact Table, or Accumulating Snapshot Fact Table based on your business requirements. Description The description of the fact table.
- After the parameters are configured, click Save. You can view the fact table in the left-side directory tree.