DWH administration

Introduction to the data warehouse in ConSol CM

The data warehouse is a separate database which contains certain data from ConSol CM in a data model tailored specifically for reporting and data analysis purposes.

Concepts, terms and definitions

Concept	Other terms	Definition
DWH	data warehouse	Database which contains data from ConSol CM in a data model tailored specifically for reporting purposes
CMRF	ConSol CM Reporting Framework	ConSol CM module in charge of transferring data to the DWH database
cube	view	View on the DWH database which contains data needed for reporting

Prerequisits

The DWH requires a CMRF, which is a separate application with its own database. The CMRF must be installed before configuring the DWH, see ConSol CM Setup Manual.

You can find detailed information about the DWH database structure in the ConSol CM DWH Manual.

Purpose and usage

By default, the DWH is disabled, you need to configure it, initialize it and transfer data to start using the DWH. This is done on the Administration page in the DWH menu. If you use the DWH in admin mode, you need to remember to update the data in the CMRF database regularly. Once the DWH is ready, i.e. the CMRF database is filled with data, you can create a cube on the Cubes page to facilitate reporting.

The current status is shown in the three header icons. The first icon indicates the DWH status, the second icon indicates the status of the live mode, and the third icon shows the status of the connection to the CMRF database. You can refresh the database status by clicking the Test connection to CMRF DB button.

The Administration page shows a list of all DWH-related actions with several filters, which allows you to track the actions. In addition, you can see current tasks on the Tasks page, which allows you to execute the tasks. The Monitor page provides an overview of the DWH status and content, see Using the data warehouse monitor.

Available settings for the data warehouse

The following settings are available for the data warehouse:

• DWH mode: Mandatory. Determines if the data warehouse is enabled and how data is updated. Possible values:

  • Off: The data warehouse is disabled.

  • Admin: The data warehouse is enabled, but the data is not updated automatically. The administrator needs to create an update action for this purpose, see Updating the data in the data warehouse.

  • Live: The data warehouse is enabled and the data is updated automatically. This means that the required update actions are created and executed automatically. Therefore, the data is always up-to-date without administrator actions.

• Email connection: Optional. Determines the outgoing email connection used to send emails related to the data warehouse.

  • Protocol: The protocol used to send emails. Supported protocols are SMTP, SMTP with STARTTLS and SMTPS.

  • Host: The host name or IP address of the email server.

  • Port: The port used to send emails. It depends on the used protocol.

  • User name: The name of the email account.

  • Password: The password of the email account.

• Email properties: Optional. Defines the sender, recipient, subject and body of the emails which are sent in the following situations:

  • Error: Defines the email which is sent when an error occurs.

  • Successful transfer: Defines the email which is sent when a data transfer has been finished successfully.

Available data in the data warehouse

The data warehouse automatically contains the basic data needed for reporting. This includes the basic data of the objects in ConSol CM (cases, contacts, resources, users, queues, workflows, etc.), the relations between the objects and the case history. In addition, custom-defined data fields can be added to the data warehouse. This is done by setting Transfer to DWH to true. You can set Field values omitted from DHW history to true, if you do not need the field history. These two settings are available both for the whole field group and for single fields.

Basic tasks

Configuring the data warehouse

You can configure the data warehouse by clicking the Configure DWH button and filling out the data in the four tabs:

• Mode and email connection: Enable the data warehouse by selecting the DWH mode Admin or Live. Provide the outgoing email connection to receive notifications about DWH operations.

• Error: Enter the properties of emails which are sent when an error occurs.

• Successful transfer: Enter the properties of emails which are sent when a data transfer has been finished successfully.

See Available settings for the data warehouse for details about the settings.

Initializing the data warehouse

The initialization operation creates a database structure with all static tables and relations. This step is only performed once during the initial setup of the data warehouse.

Click the Initialize button and confirm the modal window by clicking Initialize DWH to create an action which performs the initialization. You can view the details and progress of the action by selecting it in the table. In addition, you can click the View log icon to display the cmrf.log file.

If you want to reinitialize the data warehouse, i.e. rebuild it from scratch after an error, you must select the Delete existing data checkbox. This drops the whole database structure and creates it again.

Transferring data to the data warehouse

The transfer operation creates the dynamic tables and fills both the static and the dynamic tables with data from ConSol CM. This step is only performed once during the initial setup of the data warehouse.

If the data warehouse tables are not empty, all existing data and all custom tables are removed during the transfer.

Click the Transfer button and confirm the modal window by clicking Transfer data to DWH to create an action which performs the initial transfer. If needed, you can provide special settings, see Providing advanced settings for transfer or update. You can view the details and progress of the action by selecting it in the table. In addition, you can click the View log icon to display the cmrf.log file.

Depending on the size of the system, the initial transfer might take some time.

Updating the data in the data warehouse

The update operation adapts the data warehouse structure to configuration changes and transfers data which has been added or changed since the last update or transfer operation. Updates are required during the regular operation of the data warehouse.

If the DWH is running in live mode, the required update actions are created and executed automatically. If the DWH is running in admin mode, you need to create the update action manually. To do so, click the Update button and confirm the modal window by clicking Update data in DWH. If needed, you can provide special settings, see Providing advanced settings for transfer or update. You can view the details and progress of the action by selecting it in the table. In addition, you can click the View log icon to display the cmrf.log file.

