Converting a pseudo table into an in-memory table/table sequence

Some DQL tables have their foreign key tables. When the foreign key table’s primary key values are unique and the key field isn’t foreign key, the table is called dimension table. A dimension table can only be a table sequence or an in-memory table. If the table a DQL table’s foreign key points to is dimension table, we need to load the pseudo t able into a table sequence or an in-memory table in the pseudo table definition.

For example: City is Customer’s dimension table

Following illustrates how to convert City table into a table sequence or an in-memory table:

In the open metadata file demo.glmd, click  (Add pseudo table) button to add a new pseudo table Customer:

 

Click  button again to add another pseudo table City:

 

In pseudo table City’s definition, uncheck “Use columnar storage” and check “Load into memory” option; select “In-memory table” in “Load as” drop-down list:

 

l  Load as: For setting the type of loading a pseudo table: In-memory table or Table. You can set up index, fields to be selected and filter condition for both in-memory tables and table sequences.

 

Select “Create index table” to create an index table on the table sequence’s primary key if you want to speed up the query. Edit “Index length” to set length of the index; when the property is left blank, system will automatically select a length.

l  Index table options: For configuring options related to creating an index table:

Ø  Basic key is ordinal number: Create an ordinal-number-based index on a table sequence. Such an index is used for foreign key numberization, which requires that the fact table’s foreign key values correspond to ordinal numbers of dimension records. The ordinal number key can be omitted when applying the ordinal-number-based index. When this option works, ignore “Index length” property; and it does not apply scenarios where the table sequence has the time key.

Ø  Create tree-structured index when the basic key is serial byte type: Create a multilevel tree-structured index when the basic key is serial byte type. When this option works, ignore “Index length” property.

Ø  Use parallel processing: Enable parallel processing when it is selected.

 

l  The fields under “Select field(s)” will be converted to corresponding fields in the DQL table when executing “Generate table from pseudo table”.

Ø  : This icon is used to add a field under “Select field(s)”. The new field can come from a physical table or the list of special field definitions.

Ø  : This icon is used to delete a field from under “Select field(s)”.

Ø  Primary key: For setting whether a field acts as the primary key; read the physical table’s primary key when it is not selected.

l  Filter condition: Set filtering condition in the edit box.

 

Execute “Generte table pseudo table” to convert the pseudo table into a DQL table, and then City table can be defined to work as Customer table’s foreign key table. See Foreign key-based queries to learn details and related query statements.