Each application table is suitable for different business scenarios. An application table is used to organize statistical data collected by atomic and derived metrics of the same statistical period, dimension, and statistic granularity. This allows you to perform subsequent business queries, online analytical processing (OLAP) analysis, and data distribution in an efficient manner. This topic describes how to create an application 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, an application table is stored at the application data service (ADS) layer and aggregates statistical data collected based on multiple atomic metrics, derived metrics, or statistic granularities. A statistic granularity is a dimension or a set of dimensions. The statistical data can be used for subsequent business queries and data distribution. You can also store application 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 data mart or a subject area is created to determine the business scenario based on which you want to aggregate statistical data. For information about how to create a data mart or a subject area, see Data mart or Subject area.
A period is created to determine the time range of which you want to aggregate statistical data. For information about how to create a period, see Period.
Background information
An application table aggregates statistical data that is collected based on multiple atomic metrics or derived metrics in a data mart or a subject area by period and associated dimensions that you specified. The associated dimensions, periods, atomic metrics, and derived metrics are used to generate statistical fields in the application table to help you analyze and display data in reports. You can use an application table to display the status of your business based on the statistical data that is collected based on multiple atomic or derived metrics within the same periods and from the same dimensions.
Create an application table
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 . 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 an application table
On the Dimensional Modeling page, move the pointer over the icon and choose .
In the Basic Information section of the configuration tab that appears, configure basic information for the application table.
Parameter
Description
Data Layer
The data layer at which you want to store the application table. ADS that belongs to the application layer category is selected by default. You can also store the application table at another data layer based on your business requirements. For information about how to create a data layer, see Create a data layer.
Mart/Subject
The data mart or subject area in which you want to store the application table. For information about how to create a data mart and a subject area, see Data mart and Subject area.
Granularity
The dimension that you want to associate with the application table. For more information about dimensions, see Create a conceptual model: dimension.
Period
The time range of statistical data that you want to aggregate in the application table. Examples: last day and last week.
You can select a period from existing periods. If the existing periods do not meet your business requirements, you can create a period. For more information about how to create a period, see Period.
Modifiers
The business scope of statistical data that you want to aggregate in the application table.
You can select one or more modifiers from the existing modifiers. If the existing modifiers do not meet your business requirements, you can create a modifier. For more information about how to create a modifier, see Modifier.
Naming Rule
The checker that is used to check whether the name of the application table conforms to the configured naming conventions. You can select the checker that you created at each data layer during data warehouse planning. For information about how to configure and use a checker, see Configure and use a checker at a data layer.
Name
The name of the application 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 application table.
Lifecycle
The lifecycle of the application table. Unit: day. The maximum value of this parameter is 36000.
Owner
The owner of the application table. The default owner is the creator of the application table.
Description
The description of the application table.
Click Save in the upper part of the configuration tab to save the basic information of the application table.
Add fields for the application table
You can add fields for a table in shortcut mode or script mode. The shortcut mode supports the following methods that can be used to add fields for a table:
Import from Table/View: This method allows you to import fields from existing physical tables or views that are created within a compute engine instance to the table. To use this method to add fields for an application 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 application table.
NoteYou can import fields only from physical tables or views that are created within MaxCompute, Hologres, and E-MapReduce (EMR) Hive compute engine instances.
Import from Metrics: This method allows you to use derived metrics that are created in Data Metric as fields in the table.
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 application 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 application table.
: imports specific fields from the selected physical table or view to the application 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 one of the imported fields has no display name, you can fill the description of the field as the display name of the field as prompted.
Shortcut Mode: Import from Metrics
Click Quick Import next to Import from Metrics.
In the Import from Metrics dialog box, select the desired derived metrics from all derived metrics that are created and displayed. You can also specify filter conditions such as Period, Business Process, Modifiers, and Atomic Metric to search for the desired derived metric.
Click Import in the lower part of the dialog box.
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 table
After fields are added for the fact table, you can configure Field Standard to Associate, 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, Data Type, Field Display Name, Description, Primary Key, Not Null, 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 Field Standard to Associate.
You can associate field standards with the added fields by configuring Field Standard to Associate for fields in the table.
Field Standard to Associate: 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.
Configure Redundant Field for fields in the 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.
Configure Associated Granularity/Metric for fields in the table.
You can configure Association Type to specify the statistical type of the value of each field in an aggregate or application table. Association Type has the following options: Statistical Granularity, Derived/Composite Metric, and Atomic Metric.
Statistical Granularity: allows you to associate a dimension table and fields in the dimension table with fields in an aggregate table or application table. For example, you can associate a commodity or merchant dimension table and fields in the commodity or merchant dimension table with fields in an aggregate table or application table.
Derived/Composite Metric: allows you to associate a derived metric with a field in an aggregate table or application table. For example, you can associate the derived metric payment amount of orders that are placed in the Hema application over the last seven days with a field in an aggregate table or application table.
Atomic Metric: allows you to associate an atomic metric with a field in an aggregate table or application table. For example, you can associate the atomic metric payment account of orders that are placed with a field in an aggregate table or application table.
NoteBy default, Association Type is not configured for fields that are imported from a table or added in script mode. You can configure Association Type for the fields based on your business requirements.
To modify Association Type for a field, perform the following operations. Click Associated Granularity/Metric in the Field Management section. In the Associated Granularity/Metric dialog box, modify Association Type and Associated Object for the field. Then, click OK.
Then, click Save in the upper-left corner of the configuration tab of the table.
What to do next
After the application table is created, you must configure partitions for the table and publish the table to the required environment. For more information, see Materialize a table to a compute engine.