Analysis Server Console Application Usage

The Console Application of Analysis Server is a command line application that contains various commands for executing background and configuration tasks to extract, load, and process data to SQL and OLAP databases.

Analysis Server Console Tasks

To show the available tasks, follow these 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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe

   This command displays the available options.

   Example

   C:\Program Files\OpeniT\Analysis Server>OpeniT.Server.Etl.Console.exe 
   Open iT Analysis Server, <version>. Copyright 2008 Open iT, Inc. All rights reserved. http://www.openit.com
   
   Sql Database: OpeniT @ <hostname>
   Olap Database: OpeniT @ <hostname>
   
   Usage: OpeniT.Server.Etl.Console <subcommand> [args]
   Type 'OpeniT.Server.Etl.Console help <subcommand>' 
   for help on a specific subcommand.
   
   Available subcommands:
   	CreateOlap					Create Olap database on server.
   	CreateSql					Create SQL database on server, in case it does not already exist.
   	DeleteOlap					Delete Olap database.
   	DeleteSql					Delete SQL database.
   	DeployOlap					Deploy Olap database schema.
   	DeploySql					Deploy SQL database schema to either the latest or a specific version.
   	DeployTimeZoneInfo			Deploy Time Zone conversion data to SQL database.
   	GenerateUserToGroupsMap		Dump a new version of user-to-groups.map mapping file to the Core Server config directory.
   	SetAzureSqlCredentials		Set the Azure SQL Database Server Admin Credentials for Azure Tabular Model data source.
   	Install 					Install or upgrade.
   	ProcessOlap 				Process Analysis Services cubes.
   	UpdateRoleGroups 			Pull Role's Group Members from the Active Directory.
   	ProcessSql 					Process SQL data warehouse with new data.
   	RunEtl 						Run all configured jobs.
   	SyncDatabase 				Synchronize all active data types from Core Server to the data warehouse.
   	SyncDataType 				Synchronize a single data type from Core Server to the data warehouse.
   	SyncLicenseStatus 			Sync License Status data from Core Server to the data warehouse.
   	SyncMapping 				Sync user mapping information from LDAP or flatfile.
   	SyncCostInformation 		Syncs cost information from core database to the data warehouse.
   	SyncClientList 				Syncs client information from core database to the data warehouse.
   	SyncRawOptionsFile 			Syncs raw options file from core database to the data warehouse.
   	SyncSoftwareCatalog			Syncs software catalog information from core database to the data warehouse.
   	TruncateFacts 				Truncate fact tables
   	UpdateConfig 				Update config parameter
   	UpdateFactTableSchema 		Update fact table definitions
   	CreateScheduledTask 		Create scheduled task for the ETL process
   	DeleteScheduledTask 		Delete scheduled task for the ETL process
   	CustomActions 				Run non-major actions for ETL.
   	ConfigureInvoicing 			Automatically configure invoice reporting.
   	ResetSchedules				Reset Job and Alert schedules.

   To show the available parameters for each command, add a help parameter before the command. Use the following syntax:

   Command Syntax

   OpeniT.Server.Etl.Console.exe help DeploySql

   This command displays the available options.

   Example

   C:\Program Files\OpeniT\Analysis Server>OpeniT.Server.Etl.Console.exe help DeploySql
   Open iT Analysis Server, <version>. Copyright 2008 Open iT, Inc. All rights reserved. http://www.openit.com
   
   Sql Database: OpeniT @ <hostname>
   Olap Database: OpeniT @ <hostname>
   
   Deploy SQL database schema to either the latest or a specific version.
   usage: OpeniT.Server.Etl.Console DeploySql [/version:value] [/trace]
   	[/version:value] 	Specific version to migrate to
   	[/trace] 			Enable tracing of SQL statements.

Available Tasks

CreateOlap

