Data Warehouse (DWH) Management

To set up a DWH, a running ConSol CM Reporting Framework (CMRF) instance is required. If your system does not include a CMRF yet, please consult your ConSol CM manager or contact ConSol Software.

Introduction

Data Warehouse

A data warehouse (commonly known as a DWH) is a collection of data from one or more systems and/or databases that provides a basis for reporting and data analysis. Often, imported data is combined or rearranged (integrated) to make it more suitable for reporting and analysis purposes. A more detailed introduction to the data warehouse principle and to the ConSol CM data warehouse is provided in the ConSol CM DWH Manual.

ConSol CM Data Warehouse and ConSol CM Reporting Framework

A ConSol CM default installation comprises all modules that are required to build a CM data warehouse. One of the two core components is the ConSol CM Reporting Framework (CMRF).

This is a Java EE application which synchronizes the data between a ConSol CM database and a DWH database. The following picture shows an overview of the system architecture of a typical DWH/CMRF installation. We recommend that you use separate servers for the ConSol CM and CMRF instances. Please refer to the current System Requirements for information about the supported application servers and RDBMS.

Figure 333: ConSol CM - System architecture with DWH and CMRF (2 servers)

There are two different synchronization modes for transferring data from ConSol CM to a DWH database:

• LIVE mode
  In this mode, every change that is submitted to the ConSol CM database is immediately synchronized with the DWH.
• ADMIN mode
  In this mode, the administrator has to trigger the synchronization manually.

Only data from ticket fields, customer fields, and resource fields with the annotation reportable = true will be synchronized with the DWH. In addition, all data which will be transferred as default will be transferred to the DWH. For a detailed explanation, please refer to the ConSol CM DWH Manual.

DWH Management Using the Admin Tool

To manage the DWH, use the navigation items in the navigation group Data Warehouse:

• Administration
• Tasks

Administration

For the administration of all DWH operations, open the navigation group Data Warehouse, navigation item Administration.

Figure 334: ConSol CM Admin Tool - DWH, Tasks: Configuration and logs

On the left hand side, the table Actions is displayed which lists all DWH operations which have been performed or which are still running.

You might want to filter the list by one or more of the filters above the list. The filter criteria will be combined by an AND.

• Since
  offers a date picker menu. Only the DWH actions which were started after the selected date will be displayed.
• Type
  only the DWH actions of the selected type will be displayed
  • All actions
    all available actions
  • Already executed
    Actions which are finished. The CM and CMRF status is SUCCESS or ERROR
  • Currently executing
    Actions which are currently running. The CM and/or CMRF status is ACTIVE.
  • Planned to execute
    Actions which have been configured and saved and entered into the list but which are not yet executed. The CM and/or CMRF status is NEW.
• CM Status
  only the DWH actions which have the selected CM status will be displayed.
  • All
  • NEW - first status after creation
  • ACTIVE - data is sent to CMRF
  • PAUSED - active state is stopped until the Resume button is clicked
  • SUCCESS - sending of data to CMRF has been finished successfully
  • ERROR - sending of data to CMRF has been finished unsuccessfully (see log files for information)
• CMRF status
  only the DWH actions which have the selected CMRF status will be displayed
  • ALL
  • NEW - first status after creation
  • ACTIVE - data is processed in CMRF
  • PAUSED - active state is stopped until the Resume button is clicked
  • SUCCESS - processing of data has been finished successfully
  • ERROR - processing of data has been finished unsuccessfully
    

A very elaborated explanation of the ConSol CM DWH transfer processes is provided in the ConSol CM Operations Manual, section CMDB / CMRF/ Data Warehouse Synchronization Process. In the following section in the current manual, only short information will be provided.

The list contains columns and values which are retrieved from the CM database table cmas_dwh_synchronization.

• Type
  • INITIALIZATION
    The DWH is new/empty. In the initialization step, the database structure is built.

  • REINITIALIZATION
    A new initialization of an already existing DWH

  • TRANSFER
    Initial data transfer after set-up. For a detailed explanation, please see section First DWH Synchronization (Transfer).
  • UPDATE
    Only new data is transferred to an already existing DWH. For a detailed explanation, please see section DWH Synchronization During System Operation (Update).
• From
  The start date of the DWH action
