DataWorks Data Modeling allows you to define data standards before or during the modeling process. By standardizing field standards, lookup tables, measurement units, and naming dictionaries, you can ensure consistency in data processing for later modeling and application development. This approach promotes standardized data production from the source, reducing the costs of data application and processing.
Supported data standards
DataWorks supports the following data standards: Field Standard, Lookup Table, Measurement Unit, and Naming Dictionary.
Field standard
A field standard normalizes the definition of a field, including its name, data type, and value range. Standardizing fields with the same meaning prevents confusion caused by inconsistent names or types. For example, create a field standard named member_id and apply it to relevant tables to ensure that all member ID fields are standardized.
Table name | Original field | Issue description | Standardized field |
Registration table | user_id | Inconsistent naming convention | member_id |
Logon table | userid | Missing underscore |
Lookup tables
Lookup tables define the permissible value range for a field. For example, the values for a gender field might be restricted to Male, Female, or Unknown.
Measurement units
Define the measurement units used in your business. For example, the unit of measure for item_quantity is pieces.
Naming dictionaries
A naming dictionary provides standard terms and translations for business terms, physical tables, and fields. It serves as an enterprise-wide library for naming conventions. For example, the standard term for a company's annual revenue can be defined as Annual Gross Revenue.
Data standard relationship diagram
In this context, an association refers to linking a data standard to a specific field in a logical model. The field must comply with the standard's rules.
Access data standards
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.
On the Data Modeling page, click Data Standard in the top navigation bar.
Data standards: Field standards
A field standard is a standardized definition of a field, which specifies its naming, data type, and value range. Use field standards to associate fields that have the same meaning but different names across multiple tables. If the field standard changes later, you can quickly locate and modify the associated tables.
Hierarchy
When you create a field standard, you must place it under the root directory, a directory, or a standard set.
Root directory: The top-level directory. All other directories, standard sets, and standards must be placed under the root directory.
Directory: Used to store standards and standard sets, similar to a folder in an operating system.
Standard set: Similar to a directory, but can only contain standards.
Field standards can have inheritance relationships. For example, both buyer ID and seller ID standards can inherit from a member ID standard.
Define a field standard
If you need to enter a large number of field standards, you can use the batch import feature.
On the Data Standard page, click Field Standard in the left-side navigation pane.
In the directory tree on the left, right-click the target directory or standard set and click Create Standard.
Configure key parameters in the dialog box
Parameter
Description
Abbreviation
Used as the name of the associated field in the logical model.
Display Name
Used as the field display name of the associated field in the logical model.
Length
A parameter related to the data type. For example, for the DECIMAL type, this corresponds to 20 in DECIMAL(20, 4).
Precision
A parameter related to the data type. For example, for the DECIMAL type, this corresponds to 4 in DECIMAL(20, 4).
Not Empty
Specifies whether the field that uses this standard can be null. The default value is nullable.
Default Value
The default value for the field if no value is provided. The maximum length is 2048 characters.
Parent Standard
Select an existing standard as a parent to establish an inheritance relationship. This helps you better identify field relationships.
For example, because buyer IDs and seller IDs are types of member IDs, the parent standard for both the Buyer ID Standard and Seller ID Standard would be the Member ID Standard.
Referenced Lookup Table
Select a table from the created lookup tables to constrain the value range of the field.
ImportantRemove all references before deleting a field standard.
Use a field standard
Use field standards to define specific fields in logical models, including source tables, dimension tables, fact tables, aggregate tables, and application tables. For example, you can associate the member ID field in the member information dimension table dim_ecom_mbr_info_df with the field standard member_id. In this case, the field name comes from the standard's abbreviation, the display name comes from the standard's display name, and the Type and Not Empty properties are also inherited. For more information see Configure attributes and associations for fields in the dimension table.
Data standards: Lookup tables
Lookup tables define the permissible value range for a field.
Define a lookup table
If you need to enter a large number of lookup tables, you can use the batch import feature.
On the Data Standard page, click Lookup Table in the left-side navigation pane.
Right-click a directory name and click Create Lookup Table.
In the dialog box, configure the parameters and add enumerated values.
For example, set ID to gender, Display Name to gender, and Name to gender.
Code ID
Code Name
Name
Description
0
Unknown
unknown
Gender not specified
1
Male
male
Male
2
Female
female
Female
ImportantRemove all references before deleting a lookup table.
Publish a lookup table
On the lookup table's details page, click Publish in the upper-right corner to publish the lookup table as a physical table or a materialized view.
Use a lookup table
Use standard codes to define specific fields in logical models, including source tables, dimension tables, and fact tables. For example, you can associate the gender field in the member information dimension table dim_ecom_mbr_info_df with the gender field in the lookup table. In this case, the field name comes from the lookup table's ID, and the display name comes from the lookup table's Display Name. For more information, see Configure dimension table fields.
If a field has different names across multiple tables, associating a lookup table can automatically correct and unify the field name.
Table name | Original field | Original enumerated value | Standardized field | Standardized enumerated value |
Member information table | sex | 1, 2 | gender | 0, 1, 2 |
Member logon table | gender | 0, 1, 2 |
Implement standards based on lookup tables
When a fact table or dimension table in a logical model is published to a physical table, you can generate quality rules for the specific fields that are associated with standard codes. Then create monitoring rules based on these quality rules to monitor and enforce standards in the physical table. For more information, see Generate monitoring rules based on data standards.
Data standards: Measurement units
Define the measurement units relevant to your business, such as currency, quantifiers, and time units.
Define a measurement unit
If you need to enter a large number of measurement units, you can use the batch import feature.
On the Data Standard page, click Measurement Unit in the left-side navigation pane.
Right-click a directory name and click Create Measurement Unit.
In the dialog box, configure the parameters and click OK.
For example, set Abbreviation to m, Name to meter, and Display Name to meter.
Use a measurement unit
Associate with a logical model
Use a unit of measure to define the measurement unit for specific fields in a logical model, including fact tables, aggregate tables, and application tables. For example, you can associate the item_quantity field in the order creation fact table dwd_trade_order with the unit of measure pieces. For more information, see Configure attributes and associations for fields in the fact table.
Associate with an atomic metric
When defining an atomic metric, select an appropriate unit of measure based on the statistical data type of the atomic metric.
Data standards: Naming dictionaries
A naming dictionary provides standard terms and translations for business terms, physical tables, and fields. It serves as an enterprise-wide library for naming conventions.
Define a naming dictionary
If you need to enter a large number of naming dictionary entries, you can use the batch import feature.
On the Data Standard page, click Naming Dictionary in the left-side navigation pane.
Click Create. In the dialog box, configure the parameters and click OK.
For example, set Display Name to Engine, Name to engine, and Abbreviation to eng.
Use a naming dictionary
Use the naming dictionary to check the naming compliance of tables in your data warehouse layers, including source tables, dimension tables, fact tables, aggregate tables, and application tables. For example, if a naming dictionary entry with the abbreviation trade does not exist, the table dwd_trade_order would not comply with the naming convention for the DWD fact data layer.
To use this feature, configure it in the checker within Data Warehouse Planning. For more information, see Configure data warehouse layer checkers and Use checkers.
More operations
Batch import data standards
If you have a large number of data standards to create, you can import them in bulk. DataWorks provides an import template that you can fill out and use for bulk imports.
On the Data Standard page, click Naming Dictionary in the left-side navigation pane.
The import and export buttons are also available on the details pages for field standards, lookup tables, and measurement units.
Click Import and select an Import Type.
In Template Preview, click Download Template and fill in the required fields.
Click Next step. On the Data Import tab, upload and preview the data file.
NoteImport Mode: If an object with the same name as one in the import file already exists in DataWorks, you can either skip the object or overwrite it with the content from the imported file.
You can bulk import only
.xlsxfiles. Up to 30,000 data records can be imported at a time, and the file size cannot exceed10 MB.
On the OK page, view the import results. Click View More Details for more operations. If the import status is Failed, you must resolve the issue based on the error logs and try importing again.
Batch export data standards
Use the export feature to reuse data standards across different workspaces. The export button is also available on the details pages for Field Standard, Lookup Table, Measurement Unit, and Naming Dictionary.