Open topic with navigation

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.

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. The core component 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 257: 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 Custom Fields, Data Object Group 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:

• DWH Tasks
• DWH Configuration and Logs

DWH Configuration and Logs

Figure 258: ConSol CM Admin Tool - DWH configuration and logs

In the center area, the log file information of DWH operations is displayed. Use the radio buttons on the right-hand side to select which log file should be displayed. The DWH operations are available as buttons in the row below the center area.

• Initialize
  Create tables during DWH set-up. See Initialization of the DWH.
• overwrite
  Used for re-initialization. See Initialization of the DWH.
• 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.
• Configuration
  Opens the DWH configuration panel. See Basic DWH Configuration.

The radio buttons and the buttons for DWH operations do not influence each other, i.e., when you select an operation, the log file display is not changed. See the following paragraphs for detailed explanations about all operations.

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 prepare the system for the DWH synchronization, you have to configure the database and the DWH mode. In the Admin Tool, open the navigation group Data Warehouse, navigation item DWH Configuration and Logs. Click on Configuration, open the tab Configuration, and insert all values of the CMRF server.

Figure 259: ConSol CM Admin Tool - DWH configuration and logs: DWH configuration

For DWH Mode Selection, choose one of the available options:

• 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 260: ConSol CM Admin Tool - System property for DWH mode

For the connection, the following parameters are required:

• For JBoss:
  • Initial context factory
    The Java class that is used for the connection. No changes are required here, as ConSol CM selects the correct value during system set-up.
  • URL factory packages
    The Java package that comprises the required connection classes. No changes are required here, as ConSol CM selects the correct value during system set-up.

  • CMRF URL
    The URL of the CMRF, i.e., the URL to which the ConSol CM system should connect in order to provide information about new synchronization tasks. The notation is:

    <CMRF_HOST_IP>:<JNDI_PORT>

    (e.g., 192.168.0.1:1099). Please note that the default JNDI port is 1099. If you are using different JBoss port mappings then the JNDI port will also differ. I.e., when using ports-01 then the JNDI port is 1199, for ports-02 it is 1299, and so on.

• For Weblogic:

  • Initial context factory
    The Java class that is used for the connection. Use:

    weblogic.jndi.WLInitialContextFactory

  • URL factory packages
    The Java package that comprises the required connection classes. Use:

    weblogic.jndi.factories:weblogic.corba.j2ee.naming.url:weblogic.corba.client.naming

  • CMRF URL
    The URL of the CMRF, i.e., the URL to which the ConSol CM system should connect in order to provide information about new synchronization tasks. The t3 protocol has to be used, i.e.,

    t3://<CMRF_HOST_IP>:<JNDI_PORT>

    (e.g., t3://localhost:7010).

In the tab Notification you can configure the format of the messages (e-mails) which are sent by the system concerning DWH operations. These might be errors, success messages, or an information about an unsuccessful operation.

The values are saved in the DWH notification properties (see System Properties for details).

Figure 261: ConSol CM Admin Tool - DWH configuration: Tab Notification

The following fields are available:

• Protocol
  Required - The protocol that is used to send the message. This is usually SMTP.
• Host
  Required - The e-mail server. You can enter a name (DNS-resolvable) or an IP address.
• Port
  Required - The port on the e-mail server where the mail daemon is listening.
• User
  Optional - User name, if user authentication is required by the e-mail server.
• Password
  Optional - Password of the e-mail user if user authentication is required by the e-mail server.
• Tabs Error/Successful/Unsuccessful
  Here the e-mail parameters for e-mails 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 e-mail address for messages (this may differ from the FROM address used for e-mails to customers and to engineers).
  • To
    The e-mail address of the recipient of the DWH messages. Initially this will be the ConSol CM administrator's e-mail address.
  • Subject
    The (e-mail) 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 Initialize and follow the entries in the log panel. Be sure you have marked Initialization (radio button) in the top/right corner to display the initialization events from the log file.

Figure 262: ConSol CM Admin Tool - DWH initialization

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 overwrite option in order to delete the old database structure and create a new one, then click Initialize.

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 Transfer in the log panel.

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., all 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 DWH Tasks panel.

If a Custom Field, Data Object Group or Resource Field 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.

You can follow the log entries for the DWH operation by opening the Update part of the log panel.

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!

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 DWH Tasks.

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

In the list of DWH 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.
• Custom Field, Data Object Group 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 a task in the list and execute it manually.

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 respective log panel.

You can also check the original log file under the following path:

• JBoss 5:

  <JBOSS_HOME>\server\<CMRF_SERVER_NAME>\log\cmrf.log

• 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 log4j.xml file. A detailed description of CM logging and log files is provided in the ConSol CM Set-Up 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.

There are two possible modes:

• JMS mode
• DIRECT mode

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.
• "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

JMS Mode (CM Versions Lower Than 6.9.4)

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)

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 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 264: ConSol CM Admin Tool - Localized values of projects

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