DataWorks:Create and manage dimension tables

Limits

• Permissions: To create dimension tables, you must have the Workspace Admin, Project Owner, or Developer role in a DataWorks workspace. For more information, see Add workspace members.

• Field type limits: Fields in MaxCompute tables created from dimension tables must be of the STRING type. To use other field types, you must create the table by executing a DDL statement on the Data Studio page instead of using the dimension table feature. For more information, see Standard DDL statements.

• Sharing: The maximum number of editors and viewers that you can specify varies by edition.

  Feature/Edition	Basic Edition	Standard Edition	Professional Edition	Enterprise Edition
  Maximum number of editors	0	3	5	10
  Maximum number of viewers	0	10	20	30

Go to the dimension tables page

On the Data Analysis page, click Go To DataAnalysis. In the menu bar on the left, click the icon to go to the Dimension Table page.

Create a dimension table

1. On the Dimension Table page, in the New Dimension Table section, click the icon.

2. In the New Dimension Table dialog box, configure the parameters or import a table definition using a Data Definition Language (DDL) statement.

   Parameter	Description
   MaxCompute Project	Select the MaxCompute project for the dimension table.
   Table Name	The name of the dimension table. The table created here is synced as a MaxCompute production table.
   Table Description	A supplementary description of the table's purpose and properties.
   Field	Add the Field Name and Field Description for the table. Only the STRING Field Type is supported.
   Lifecycle	The period after which the table is automatically reclaimed by MaxCompute if the data has not been updated. The period starts from the time of the last update.

3. Select I have known this risk and confirmed that as owner of this table, I am responsible for the subsequent changes to this table. and click OK. You are then taken to the dimension table editor page, where you can view and manage the table information.

   MaxCompute tables for Data Analysis are maintained in a production environment. Therefore, the table creator is responsible for creating and maintaining the table.

Import data into a dimension table

You can write data directly into a dimension table or import data from spreadsheets, local CSV files, and Excel files for data analysis.

1. On the Dimension Table page, under All Dimension Tables, click a File Name to open the editor page for the dimension table.

2. On the dimension table editor page, click Start editing in the upper-right corner. You can then click Import to import data.

   If you have already clicked Start Editing, you can directly import data the next time you open the page.

3. In the Import dialog box, select the file type and configure the parameters.

   Important

   Dimension tables support only data of the STRING type. If the imported data contains non-STRING types, they are automatically converted to the STRING type.

   • Spreadsheet

     Parameter	Description
     Spreadsheet	From the Spreadsheet drop-down list, select the name of the spreadsheet to import.
     Sheet	From the Sheet drop-down list, select the sheet to import from the spreadsheet.
     Data Preview	Preview the data to import. You can select or clear First Row as Field Names.
     Field Mapping	Set the mapping between Dimension Table Field and Data Column.
     Import Data Mode	Includes Append and Overlay.

   • Local CSV file

     Parameter	Description
     File	Click Select File, select the local CSV file to import, and click Open.
     Original Character Set	Includes UTF-8 and GBK. If garbled text appears, switch the character set.
     Separator	Includes row and column separators: Row delimiters include \r\n, \n, and \r. Column delimiters include ,, ;, and \t. If the data in cells is not separated correctly, switch the separator.
     Data Preview	Preview the data to import. You can select or clear First Row Is Header.
     Field Mapping	Set the mapping between Dimension Table Field and Data Column.
     Import Data Mode	Includes Append and Overlay.

   • Local Excel File

     Parameter	Description
     File	Click Select File, select the local Excel file to import, and click Open.
     Sheet	From the Sheet drop-down list, select the sheet to import.
     Data Preview	Preview the data to import. You can select or clear First Row as Field Names.
     Field Mapping	Set the mapping between Dimension Table Field and Data Column.
     Import Data Mode	Includes Append and Overlay.

4. Click OK.

5. In the upper-right corner of the page, click Save. The data in the MaxCompute table is then updated.

   After you save the dimension table, you can use the Diff feature to verify that the modifications are correct and prevent unintended operations.

Edit a dimension table

You can visually edit the data in a MaxCompute table that is associated with a dimension table on the editor page without writing SQL statements.

1. On the Dimension Table page, under All Dimension Tables, click the desired File Name to open the dimension table editor page.

2. On the dimension table editor page, you can view and modify the dimension table information.

   • View dimension table information: In the left pane, you can view the Dimension table information for the MaxCompute production table, such as MaxCompute Project, Table Name, Table Description, and Lifecycle.

   • Modify dimension table information: Click Modify field settings. In the Modify the field settings dimension table dialog box, you can modify the Table Description and Lifecycle, and add fields.

     On the right side of the page, all data from the MaxCompute table is displayed. The first row contains the field names. You can double-click a cell to modify its content.

3. In the upper-right corner of the page, click Save to apply the changes to the MaxCompute table.

   After saving the dimension table, you can view all the data in the MaxCompute table. You can also click Compare Versions in the upper-right corner to view the changes in the Diff dialog box.

Share a dimension table

If multiple users need to collaboratively edit a dimension table, you can share the table and grant them editing permissions.

1. On the Dimension Table page, under All Dimension Tables, click the File Name of the dimension table that you want to edit.

2. On the dimension table editor page, click Share in the upper-right corner to set the sharing method.

   You can share the dimension table in the following ways:

   • Link: Specify which members can edit or view the table. Click Copy Link and send the link to the intended recipients.

   • Users with Edit Access: To grant permission to specific users to edit the dimension table, click Users with Edit Access > Add. In the dialog box, enter and select the members, and then click OK.

     Note

     The maximum number of editors that you can specify varies by edition. For more information, see Limits.

   • Users with Read Access: Click Users with Read Access > Add to grant specific users permission to view the dimension table. In the dialog box, enter and select the members, and then click OK.

     Note

     The maximum number of viewers that you can specify varies by edition. For more information, see Limits.

   After sharing the table, send the link to the recipients so they can access it. You can also return to the Dimension Table page to view tables under Share it with me.

View and manage dimension tables

1. On the Dimension Table page, in the All Dimension Tables area, you can view the following lists of tables: I Created and Share it with me.

   You can also share a dimension table with specific members.

2. Click the name of a dimension table or the icon next to a dimension table to open its editor page. On this page, you can also perform the following management operations:

   • Change Owner: Click the icon next to the file. In the Change Owner dialog box, enter and select the new owner, and click OK.

   • Delete: Click the icon next to the file. In the Delete dialog box, click OK.