Create and manage dimension tables

Limitations

• 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 limitations: Fields in MaxCompute tables created by using the dimension table feature must be the STRING type. To use other field types, create the table by running a DDL statement on the DataStudio page. For more information, see Standard DDL statements.

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

  Feature/edition	Basic	Standard	Professional	Enterprise
  Max editors	0	3	5	10
  Max viewers	0	10	20	30

Dimension table page

On the Data Analysis page, click Go To DataAnalysis. In the navigation pane on the left, click the icon to open 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. You can also import the table definition by using a DDL statement.

   Parameter	Description
   MaxCompute Project	Select the MaxCompute project for the dimension table.
   Table Name	The name of the dimension table. The table that you create is synchronized as a production table in MaxCompute.
   Table Description	Describe the table's purpose and properties.
   Field	Add a Field Name and Field Description for the table. The Field Type supports only STRING.
   Lifecycle	If the table data is not updated within the specified period, MaxCompute automatically reclaims the table.

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 then click Confirm. This action redirects you to the dimension table editor page to view and manage the table.

   Data Analysis maintains its MaxCompute tables in a production environment, so the table creator is responsible for creating and maintaining them.

Import data into a dimension table

You can write data directly into a dimension table, or import data from a spreadsheet, local CSV file, or local Excel file to prepare for subsequent data analysis.

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

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

   After you click Start editing once, you can import data directly on subsequent visits.

3. In the Import dialog box, click the type of file to import 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 spreadsheet to import.
     Sheet	From the Sheet drop-down list, select the sheet to import.
     Data preview	Preview the data to be imported. You can select or clear First Row as Field Names.
     Field Mapping	Map a Dimension Table Field to a Data Column.
     Import Data Mode	Valid values: Append and overlay.

   • Local CSV File

     Parameter	Description
     File	Click Select File, select the local CSV file that you want to import, and then click Open.
     Original Character Set	Valid values: UTF-8 and GBK. If garbled text appears, you can switch the character set.
     Separator	Includes row and column separators: Row separators include \r\n, \n, and \r. Column separators include , (comma), ; (semicolon), and \t (tab). If cell data is not separated correctly, try switching the separator.
     Data preview	Preview the data to be imported. You can select or clear First Row as Field Names.
     Field Mapping	Map a Dimension Table Field to a Data Column.
     Import Data Mode	Valid values: Append and overlay.

   • Local Excel File

     Parameter	Description
     File	Click Select File, select the local Excel file that you want to import, and then click Open.
     Sheet	From the Sheet drop-down list, select the sheet to import.
     Data preview	Preview the data to be imported. You can select or clear First Row as Field Names.
     Field Mapping	Map a Dimension Table Field to a Data Column.
     Import Data Mode	Valid values: Append and overlay.

4. Click OK.

5. Click Save in the upper-right corner of the page to update the data in the MaxCompute table.

   After you save the dimension table, use the Diff feature to verify your changes and prevent mistakes.

Edit a dimension table

The dimension table editor allows you to visually edit table data and information without writing SQL code.

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

2. On the editor page, view and modify the dimension table's information.

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

   • Modify 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 manage fields. The field list includes the Field Name, Type, and Description columns. Click Add to add a new field or click the Delete link in a field's row to remove it. When you are finished, click OK.

     The right side of the page displays all data in the MaxCompute table in a grid, where the first row contains the field names. You can double-click a cell to directly modify its content.

3. Click Save in the upper-right corner of the page to apply the changes to the MaxCompute table.

   After you save the dimension table, you can view all data in the MaxCompute table. You can also click Diff in the upper-right corner to view the changes in the Diff From the Previous Version dialog box.

Share a dimension table

If multiple users need to collaborate on editing 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 desired table to open its editor page.

2. On the editor page, click Share in the upper-right corner and configure the sharing method.

   You can share a dimension table in the following ways:

   • Link: Specify members who can edit or view the table, click Copy Link, and then send the link to the intended recipients.

   • Specify editors: To grant a user edit permissions on the dimension table, click Users with Edit Access > Add. In the dialog box that appears, enter and select the member, and then click Confirm.

     Note

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

   • Users with Read Access: To grant a user read-only permissions on the dimension table, click Users with Read Access > Add. In the dialog box that appears, enter and select the member, and then click Confirm.

     Note

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

   After you share the table, recipients can access it via the link. You can also find the table on the Dimension Table page under the Share it with me section.

View and manage dimension tables

1. On the Dimension Table page, in the All Dimension Tables section, view tables under I created and Share it with me.

   You can also share a dimension table with specified members.

2. Click the file name or the icon next to a dimension table to open its editor page. You can also manage the table in the following ways:

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

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