• To
  The end date of the DWH action
• CM status
  During a DWH action, data is transferred from the CM database to the DWH. This column indicates the status of the CM part of the action, i.e. the status of the transfer from CM to the DWH control tables. For an explanation of the status, please refer to the list above.
• CMRF status
  During a DWH action, data is transferred from the CM database to the DWH. This column indicates the status of the CMRF part of the action. For an explanation of the status, please refer to the list above.
  The start date of the DWH action
  

Below the list, the following buttons provide all possible DWH actions: 

• Initialize
  • case a) Create tables during DWH set-up. See Initialization of the DWH (Initialize).
  • case b) If the DWH already exists, you can here start a re-initialization. In order to do this, select the Delete existing data option. The database will the be rebuilt from scratch.
• Transfer
  Start initial data transfer after set-up. For a detailed explanation, please see section First DWH Synchronization (Transfer).
• Update
  Transfer new/additional data to the DWH. For a detailed explanation, please see section DWH Synchronization During System Operation (Update).
• Pause
  Pause a running DWH action
• Resume
  Continue a DWH action which has been paused
• Delete
  Deletes an unfinished data warehouse operation from the list, works for scheduled unstarted and previously paused operations. The most common case for this happens when queuing an initialize, transfer and update operation in a row without waiting.
• Log
  Open a panel which displays the cmrf.log file. This always provides the entries from the log file, the selection in the list does not have any influence.
• Configuration
  
  Opens the DWH configuration pop-up menu. See Basic DWH Configuration.

On the right hand side of the Administration panel, the Details section is displayed which lists all details of the action which is selected in the list.

• CM Progress
  Progress bars which indicate the progress of the transfer of CM data to the DWH control tables:
  • upper row: the overall progress for the operation on the sending side
  • lower row: the progress for the current sub-operation
• CMRF progress
  Progress bars which indicate the progress of CMRF updating the DWH database.
  • upper row: the overall progress for the operation on the receiving side
  • lower row: the progress for the current sub-operation
• Parameters
  All parameters of the selected DWH action:
  • Creation date
    Set automatically. Timestamp of the creation of the DWH action
  • Type
    DWH action type (INITIALIZATION |UPDATE etc.), see list above.
  • From
    The start date of the DWH transfer action. Might differ from the creation date when an action has been planned for a time in the future.
  • To
    The end date of the DWH action. Only set for finished actions.
  • CM status
    see above
  • CMRF status
    see above
  • Comment
    The comment which has been set when the DWH action was defined. Might also be empty.
  • CM
    • Package size
      Number of transferred objects per transaction from CM to the DWH control tables
    • Retry count
      Number of retries which are performed if an error occurs. After this number of retries has been reached, the operation is finished with the CM status ERROR.
  • CMRF

    • Package size
      Number of transferred objects per transaction from the DWH control tables to the DWH tables

    • Retry count
      Number of retries which are performed if an error occurs. After this number of retries has been reached, the operation is finished with the CMRF status ERROR.

Basic DWH Configuration

Before you can set up a ConSol CM DWH you have to prepare a database (or database schema) which will contain the DWH data. The respective database server has to be available for the CMRF server. For a detailed description of those topics, please refer to the ConSol CM Setup Manual. Once the database (or database schema) for the DWH has been prepared and the CMRF is up and running, you can continue with the following steps.

In order to configure the DWH synchronization mode and the DWH-relevant notifications, open the navigation group Data Warehouse, navigation item Administration. Click on the Configuration button and enter all required values.

Figure 335: ConSol CM Admin Tool - Data Warehouse, Administration: DWH configuration

• DWH Mode Selection:
  • LIVE
    In this mode, every change that has been submitted to the ConSol CM database is immediately synchronized to the DWH.
  • ADMIN
    In this mode, the administrator has to trigger the synchronization manually.
  • OFF
    No data is transferred to the DWH.

You can also see the current DWH mode by looking at the corresponding DWH system property cmas-dwh-server, dwh.mode(see System Properties).

Figure 336: ConSol CM Admin Tool - System property for DWH mode

