Data Warehouse
##############

Rexpondo BI DWH project was designed and created with MySQL technology. The name assigned to this database is **rexpondo_dwh**.
 
Tables data warehouse are the following: 
 
*   :ref:`DIM_ARTICLE` 
*   :ref:`DIM_COMMUNICATION_CHANNEL`
*   :ref:`DIM_CUSTOMER`
*   :ref:`DIM_CUSTOMER_USER`
*   :ref:`DIM_DATE`
*   :ref:`DIM_PARAMETER`
*   :ref:`DIM_PRIORITY`
*   :ref:`DIM_QUEUE`
*   :ref:`DIM_SERVICE`
*   :ref:`DIM_SLA`
*   :ref:`DIM_STATE`
*   :ref:`DIM_TICKET`
*   :ref:`DIM_TICKET_LOCK_TYPE`
*   :ref:`DIM_TICKET_TYPE`
*   :ref:`DIM_TICKET_TYPE_HISTORY`
*   :ref:`DIM_USER`
*   :ref:`DIM_USER_GROUP`
*   :ref:`FACT_DYNAMIC_FIELD`
*   :ref:`FACT_TICKET`
*   :ref:`FACT_TICKET_WORKING`
*   :ref:`UTI_CITIES`
*   :ref:`UTI_DATES_UPDATE`

Rexpondo Business Intelligence DWH project entity–relationship models are the following (grouped by facts tables): 
 
**FACT_TICKET - ER MODEL**
 
.. image :: images/MER001.png
    :class: with-shadow

**FACT_TICKET_WORKING - ER MODEL**
 
.. image :: images/MER002.png
    :class: with-shadow

**FACT_DYNAMIC_FIELD - ER MODEL**
 
.. image :: images/MER003.png
    :class: with-shadow

 
Below the description of each single table into data warehouse.

.. _DIM_ARTICLE:

DIM_ARTICLE
~~~~~~~~~~~
 
**DESCRIPTION:** Information about articles assigned to each individual ticket.  
 
**BI ELEMENT:** DIMENSION.

.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55

    "idArticle","Bigint","20","No","Primary key"
    "articleSubject","Text","","Yes","Message subject"
    "articleBody","Mediumtext","","Yes","Message body"
 
.. _DIM_COMMUNICATION_CHANNEL:

DIM_COMMUNICATION_CHANNEL
~~~~~~~~~~~~~~~~~~~~~~~~~
  
**DESCRIPTION:** Information about communications channels. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55    

    "idCommunicationChannel","Biigint","20","No","Primary key"
    "nameCommunicationChannel","Varchar","200","No","Communication channel name"
 
.. _DIM_CUSTOMER:

DIM_CUSTOMER
~~~~~~~~~~~~
  
**DESCRIPTION:** Information about customer companies. 
  
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idCustomer","Varchar","150","No","Primary key"
    "nameCustomer","Varchar","200","No","Customer company name"
    "streetCustomer","Varchar","200","Yes","Customer company address"
    "zipCustomer","Varchar","200","Yes","Customer company postal code"
    "cityCustomer","Varchar","200","Yes","Customer company city"
    "countryCustomer","Varchar","200","Yes","Customer company country"
    "urlCustomer","Varchar","200","Yes","Customer company URL web site"
    "latitudineCustomer","Decimal","12,7","Yes","Customer company latitude"
    "longitudineCustomer","Decimal","12,7","Yes","Customer company longitude"
 
.. _DIM_CUSTOMER_USER:

DIM_CUSTOMER_USER
~~~~~~~~~~~~~~~~~
  
**DESCRIPTION:** Information about user’s customer. 
  
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idCustomerUser","Int","11","No","Primary key" 
    "loginCustomerUser","Varchar","191","No","No User customer login" 
    "nameCustomerUser","Varchar","210","No","User customer name" 
    "customerUserId","Varchar","150","No","Customer ID to which the user customer belongs" 
 
