To achieve the optimal performance for your data warehouse, it is important to have the right indexes on your tables. TimeXtender Data Integration (TDI) can generate the necessary indexes automatically and also assist with the manual creation of indexes. Indexes can also be disabled and handled using the legacy approach.

With the Index Automation feature, TDI can manage all the index creation and maintenance. Index Automation considers the following when designing indexes for the instance:

  • Relations between tables with relationship type set to Error or Warning.
  • Joins on conditional lookup fields not using None as the type.
  • Primary key fields (on the raw table)
  • Selection rules on the data area.
  • Incremental selection rules on the data area.
  • Partitioning fields (DW_Partitionkey, DW_TimeStamp.)

Index Automation attempts to minimize the number of indexes. If two lookups can use the same index, TDI will take advantage of that, and also takes any manually created indexes into consideration. Index Automation will not alter any of the manually created indexes, but will instead use them in lieu of creating similar indexes. The indexes created by Index Automation will be named AutoIndex and post-fixed with a number for uniqueness within each table.

Content

Setting up Index Automation

Index Automation is configured on the Prepare instance level, but can be overwritten on individual tables. The following options are available:

  • Automatic (default): Index automation updates the indexes whenever the user changes the instance in a way that could trigger a new or altered index.
  • Manual: The user can have TDI create indexes on selected tables. However, these indexes are not managed by TDI. Nothing happens automatically if the table is changed in a way that impacts the indexes.
  • Disabled: TDI will use the legacy index generation behavior. Indexes will be generated during execution when needed by a data cleansing procedure. However, the same index might be created multiple times, since the index generation behavior is not fine tuned for performance. In addition to that, these auto-generated indexes are not visible to the end user.

Configuring Index Automation for the Instance

Use the following steps to configure the Index Automation setting on an entire instance.

  1. Right-click on a Prepare instance and select Edit Instance to open the following window. 
  2. In the Index generation list, select the appropriate index option.
  3. Click OK.

Configuring Index Automation for a Table

Use the following steps to configure the Index Automation setting on a specific table.

  1. Right-click the table and select Table Settings to open the following window
  2. On the General tab, in the Index Automation group, select the appropriate index option
  3. Click OK

Manual Index Generation

Configuring the index automation setting to manual enables users to utilize the index generation features of TDI while still retaining complete control over the indexes in their instance. When manual index generation is executed on a table or Prepare instance, TDI creates any indexes deemed necessary by Index Automation. However, users have the freedom to delete and modify indexes according to their preferences. It is important to note that TDI will not generate new indexes for the tables unless manual index generation is performed again. This functionality allows users to manage indexes based on their specific needs and preferences.

Generate Indexes Manually on an Instance

Use the following steps to configure the manual generation of indexes on an instance.

  1. Right-click the instance you want to use manual index generation on and change Index Automation to Manual

     

  2. The existing Automatic indexes will stay, but you can now manually add them as well

Generate Indexes Manually on a Table

Follow the steps below to configure the manual generation of indexes on a table.

  1. Right-click the table, select Advanced, and then click on Index Settings
  2. If the table already has one or more indexes, then the Index Settings will not be located in the Advanced menu, but instead in an Indexes menu that is in the expanded table tree as shown below
  3. In the Index Settings menu, click on Add Index, Edit Index, or Delete Index as appropriate
  4. Clicking on Add Index will open the following Create Index window
  5. Depending on the index option selected, the dialog window may include different items. The following are the different options along with their dialog windows
    1. NonClustered Index is the default index option and has a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value. Select the fields to be indexed, which would usually include the Primary Keys but can also include other specified fields as well. The following example shows the automated index for a lookup from a table

      In the Include Fields area above, the Name field is selected because it is used as the Lookup Field from this table, and the query for this will appear as follows in the SQL Server Management Studio. 

      SET ANSI_PADDING ON
GO

/****** Object:  Index [AutoIndex]    Script Date: 2023-05-10 14:05:14 ******/
CREATE NONCLUSTERED INDEX [AutoIndex] ON [Landing].[Territory]
(
	[Code] ASC,
	[Source Table] ASC
)
INCLUDE([Name]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
    2. Unique Index will guarantee that each combination of values in the index key is unique. For example, if a unique index is created on a combination of the LastName, FirstName, and MiddleName columns, then no two rows in the table can have the same combination of values for these three columns

       

    3. Columnstore Index. A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format. Using this removes the option to Include Fields, so only Index Fields can be seleted
    4. Clustered Columnstore Index, is similar to a NonClustered Columnstore Index with the difference being that it is the primary storage for the entire table

      It is not possible to select any fields for this option, simply selecting this option is sufficient for its configuration.