• Notification
  Here, the parameters for the emails which are sent due to DWH events, are configured: 
  • Protocol
    Required - The protocol that is used to send the message. This is usually SMTP.
  • Host
    Required - The email server. You can enter a name (DNS-resolvable) or an IP address.
  • Port
    Required - The port on the email server where the mail daemon is listening.
  • User
    Optional - User name, if user authentication is required by the email server.
  • Password
    Optional - Password of the email user if user authentication is required by the email server.
  • Tabs Error/Successful/Unsuccessful
    Here the email parameters for emails that are sent by the system regarding DWH operations can be configured. There are three types of messages: in case of an error, in case of a successful operation, and in case of an unsuccessful operation.
    
    • From
      The From email address for messages (this may differ from the From address used for emails to customers and to engineers).
    • To
      The email address of the recipient of the DWH messages. Initially this will be the ConSol CM administrator's email address (CM system property cmas-core-security, admin.email).
    • Subject
      The (email) subject of the error/success/unsuccessful message.
    • Description
      The body (text) of the message.

Initialization of the DWH (Initialize)

When the basic configuration has been performed, the DWH initialization can be started. Click the Initialize button. A new DWH action of type INITIALIZATION will be entered in the list.

During this step, the database structure in the DWH is created with all tables and relations. No data will be transferred yet.

If the DWH has been in operation and has to be set-up a second time, a reinitialization has to be performed. Check the Delete existing data option in order to delete the old database structure and create a new one.

First DWH Synchronization (Transfer)

To fill the data warehouse with the ConSol CM data for the first time, click Transfer. The initial transfer is started. Depending on the number of tables, this might take some time (even several hours). You can follow the log entries by opening the cmrf.log file using the Log button.

For each Transfer operation, several parameters can be set, see section Parameters for Transfer and Update Operations.

The action Transfer should only be used directly after initialization, as this deletes all existing data from the standard tables and deletes all custom tables! For large databases this (transfer) can lead to problems concerning the available space on the database server due to a very large transaction log volume.

DWH Synchronization During System Operation (Update)

For each Update operation, several parameters can be set, see next section (Parameters for Transfer and Update Operations).

System running in ADMIN mode

If the DWH is running in ADMIN mode, the DWH administrator has to start the update manually by clicking Update. When the Update button is clicked, a new task of type Update will be created for the operation. This task will appear in the list in the Tasks panel (navigation item Tasks). Then all data that are supposed to be transferred, i.e., data from fields with the reportable = true annotation that have been added or changed since the end date of the last Transfer or Update action, are transferred.

If a ticket field, customer field or resource field did not have the reportable annotation at the time of the last transfer and has it now, the corresponding content of the field from all tickets, customers and resources is transferred.

System running in LIVE mode

In case the system is running in DWH live mode, the task of type Update is created and managed as mentioned for the ADMIN mode. Additionally, the LIVE mode will be suspended for the time of the update and will be reactivated automatically when the update is finished.

Notes for ADMIN and LIVE mode

Do not remove the annotation reportable = true for any field without being absolutely sure that the data is not required in reports any longer! If you remove a field that is used in reports and/or data cubes, the reporting will fail at run-time!

Parameters for Transfer and Update Operations

For each Transfer or Update operation, several parameters can be set in the pop-up menu which is opened when the Transfer or the Update button has been clicked.

Figure 337: ConSol CM Admin Tool - Data Warehouse, Administration: Configuration of a transfer operation

Select predefined configuration for the individual job to start:

• Default
  run the job with the default settings, just like without any modification in the dialog.
• Last
  run the job with the same configuration like the previous run.
• Safe
  run the job with cautious settings, which may cause it to run longer, however, the settings ensure that no OutOfMemory exception will occur.
• Fast
  run the job with settings optimized for fast completion, however, OutOfMemory exceptions can occur, if the Java Virtual Machine is not well-tuned.

When you click one of the buttons, the parameters for the other fields (e.g. package size) are loaded into the fields from the respective configuration. No operation is started yet. You can further modify the values for the planned operation.

The following fields are available: 

• CM (sending side)
  • Package Size
    Number of objects sent in one DWH message. Higher values mean better transfer performance and bigger memory usage. If the system has enough RAM, the value can be 1000 or even more. (In CM versions lower than 6.11, this was covered by the CM system property cmas-dwh-server, batch-commit-interval, but this was a system-wide parameter. Starting with CM version 6.11, it is possible to set this for every DWH operation individually.)
  • Retry Count
    Number of retries until an ERROR is thrown.