.. _DIM_DATE:
 
DIM_DATE
~~~~~~~~
  
**DESCRIPTION:** Dates and extract of them used to filter information. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idDate","Int","11","No","Primary key" 
    "date","Date","","No","Date" 
    "day","Smallint","2","No","Day number into the month" 
    "month","Varchar","10","No","Month name" 
    "year","Smallint","4","No","Year" 
    "dayWeek","Varchar","10","No","Name day" 
    "dayYear","Smallint","2","No","Day number into the year" 
    "weekYear","Smallint","2","No","Week number into the year" 
    "monthYear","Smallint","2","No","Month number into the year" 
    "numMinTicketsMonth","Int","2","Yes","Value returned from the table dim_parameter" 
    "numMaxTicketsMonth","Int","11","Yes","Value returned from the table dim_parameter" 
    "numMinEmailsMonth","Int","11","Yes","Value returned from the table dim_parameter"  
    "numMaxEmailsMonth","Int","11","Yes","Value returned from the table dim_parameter"  
    "numMinTicketsYear","Int","11","Yes","Value returned from the table dim_parameter"  
    "numMaxTicketsYear","Int","11","Yes","Value returned from the table dim_parameter"  
    "numMinEmailsYear","Int","11","Yes","Value returned from the table dim_parameter"  
    "numMaxEmailsYear","Int","11","Yes","Value returned from the table dim_parameter"  
   
.. _DIM_PARAMETER:

DIM_PARAMETER
~~~~~~~~~~~~~
  
**DESCRIPTION:** Parameters values used and configurated by the user. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idParameter","Smallint","6","No","Primary key" 
    "numMinTicketsMonth","Int","11","No","Minimum target number of tickets created by month" 
    "numMaxTicketsMonth","Int","11","No","Maximum target number of tickets created by month" 
    "numMinEmailsMonth","Int","11","No","Minimum target number of emails handled by month" 
    "numMaxEmailsMonth","Int","11","No","Maximum target number of emails handled by month" 
    "numMinTicketsYear","Int","11","No","Minimum target number of tickets created by year" 
    "numMaxTicketsYear","Int","11","No","Maximum target number of tickets created by year" 
    "numMinEmailsYear","Int","11","No","Minimum target number of emails managed by year" 
    "numMaxEmailsYear","Int","11","No","Maximum target number of emails managed by year"  

.. _DIM_PRIORITY:
 
DIM_PRIORITY
~~~~~~~~~~~~
  
**DESCRIPTION:** Information about priority’s ticket.  
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idPriority","Smallint","6","No","Primary key" 
    "namePriority","Varchar","200","No","Priority name" 
  
.. _DIM_QUEUE:
 
DIM_QUEUE
~~~~~~~~~
  
**DESCRIPTION:** Information about queues. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idQueue","Int","11","No","Primary key" 
    "nameQueue","Varchar","200","No","Queue name"  
    "nameGroup","Varchar","200","No","Group name to which the queue belongs" 
 
.. _DIM_SERVICE:
 
DIM_SERVICE
~~~~~~~~~~~
  
**DESCRIPTION:** Information about services. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idService","Int","11","No","Primary key" 
    "nameService","Varchar","200","No","Service name"  
    "criticalityService","Varchar","200","No","Service’s criticality, for example, low, very low, normal, etc" 
 
.. _DIM_SLA:
 
DIM_SLA
~~~~~~~
  
