Project settings

You can change the project settings according to your needs.

To access the project settings:

1. Open your project as described in Managing and monitoring projects.

2. Click the downward arrow to the right of the project name and select Settings from the drop-down menu.

   The Settings window opens, displaying the following tabs:

   • General tab
   • Naming tab
   • Environment tab
   • Table creation modifiers tab

General tab

In this tab, the following settings are available:

Miscellaneous

• Generate DDL scripts but do not run them: By default, Compose executes the CREATE, ADJUST and DROP statements immediately upon user request. When you select this option, Compose will only generate the scripts but not execute them. This allows you to review and edit the scripts before they are executed.

  For example, if you want your data warehouse/storage tables to contain partitions, you will need to edit the CREATE statement to create the partitions.

  You can view, copy and download the DDL scripts as described in Viewing and downloading DDL scripts.

  Information note

  When this option is selected, you need to do the following to see the results:

  • After running the scripts, clear the metadata cache as described in Clearing the data warehouse metadata cache.
  • When this option is selected, you need to press [F5] (i.e. refresh the page) in order for the web console to display the updated list of tables. This can be done either before running the scripts (recommended) or after running the scripts. Note that until you refresh the browser, the information in the web console will only be partially updated.
• Ignore Mapping Data Type Validation: By default, Compose issues a validation error when a landing table is mapped to a logical entity with a different data type. You can select this option to allow the mapping of different data types. Note that you should only select this option if you need to map landing table data types to compatible (though not identical) logical entity data types.

• Write metadata to the TDWM tables in the data warehouse:

  When this option is selected (the default unless Amazon Redshift is the data warehouse), Compose writes the metadata for the data warehouse tables to the following tables:

  <schema>.TDWM_Tables and <schema>.TDWM_COLUMNS.

  Centralizing the metadata in two dedicated tables makes it easier for external metadata tools to analyze the metadata. The metadata is also written to the local Compose repository, so clearing this option (if performance issues are encountered) will not affect Compose functionality in any way.

• Do not display the default workflows in the monitor: Select this option if you want to prevent the default workflows from being executed.

Dates

• Lowest Date: The value stored in the "From Date" column. This is the date when the version started.
• Highest Date: The value stored in the "To Date" column. This is the date when the version ended.

Current Time Convention

When a source record’s timestamp cannot be determined, select one of the following to use instead:

• Current time in UTC (the default for new projects)

  Information note

  To preserve backward compatibility when upgrading or deploying old projects, local server time is the default.

  Before changing this option, make sure that existing data will not be impacted.

• Current time in server local time

For existing objects, Compose will not be able to determine a source record's timestamp if both of the following are true:

• The "From Date" columns are not mapped
• The task is set up to perform Incremental Load

Naming tab

In this tab, you can change the default "From Date" and "To Date" column names, as well as the prefixes and suffixes used to identify tables, views, and columns.

Environment tab

In this tab, you can:

• Specify information about your environment, part of which will be displayed as a banner at the top of the window when you open the project.
• Determine the number of database connection to open concurrently.

After providing the following information, click OK to save your settings:

• Environment type: Select one of the following types according to your environment type: Development, Test, Acceptance, Production, Other. This information will not be displayed in the banner.

• Environment title: Specify a title for your environment. The title will be displayed in the banner at the top of the console.

• Project title: Specify a title for your project. The project title will be shown in the console banner. If both an Environment Title and a Project Title are defined, the project title will be displayed to the right of the environment title.

  Information note

  • The Project title option requires Compose August 2021 Patch Release 12 or later.
  • When a project is deployed to a new environment, the environment title and environment type in the new environment will not be overridden.

  The following image shows the banner with both an Environment title and a Project title:

   

  

  Tip noteThe banner text is shown without the Environment title and Project title labels. This provides greater flexibility as it allows you add any banner text you like, regardless of the actual label name. For example, specifying Project owner: Mike Smith in the Project title field, will display that text in the banner.
  

Creating or Dropping Data Warehouse Tables

Limit the number of database connections to: The higher the number of database connections, the more data warehouse tables Compose will be able to create or drop in parallel. While increasing the default should improve performance, it might also impact other database applications. It is therefore not recommended to increase the default unless you encounter performance issues.

Task recovery

You can set SQL state classes and error codes, on the occurrence of which, a task will be retried.

You can set the following parameters:

• Maximum retry count: The number of times to retry a task before exiting with failure. Increasing the number of retries will impact system resources. Therefore, only increase the default value if you expect tasks to recover after the default number of retries.

• Interval between retry attempts (sec): The time to wait between retry attempts. Increasing the interval will consume more system resources. Therefore, only increase the default value if it is critical that the task recover as soon as possible.

• Retry on these SQL state classes: The default is 08 (connection exceptions). You can add additional classes as desired. Classes should be separated with a comma.

   

  Example: 08,22,2F

   

• Retry also on these error codes: The default is 1205 (which occurs when a table is locked by another process). You can add additional error codes as desired. Error codes should be separated with a comma.

   

  Example: 1205,2020,233

Limitations and considerations:

• ODBC statements comprise a small part of the task execution sequence. However, as the task retry mechanism is JDBC-based, ODBC statements will not be retried even if the specified SQL state/error code is encountered.

Task and Workflow Information Retention

You can set the maximum number of runs to keep task and workflow logs and messages. The default is 100. Task information includes logs, the number of inserted/updated rows per table, errors, and various other runtime messages. If you find that the number of accumulated logs and messages is degrading performance, reducing this value might help.

Table creation modifiers tab

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.

In the Table creation modifiers tab, you can append table creation modifiers as SQL parts to the CREATE TABLE statement. You can set table creation modifiers for both data warehouse tables and for data mart tables. In the data warehouse, separate modifiers can be set for Hub and Satellite tables while in the data mart, separate modifiers can be set for fact and dimension tables. Once set, all tables will be created using the specified modifiers, unless overridden at the entity level.

To set table creation modifiers:

1. Select the Custom option for any of the available table types.
2. Click the Edit button to open the Table Creation Modifier editor.
3. Enter the SQL parts you want to append to the CREATE TABLE statement.

4. 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.

5. Click OK to close the editor and save your SQL.

Limitations

If you change an existing table creation modifier, you will not be prompted to adjust affected tables when validating the model. If you want to apply the change to existing tables, dropping and recreating all tables might not be an issue in a development environment. However, in a production environment (where dropping and recreating all tables might not be a viable option), you will need to adjust the tables outside of Compose.

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)

 

For an explanation of how to define table creation modifiers for individual data warehouse tables, see Defining Table Creation Modifiers

For an explanation of how to define table creation modifiers for individual fact tables, see Defining Fact Table Creation Modifiers.

For an explanation of how to define table creation modifiers for individual dimension tables, see Defining Dimension Table Creation Modifiers.