Hello friend!

The beta release of Open iT version 10.1 is now available!
Try it out and help us enhance your Open iT experience. Contact support@openit.com to get started.

# Switching Database Index Type

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.

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:

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\.
Example
```
cd "C:\Program Files\OpeniT\Analysis Server\"
```

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

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

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.
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:

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\.
```
cd "C:\Program Files\OpeniT\Analysis Server\"
```

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

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

# Recommended Reading

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

Using Analysis Server Console Application - UpdateFactTableSchema