_DE_und_EN_V5.png)
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.
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 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 321: 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:
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.
To manage the DWH, use the navigation items in the navigation group Data Warehouse:
Figure 322: 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.
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.
IMPORTANT BACKGROUND INFORMATION ABOUT DWH OPERATIONS
Please be aware of the ConSol CM/CMRF behavior with regards to operations for the DWH!
When you click one of the buttons Initialize, Transfer or Update, an operation with this type will be created as an entry in the ConSol CM database table cmas_dwh_synchronization. Each click on a button creates a new operation of the respective type. The operations are then executed one after another in the order of their creation, i.e., in FIFO (first-in-first-out) order!
When you have started an operation, the Admin Tool is blocked and the "turning wheel" is displayed for as long as it takes to execute the first steps of the operation. However, when the Admin Tool finished the operation preparation and control is restored to the user, the operation might still be running in the background! Do not click another button unless you really want to start another DWH operation, which will be queued as an entry in the table cmas_dwh_synchronization and will be executed as soon as the first operation is finished.
You can follow the update operations using the log file display (via radio buttons). When an operation is finished, a line like the following will be displayed:
10.11.2015 07:57:29 Transfer finished successfully
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 323: ConSol CM Admin Tool - DWH configuration and logs: DWH configuration
For DWH Mode Selection, choose one of the available options:
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 324: ConSol CM Admin Tool - System property for DWH mode
For the connection, the following parameters are required:
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.
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 325: ConSol CM Admin Tool - DWH configuration: Tab Notification
The following fields are available:
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 326: 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.
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.
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!
The DWH Update can also be started via command line or script. In this case you have to use a tool which can access the ConSol CM MBeans via command line, e.g. Twiddle for JBoss. The MBean to use is consol.cmas.global.dwh.synchronizationService. The command (method) is update.
The following command line shows an example command with Twiddle.
$JBOSS_HOME/bin/twiddle.sh invoke consol.cmas:type=admin,topic=global,name=dwh.synchronizationService update
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 327: Indication of active DWH tasks in the Admin Tool
In the list of DWH tasks, you will find entries (one entry per task) if ...
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.
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 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.
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.
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:
The mode is set using the system property cmas-dwh-server, communication.channel. Possible values are:
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
or
The CMRF server reads the messages from the JMS queues and writes the data into the DWH.
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.
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!
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 328: ConSol CM Admin Tool - Localized values of projects
Figure 329: 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.
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.