Skip to main content
Version: 10.2

Database Index Type Configuration

The UpdateFactTableSchema command in the Analysis Server Console application includes a switching capability. Columnstore indexes are used for analytical workloads, while rowstore indexes are utilized for transactional workloads. The default index type used by Open iT is the rowstore. This guide provides instructions on how to switch between columnstore and rowstore index types in Open iT.

warning

When converting indexes with huge amount of data, it expected to take a while to finish the process. For example, converting a table with 1 billion records can take up to 30 minutes or more on high-performance hardware. To avoid process timeout, specify the /timeout parameter with high value or set it to 0 which means no timeout.

Switching to Columnstore​

You can switch to columnstore by using the /columnstore command. Passing /uncompress will apply PAGE or COLUMNSTORE_ARCHIVE compression to columnstore.

To switch to columnstore index, follow these instructions:

Instructions

  1. Open the command prompt and run the following command to go to the installation directory of Analysis Server, which is by default in C:\Program Files\OpeniT\Analysis Server\.

    Command Syntax
    cd "C:\Program Files\OpeniT\Analysis Server\"

  2. Once in the directory, run the following command:

    Example
    OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /columnstore /uncompress </tables:number<+number>> </timeout:number> </truncate>
    Example
    OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /columnstore /uncompress /tables:75+93+94+95+102 /timeout:60 /truncate

  3. The Console Application will display information messages in the command prompt. Allow the process to finish.

    tip

    Back up the SQL Server database (Optional). Use the instructions in the Microsoft Learn article – Create a Full Database Back Up (Using SQL Server Management Studio) to properly back up the SQL Server database.

  4. To reclaim the true storage size of the database, use the instructions in the Microsoft Learn article on How to Shrink Database.

Switching to Rowstore​

You can switch to rowstore by using the /rowstore command. Passing /compress will apply PAGE or COLUMNSTORE_ARCHIVE compression to rowstore.

To switch to rowstore index, follow these instructions:

Instructions

  1. Open the command prompt and run the following command to go to the installation directory of Analysis Server, which is by default in C:\Program Files\OpeniT\Analysis Server\.

    Command Syntax
    cd "C:\Program Files\OpeniT\Analysis Server\"

  2. Once in the directory, run the following command:

    Example
    OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /rowstore /compress </tables:number<+number>> </timeout:number> </truncate>
    Example
    OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /rowstore /compress /tables:75+93+94+95+102 /timeout:60 /truncate

  3. The Console Application will display information messages in the command prompt. Allow the process to finish.

The following article can help you understand the usage of the command and other parameters for UpdateFactTableSchema.

Using Analysis Server Console Application - UpdateFactTableSchema