Indexes

To achieve the optimal performance on your data warehouse, it is important to have the right indexes on your tables. TimeXtender can generate the necessary indexes automatically or assist you in creating indexes manually. You can also choose to use a legacy approach to indexes instead.

With the Index Automation feature, you can let TimeXtender handle all index creation and maintenance. Index Automation considers the following when designing indexes for the project:

Relations between tables with relationship type set to Error or Warning
Joins on conditional lookup fields
Primary key fields (on the raw instance of the table)
Selection rules on the data warehouse
Incremental selection rules on the data warehouse
Partitioning fields (DW_Partitionkey, DW_TimeStamp)

Index Automation will try to minimize the number of indexes. If two lookups can use the same index, TimeXtender will take advantage of that. In addition to that, TimeXtender takes any manually created indexes into consideration. It will not change your manually created indexes, but it will use them instead of creating similar indexes. The indexes created by Index Automation will be named AutoIndex and postfixed with a number for uniqueness within each table.

Setting up Index Automation

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

Automatic (default): Index automation updates the indexes whenever the user changes the project in a way that could trigger a new or altered index.
Manual: The user can have TimeXtender create indexes on selected tables. However, these indexes are not managed by TimeXtender. Nothing happens automatically if the table is changed in a way that impacts the indexes.
Disabled: TimeXtender 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 tuned for performance. In addition to that, these auto-generated indexes are not visible for the end user.

Configuring Index Automation for the Project

To configure the Index Automation setting on the entire project, follow the steps below.

Right click the project node, and click Edit Project. The Edit Project window appears. Click Advanced Settings...
In the Index generation list, click the option you want to use.
Click OK.

Configuring Index Automation for a Table

To configure the Index Automation setting on a specific table, follow the steps below.

Right click the table and click Table Settings. The table settings window appears.
On the Generaltab, in the Index Automation group, click the option you want to use.
Click OK.

Manual Index Generation

Setting the index automation setting to manual, makes it possible for you to use the index generation features of TimeXtender while maintaining complete control over the indexes in your project. When you run manual index generation on a table, data warehouse, staging database or the project, TimeXtender creates any indexes Index Automation finds necessary. However, you can delete and edit indexes as you see fit. TimeXtender will not create new indexes on the tables unless you run manual index generation again.

Generate Indexes Manually on the Project, a Data Warehouse or a Staging Database

To generate indexes on a data warehouse, staging database or the entire project, follow the steps below.

Set or make sure Index Automation is set to manual. See Configuring Index Automation for the Project.
Right-click the project, data warehouse or staging database you want to use manual index generation on, click Advanced and click Index Automation(manual).
A message will appear to tell you how many tables TimeXtender checked. Click OK.

Generate Indexes Manually on a Table

To generate indexes on a specific table, follow the steps below.

Set or make sure Index Automation is set to manual. See Configuring Index Automation for the Project.
Right-click the table you want to use manual index generation on, click Advanced and click Index Automation(manual).
Note: If the table you want to add an automatic index to already has one or more indexes, the Index Automation (manual) option is not available in the Advanced menu. Instead, expand the table, right click Indexes and click Index Automation (manual).