I am structuring a new DataWarehouse that will eventually be consumed on a BI platform.
I have already created some dimensions for my database, one of them was Dates , in which I break the date in several formats for future manipulation. I am currently creating the Hours dimension which for now is in the following format:
HORA_COMPLETA | HORA | MINUTO | SEGUNDO | PERIODO |TURNO
10:01:25 | 10 | 01 | 25 | Manhã | 1
17:43:32 | 17 | 43 | 32 | Tarde | 1
23:12:13 | 23 | 12 | 13 | Noite | 2
03:07:46 | 03 | 07 | 46 | Madrugada | 2
.
.
.
- There are 2 possible shifts (6:00 to 6:00 'shift 1' and 6:00 to 6:00 'shift 2')
The problem I'm having is the following:
In the future I will want to know the results of the Shift 2 team per day. The point is that this shift (2) goes through two days (the night of 7/05 and the dawn of 8/05 for example), in this case the result of shift 2 would be the junction of these two periods that are on different days .
The structure of the date table is briefly as follows:
DATA_COMPLETA | DIA | MES | ANO
07/05/2018 | 07 | 05 | 2018
08/05/2018 | 08 | 05 | 2018
09/05/2018 | 09 | 05 | 2018
.
.
.
Is there any way to put this in the table to fit this situation?
* Just to complement, these two tables do not directly access other tables that are using that data.