This command creates the SQL Server Analysis Services Database in the configured OLAP server.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe CreateOlap

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe CreateOlap </tabular>

   Optional Parameter:

   /tabular
   Use this parameter to create a blank tabular database as a placeholder for the deployment of the tabular database objects.

   Example

   OpeniT.Server.Etl.Console.exe CreateOlap /tabular

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

CreateSql

This command creates the SQL Server Database in the configured SQL server.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe CreateSql

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

DeleteOlap

This command deletes the SQL Server Analysis Services Database in the configured OLAP server.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeleteOlap

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeleteOlap /tabular 

   Optional Parameter:

   /tabular
   Use this parameter to delete the Tabular Database.

   Example

   OpeniT.Server.Etl.Console.exe DeleteOlap /tabular

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

DeleteSql

This command deletes the SQL Server Database in the configured SQL server.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeleteSql

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeleteSql </deleteLogin>

   Optional Parameter:

   /deleteLogin
   Use this parameter to delete the service account login in SQL Server.

   Example

   OpeniT.Server.Etl.Console.exe DeleteSql /deleteLogin

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

DeployOlap

This command deploys important object in the SQL Server Analysis Services database in the OLAP server.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeployOlap

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeployOlap </trace> </clean> </tabular>

   Optional Parameters:

   /trace
   Use this parameter to display SQL statements for tracing.

   Example

   OpeniT.Server.Etl.Console.exe DeployOlap /trace

   /clean
   Use this parameter to remove or clean missing and old objects in the OLAP database before deployment.

   Example

   OpeniT.Server.Etl.Console.exe DeployOlap /clean

   /tabular
   Use this parameter to execute the createOrReplace sh script of the Tabular Database.

   Example

   OpeniT.Server.Etl.Console.exe DeployOlap /tabular

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe DeployOlap /clean /trace /tabular

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

DeploySql

This command deploys important object in the SQL Server database in the SQL server.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeploySql

   To further customize the command, use the optional parameters.

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeploySql </version:value> </trace>

   Optional Parameters:

   /version:<migration_version>
   Use this parameter to limit the migration of the database to the specified version. The migration version format is YYYYMMDDHHMMSS.

   Example

   OpeniT.Server.Etl.Console.exe DeploySql /version:20220101143000

   /trace
   Use this parameter to display SQL statements.

   Example

   OpeniT.Server.Etl.Console.exe DeploySql /trace

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe DeploySql /version:20220101143000 /trace

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

DeployTimezoneInfo

This command deploys various Time Zone information used for reporting in different time zones.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeployTimezoneInfo

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

GenerateUserToGroupsMap

This command creates a new version of user-to-group mapping file.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe GenerateUserToGroupsMap

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

SetAzureSqlCredentials

This command sets the pre-created Server Login credentials for the Azure Tabular Model data source.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SetAzureSqlCredentials

   The console application will display the following messages, provide the Azure SQL Database Server Admin user name and password.

   Example

   C:\Program Files\OpeniT\Analysis Server>OpeniT.Server.Etl.Console.exe SetAzureSqlCredentials
   Open iT Analysis Server, <version>. Copyright 2008 Open iT, Inc. 
   All rights reserved. http://www.openit.com
   
   Azure Analysis Service Tabular Model needs SQL Server Authentication
    for its data source to connect to Azure SQL database.
   Azure SQL Server need a Server Login with DBMANAGER server role
    which will be use to connect to the server successfully.
   
   Azure SQL Database Server Admin []: <user name>
   Azure SQL Database Server Admin Password (in clear text!): <password>
   
   Save changes [y]? [y/n]

3. When prompted, type y and press Enter to save changes.

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

Install