If a configuration change affects fields or relations which existed before the last update, the data which is older than the last update must be transferred in a task, see Executing data warehouse tasks.

Tracking data warehouse actions

The table on the Administration page contains all data warehouse actions. You have several options to filter the table:

• Type: Display only actions of a certain type (Initialization, Reinitialization, Transfer, Update, Task, Cases or Contacts)

• Execution: Display only actions with a certain execution time (Past (action finished successfully or with an error), Present (action is active on CM or CMRF side) or Future (action is new))

• CM status: Display only actions with a certain status of the processing on CM side (Active, Paused, New, Error, Success). The CM status indicates the status of the data transfer from the CM database to the DWH control tables.

• CMRF status: Display only actions with a certain status of the processing on CMRF side (Active, Paused, New, Error, Success). The CMRF status indicates the status of the data processing within the DWH.

• Since: Display only actions newer than the selected date.

You can select the row of an action to display the action details.

A new action can be removed during the first 60 seconds after its creation. Click the Delete icon in the row of the action to remove it. Afterwards, you can only pause an action which is active on CM side by clicking the Pause icon. You can resume the processing by clicking the Resume icon.

Executing data warehouse tasks

Data warehouse tasks are created for certain configuration changes:

• Add or remove the fields from the DWH (Transfer to DWH setting)

• Add or remove the field history from the DWH (Field value omitted from DWH history setting)

• Set a contact or resource relation to Reportable

• Create a cube

Tasks are used to transfer the data which is not transferred with the update operation because it is outside the time period covered by the update.

The currently open tasks are shown on the Tasks page. Their handling depends on the DWH mode:

• Admin mode: Tasks are created with the status New. You must either perform an update on the Administration page to change the task status to Ready for execution, or click the Start icon is the row of the task to execute the task directly.

• Live mode: Tasks are created with the status Ready for execution.

If the checkbox Administrative changes are applied automatically is checked, the tasks with the status Ready for execution are executed automatically. Otherwise, you must execute them manually by clicking the Start icon in the row of the task. You can also execute several tasks at once.

Advanced tasks

Providing advanced settings for transfer or update

It is possible to modify the data transfer settings for transfer and update actions. The following settings are available:

• CM package size: Number of transferred objects per transaction from CM to the DWH control tables. Higher values mean better performance and bigger memory usage.

• CM retry count: Number of retries performed before the action is finished with the CM status Error.

• CMRF cache size: Number of transferred objects per transaction from the DWH control tables to the DWH tables.

• CMRF retry count: Number of retries performed before the action is finished with the CMRF status Error.

You can either modify these settings manually or choose one of the predefined configurations:

• Default: default settings providing a balance between safety and velocity

• Safe: settings to avoid out of memory errors, the action will take longer

• Fast: settings for fast execution, might cause out of memory errors

• Last: same settings as the last execution

The Advanced configuration section allows to set start and end dates for the operation. These dates are set automatically and should only be changed in special situations if recommended by the ConSol CM support.

Using the data warehouse monitor

The Monitor page shows details about the data warehouse status. The summary is shown in the three header icons. The first icon indicates the DWH status, the second icon indicates the status of the live mode, and the third icon shows the status of the connection to the CMRF database. You can refresh the database status by clicking the Test connection to CMRF DB button.

The page consists of three sections:

• Status: Shows the data of the last transfer to the CMRF and of the last live message received by the CMRF.

• Queues: Shows the number of items in the different data warehouse queues.

• CMRF data: Shows the number of entities saved in the CMRF database. The first column shows entities which belong to the configuration data, and the second column shows runtime entities.

You can click the Refresh data button to refresh the data shown on the page.

Understanding the implications of configuration changes

The following table shows the effect of configuration changes in ConSol CM on the DWH database:

Change	Setting	Actions on update	Task for data transfer
Add field to the DWH - field group with Transfer to DWH not set	Transfer to DWH set to true	A column for the field is added to the field group table, except for list and table fields, for which an own table is created. If Field value omitted from DWH history is NOT set to true, a table for the field history is created.	Yes
Add field to the DWH - field group with Transfer to DWH set to true	Transfer to DWH set to true	A column for the field is added to the field group table, except for list and table fields, for which an own table is created. If Field value omitted from DWH history is NOT set to true, a table for the field history is created.	No
Remove field from the DWH	Transfer to DWH set to false	The column or table of the field is deleted.	No
Add the field history to the DWH	Field value omitted from DWH history set to false	A table for the field history is created.	Yes
Remove field history from the DWH	Field value omitted from DWH history set to true	The table with the field history is deleted.	No
Add an existing contact or resource relation	Relation set to Reportable	Row for the relation is added to the relations table.	Yes
Create a contact or resource relation	Relation set to Reportable	Row for the relation is added to the relations table.	No
Remove a contact or resource relation	Reportable setting removed	Row of the relation is removed from the relations table and from related tables.	No

Adding localized names to the data warehouse

By default, the internal names of the entities are transferred to the DWH. If needed, you can transfer the localized names by adding the parameter -Dcmrf.localization.enabled=true to the start command of the application server running the CMRF or by adding the Java property cmrf.localization.enabled to the configuration file. A DWH update is required to transfer the localized values.

Transferring localized values can significantly increase the duration of the DWH operations, especially if there is more than one system language.