DataWorks:Create and manage dimension tables

Prerequisites

Before you begin, ensure that you have:

• A MaxCompute data source created in your DataWorks workspace with a computing resource bound to Data Studio. For details, see Bind a MaxCompute computing resource

• A MaxCompute table. For details, see Create and use MaxCompute tables

Limitations

Go to the dimension tables page

On the Data Analysis page, click Go To DataAnalysis. In the left menu bar, 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 or import a table definition using a Data Definition Language (DDL) statement.

   Parameter	Description
   MaxCompute Project	The MaxCompute project the dimension table belongs to.
   Table Name	The name of the dimension table. The table is synced to MaxCompute as a production table.
   Table Description	A description of the table's purpose and properties.
   Field	The Field Name and Field Description for each column. Only the STRING Field Type is supported.
   Lifecycle	The period after which MaxCompute reclaims the table if its data has not been updated. The period starts from the last update time.

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, then click OK. You are taken to the dimension table editor page. Because dimension tables are maintained in a production environment, the table creator is responsible for ongoing changes.

Import data into a dimension table

Dimension tables accept data written directly or imported from spreadsheets, local CSV files, and local Excel files. All imported data is stored as STRING — non-STRING types are automatically converted.

1. On the Dimension Table page, under All Dimension Tables, click the File Name of the table.

2. Click Start editing in the upper-right corner, then click Import.

   If you previously clicked Start editing, you can import data directly the next time you open the page.

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

   Spreadsheet

   Parameter	Description
   Spreadsheet	Select the spreadsheet to import from the drop-down list.
   Sheet	Select the sheet within the spreadsheet.
   Data Preview	Preview the data before importing. Select or clear First Row as Field Names as needed.
   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 CSV file, then click Open.
   Original Character Set	UTF-8 or GBK. Switch to the other option if garbled text appears in the preview.
   Separator	Row delimiters: \r\n, \n, or \r. Column delimiters: ,, ;, or \t. Switch if data is not separated correctly.
   Data Preview	Preview the data before importing. Select or clear First Row Is Header as needed.
   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 Excel file, then click Open.
   Sheet	Select the sheet to import.
   Data Preview	Preview the data before importing. Select or clear First Row as Field Names as needed.
   Field Mapping	Set the mapping between Dimension Table Field and Data Column.
   Import Data Mode	Includes Append and Overlay.

4. Click OK.

5. Click Save in the upper-right corner. The data in the MaxCompute table is updated. After saving, use the Diff feature to review changes and confirm the import is correct before proceeding.

Edit a dimension table

The dimension table editor lets you view and modify MaxCompute table data without writing SQL statements.

1. On the Dimension Table page, under All Dimension Tables, click the File Name of the table.

2. On the editor page:

   • View table information: The left pane shows the MaxCompute Project, Table Name, Table Description, and Lifecycle.

   • Modify table settings: Click Modify field settings. In the Modify the field settings dimension table dialog box, update the Table Description or Lifecycle, or add fields.

   • Edit cell data: The right side displays all data in the table, with field names in the first row. Double-click any cell to edit its value.

3. Click Save in the upper-right corner to apply changes to the MaxCompute table. To review what changed, click Compare Versions to open the Diff dialog box.

Share a dimension table

If multiple users need to collaborate on a dimension table, share the table and grant them editing or viewing permissions.

Before sharing, a Resource Access Management (RAM) user with the tenant administrator, tenant security administrator, or authorized workspace administrator role must enable Allow Sharing in Security Center > Security Policies > Data Query and Analysis Control > Query Result Control. For details, see Data Query and Analysis Control.

1. On the Dimension Table page, under All Dimension Tables, click the File Name of the table.

2. Click Share in the upper-right corner and set the sharing method:

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

   • Users with Edit Access: Click Users with Edit Access > Add, enter and select members, then click OK. The maximum number of editors varies by edition — see Limitations.

   • Users with Read Access: Click Users with Read Access > Add, enter and select members, then click OK. The maximum number of viewers varies by edition — see Limitations.

3. Send the link to recipients. Shared tables appear under Share it with me on the Dimension Table page.

View and manage dimension tables

On the Dimension Table page, the All Dimension Tables area lists tables under two tabs: I Created and Share it with me.

To manage a table, click its name or click the icon next to it. From the table list, you can also:

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

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