Sign Up for Training |
Jet Global Company Site
Community
Downloads
Training
Submit a Request
Become a Jet Insider
Give Feedback

Adding 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. Knowing what an index is and how an index works involves a little knowledge of database theory, but an excellent overview of the topic can be found at the following website (external link).

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

Process

  1. Verify that the "Physical Valid Table" option is enabled on the table by going to Advanced >> Advanced Settings >> Physical Valid Table

  2. Navigate to "Index Settings" by going 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.

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 indexe

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 on the table and highlight Advanced, then click on Index Settings.

  2. Click Add Index

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

    Click OK once finished.

  4. Deploy and Execute the table. 

Was this article helpful?
1 out of 1 found this helpful

Comments