This includes using a unified command for setting service account and database connections and creating and updating SQL and OLAP database schema.

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 Install subcommand by using the syntax:

   Command Syntax

   OpeniT.Server.Etl.Console.exe Install

   The console application will display the available options.

   Example

   C:\Program Files\OpeniT\Analysis Server>OpeniT.Server.Etl.Console.exe install
   Open iT Analysis Server, <version>. Copyright 2008 Open iT, Inc. 
   All rights reserved. http://www.openit.com
   
   Sql Database: OpeniT @ DESKTOP123WIN
   Olap Database: OpeniT @ DESKTOP123WIN
    Service account: openit_service_account
   	 SQL server: DESKTOP123WIN
   	 SQL database: OpeniT
   	 OLAP server: DESKTOP123WIN
   	 OLAP database: OpeniT
   	 Tabular Server: DESKTOP123WIN\SQL2019STAB
   	 Tabular database: Tabular1
   
    [1]: Set service account
    [2]: Set databases
    [3]: Save configuration
    [4]: Create/Update SQL Database
    [5]: Create/Update OLAP database
    [6]: Create/Update website in IIS
    [0]: Exit
    
    Select:

3. Select from options 1 to 6, then press Enter.

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

Install - [1] Set service account

Instructions

1. Follow the instructions in the Install section.

2. Select option 1.

3. Provide a valid domain or local user. Password is in clear text.

   Example

   Sql Database: OpeniT @ hostname
   Olap Database: OpeniT @ hostname
    Service account: openit_service_account
   	 SQL server: sql_server
   	 SQL database: OpeniT
   	 OLAP server: olap_server
   	 OLAP database: OpeniT
   	 Tabular Server: DESKTOP123WIN\SQL2019STAB
   	 Tabular database: Tabular1
   
    [1]: Set service account
    [2]: Set databases
    [3]: Save configuration
    [4]: Create/Update SQL Database
    [5]: Create/Update OLAP database
    [6]: Create/Update website in IIS
    [0]: Exit
    
    Select: 1
   	Service Account [openit_service_account]: new_service_account
   	Password (in clear text!): [password]
   	Testing credentials: success
   

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

5. Select option 3 to save the changes and allow the process to finish.

   Example

   	Service account: new_service_account
   	SQL server: sql_server
   	SQL database: OpeniT
   	OLAP server: olap_server
   	OLAP database: OpeniT
   	Tabular Server: DESKTOP123WIN\SQL2019STAB
   	Tabular database: Tabular1
   	 
    [1]: Set service account
    [2]: Set databases
    [3]: Save configuration
    [4]: Create/Update SQL Database
    [5]: Create/Update OLAP database
    [6]: Create/Update website in IIS
    [0]: Exit
   
   Select: 3
   2022-04-06 20:51:02,255 +08:00 INFO Config - Updated
   configuration files
   2022-04-06 20:51:02,846 +08:00 INFO Setup - Allow
   "FullControl" permissions to "C:\Program Files\OpeniT\Analysis Server" for
   "new_service_account"
   2022-04-06 20:51:03,423 +08:00 INFO Setup - Allow "Read"
   permissions to "C:\Program Files\OpeniT\Analysis Server" for "Everyone"

Install - [2] Set Database

Instructions

1. Follow the instructions in the Install section.

2. Select option 2.

3. Provide valid SQL and OLAP server host names and SQL and OLAP database names.

   Example

   Sql Database: OpeniT @ hostname
   Olap Database: OpeniT @ hostname
    Service account: openit_service_account
   	 SQL server: sql_server
   	 SQL database: OpeniT
   	 OLAP server: sql_server
   	 OLAP database: OpeniT
   	 Tabular Server: DESKTOP123WIN\SQL2019STAB
   	 Tabular database: Tabular1
   
    [1]: Set service account
    [2]: Set databases
    [3]: Save configuration
    [4]: Create/Update SQL Database
    [5]: Create/Update OLAP database
    [6]: Create/Update website in IIS
    [0]: Exit
    
    Select: 2
   		SQL server [DESKTOP123WIN]: <new_sql_server>
   		SQL database [OpeniT]: <new_sql_database>
   		 OLAP server [DESKTOP123WIN]: <new_olap_server>
   		 OLAP database [OpeniT]: <new_olap_database>
   	 Tabular Server [DESKTOP123WIN\SQL2019STAB]: <new_tabular_server>
   	 Tabular database [Tabular1]: <new_tabular_database>

