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

ConSol CM Data Warehouse and ConSol CM Reporting Framework

A ConSol CM default installation comprises all modules that are required to build a 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 339: 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 documentation.

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 340: 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
  • ACTIVE
  • PAUSED
  • SUCCESS
  • ERROR
• CMRF status
  only the DWH actions which have the selected CMRF status will be displayed
  • ALL
  • NEW
  • ACTIVE
  • PAUSED
  • SUCCESS
  • ERROR
    

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.
  • UPDATE
    Only new data is transferred to an already existing DWH.
• 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.
  • case b) If the DWH already exists, you can here start a re-initialization. In order to do this, select the Delete existing dataoption. The database will the be rebuilt from scratch.
• Transfer
  Start initial data transfer after set-up. See First DWH Synchronization.
• Update
  Transfer new/additional data to the DWH. See DWH Synchronization During System Operation.
• 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 Set-Up 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 Configurationbutton and enter all required values.

Figure 341: 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 342: 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

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

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.

DWH Synchronization During System Operation

If the DWH is running in ADMIN mode, the DWH administrator has to start the transfer manually by clicking Update. Then all data that is supposed to be transferred, i.e., data from fields with the reportable = true annotation that has been added or changed since the last transfer, is transferred. When the Update button is clicked, all required operations will be created as tasks and all open tasks will be listed in the Tasks panel (navigation item Tasks).

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 or resources is transferred.

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!

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

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.

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 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 343: 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 set to reportable = true and the checkbox Automatic commit of administrative changes has not been checked.

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.

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 under the following path:

• JBoss 7 (single server):

  <JBOSS7_HOME>/standalone/log/cmrf.log

• Weblogic:

  <ORACLE_HOME>\Middleware\user_projects\domains\consolcm6_domain\cmrf-logs\cmrf.log

Please note that these are the standard paths. In ConSol CM, e.g., Log4J is used. 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.

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.

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 Modes: JMS or DIRECT

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 CM version 6.10 and older, there were two possible transfer modes. Only one transfer mode is available in CM versions 6.11 and up:

• JMS mode: deprecated in CM versions 6.11 and up
• DIRECT mode: available in CM versions 6.8.5.0 and up. Only possible transfer mode in CM versions 6.11 and up.

Configuring the Transfer Mode

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

• "JMS": only possible value in versions prior to 6.8.5.0, default value before 6.9.4.1. Not supported anymore in CM versions 6.9.4 and up. Finally deprecated in version 6.11.
• "DIRECT": database communication channel, exists since version 6.8.5.0 (has to be added manually in versions 6.8.5.0 to 6.9.4.0), default value since 6.9.4.1. Only available value since version 6.11.

JMS Mode (CM Versions Lower Than 6.9.4, deprecated in version 6.11)

In JMS mode, the ConSol CM server sends messages to the CMRF by using the JMS queues transfer, live, and control. These JMS queues are located either in

• the table JBM_POSTOFFICE in the CM database (in overlay mode)

or

• in a separate JBM_POSTOFFICE table in the DWH database (standalone mode).

The CMRF server reads the messages from the JMS queues and writes the data into the DWH.

DIRECT Mode (Available in CM Versions 6.8.5.0 and Up, only available mode in 6.11 and up)

In ConSol CM versions 6.9.4 and up, this is the only available transfer mode!

In DIRECT mode, the CM server sends messages to the CMRF by using direct access to database tables (INT_CONTROL_QUEUE, INT_LIVE_QUEUE, INT_TRANSFER_QUEUE in the DWH database). JMS is not involved. 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 anyway
• in standalone mode:
  the CMRF database configuration file is also located on (copied to) the ConSol CM application server

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

Please note that the JMS mode is only supported in ConSol CM versions prior to 6.9.4! Starting with ConSol CM version 6.9.4.1, only the DIRECT mode is supported!

Expert DWH Information

Internationalization of Static DWH Tables

Starting with CM version 6.10.1, database fields for the localized values in static DWH tables were added. This means, the localized descriptions of parameters will 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 344: ConSol CM Admin Tool - Global Configuration, Projects: Localized values of a project

Figure 345: DWH table with internationalized values

The use of those fields can increase the DWH update time. Thus, to prevent an update from running too long, you can start CM with the Java system property cmrf.localization.enabled. This property can be used to switch the transfer of localized values to the DWH on or off.

Example start command:

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

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

MBeans for DWH Management

Starting with version 6.10.5.4, the MBean dwh.admin.service (to be found 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, or it can be used via REST API. This is described in the ConSol CM REST API Documentation.