**DESCRIPTION:** Information about SLA (service level agreements). 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idSla","Int","11","No","Primary key" 
    "nameSla","Varchar","200","No","SLA name" 
    "firstResponseTimeSla","Int","11","Yes","Time enabled to read a new ticket belonging to the SLA" 
    "firstResponseNotifySla","Smallint","6","Yes","Time enabled to notify the start of management of a new ticket belonging to the SLA" 
    "updateTimeSla","Int","11","Yes","Time enabled to set a new ticket belonging to the SLA as open" 
    "updateNotifySla","Smallint","6","Yes","Time enabled to notify when a new ticket is set as open belonging to the SLA" 
    "solutionTimeSla","Int","11","Yes","Time enabled to resolve a ticket belonging to the SLA" 
    "solutionNotifySla","Smallint","6","Yes","Time enabled to notify the solution of a ticket belonging to the SLA" 
    "minTimeBetIncidentsSla","Int","11","Yes","Time from target" 
    "availableTimeSla","Int","11","Yes","Time from data target" 
  
.. _DIM_STATE:
 
DIM_STATE
~~~~~~~~~
  
**DESCRIPTION:** Information about state’s ticket. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idState","Smallint","6","No","Primary key" 
    "nameState","Varchar","200","No","State name" 
    "idStateType","Smallint","6","No","State type ID to which the state belongs" 
    "nameStateType","Varchar","200","No","State type name" 
 
.. _DIM_TICKET:
 
DIM_TICKET
~~~~~~~~~~
  
**DESCRIPTION:** Information about tickets. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idTicket","Bigint","20","No","Primary key" 
    "codeTicket","Varchar","50","No","Ticket code, for example, 2015022557000039" 
    "nameTicket","Varchar","255","No","Ticket name or tittle" 
    "idQueue","Int","11","No","Queue ID to which the ticket belongs" 
    "idSla","Int","11","Yes","SLA ID to which the ticket belongs" 
    "FirstResponseTime","Int","11","Yes","Time, measured in seconds, used for the first ticket response" 
    "FirstResponseAvailableTime","Int","11","Yes","Time available in seconds, to be used for the first ticket response" 
    "UpdateTime","Int","11","Yes","Time, measured in seconds, used to indicate the time of the last response to the customer" 
    "UpdateAvailableTime","Int","11","Yes","Time available in seconds, to be used to respond to the customer" 
    "SolutionTime","Int","11","Yes","Time, measured in seconds, used to resolve the ticket" 
    "SolutionAvailableTime","Int","11","Yes","Time available in seconds, to be used to resolve the ticket" 
    "TimeUnit","Decimal","18,2","Yes","Sum of times indicated by users (articles)" 
    "EscalationDateTicket","Datetime","","Yes","Escalation date of the ticket" 
    "EscalationUpdateDateTicket","Datetime","","Yes","Escalation date of upgrade of the ticket" 
    "EscalationResponseDateTicket","Datetime","","Yes","Escalation date of response of the ticket" 
    "EscalationSolutonDateTicket","Datetime","","Yes","Escalation date of solution of the ticket" 
 
.. _DIM_TICKET_LOCK_TYPE:
 
DIM_TICKET_LOCK_TYPE
~~~~~~~~~~~~~~~~~~~~
  
**DESCRIPTION:** Information about lock type’s ticket. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idTicketLockType","Smallint","6","No","Primary key" 
    "nameTicketLockType","Varchar","200","No","Lock type ticket name" 
 
.. _DIM_TICKET_TYPE:
 
DIM_TICKET_TYPE
~~~~~~~~~~~~~~~
  
**DESCRIPTION:** Information about type’s ticket. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idTicketType","Smallint","6","No","Primary key" 
    "nameTicketType","Varchar","200","No","Type ticket name" 
 
.. _DIM_TICKET_TYPE_HISTORY:
 
DIM_TICKET_TYPE_HISTORY
~~~~~~~~~~~~~~~~~~~~~~~
  
**DESCRIPTION:** Information about history type’s ticket. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idTicketTypeHistory","Smallint","6","No","Primary key" 
    "nameTicketTypeHistory","Varchar","200","No","History type ticket name" 
 
.. _DIM_USER:
 
DIM_USER
~~~~~~~~
  
**DESCRIPTION:** Information about agents. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idUser","Int","11","No","Primary key" 
    "nameUser","Varchar","210","No","Agent name" 
 