4. Select option 3 to save the changes.

5. A warning message and a confirmation message will be displayed.

   Read the message carefully and select from the available options. The message varies with the changes made to the server and database.

   Example

   Service account: openit_service_account
   	 SQL server: new_sql_server
   	 SQL database: new_sql_database
   	 OLAP server: new_olap_server
   	 OLAP database: new_olap_database
   	 Tabular Server: DESKTOP123WIN\SQL2019STAB
   	 Tabular database: Tabular1
   	 		 
    [1]: Set service account
    [2]: Set databases
    [3]: Save configuration
    [4]: Create/Update SQL Database
    [5]: Create/Update OLAP database
    [6]: Create/Update website in IIS
    [0]: Exit
    
    Select: 3
    
    2022-04-06 21:32:13,844 +08:00 WARN Actions - You have changed the SQL 
    & OLAP Database/Server. If you intend to reflect currently configured 
    SQL Database in OLAP datasource, please use option [5]. Would you like 
    to apply changes in SQL and OLAP Server/Database in SQL Server Reporting 
    Services [y -yes update both, s -sql only, o -olap only, n -no]? [y]: y
   
   2022-04-06 21:32:23,690 +08:00 INFO Actions - Updating SSRS data source, 
   OpeniT SQL Shared DataSource
   2022-04-06 21:32:48,863 +08:00 INFO Actions - Setting Web Service URL.
   2022-04-06 21:32:48,874 +08:00 INFO Actions - Web Service URL has been 
   updated to http://DESKTOP123WIN/ReportServer/ReportService2005.asmx
   2022-04-06 21:32:48,990 +08:00 INFO Actions - Successfully updated SSRS 
   Datasource. SSRS Datasource Connection String: 
   Data Source=<new_sql_server>;Initial Catalog=OpeniT2;
   2022-04-06 21:32:48,992 +08:00 INFO Actions - Updating SSRS data source, 
   OpeniT Shared DataSource
   2022-04-06 21:33:13,515 +08:00 INFO Actions - Setting Web Service URL.
   2022-04-06 21:33:13,533 +08:00 INFO Actions - Web Service URL has been 
   updated to http://DESKTOP123WIN/ReportServer/ReportService2005.asmx
   2022-04-06 21:33:13,565 +08:00 INFO Actions - Successfully updated SSRS 
   Datasource. SSRS Datasource Connection String: 
   Data Source=<new_sql_server>;Initial Catalog=OpeniT2;
   2022-04-06 21:33:13,574 +08:00 INFO Config - Updated configuration files
   2022-04-06 21:33:14,126 +08:00 INFO Setup - Allow "FullControl" 
   permissions to "C:\Program Files\OpeniT\Analysis Server" for 
   "openit_service_account"
   2022-04-06 21:33:14,708 +08:00 INFO Setup - Allow "Read" permissions 
   to "C:\Program Files\OpeniT\Analysis Server" for "Everyone"

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

Install - [4] Create/Update SQL database

Instructions

1. Follow the instructions in the Install section.

