Add Indexes in the Jet Data Manager

Overview

The Jet Data Manager is capable of adding indexes on tables in the data warehouse and staging databases. For more information on Indexes, see Indexes.

This article covers the creation of non-clustered indexes using the Jet Data Manager.

Process

1. Go to Advanced > Advanced Settings >Physical Valid Table and whether the Physical Table option is enabled.

   

   

2. Go to Advanced > Advanced Settings > Index Settings.

   

3. Click Add Index. Give your index a descriptive name, and select fields on the left for the values that will comprise the index.

   Fields on the right, in the Include Fields column, may be selected as well. Items on the right will not be part of the index, but are still stored with the index.

   For instance, in the example below, the fields on the left are uniquely specifying a transaction, and the fields on the right are fields that we have instant access to should we decide to use it as a filter in a report, because fields on the right are stored with the index.

   

4. If desired, change the sort order. When completed, click Close.

   

5. Note that the table has a new node, titled Indexes , containing the fields we selected above. Also, we can see that, in our example, ITEMID is not in the index, because it was added as an Included Field.

   

Add Columnstore Indexes

In the Jet Data Manager, you can create Columnstore Indexes to significantly improve performance when querying your large fact tables in typical data warehouse scenarios.

SQL Server Enterprise Edition is required to utilize Columnstore indexes.

Currently the Jet Data Manager supports the creation of non-clustered Columnstore indexes.

For a list of supported data types on different SQL Server versions:

SQL Server 2012 Columnstore Indexes

SQL Server 2014 Columnstore Indexes

1. Select the table on which you wish to place a Columnstore index. Right-click the table and highlight Advanced, then click Index Setting.

   

2. Click Add Index.

   

3. Enter a Friendly Index Name , select Columnstore Index, and choose the Index Fields and click OK.

4. Deploy and Execute the table.