Database

The DWH database has an optimized data model which is tailored specifically for reporting requirements. You can execute complex queries on the DWH without impairing the performance of the production database. The data which is relevant for reporting is transferred automatically from the production database to the data warehouse database.

Structure of the DWH database

The DWH contains different kinds of tables, which can be distinguished by their prefixes. It is not allowed to modify the structure of the DWH tables.

Internal tables

The internal tables are created when the CMRF is started for the first time. The names of these tables start with the prefix int_ or hlp_*. They contain internal data and are used to control the data transfer from the ConSol CM database to the DWH database and the email notifications which can be sent by CMRF. The usage of the internal tables for data transfer is described in detail in Synchronisation.

Helper tables

There are several helper tables which hold entities which are not used directly in reports but are needed to generate the reports.

• hlp_calendar_*: Calendars defined on the Calendars page of the Web Admin Suite.
• hlp_cube_*: Cubes defined on the Cubes page of the Web Admin Suite.
• hlp_queue_client: Maps customer groups to queues.

Static data tables

The static tables are common to all DWH databases. They contain entities which are present regardless of the individual configuration of ConSol CM. The static data tables are created during the first data transfer.

The names of these tables start with the prefix dim_* or fact_* followed by the name of the entity type. Tables whose names end with *_chg or *_log include the history of the objects. The history includes the current and previous values and the date and time of the modification.

Dynamic data tables

The dynamic tables depend on the implemented data models. They are created for the data fields which have a setting for transfer to the DWH. The dynamic data tables are created during the first data transfer. If a configuration change requires additional data to be transferred to the DWH, the respective table is created during an update operation.

The names of these tables start with the prefix dim_* or fact_* followed by a letter for the type of object. Tables whose names end with *_chg include the history of data fields.

Custom tables

You can add custom tables to extend the DWH database. This can be useful for example if the reports should contain data from other applications. The names of the custom tables must not begin with FACT, fact, DIM, dim, INT, int, HLP, or hlp, as these are reserved for the DWH tables from ConSol CM.

Content of the DWH database

Static tables

By default, the following data is transferred to the DWH database and saved in static database tables.

Area	Configuration data	Runtime data
Cases	dim_content_entry_class: text classes dim_engineer: users dim_project: projects dim_queue: queues dim_scope: scopes dim_ticket_function: user functions dim_workflow: workflows	fact_content_entry: information about emails, comments and attachments in the case history fact_content_entry_class_chg: history of text classes set for emails, comments and attachments fact_ticket: cases fact_ticket_activity_chg: history of executed workflow activities fact_ticket_contact: contacts of cases fact_ticket_engineer_chg: history of assignees fact_ticket_engineer_user: assignees and participants of cases fact_ticket_log: history of changes to cases fact_ticket_queue_chg: history of changes of the cases' queues fact_ticket_relation: case-case relations fact_ticket_time: duration of case activities fact_time_booking: time bookings
Contacts	dim_client_group: customer groups dim_contact_role: contact roles dim_customer_definition: contact model dim_unit_relation_definition: contact-contact relations	dim_contact: contacts dim_unit_relation: contact-contact relations fact_unit_log: history of changes to contacts
Resources	dim_resource_group: resource categories dim_resource_relation_def: resource relations dim_resource_type: resource types	dim_resource: resources dim_resource_resource_relation: resource-resource relations dim_resource_ticket_relation: resource-case relations dim_resource_unit_relation: resource-contact relations fact_resource_log: history of changes to resources
General	dim_action: available action types for cases, contacts and resources dim_activity: workflow activities dim_date: dates dim_enum_group: sorted lists dim_field_definition: case, contact and resource fields dim_group_definition: case, contact and resource field groups dim_localized_property: localizations and their history dim_mla: hierarchical lists dim_supported_locale: locales dim_time: times

Dynamic tables

Dynamic tables are created for all defined sorted and hierarchical lists. In addition, you can decide which case, contact and resource fields should be transferred by setting Transfer to DWH to true for field groups or individual fields. This is done on the Case fields, Contact fields and Resource fields pages of the Web Admin Suite.

The dynamic tables follow the following naming convention:

• dim_c_*: Contact data fields. There is one table for each field group. The table contains the defined fields and their values in the contacts. There are separate tables for list fields.
• dim_e_*: Sorted lists. There is one table for each sorted list used. The table contains the defined list values and their localizations.
• dim_m_*: Hierarchical lists. There is one table for each hierarchical list. The table contains the defined values and their paths.
• dim_r_*: Resource data fields. There is one table for each field group. The table contains the defined fields and their values in the resources. There are separate tables for list fields.
• fact_l_*: List fields. There is one table for each list field in a case field group. There contains the list rows with the field values in the different cases.
• fact_t_*: Case data fields. There is one table for each field group. The table contains the defined fields and their values in the cases.

By default, a history table whose name ends with *_chg is created for each data field. It includes the current and previous values and the date and time of the modification. If no history should be kept for the field in the DWH, you need to set Field value omitted from DWH history to True for the field or field group.

Deactivating a data field or field group does not remove it from the data warehouse. Therefore, old reports will still work. If you want to remove a data field from the DWH database you need to set Transfer to DWH to False or delete the data field.

Field and field group names

The maximum length of table and column names is limited to 30 and 20 characters respectively. Longer names are cut off and numbers are used to avoid duplicates.

• Use shorter names and / or put the significant part of the name at the beginning to avoid ambiguities.
• Be aware that table and column names which are distinguished using numbers can refer to different fields in different systems as the numbering depends on the order of creation, which can be different if you e.g. import a scene to another system.

The table and column names are determined once when the information about the field is transferred to the DWH for the first time. It is only modified if the DWH is rebuilt from scratch afterwards.

• Switch the DWH from live mode to admin mode before copying fields to avoid creating fields in the DWH which are renamed in the Web Admin Suite later.

IDs

The tables which contain the main data of cases (fact_ticket), contacts (dim_contact) and resources (dim_resource) have several columns holding IDs:

• ticket_id / contact_id / resource_id: This is the internal ID used for the case, contact or resource in the DWH database.
• ticket_uid / contact_uid /resource_uid: This is the transfer key of the case, contact or resource from the ConSol CM database.
• core_id: This is the ID of the contact or resource in the ConSol CM database. Use this ID if you want to include direct links to contacts or resources in the Web Client in your reports.

Dates and times

Dates are saved in the table dim_date and times are saved in the table dim_time. The dates and times are referenced from other tables in columns ending with *_date_id and *_time_id. This allows you to aggregate times easily using joins.

Time periods, e.g. the duration between two activities or escalation times, are saved in columns ending with *_brutto and *_netto. The brutto column contains the duration. The netto column the duration taking into account the business calendar. They correspond to the previous activity.

In history tables, the moment of the change is saved in the columns starting with insert_*.

Localizations

Localizations are saved in the table dim_localized_property. It includes both localizations defined using labels and localizations of entities in the defined data models and workflows.

In addition, some tables contain columns for localized names by default. This is the case for values of sorted lists and hierarchical lists, i.e. tables starting with dim_e_* and dim_m_*.

You can add localization columns to additional tables by selecting one or several languages in the Transfer and Update dialogs in the DWH administration of the Web Admin Suite. This applies to the following tables:

• dim_activity
• dim_client_group
• dim_contact_role
• dim_customer_definition
• dim_enum_group
• dim_field_definition
• dim_group_definition
• dim_mla
• dim_project
• dim_queue
• dim_resource_group
• dim_resource_relation_def
• dim_resource_type
• dim_scope
• dim_ticket_function
• dim_unit_relation_definition

:::warn Performance warning Transferring localized names for additional tables to the DWH may have a significant performance impact. Only select languages here if they are really needed for reporting. :::

Text fields on MySQL

When using a MySQL database, the data transfer can fail due to the row size limit if a field group contains several text fields to be transferred. Therefore, the values of data fields of the type Text (string) are truncated, so that only the first 512 characters of the field values are written to the DWH database. You can change the number of characters to be transferred by adding the property cmrf.mysql.truncate.string with the desired value to the start command of the CMRF application.

Update of the DWH database

If the DWH database schema needs to be modified in context of a ConSol CM update, all required changes are performed automatically when deploying the new version. If you have created additional indexes on your database, please check that they still exist after performing an update.