2. Select option 4.

   Example

   Sql Database: OpeniT @ hostname
   Olap Database: OpeniT @ hostname
    Service account: openit_service_account
   	 SQL server: sql_server
   	 SQL database: OpeniT
   	 OLAP server: olap_server
   	 OLAP database: OpeniT
   	 Tabular Server: DESKTOP123WIN\SQL2019STAB
   	 Tabular database: Tabular1
   	 		 
    [1]: Set service account
    [2]: Set databases
    [3]: Save configuration
    [4]: Create/Update SQL Database
    [5]: Create/Update OLAP database
    [6]: Create/Update website in IIS
    [0]: Exit
    
    Select: 4
   
   2022-04-06 21:58:44,979 +08:00 INFO Config - License file is
   valid. "core" valid until 2021-09-25 (536 days), "server_license" valid until
   2021-09-25 (536 days), "server_olapreports" valid until 2022-09-25 (171 days)
   2022-04-06 21:58:45,018 +08:00 INFO CreateSqlDatabaseJob - Open iT Analysis
   Server Version: 9.5.23 (b52305b7)
   2022-04-06 21:58:45,020 +08:00 INFO CreateSqlDatabaseJob - Job started.
   Running as openit_service_account
   2022-04-06 21:58:45,047 +08:00 INFO CreateSqlDatabaseJob - Database OpeniT
   already exists on server DESKTOP123WIN.
   2022-04-06 21:58:45,054 +08:00 INFO CreateSqlDatabaseJob - SQL Server login
   openit_service_account already exists on server DESKTOP123WIN.
   2022-04-06 21:58:45,065 +08:00 INFO CreateSqlDatabaseJob - SQL database user
   openit_service_account already exists on OpeniT.
   2022-04-06 21:58:45,070 +08:00 INFO CreateSqlDatabaseJob - Job done.
   Duration 00.05s

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

Install - [5] Create/Update OLAP database

Instructions

1. Follow the instructions in the Install section.

2. Select option 5.

   Example

   Sql Database: OpeniT @ hostname
   Olap Database: OpeniT @ hostname
    Service account: openit_service_account
   	 SQL server: sql_server
   	 SQL database: OpeniT
   	 OLAP server: olap_server
   	 OLAP database: OpeniT
   	 Tabular Server: DESKTOP123WIN\SQL2019STAB
   	 Tabular database: Tabular1
   	  
    [1]: Set service account
    [2]: Set databases
    [3]: Save configuration
    [4]: Create/Update SQL Database
    [5]: Create/Update OLAP database
    [6]: Create/Update website in IIS
    [0]: Exit
    
    Select: 3
   
   2022-04-06 22:04:35,588 +08:00 INFO Config - License file is
   valid. "core" valid until 2021-09-25 (536 days), "server_license" valid until
   2021-09-25 (536 days), "server_olapreports" valid until 2022-09-25 (171 days)
   2022-04-06 22:04:35,624 +08:00 INFO CreateOlapJob - Open iT Analysis
   Server Version: 9.5.23 (b52305b7)
   2022-04-06 22:04:35,626 +08:00 INFO CreateOlapJob - Job started.
   Running as openit_service_account
   2022-04-06 22:04:35,910 +08:00 INFO CreateOlapJob - Created/selected
   OLAP database OpeniT on DESKTOP123WIN
   2022-04-06 22:04:35,964 +08:00 INFO CreateOlapJob - Updated service
   account in SQL data source impersonation
   2022-04-06 22:04:36,630 +08:00 INFO CreateOlapJob - Updated SQL
   datasource connection. Connection String: Provider=SQLNCLI11.1; Data Source=sql_server;
   Initial Catalog=OpeniT; Integrated Security=SSPI
   2022-04-06 22:04:36,635 +08:00 INFO CreateOlapJob - Service account
    openit_service_account is already registered as member in admin role.
   2022-04-06 22:04:36,655 +08:00 INFO CreateOlapJob - Updated database
    permissions for service account role.
   2022-04-06 22:04:37,075 +08:00 INFO CreateOlapJob - Job done.
   Duration 01.45s

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

ProcessOlap

