Skip to main content Skip to complementary content
Qlik Resources

Defining Fact Table Creation Modifiers

You can set table creation modifiers for individual star schema (fact) tables, thereby overriding the default settings in the project settings' Table creation modifiers tab. Table modifiers allow you to append additional table properties to the default Compose CREATE TABLE statement.

The available options are located below the Columns list in the Physical Table tab, and are as follows:

  • Project settings default - When this option is selected (the default), the settings from the project settings' Table creation modifiers tab will be used.
  • Custom - This option is useful for if you need to append table creation modifiers to the default CREATE TABLE statement Compose uses for fact tables. Leveraging this option requires SQL coding knowledge.

  • Custom distribution and sort keys - This option is useful if you only need to define custom distribution keys or sort keys for the fact table. Although this can also be done using the Custom option (see below), the Custom distribution and sort keys option is more convenient as it does not require any prior SQL coding knowledge.

    Information note
    • Supported with Amazon Redshift only.
    • The default distribution key for all data warehouse tables is the ID column.

Setting table creation modifiers

By default, Compose creates tables in the data warehouse using the standard CREATE TABLE statement. However, organizations often need tables to be created with custom properties for better performance, special permissions, custom collation, and so on. For example, in Microsoft Azure Synapse Analytics, it’s possible to create a table as a HEAP, which is optimized for smaller tables. By default, Compose creates tables in Microsoft Azure Synapse Analytics as a CLUSTERED COLUMNSTORE INDEX, which offers the best overall query performance for large tables.

The procedure for settings table modifiers is as follows:

  1. Open the star schema and select the Physical Table tab.
  2. Select the Custom option.
  3. Click the Edit button to open the Table Creation Modifier editor.
  4. Enter the SQL parts you wish to append to the CREATE TABLE statement.

  5. Optionally, but strongly recommended, validate the SQL in an external validation tool that supports your specific database and version.

    Information note

    Compose does not provide any way of validating your SQL. Therefore, make sure to validate the SQL before deploying in a production environment.

  6. Click OK to close the editor and save your SQL parts.

Example of a Valid Table Creation Modifier

In the following example, the Compose CREATE TABLE statement (rows 1-5) is appended with an SQL part instructing Compose to create the table as a HEAP (row 6).

CREATE TABLE MyTable

(

column1 integer,

column2 varchar(50),

)

WITH (HEAP)

Setting and managing custom distribution keys for Amazon Redshift tables

Set and manage distribution keys for Amazon Redshift Data Warehouse according to the table below.

Distribution key procedures
To Do This

Add a distribution key

  1. Click the Add Distribution Key button.

    A row is added to the table displaying a drop-down list.

  2. Select one of the available columns.

Set a distribution style

From the Distribution Style drop-down, select Even, Key or All.

For more information on distribution styles, see:

Distribution styles - Amazon Redshift

Edit a distribution key

  1. Double-click the row.

    A drop-down list will be shown in the Column column.

  2. Select one of the available columns.

Delete a distribution key

Select the distribution key and then click the Delete button. The key is deleted.

Change the position of a distribution key

Select the distribution key and then click the "Up" or "Down" buttons to move the key to the desired position.

Setting and managing custom sort keys for Amazon Redshift tables

You can define one or more of the physical table columns as sort keys. Amazon Redshift stores your data on disk in sorted order according to the sort key. The Amazon Redshift query optimizer uses sort order when it determines optimal query plans. For guidelines on choosing sort keys, visit Choose the best sort key - Amazon Redshift.

Set and manage sort keys for Amazon Redshift Data Warehouse according to the table below.

Sort key procedures
To Do This

Add a sort key
  1. Select the Sort Keys tab below the Columns list.

  2. From the Sort key style drop-down list, choose one of the following styles:

    • None to disable the sort keys
    • Compound to use all of the columns listed in the sort key definition, in the order they are listed
    • Interleaved to give equal weight to each column in the sort key

  3. Click the Add Sort Key button.

    A new row is added to the Sort Keys list. The Position column indicates the order of the column.

  4. From the drop-down list in the Column column, select the desired column.

    The column is add to the list.

  5. Click OK to save your settings and close the Edit Dimension/Edit Star Schema window.

Edit a sort key

  1. Double-click the row.

    A drop-down list will be shown in the Column column.

  2. Select one of the available columns.

Change the position of a sort key

Select the sort key you want to move and then click the up or down arrows to promote or demote the key.

Delete a sort key

Select the sort key you want to delete and then click the Delete button.

For more information about sort keys, visit: Choosing sort keys - Amazon Redshift.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here