• CMRF (receiving side)
  • Cache Size
  • Retry Count
    Number of retries until an ERROR is thrown.
• Advanced Configuration
  • From
  • To

• Comment
  Optional. A comment which characterizes the DWH action. Will be written into the database and displayed in the Details section.

DWH Tasks

If there are due or active DWH tasks, this will be indicated in the Admin Tool, see following figure. The navigation group Data Warehouse shows an exclamation mark so that even when this navigation group is closed you will know that there are active tasks. The number of active tasks is indicated at the navigation item Tasks.

Figure 338: Indication of active DWH tasks in the Admin Tool

The list Current tasks contains columns and values which are retrieved from the CM database table cmas_dwh_task.

In the list of Current tasks, you will find entries (one entry per task) if ...

• the DWH is running in ADMIN mode and the administrator has started an update: all tasks that have to be performed are listed.
• the DWH is running in LIVE mode but the check box Automatic commit of administrative changes has not been checked.
• Ticket field, customer field or resource field annotations have been changed to reportable = true and the checkbox Automatic commit of administrative changes has not been checked.
• Ticket field, customer field or resource field annotations have been added and changed to reportable = true and the checkbox Automatic commit of administrative changes has not been checked.
• Ticket field, customer field or resource field annotations have been changed from reportable = true to false and the checkbox Automatic commit of administrative changes has not been checked.

If the checkbox Automatic commit of administrative changes has not been checked, the tasks will remain in the status "ready for execution".

You can mark one or more tasks in the list and execute them manually (Run tasks button).

If the checkbox Automatic commit of administrative changes has been checked, the tasks will be run automatically by the system. The execution can take several minutes.

There are some special cases to consider:

• Removing the annotation reportable = true or reportable group = true does not create a DWH task. These column and the respective DWH  tables are only removed by the next DWH update.
• In ADMIN mode, a task for changing reportable to false is ignored! A task is created and is removed instantly. The DWH table is not changed! This change is performed by the next DWH update.
• In LIVE mode, the task is executed immediately, even when the checkbox Automatic commit of administrative changes is not checked.
• With the DWH update action the table structure is extended by the newly annotated field from the open tasks, but the data is only initially filled in during the execution of the respective DWH task!

DWH Monitor

To open the DWH Monitor, use the navigation group Data Warehouse, navigation item Monitor.

Figure 339: Consol CM Admin Tool - Data Warehouse, Monitor: DWH Monitor

The DWH monitor provides a quick overview of all DWH parameters. The date of the current snapshot is indicated at the bottom right of the screen.

The following parameters are displayed: 

• Status
  A quick overview of the most important parameters.
  • DWH status

    Color of status symbol	DWH status message
    Green	OK
    Yellow	Uninitialized
    	Action is processed
    	Action is paused
    	Action is planned
    Red	Error
    Grey	Disabled

  • Last transfer date
    Date of the last transfer or update operation
  • Live mode status

    Color of status symbol	Live mode status message
    Green	OK
    Yellow	Uninitialized
    	Action is processed
    	Action is paused
    	Action is planned
    	Started (update is needed)
    Red	Error
    	Error (update is needed)
    Grey	Disabled

• Queues
  Informs about the number of message entries in the internal data warehouse message queue tables. Use the Refresh button to update the values.
  • Transfer: Number of messages in the data warehouse transfer table (INT_TRANSFER_QUEUE)
  • Live: Number of messages in the LIVE messages table (INT_LIVE_QUEUE)
  • Log: Number of messages in the log messages table (INT_LOG_QUEUE)
  • Control: Number of messages in the control messages table (INT_CONTROL_QUEUE)
• Connection
  Test the connection to the DWH database using the Test button.
• CMRF statistics
  Provides a list of the number of objects which have been transferred.

DWH Troubleshooting and Repair

If any errors have occurred during initialization, transfer, or update, the log entries are displayed in the log panel which is opened with the Log button .

You can also check the original log file in <JBOSS_HOME>/standalone/log/cmrf.log.

Please note that these are the standard paths. They may be configured to use different paths in the file cm6-cmrf.xml file. A detailed description of CM logging and log files is provided in the ConSol CM Setup Manual and the ConSol CM Operations Manual.