This command triggers the processing of loaded and processed data in the SQL database, making the OLAP cubes accessible for reporting.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe ProcessOlap

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe ProcessOlap </fullprocess> </tabular> </tabularonly>

   Optional Parameter:

   /fullprocess
   Use this parameter to fully process the OLAP cubes.

   Example

   OpeniT.Server.Etl.Console.exe ProcessOlap /fullprocess

   /tabular
   Use this parameter to include the processing of the tabular model.

   Example

   OpeniT.Server.Etl.Console.exe ProcessOlap /tabular

   /tabularonly
   Use this parameter to process only the tabular model.

   Example

   OpeniT.Server.Etl.Console.exe ProcessOlap /tabularonly

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe ProcessOlap /fullprocess /tabular
   Example

   OpeniT.Server.Etl.Console.exe ProcessOlap /fullprocess /tabularonly
   warning

   It is not recommended to use /tabular and /tabularonly in the same command.

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

UpdateRoleGroups

This command updates the list of groups used for accessibility permission purposes (Roles).

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe UpdateRoleGroups

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

ProcessSql

This command triggers the processing of loaded data in the SQL database and applies all enabled configurations to the data for reporting.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe ProcessSql

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe ProcessSql </fullwipe>

   Optional Parameter:

   /fullwipe
   Usebt his parameter to delete all processed and cached data.

   Example

   OpeniT.Server.Etl.Console.exe ProcessSql /fullwipe

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

RunEtl

This command triggers the complete data processing, from extracting the data from the Core Server database, loading and processing in the SQL database, and processing in the OLAP cubes. This includes the following commands in chronological order:

1. SyncSoftwareCatalog
2. SyncMapping
3. SyncDatabase
4. ProcessSql
5. ProcessOlap

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe RunEtl

   To further customize the command, use the optional parameters.

   Command Syntax

   OpeniT.Server.Etl.Console.exe RunEtl </fullwipe> </from:date> </to:date> </tabular> </tabularonly>

   Optional Parameters:

   /fullwipe
   Use this parameter to wipe all processed and cached data before loading and processing data from the server.

   Example

   OpeniT.Server.Etl.Console.exe RunEtl /fullwipe

   /from:<date>
   Use this parameter to limit the data loading and processing to start on the specified date. The format of the date is YYYY-MM-DD.

   Example

   OpeniT.Server.Etl.Console.exe RunEtl /from:2022-01-01

   /to:<date>
   Use this parameter to limit the data loading and processing to end from the specified date. The format of the date is YYYY-MM-DD.

   Example

   OpeniT.Server.Etl.Console.exe RunEtl /to:2022-03-01

   /tabular
   Use this parameter to include the processing of the tabular model.

   Example

   OpeniT.Server.Etl.Console.exe RunEtl /tabular

   /tabularonly
   Use this parameter to process only the tabular model.

   Example

   OpeniT.Server.Etl.Console.exe RunEtl /tabularonly

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe RunEtl /from:2022-01-01 /to:2022-03-01 /fullwipe /tabularonly

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

SyncDatabase

This command triggers the extraction and loading of all enabled data types from the Core Server database to the SQL database.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncDatabase

   To further customize the command, use the optional parameters.

   Command Syntax

   OpeniT.Server.Etl.Console.exe  SyncDatabase </from:date> </to:date> </clean> </bs:number>

   Optional Parameters:

   /from:<date>
   Used this parameter to limit the data loading to start from the specified date. The format of the date is YYYY-MM-DD.

   Example

   OpeniT.Server.Etl.Console.exe SyncDatabase /from:2022-01-01

   /to:<date>
   Use this parameter to limit the data loading to end on the specified date. The format of the date is YYYY-MM-DD.

   Example

   OpeniT.Server.Etl.Console.exe SyncDatabase /to:2022-03-31

   /clean
   Use this parameter to delete missing data files in the Open iT Core Server database before loading new data.

   Example

   OpeniT.Server.Etl.Console.exe SyncDatabase /clean

   /bs:number
   Use this parameter to limit the size of the data in each batch transfer in bytes. The default value is 0, which disables batching.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102 /bs:10500

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe SyncDatabase /from:2020-01-01 /to:2022-03-31 /bs:10500 /clean

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

