Enumerated Numberized Column

One fact table usually corresponds to multiple dimension tables. Storing all dimension attributes in the dimension table relieves the fact table of storing a large number of descriptive fields redundantly, bringing higher storage efficiency and, based on standardized dimension table association structure and index optimization, further booting multidimensional query performance.

In ETL interface, you can perform a join operation on an existing dimension table directly according to the foreign key field. For example, the above-mentioned source table EMPLOYEE can associate with dimension table gender.txt through foreign key field gender. If an enumerated field coming from the source table does not have a corresponding dimension table, you can create one for the source table through the Create Enumerated Numberized Column functionality. A dimension table in ETL also called Numberized Table. On the Create Enumerated Numberized Column window, the text format enumerated values are converted to ordered numerical marks so that storage efficiency is optimized and query performance is increased.

Take enumerated column DEPT in source table EMPLOYEE. Now let’s look at how to generate the numberized table for the column using Create Enumerated Numberized Column:

Select Employee as the data source, click Create Enumerated Numberized Column button to perform the following setup:

Name: Set the numberized table name as EMPLOYEE_DEPT.

Export to Directory: A namesake BTX file will be generated by exporting the numberized table generated through Create Enumerated Numberized Column. The directory to which data is exported is an ETL data path.

Index Column Name: Specify name for the Index Column of the numberized table (like deptID here). The option is optional; by default, use record number as the index for computation.

Value Column Name: Specify name of Value Column (here is dept), whose values of are unique values in the enumerated column coming from the source table.

Enumerated Column: Select and enumerate one or more fields of the source table. Double-click a field name under the source table in the left part to automatically display the field under Enumerated Column in the form of source table name_field name.

Click OK and numberized table EMPLOYEE_DEPT is generated:

In the newly-generated numberized table, values of column deptID are continuous integers starting from 1, and dept values are a sequence consisting of distinct EMPLOYEE’s DEPT field values. .