DataWorks Data Modeling uses the dimensional modeling methodology developed by Ralph Kimball. When you use the Dimensional Modeling service of DataWorks to design a data warehouse model, you can design and create a dimension table, fact table, aggregate table, or application table based on your business requirements. Then, you can quickly publish the table to an R&D engine. You can use the reverse modeling feature to generate models based on existing physical tables in DataWorks Dimensional Modeling.

Perspectives for data modeling

Dimensional Modeling allows you to create tables at a common layer, an application layer, or the unidentified layer based on your business requirements. Different data layers support different perspectives for table management. Perspectives for data modeling
  • Common layer: A common layer is used to process and aggregate common data that is stored at a data import layer. You can create a unified metric dimension, create reusable fact tables that are used for data analysis and collection, and aggregate common data at a common layer. You can manage tables from the data domain or business category perspective at a common layer.
  • Application layer: An application layer is used to store data that is processed and aggregated at a common layer. You can store statistical data that is dedicated for specific application scenarios or specified products at an application layer. You can manage tables at an application layer only from the business category perspective.
  • Unidentified layer: This layer is used to store tables that do not belong to a common layer or application layer.
After you select a data layer, you can create tables only at the data layer. You can select a perspective that is supported by the data layer and view and manage tables from the perspective.

Dimension table

Extract all the dimensions that possibly exist in each data domain, and store the dimensions and attributes of the dimensions in dimension tables. For example, when you analyze e-commerce business data, possible dimensions (attributes of each dimension) include order (order ID, order creation time, buyer ID, and seller ID), user (gender and birthdate), and commodity (commodity ID, commodity name, and commodity put-on-shelf time). In this case, you can create the following dimension tables: order dimension table, user dimension table, and commodity dimension table. The attributes of each dimension are used as the fields in the dimension table. You can deploy the dimension tables in a data warehouse and perform extract, transform, and load (ETL) operations to store dimension data in the format defined in the dimension table. This allows business personnel to access the data for subsequent data analysis.

For information about how to design and create a dimension table, see Create a dimension table.

Fact table

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.

For information about how to design and create a fact table, see Create a fact table.

Aggregate table

You can use an aggregate table to store statistical data of multiple derived metrics that use the same period and dimension in a data domain. An aggregate table is obtained based on the abstraction and sorting results of business data and can be used for subsequent business queries, online analytical processing (OLAP) analysis, and data distribution. For information about how to design and create an aggregate table, see Create an aggregate table.

Application table

Different application tables are suitable for different business scenarios. An application table is used to aggregate atomic metrics and derived metrics of the same statistical period, dimension, and statistical granularity. This way, you can perform subsequent business queries, OLAP analysis, and data distribution in an efficient manner. You can design an application table based on your business requirements. For information about how to design and create an application table, see Create an application table.

Reverse modeling

Reverse Modeling allows you to generate models based on existing physical tables. This way, you do not need to manually create tables to generate models in Dimensional Modeling. This helps reduce the period of time that is required to generate models. For information about reverse modeling, see Reverse modeling.