SyncDatatype

This command triggers the extraction and loading of a specific data type from the Core Server database to the SQL database.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncDataType datatype

   Required Parameters:

   datatype
   Use this parameter to specify the Open iT data type number to synchronize.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102

   To further customize the command, use the optional parameters.

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncDataType datatype </bco:value> </from:date> </to:date> </ps:number> </bs:number> </reload>

   Optional Parameters:

   /bco:value<+value>
   Use this parameter to select one or more options for BulkLoad: Default, KeepIdentity, CheckConstraints, TableLock, KeepNulls, FireTriggers, UseInternalTransaction, AllowEncryptedValueModifications.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102 /bco:Default+TableLock+FireTriggers

   /from:<date>
   Use this parameter to start the synchronization process from the specified date. The format of the date is YYYY-MM-DD.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102 /from:2022-01-01

   /to:<date>
   Use this parameter to limit the synchronization up to the specified date. The format of the date is YYYY-MM-DD.

   Example

   OpeniT.Server.Etl.Console.exe  SyncDataType 102 /to:2022-04-30

   /ps:<number>
   Use this parameter to limit the size of the data packets transfer in bytes.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102 /ps:6500

   /bs:<number>
   Use this parameter to limit the size of the data in each batch transfer in bytes. The default value is 0, which disables batching.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102 /bs:10500

   /reload
   Use this parameter to delete all loaded data before synchronizing.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102 /reload

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe SyncDataType 102 /bco:Default /from:2022-01-01 /to:2022-04-30 /ps:6500 /bs:10500 /reload

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

SyncLicenseStatus

This command triggers the synchronization process of the License Status data from the Core Server database for the real-time dashboard.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncLicenseStatus

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncLicenseStatus </reload>

   Optional Parameters:

   /reload
   Use this parameter to delete all loaded data before synchronization.

   Example

   OpeniT.Server.Etl.Console.exe SyncLicenseStatus /reload

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

SyncMapping

This command triggers the synchronization process of the user information from the configured mapping files.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncMapping

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

SyncCostInformation

This command triggers the synchronization process of the configured cost information for chargeback reporting.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncCostInformation

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

SyncClientList

This command triggers the synchronization process of the client list information from the Core Server.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncClientList

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

SyncRawOptionsFile

This command triggers the synchronization process of the raw data from the Core Server based on options file.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncRawOptionsFile

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

SyncSoftwareCatalog

This command triggers the synchronization process of the configured Software Catalog information from the Core Server and loads it into the SoftwareCatalog table.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncSoftwareCatalog

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

ConfigureInvoicing

This command sets up important configurations for creating the invoicing template for chargeback reporting.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe ConfigureInvoicing

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe ConfigureInvoicing  </name:value> </action:value>

   Optional Parameters:

   /name:<value>
   Use this parameter to specify the name of the linked server to manage. If not specified, the default value is OPENIT_INVOICE.

   Example

   OpeniT.Server.Etl.Console.exe ConfigureInvoicing /name:OPENIT_LINKEDSERVER

   /action:<value>
   Use this parameter to trigger the action with the command. It accepts Create, Recreate or Delete. The default value is Create.

   Example

   OpeniT.Server.Etl.Console.exe ConfigureInvoicing /action:Delete

   If the Delete action is chosen, a confirmation message will be displayed. Read the message carefully before continuing.

   Example

   Open iT Analysis Server, <version> Copyright 2008 Open iT, Inc. All rights reserved. http://www.openit.com
   
   Sql Database: 91720 @ sql_database
   Olap Database: 91720 @ olap_database
   
   2022-03-15 17:22:36,604 +08:00 INFO Config - License file is valid. "core" valid until 2023-06-15 (456 days), "server_license" valid until 2023-06-15 (456 days), "server_olapreports" valid until 2022-06-15 (91 days)
   2022-03-15 17:22:36,873 +08:00 INFO ManageLinkedServer - Open iT Analysis Server Version: 9.18.1 (e8960cef)
   2022-03-15 17:22:36,877 +08:00 INFO ManageLinkedServer - Job started. Running as service_account
   2022-03-15 17:22:36,882 +08:00 INFO ManageLinkedServer - Deleting Linked Server Deleting a remote server deletes all remote logins defined for that server.
   
   Deleting a remote server deletes all remote logins defined for that server. Are you sure you want to delete linked server OPENIT_INVOICE? [n]

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe ConfigureInvoicing /name:OPENIT_LINKEDSERVER /action:Delete

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

