Postgres : creating DATE Dimension as per Ralph Kimball

I am new to postgres and DW, and I have to design a DATE Dimension as given in book. I saw many places on the web and I did not succeed so far, can some explain how to populate fields like 'Fiscal Week', 'Fiscal Month', 'Fiscal Half year'开发者_运维百科

Thank you

---

I guess that you want 10 years Date Dimension table with all columns presented in Figure 2.4 (based on Google Books). Check in documentation:

• Formatting function to_char with Table 9-21 for template patterns
• Extracting function date_part
• Generating row series with generate_series(start, stop, step interval)

To get all days within 10 years you could write:

SELECT generate_series('2001-01-01'::date, '2010-12-31'::date, '1 day') AS day;

SELECT count(*) FROM generate_series('2001-01-01'::date, '2010-12-31'::date, '1 day');
 count 
-------
  3652
(1 row)

According to Figure 2.5 create table as e.g.:

DROP TABLE IF EXISTS "Date Dimension";
CREATE TABLE "Date Dimension"
(
    "Date Key" serial,
    "Date" date,
    "Full Day Description" text,
    "Day Of Week" text,
    "Calendar Month" text,
    "Calendar Year" integer,
    "Fiscal Year Month" text,
    "Holiday Indicator" text,
    "Weekday Indicator" text 
);

Insert command:

INSERT INTO "Date Dimension"
    ("Date", "Full Day Description", "Day Of Week", "Calendar Month",
    "Calendar Year", "Fiscal Year Month", "Holiday Indicator",
    "Weekday Indicator")
SELECT
    day,
    rtrim(to_char(day, 'Month')) || to_char(day, ' DD, YYYY'),
    to_char(day, 'Day'),
    rtrim(to_char(day, 'Month')),
    date_part('year', day),
    'F' || to_char(day, 'YYYY-MM'),
    '', --omitting (trivial 'Holiday'/'Non-Holiday, but how to get this ??),
    CASE
        WHEN date_part('isodow', day) IN (6, 7) THEN 'Weekend'
        ELSE 'Weekday'
    END
FROM
    generate_series('2001-01-01'::date, '2010-12-31'::date, '1 day') day;

I hope this gives you some framework and starting point.

---

How is your fiscal year structured? When does it start? Does it follow any standard 4-4-5 or 4-5-4 rules? Your best bet might be loading it from a CSV file such as discussed in the answer to this question: 4-5-4 National Retail foundation Calendar csv download or function to create. Fiscal years are notoriously non-standard. For example, the fiscal year of our company starts July 1st.

I had to add fields to our calendar dimension for the broadcast calendar (starts on the Monday of the week that includes January 1st) and was able to do it with some calculations.