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:
Rexpondo Business Intelligence DWH project entity–relationship models are the following (grouped by facts tables):
FACT_TICKET - ER MODEL
FACT_TICKET_WORKING - ER MODEL
FACT_DYNAMIC_FIELD - ER MODEL
Below the description of each single table into data warehouse.
DIM_ARTICLE
DESCRIPTION: Information about articles assigned to each individual ticket.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idArticle |
Bigint |
20 |
No |
Primary key |
articleSubject |
Text |
Yes |
Message subject |
|
articleBody |
Mediumtext |
Yes |
Message body |
DIM_COMMUNICATION_CHANNEL
DESCRIPTION: Information about communications channels.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idCommunicationChannel |
Biigint |
20 |
No |
Primary key |
nameCommunicationChannel |
Varchar |
200 |
No |
Communication channel name |
DIM_CUSTOMER
DESCRIPTION: Information about customer companies.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Information about user’s customer.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Dates and extract of them used to filter information.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Parameters values used and configurated by the user.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Information about priority’s ticket.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idPriority |
Smallint |
6 |
No |
Primary key |
namePriority |
Varchar |
200 |
No |
Priority name |
DIM_QUEUE
DESCRIPTION: Information about queues.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Information about services.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Information about SLA (service level agreements).
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Information about state’s ticket.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Information about tickets.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Information about lock type’s ticket.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idTicketLockType |
Smallint |
6 |
No |
Primary key |
nameTicketLockType |
Varchar |
200 |
No |
Lock type ticket name |
DIM_TICKET_TYPE
DESCRIPTION: Information about type’s ticket.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idTicketType |
Smallint |
6 |
No |
Primary key |
nameTicketType |
Varchar |
200 |
No |
Type ticket name |
DIM_TICKET_TYPE_HISTORY
DESCRIPTION: Information about history type’s ticket.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idTicketTypeHistory |
Smallint |
6 |
No |
Primary key |
nameTicketTypeHistory |
Varchar |
200 |
No |
History type ticket name |
DIM_USER
DESCRIPTION: Information about agents.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idUser |
Int |
11 |
No |
Primary key |
nameUser |
Varchar |
210 |
No |
Agent name |
DIM_USER_GROUP
DESCRIPTION: Information about relation between group’s agent and agents.
BI ELEMENT: DIMENSION.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
idUser |
Int |
11 |
No |
Primary key |
idGroup |
Int |
11 |
No |
Primary key |
nameGroup |
Varchar |
200 |
No |
Group name |
FACT_DYNAMIC_FIELD
DESCRIPTION: Transactional information about dynamic fields.
BI ELEMENT: FACT.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Transactional or history information or about tickets.
BI ELEMENT: FACT.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Time’s information about one single event executed to the tickets.
BI ELEMENT: FACT.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Names of the cities with its coordinates (latitude and longitude).
BI ELEMENT: UTIL.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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
DESCRIPTION: Last datetimes of the ETL procedures execution.
BI ELEMENT: UTIL.
COLUMN |
TYPE |
LENGTH |
NULL |
NOTES |
|---|---|---|---|---|
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.