Zeitdimension per Table Function erstellen

Heute geht's um die Erstellung einer Zeitdimension mithilfe einer Table-Function. Dazu verwendet man im Wesentlichen die Möglichkeiten der Funktion to_char, wie das folgende Beispiel zeigt.

Zunächst wird der Objekt-Typ erstellt:

create type time_dim_t as object
(
 v_day date,
 v_day_name varchar2(30),
 v_day_of_week integer,
 v_day_of_month integer,
 v_day_of_year integer,
 v_week integer,
 v_week_start date,
 v_week_end date,
 v_iso_week integer,
 v_iso_week_start date,
 v_iso_week_end date,
 v_month integer,
 v_month_name varchar2(30),
 v_month_start date,
 v_month_end date,
 v_month_days integer,
 v_quarter integer,
 v_quarter_name varchar(2),
 v_quarter_start date,
 v_quarter_end date,
 v_quarter_days integer,
 v_year integer,
 v_year_start date,
 v_year_end date,
 v_year_days integer,
 v_is_leap_year varchar2(1)
);
/

Hinzu kommt der Table-Typ:

create type time_dim_tab as table of time_dim_t;
/

Damit sind alle Vorbereitungen getroffen, um die Table-Function zu erstellen:

create or replace function time_dim
(
 p_year_start_in in integer,
 p_year_end_in in integer
) 
return time_dim_tab pipelined
as
 v_day date;
 v_day_name varchar2(30); 
 v_day_of_week integer;
 v_day_of_month integer;
 v_day_of_year integer; 
 v_week integer;
 v_week_start date;
 v_week_end date;
 v_iso_week integer;
 v_iso_week_start date;
 v_iso_week_end date;
 v_month integer;
 v_month_name varchar2(30);
 v_month_start date;
 v_month_end date;
 v_month_days integer;
 v_quarter integer;
 v_quarter_name varchar(2);
 v_quarter_start date;
 v_quarter_end date;
 v_quarter_days integer;
 v_year integer;
 v_year_start date;
 v_year_end date;
 v_year_days integer;
 v_is_leap_year varchar2(1);
begin

 begin

  for y in p_year_start_in .. p_year_end_in loop
   
   v_year := y;
   v_year_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
   v_year_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
   v_year_days := v_year_end - v_year_start + 1;
  
   if (v_year_days = 366) then
    v_is_leap_year := 'Y';
   else
    v_is_leap_year := 'N';
   end if;
   
   for q in 1 .. 4 loop
   
    v_quarter := q;
    v_quarter_name := 'Q' || v_quarter;
    case v_quarter_name
     when 'Q1' then
      v_quarter_start := to_date('01.01.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('31.03.' || v_year, 'DD.MM.YYYY');
     when 'Q2' then
      v_quarter_start := to_date('01.04.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('30.06.' || v_year, 'DD.MM.YYYY');
     when 'Q3' then
      v_quarter_start := to_date('01.07.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('30.09.' || v_year, 'DD.MM.YYYY');
     when 'Q4' then
      v_quarter_start := to_date('01.10.' || v_year, 'DD.MM.YYYY');
      v_quarter_end := to_date('31.12.' || v_year, 'DD.MM.YYYY');
    end case;
    v_quarter_days := v_quarter_end - v_quarter_start + 1;
    
    for m in to_number(to_char(v_quarter_start, 'MM')) .. 
             to_number(to_char(v_quarter_end, 'MM')) loop
   
     v_month := m;
     v_month_start := to_date('01.' || v_month || '.' || v_year, 'DD.MM.YYYY');
     v_month_end := last_day(v_month_start);
     v_month_days := v_month_end - v_month_start + 1;
     v_month_name := to_char(v_month_start, 'MONTH');
 
     for d in 1 .. v_month_days loop
     
      v_day := to_date(d || '.' || v_month || '.' || v_year, 'DD.MM.YYYY');
      v_day_of_week := to_number(to_char(v_day, 'D'));
      v_day_of_month := to_number(to_char(v_day, 'DD'));
      v_day_of_year := to_number(to_char(v_day, 'DDD'));
      v_day_name := to_char(v_day, 'DAY');
      v_week := to_number(to_char(v_day, 'WW'));
      v_week_start := trunc(v_day, 'WW');
      v_week_end := v_week_start + interval '6' day;
      v_iso_week := to_number(to_char(v_day, 'IW'));
      v_iso_week_start := trunc(v_day, 'IW');
      v_iso_week_end := v_iso_week_start + interval '6' day;
 
      pipe row
     (
      time_dim_t
      (
       v_day,
       v_day_name,
       v_day_of_week,
       v_day_of_month,
       v_day_of_year,
       v_week,
       v_week_start,
       v_week_end,
       v_iso_week,
       v_iso_week_start,
       v_iso_week_end,
       v_month,
       v_month_name,
       v_month_start,
       v_month_end,
       v_month_days,
       v_quarter,
       v_quarter_name,
       v_quarter_start,
       v_quarter_end,
       v_quarter_days,
       v_year,
       v_year_start,
       v_year_end,
       v_year_days,
       v_is_leap_year 
      )
     );  
     end loop;
    
    end loop;
    
   end loop;
   
  end loop;

 end;

end;

Ein Aufruf der Table-Function für die Jahre 2011 und 2012 sieht dann wie folgt aus:

select * from table(time_dim(2011, 2012));

Auf der Grundlage dieser Table-Function kann dann eine Zeitdimension erstellt werden; sei es als Star- oder Snowflake-Schema, was durch entsprechende Projektionen erreicht werden kann.

Eine Ergänzung wäre die Angabe von Feiertagen, welche oftmals im iCal-Format bereitstehen. Vielleicht widme ich ein weiteres Posting diesem Thema, um zu zeigen, wie man dieses Format in SQL bereitstellen kann.

Data Warehouse - Modellierung auf der konzeptionellen Ebene

Dieser Post beschäftigt sich mit der Fragestellung, wie das Data Warehouse Modell auf der konzeptionellen Ebene aussieht. Diese Modellierung auf der Grundlage eines Entity Relationship Diagram ist natürlich unabhängig von der physischen Modellierung als Star- oder Snowflake-Schema.

Eine Möglichkeit besteht darin, Dimensionen als Typ und die Ebenen innerhalb einer Dimensionen als Subtypen darzustellen. Die Hierarchie stellt sich dann als 1:n Beziehung zwischen den einzelnen Ebenen dar. Das folgende Beispiel zeigt die Modellierung einer Dimension für Produkte samt den Ebenen und deren Hierarchie:

Der Cube wird dann mit der detailliertesten Ebene der Hierarchie verbunden (im Beispiel die Ebene PR_ITEM):

Dieses Modell ist somit die Grundlage für Star- und Snowflake-Schema. Für ein Snowflake-Shema ergibt sich für die Produkt Dimension dann die folgende physische Struktur:

Das zugehörige Star-Schema für die ausgewählte Dimension sieht wie folgt aus:

Damit wäre die Frage beantwortet, wie man ein Data Warehouse in Form eines Entity Relationship Diagrams, unabhängig von der physischen Implementierung, darstellen kann.