ETL Rexpondo DWH

ETL processes of Rexpondo Business Intelligence project are constituted as a set of stored procedures into the data warehouse.

Stored procedures used to load facts tables are:

• ETL_FACT_DYNAMIC_FIELD

• ETL_FACT_TICKET

• ETL_FACT_TICKET_WORKING (*)

(*) To populate fact_ticket_working table inside ETL_FACT_TICKET_WORKING stored procedure, will be considered only next events:

• TICKETSTATEUPDATE

• TICKETPRIORITYUPDATE

• TICKETLOCKUPDATE

• TICKETSERVICEUPDATE

• TICKETQUEUEUPDATE

• TICKETSLAUPDATE

• TICKETOWNERUPDATE

• TICKETRESPONSIBLEUPDATE

• TICKETCUSTOMERUPDATE

Graphic representation of the stored procedures used to populate facts tables:

Stored procedures used to load dimensions tables 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

Graphic representation of the stored procedures used to populate dimensions tables.

Procedures to populate dimension tables are invoked by a single stored procedure called ETL_LOAD_DIM.

This procedure is performed into a database transaction. Below graphic diagram.

Procedures to populate facts tables are invoked by a single stored procedure called ETL_LOAD_FACT.

This procedure is performed into a database transaction. Below graphic diagram.

Both procedures (ETL_LOAD_DIM and ETL_LOAD_FACT) are invoked by a single stored procedure called ETL_LOAD_BI_REXPONDO.

The signature of this procedure is:

Procedure name: ETL_LOAD_BI_REXPONDO.

Input Parameters:

1. piDimChunk. Integer type. Not obligatory. Description: Indicates the number of minutes to be considered for reading the information from the target database starting from the last date of extraction, transformation and loading into the data warehouse. If a value is not indicated for this parameter, the default value is 1440 (one day expressed in minutes). If 0 value is indicated, all the information present into rexpondo database will be loaded starting from the last date of ETL execution process.

Output Parameters:

1. poErrorNumber. Integer type. Description: Return error number detected by the MySQL engine. If no error is detected, the return value is 0.

2. poErrorDescription. Text type. Description: Return error description detected by the MySQL engine. If no error is detected, the return value is an empty text.

Note

ETL_LOAD_BI_REXPONDO stored procedure is the one that must be scheduled inside MySQL server depending on the project requirements..

Below graphic diagram.