.. _DIM_USER_GROUP:
 
DIM_USER_GROUP
~~~~~~~~~~~~~~
  
**DESCRIPTION:** Information about relation between group’s agent and agents. 
 
**BI ELEMENT:** DIMENSION.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idUser","Int","11","No","Primary key" 
    "idGroup","Int","11","No","Primary key" 
    "nameGroup","Varchar","200","No","Group name" 
 
.. _FACT_DYNAMIC_FIELD:
 
FACT_DYNAMIC_FIELD
~~~~~~~~~~~~~~~~~~
  
**DESCRIPTION:** Transactional information about dynamic fields. 
 
**BI ELEMENT:** FACT.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idFactDynamicField","Bigint","20","No","Primary key" 
    "idDynamicField","Int","11","No","Dynamic field ID." 
    "name","Varchar","191","No","Dynamic field name" 
    "label","Varchar","200","No","Dynamic field label" 
    "typeField","Varchar","200","No","Dynamic field type, for example, text, datetime, etc" 
    "idTicket","Bigint","20","Yes","Ticket ID to which the dynamic field belongs" 
    "idArticle","Bigint","20","Yes","Article ID to which the dynamic field belongs" 
    "valueText","Text","","Yes","Dynamic field text value" 
    "valueDate","Datetime","","Yes","Dynamic field date value" 
    "valueInt","Bigint","20","Yes","Dynamic field number value" 
 
.. _FACT_TICKET:

FACT_TICKET
~~~~~~~~~~~
  
**DESCRIPTION:** Transactional or history information or about tickets. 
 
**BI ELEMENT:** FACT.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idTicketFact","Bigint","20","No","Primary key" 
    "idTicketHistory","Bigint","20","Yes","History ticket ID" 
    "idTicket","Bigint","20","Yes","Ticket ID associated to the history ticket" 
    "idCommunicationChannel","Bigint","20","Yes","Communication channel ID associated to the history ticket" 
    "idArticle","Bigint","20","Yes","Article ID associated to the history ticket" 
    "creationDate","Datetime","","Yes","Creation date of the history ticket" 
    "idQueue","Int","11","Yes","Queue ID associated to the history ticket" 
    "idPriority","Smallint","6","Yes","Priority ID associated to the history ticket" 
    "idState","Smallint","6","Yes","State ticket ID associated to the history ticket" 
    "idTicketType","Smallint","6","Yes","Type ticket ID associated to the history ticket" 
    "idTicketTypeHistory","Smallint","6","Yes","History type ticket ID associated to the history ticket" 
    "idTicketLockType","Smallint","6","Yes","Lock type ticket ID associated to the history ticket" 
    "idService","Int","11","Yes","Service ID associated to the history ticket" 
    "idSla","Int","11","Yes","SLA ID associated to the history ticket" 
    "idDate","Int","11","Yes","Date ID associated to the history ticket" 
    "idCustomer","Varchar","150","Yes","Customer company ID associated to the history ticket" 
    "idCustomerUser","Int","11","Yes","Customer’s user ID associated to the history ticket" 
    "idUser","Int","11","Yes","Agent ID associated to the history ticket" 
    "idUserResponsible","Int","11","Yes","Responsible agent ID associated to the history ticket" 
    "numberOperation","Smallint","2","Yes","Operation’s number associated to the history ticket. Default value: 1" 
    "emailOperation","Smallint","1","Yes","Email’s number associated to the history ticket. Default value: 1" 
    "isLastState","Tinyint","1","Yes","Indicates whether the status of this history type is the latest status of the ticket. Possible values are 0 or 1. Default value: 0" 
 
.. _FACT_TICKET_WORKING:

FACT_TICKET_WORKING
~~~~~~~~~~~~~~~~~~~
 
**DESCRIPTION:** Time’s information about one single event executed to the tickets. 
 
