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 department through foreign key field DEPT. 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 GENDER in source table EMPLOYEE. Its values are either F or M. 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_GENDER.

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 gender_id 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 gender), 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_GENDER is generated:

In the newly-generated numberized table, values of column gender_id are continuous integers starting from 1 in the form of [1,2], which correspond to gender values [F,M].