TruncateFacts

This command triggers the removal of loaded data from the data type tables in the SQL database.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe TruncateFacts

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe TruncateFacts </shrink>

   Optional Parameter:

   /shrink
   Use this parameter to issue a SHRINKDATABASE query to the database. This shrinks the size of the data and log file in the database. Please read the SHRINKDATABASE - Best Practice article from Microsoft Learn to know more about when to use this parameter.

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

UpdateConfig

This command updates a specific configuration key value in the Configuration page.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe UpdateConfig key value

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe SyncLicenseStatus </reload>

   Required Parameters:

   key
   Use this parameter to specify the unique key of the configuration entry to update.

   value
   Use this parameter to specify the value to assign to the specified key.

   Example

   OpeniT.Server.Etl.Console.exe UpdateConfig Root.Etl.CoreDatabasePath C:\OpeniT\Data\database

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

UpdateFactTableSchema

This command updates the table definition of data type tables in the SQL database.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema </drop> </create> </compress> </uncompress> </columnstore> </rowstore> </heap> 
   </timeout:number> </tables:number<+number>>

   Optional Parameters:

   /drop
   Use this parameter to drop all the fact tables.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /drop

   /create
   Use this parameter to create all the fact tables and update existing tables when the drop parameter is not specified.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /create

   /compress
   Use this parameter to set the fact tables with data compression.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /compress

   /uncompress
   Use this parameter to set the fact tables without data compression.

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /uncompress

   /columnstore
   Use this parameter to apply clustered column store index on tables.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /columnstore

   /rowstore
   Use this parameter to apply clustered index on tables.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /rowstore

   /heap
   Use this parameter to remove clustered index on tables.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /heap

   /timeout:<number>
   Use this parameter to set the duration (in seconds) before an SQL command times out. The default value is 30.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /timeout:60

   /tables:number<+number>
   Use this parameter to perform an operation on specific fact tables.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /tables:93+95+97+98+70+102

   Combine or use optional parameters at the same time to achieve necessary capabilities.

   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /create /compress
   Example

   OpeniT.Server.Etl.Console.exe UpdateFactTableSchema /columnstore /uncompress /tables:75+93+94+95+102

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

CreateScheduledTask

This command creates a scheduled task for the background processing of data.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe CreateScheduledTask

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

DeleteScheduledTask

This command deletes the scheduled task for the background processing of data.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe DeleteScheduledTask

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

CustomActions

This command triggers non-major actions related to data processing.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe  UpdateConfig key value

   To further customize the command, use the optional parameter.

   Command Syntax

   OpeniT.Server.Etl.Console.exe CustomActions action

   Required Parameters:

   action
   Use this parameter to specify the option number of the action. Available options are:

   • 1 - Update the Open iT Analysis Server Portlets version to support Level 2
   • 0 - Exit
   Example

   OpeniT.Server.Etl.Console.exe CustomActions 1

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

ResetSchedules

This command triggers the console application to redeploy alertJobs.config or alertJobs.custom.config in the database (AdoJobStore).

warning

Please make sure to accomplish the instructions in section Alerts Service Jobs Configuration section to see the effect of the command.

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:

   Command Syntax

   OpeniT.Server.Etl.Console.exe ResetSchedules

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