**BI ELEMENT:** FACT.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idTicketWorking","Bigint","20","No","Primary key" 
    "idTicket","Bigint","20","No","Ticket ID" 
    "eventType","Varchar","100","No","Event type name" 
    "creationDate","Datetime","","No","Event creation date" 
    "idState","Smallint","6","Yes","State ID of the ticket" 
    "idPriority","Smallint","6","Yes","Priority ID of the ticket" 
    "idTicketLockType","Smallint","6","Yes","Lock type ID of the ticket" 
    "idService","Int","11","Yes","Service ID of the ticket" 
    "idQueue","Int","11","Yes","Queue ID of the ticket" 
    "idSla","Int","11","Yes","SLA ID of the ticket" 
    "idUser","Int","11","Yes","Agent ID of the ticket" 
    "idUserResponsible","Int","11","Yes","Responsible agent ID of the ticket" 
    "idCustomer","Varchar","150","Yes","Customer company ID of the ticket" 
    "idDate","Int","11","Yes","Date ID of the ticket" 
    "workingTime","Int","11","Yes","Time, in units of seconds, that the ticket stayed in the event. Default 0. Rexpondo Calendars are used for calculations" 
    "workingTimeDate","Int","11","Yes","Time, in units of seconds, that the ticket stayed in the event. Default 0. Gregorian Calendars are used for calculations" 
 
.. _UTI_CITIES:
 
UTI_CITIES
~~~~~~~~~~
  
**DESCRIPTION:** Names of the cities with its coordinates (latitude and longitude).
 
**BI ELEMENT:** UTIL.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "idCity","Int","11","No","Primary key" 
    "nameCity","Varchar","200","No","English city name" 
    "countryCity","Varchar","100","No","English country name" 
    "latitudineCity","Decimal","12,7","Yes","City latitude" 
    "longitudineCity","Decimal","12,7","Yes","City longitude" 
    "nomeCitta","Varchar","200","Yes","Italian city name" 
    "nomePaese","Varchar","200","Yes","Italian country name" 
 
.. _UTI_DATES_UPDATE:

UTI_DATES_UPDATE
~~~~~~~~~~~~~~~~
 
**DESCRIPTION:** Last datetimes of the ETL procedures execution.
 
**BI ELEMENT:** UTIL.
 
.. csv-table ::  
   :header: "COLUMN", "TYPE", "LENGTH", "NULL", "NOTES"
   :widths: 15, 10, 10, 10, 55        

    "dim_date_next_update","Datetime","","No","Last datetime of the ETL procedures execution associated to the dimension’s tables" 
    "fact_date_next_update","Datetime","","No","Last datetime of the ETL procedures execution associated to the fact’s tables" 
    "idNextUpdate","Smallint","6","No","Field not used" 
 
**Stored procedures** inside data warehouse are:

*   ETL_DIM_ARTICLE 
*   ETL_DIM_COMMUNICATION_CHANNEL 
*   ETL_DIM_CUSTOMER  
*   ETL_DIM_CUSTOMER_USER 
*   ETL_DIM_DATE 
*   ETL_DIM_PRIORITY 
*   ETL_DIM_QUEUE 
*   ETL_DIM_SERVICE 
*   ETL_DIM_SLA 
*   ETL_DIM_STATE 
*   ETL_DIM_TICKET 
*   ETL_DIM_TICKET_LOCK_TYPE 
*   ETL_DIM_TICKET_TYPE 
*   ETL_DIM_TICKET_TYPE_HISTORY 
*   ETL_DIM_USER 
*   ETL_DIM_USER_GROUP 
*   ETL_FACT_DYNAMIC_FIELD 
*   ETL_FACT_TICKET 
*   ETL_FACT_TICKET_WORKING 
*   ETL_LOAD_BI_REXPONDO 
*   ETL_LOAD_DETAILS 
*   ETL_LOAD_DIM 
*   ETL_LOAD_FACT 
 
.. note ::  
    Stored procedures inside the data warehouse were described previously in ETL Rexpondo DWH chapter.