Estructura de la Dimension Tiempo y Script de carga con Oracle SQL

Con este script de Oracle SQL se crea una tabla DIM_TIEMPO y se rellena con los valores comprendidos entre las fechas que se indiquen en las variables FechaDesde y FechaHasta. Puede ser muy útil para la creación de la tabla de tiempo de cualquier Data Warehouse.

Esta es la versión para una base de datos Oracle, con Oracle SQL, que se suma a las que han creado anteriormente il_masacratore y Dario Bernabeu para Microsoft SQL Server y Oracle MySQL en sus respectivos blogs:

Estructura de la Dimensión Tiempo y Script de carga para Ms SQL Server

Estructura de la Dimensión Tiempo y Procedure de carga para MySQL

----------------------------------------------------
-- SQL de Creación de la tabla DIM_TIEMPO  --
----------------------------------------------------
drop table DIM_TIEMPO;
create table DIM_TIEMPO
(
    FechaSK number not null,
    Fecha date not null PRIMARY KEY,
    Año number not null,
    Trimestre number not null,
    Mes number not null,
    Semana number not null,
    Dia number not null,
    DiaSemana number not null,
    NTrimestre varchar2(7) not null,
    NMes varchar2(15) not null,
    NMes3L varchar2(3) not null,
    NSemana varchar2(10) not null,
    NDia varchar2(6) not null,
    NDiaSemana varchar2(10) not null
);
------------------------------------------------------------
-- Script Oracle SQL de carga de los datos entre fechas --
------------------------------------------------------------
DECLARE
   FechaDesde date;
   FechaHasta date;
BEGIN
    --Borrar datos actuales, si fuese necesario
    --TRUNCATE TABLE DIM_TIEMPO
    --Rango de fechas a generar: del 01/01/2006 al 31/12/Año actual+2
    FechaDesde := TO_DATE('20060101','YYYYMMDD');
    FechaHasta := TO_DATE((TO_CHAR(sysdate,'YYYY')+2 || '1231'),'YYYYMMDD'); 
    WHILE FechaDesde <= FechaHasta LOOP
      INSERT INTO DIM_TIEMPO
      (
        FechaSK,
        Fecha,
        Año,
        Trimestre,
        Mes,
        Semana,
        Dia,
        DiaSemana,
        NTrimestre,
        NMes,
        NMes3L,
        NSemana,
        NDia,
        NDiaSemana
      )
      VALUES
      (
        to_char(FechaDesde,'YYYYMMDD'),                      
        FechaDesde,
        to_char(FechaDesde,'YYYY'),
        to_char(FechaDesde, 'Q'),
        to_char(FechaDesde,'MM'),
        to_char(FechaDesde,'WW'),
        to_char(FechaDesde,'DD'),
        to_char(FechaDesde,'D'),
        'T'||to_char(FechaDesde, 'Q')||'/'||to_char(FechaDesde,'YY'),
        to_char(FechaDesde,'MONTH'),
        to_char(FechaDesde,'MON'),
        'Sem '||to_char(FechaDesde,'WW')||'/'||to_char(FechaDesde,'YY'),
        to_char(FechaDesde,'DD MON'),
        to_char(FechaDesde,'DAY')
      ); 
      --Incremento del bucle
      FechaDesde := FechaDesde + 1;
    END LOOP;
END;

 

Como cada uno se adaptará el formato de las fechas al que más le convenga, aprovecho para adjuntar esta tabla de ayuda obtenida de Oradev. Contiene descripciones de la sintaxis que se puede utilizar en las máscaras de formato de fechas de las funciones TO_CHAR y TO_DATE de Oracle:

Format mask Description
CC Century
SCC Century BC prefixed with -
YYYY Year with 4 numbers
SYYY Year BC prefixed with -
IYYY ISO Year with 4 numbers
YY Year with 2 numbers
RR Year with 2 numbers with Y2k compatibility
YEAR Year in characters
SYEAR Year in characters, BC prefixed with -
BC BC/AD Indicator *
Q Quarter in numbers (1,2,3,4)
MM Month of year 01, 02...12
MONTH Month in characters (i.e. January)
MON JAN, FEB
WW Weeknumber (i.e. 1)
W Weeknumber of the month (i.e. 5)
IW Weeknumber of the year in ISO standard.
DDD Day of year in numbers (i.e. 365)
DD Day of the month in numbers (i.e. 28)
D Day of week in numbers(i.e. 7)
DAY Day of the week in characters (i.e. Monday)
FMDAY Day of the week in characters (i.e. Monday)
DY Day of the week in short character description (i.e. SUN)
J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH Hournumber of the day (1-12)
HH12 Hournumber of the day (1-12)
HH24 Hournumber of the day with 24Hours notation (1-24)
AM AM or PM
PM AM or PM
MI Number of minutes (i.e. 59)
SS Number of seconds (i.e. 59)
SSSSS Number of seconds this day.
DS Short date format. Depends on NLS-settings. Use only with timestamp.
DL Long date format. Depends on NLS-settings. Use only with timestamp.
E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE The full era name
FF The fractional seconds. Use with timestamp.
FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM Fill Mode: suppresses blianks in output from conversion
FX Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only
RM The Roman numeral representation of the month (I .. XII)
RR The last 2 digits of the year.
RRRR The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC Century. BC dates are prefixed with a minus.
CC Century
SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH Spelled and ordinal format; 1 results in first.
TH Converts a number to it's ordinal format. For example 1 becoms 1st.
TS Short time format. Depends on NLS-settings. Use only with timestamp.
TZD Abbreviated time zone name. ie PST.
TZH Time zone hour displacement.
TZM Time zone minute displacement.
TZR Time zone region
X Local radix character. In america this is a period (.)

Adjunto procedimiento completo de PL/SQL de Oracle al que se le adicionan 3 campos: periodo que no es relevante, fecha de corte que es el ultimo dia del trimestre de la fecha, la fecha de corte anterior que es la fecha del anterior trimestre.

Espero les sirva.

CREATE OR REPLACE PROCEDURE CARGADIMTIEMPO IS
tmpVar NUMBER;
FechaDesde date;
FechaHasta date;
FechaDesdeStr VARCHAR2(8);
err_num NUMBER;
err_msg VARCHAR2(255);
BEGIN
tmpVar := 0;
FechaDesde := TO_DATE('19941231','YYYYMMDD');
FechaHasta := TO_DATE('20181231','YYYYMMDD');
WHILE FechaDesde <= FechaHasta LOOP
FechaDesdeStr := to_char( FechaDesde, 'YYYYMMDD') ;
INSERT INTO DIM_TIEMPO
(
FechaSK,
Fecha,
Año,
Trimestre,
Mes,
Semana,
Dia,
DiaSemana,
NTrimestre,
NMes,
NMes3L,
NSemana,
NDia,
NDiaSemana,
Periodo,
FechaCorte,
FechaCorteAnt
)
VALUES
(
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD') ,'YYYYMMDD'),
FechaDesde,
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MM'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'WW'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DD'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'D'),
'T'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q')||'/'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MONTH'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'MON'),
'Sem '||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'WW')||'/'||to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YY'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DD MON'),
to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'DAY'),
CASE
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN 3
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN 6
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN 9
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN 12
END,
CASE
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN TO_DATE('31/03/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN TO_DATE('30/06/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN TO_DATE('30/09/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN TO_DATE('31/12/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
END,
CASE
WHEN to_char(to_date(FechaDesdeStr,'YYYYMMDD'), 'Q') = 1 THEN TO_DATE('31/12/' || to_char(to_number(to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'))-1), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 2 THEN TO_DATE('31/03/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 3 THEN TO_DATE('30/06/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
WHEN to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'), 'Q') = 4 THEN TO_DATE('30/09/' || to_char(TO_DATE( FechaDesdeStr,'YYYYMMDD'),'YYYY'), 'DD/MM/YYYY')
END
);
-- Incremento del bucle
commit ;
FechaDesde := FechaDesde + 1;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
err_num := SQLCODE;
err_msg := SQLERRM;
DBMS_OUTPUT.put_line('Error Problemas :'||TO_CHAR(err_num) || ' ' || err_msg );
DBMS_OUTPUT.put_line(err_msg);
END CARGADIMTIEMPO;