Below are two functions that can be called to build and populate a date or time dimension.
--- Call the function that builds and populates the dimension. select buildDateDim (date '1999-01-01', date '2013-12-31') as "Date Dim Builder" select * from dim_date order by 1;
--- Create the builder function CREATE OR REPLACE FUNCTION buildDateDim(startdate date, enddate date) RETURNS text AS $BODY$ DECLARE strresult text default ('Table Created!'); loopDate date default (startDate); BEGIN execute 'drop table if exists dim_date'; execute 'create table dim_date ( date_key integer , date_year smallint , quarter_name char(2) , year_quarter char(7) , month_name varchar(15) , month_name_short char(3) , month_number smallint , year_month varchar(7) , date_value date , day_name varchar(11) , day_of_week_num smallint , day_of_year smallint , week_of_year smallint , day_type varchar(10) , date_formal varchar(20) , last_30days_ind smallint , last_60days_ind smallint , last_90days_ind smallint , rec_update_date date ) distributed by (date_key)'; while loopDate <= endDate loop execute 'insert into dim_date values (' || to_number(to_char(loopDate, 'yyyymmdd'), 99999999) || ', ' || extract(year from loopDate) || ', ' || '''Q' || extract(quarter from loopDate) || ''', ' || '''' || rtrim(to_char(loopDate, 'yyyy')) || '-Q' || extract(quarter from loopDate) || ''', ' || '''' || rtrim(to_char(loopDate, 'Month')) || ''', ' || '''' || rtrim(to_char(loopDate, 'MON')) || ''', ' || to_number(to_char(loopDate, 'MM'), 99) || ', ' || '''' || rtrim(to_char(loopDate, 'yyyy-MM')) || ''', ' || '''' || loopDate || ''', ' || '''' || rtrim(to_char(loopDate, 'Day')) || ''', ' || extract(isodow from loopDate) || ', ' || to_number(to_char(loopDate, 'DDD'), 999) || ', ' || to_number(to_char(loopDate, 'WW'), 99) || ', ' || '''' || case when rtrim(to_char(loopDate, 'Day')) in ('Saturday', 'Sunday') then 'Weekend' else 'Weekday' end || ''', ' || '''' || rtrim(to_char(loopDate, 'FMMonth FMDDth, yyyy')) || ''', ' || case when loopDate between (now()::date - interval '30 day') and now()::date then 1 else 0 end || ', ' || case when loopDate between (now()::date - interval '60 day') and now()::date then 1 else 0 end || ', ' || case when loopDate between (now()::date - interval '90 day') and now()::date then 1 else 0 end || ', ' || '''' || now()::date || '''' || ')'; loopDate := loopDate + 1;
end loop; RETURN strresult; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION builddatedim(date, date) OWNER TO gpadmin;
------ ------
--- Call the function that builds and populates the dimension. select buildTimeDim () as "Time Dim Builder" select * from dim_time order by 1
--- Create the builder function CREATE OR REPLACE FUNCTION buildTimeDim() RETURNS text AS $BODY$ DECLARE strresult text default ('Table Created!'); loopTime time default ('0:00'); BEGIN execute 'drop table if exists dim_time'; execute 'create table dim_time ( time_key char(4) , hour_minute time , time_ampm varchar(8) , hour_range_text varchar(20) , day_segment varchar(20) ) distributed by (time_key)'; for i in 0..1439 loop execute 'insert into dim_time values (' || '''' || to_char(loopTime, 'hh24mi') || ''', ' || '''' || to_char(loopTime, 'hh24:mi') || ''', ' || '''' || to_char(loopTime, 'hh:miam') || ''', ' || '''' || case rtrim(to_char(loopTime, 'hh24')) when '00' then '12am-12:59am' when '01' then '1am-1:59am' when '02' then '2am-2:59am' when '03' then '3am-3:59am' when '04' then '4am-4:59am' when '05' then '5am-5:59am' when '06' then '6am-6:59am' when '07' then '7am-7:59am' when '08' then '8am-8:59am' when '09' then '9am-9:59am' when '10' then '10am-10:59am' when '11' then '11am-11:59am' when '12' then '12pm-12:59pm' when '13' then '1pm-1:59pm' when '14' then '2pm-2:59pm' when '15' then '3pm-3:59pm' when '16' then '4pm-4:59pm' when '17' then '5pm-5:59pm' when '18' then '6pm-6:59pm' when '19' then '7pm-7:59pm' when '20' then '8pm-8:59pm' when '21' then '9pm-9:59pm' when '22' then '10pm-10:59pm' when '23' then '11pm-11:59pm' else 'other' end || ''', ' || '''' || case when rtrim(to_char(loopTime, 'hh24')) between '00' and '04' then 'Late Night' when rtrim(to_char(loopTime, 'hh24')) between '05' and '07' then 'Early Morning' when rtrim(to_char(loopTime, 'hh24')) between '08' and '11' then 'Morning' when rtrim(to_char(loopTime, 'hh24')) between '12' and '16' then 'Afternoon' when rtrim(to_char(loopTime, 'hh24')) between '17' and '20' then 'Evening' else 'Night' end || '''' || ')'; loopTime := loopTime + interval '1 minute'; end loop; RETURN strresult; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION builddatedim(date, date) OWNER TO gpadmin; |