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:


.. image :: images/ETL_FACT_DYNAMIC_FIELD.jpg
    :class: with-shadow


.. image :: images/ETL_FACT_TICKET.jpg
    :class: with-shadow
 

.. image :: images/ETL_FACT_TICKET_WORKING.jpg
    :class: with-shadow
 

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.


.. image :: images/ETL_DIM_ARTICLE.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_COMMUNICATION_CHANNEL.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_CUSTOMER.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_CUSTOMER_USER.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_DATE.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_PRIORITY.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_QUEUE.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_SERVICE.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_SLA.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_STATE.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_TICKET.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_TICKET_LOCK_TYPE.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_TICKET_TYPE.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_TICKET_TYPE_HISTORY.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_USER.jpg
    :class: with-shadow


.. image :: images/ETL_DIM_USER_GROUP.jpg
    :class: with-shadow


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.


.. image :: images/ETL_LOAD_DIM.jpg
    :class: with-shadow
        

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.


.. image :: images/ETL_LOAD_FACT.jpg
    :class: with-shadow


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:**

#.  **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:**

#.  **poErrorNumber.** Integer type. Description: Return error number detected by the MySQL engine. If no error is detected, the return value is 0.
#.  **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.


.. image :: images/ETL_LOAD_BI_REXPONDO.jpg
    :class: with-shadow