Usually the log file and/or log panel entries give good hints regarding the initial reason for a transfer failure. If you run into a problem you cannot resolve and you have a maintenance contract with ConSol, please contact our support team.

For more details on Troubleshooting and Repair see section CM / CMRF / DWH Synchronization: FAQs and Tips for Troubleshooting in ConSol CM Operations Manual

DWH-Related System Properties

A list of all system properties which are relevant for a specific DWH configuration can be found in section CMRF & DWH Configuration of the System Properties chapter.

Transfer Mode

All data which has been annotated as reportable = true has to be transferred to the Data Warehouse. This is performed by ConSol CM and the CMRF.

In ConSol CM versions 6.11. and up, there is one transfer mode:

• DIRECT mode

The mode is set using the system property cmas-dwh-server, communication.channel. Possible values are:

• DIRECT: database communication channel, only available value since version 6.11.

In DIRECT mode, the CM server sends messages to the CMRF by using direct access to database tables in the DWH database:

• INT_CONTROL_QUEUE
• INT_LIVE_QUEUE
• INT_TRANSFER_QUEUE

CM is able to do that because in DIRECT mode, the CMRF datasource is made accessible for ConSol CM as well, i.e.

• in overlay mode:
  ConSol CM and CMRF use the same application server
• in standalone mode:
  the CMRF database configuration file is also located on (copied to) the ConSol CM application server

The CMRF server reads the entries from the tables and writes the data into the DWH.

Expert DWH Information

Creating DWH Cubes

The ConSol CM API includes methods to create, update and delete cubes on the DWH database using task scripts. The created cube contains the following information:

• Basic ticket data (ID, name, subject, queue, scope, creation date, close date)
• Assigned engineer (first name, last name, login)
• Ticket fields which are annotated as reportable, except for lists and structs
• Main customer, this can be either a contact or a company (ID and customer group)
• Company of the main customer (ID)

Optionally, the cube can be configured to contain:

• Customer fields of the main customer which are annotated as reportable, except for lists and structs
• Customer fields of the main customer’s company which are annotated as reportable, except for lists and structs

The IDs used in the cube are the IDs from CMRF, not the IDs which the objects have in ConSol CM.

The following settings can be made for the cube:

• Name:
  
  The name of the cube, i.e. the name of the view in the DWH database.
• Queues:
  
  One or several queues whose tickets should be included in the cube.
• Language:
  
  Language which should be used for the data fields and the enum values.
• Customer fields:
  Whether the customer data fields should be included in the cube.
• Company fields:
  Whether the data fields of the customer’s company should be included in the cube.

The methods belong to the classes DwhCubeService and DwhCube. The following example shows a task script to create a cube.

The cube must be updated using the dwhCubeService.update(cube) method if there are changes to reportable annotations or localized labels of data fields / enum values. Otherwise, there changes are not reflected in the cube.

Localization of Static DWH Tables

The static DWH table can include database columns for the localized values of some entities. This means that the localized descriptions of the entities can also be transferred to the DWH. The following example shows the fields in the Admin Tool and in the DWH for projects in ConSol CM.

Figure 340: ConSol CM Admin Tool - Global Configuration, Projects: Localized values of a project

Figure 341: DWH table with localized values

By default, the transfer of the localized values is disabled, as it can significantly increase the duration of the DWH updates. You can enable the transfer of the localized values using the Java system property cmrf.localization.enabled.

Add the parameter -Dcmrf.localization.enabled=true to the CMRF start command. A DWH update is required to fill the corresponding columns.

Example start command with localization enabled:

nohup $JBOSS_HOME/bin/standalone.sh --server-config=cm6-cmrf.xml -b=0.0.0.0 -Dcmrf.localization.enabled=true

Setting cmrf.localization.enabled to true can increase the runtime of DWH updates significantly.

For a detailed explanation, please see also the ConSol CM DWH Manual.

MBeans for DWH Management

The MBean dwh.admin.service, in consol.cmas.admin.global, provides access to the DWH mode (OFF |ADMIN | LIVE) . The two methods getMode() and setMode() are available.

The MBean can be accessed using graphic tools, e.g., JConsole for JBoss.