From: | Mark Wong <markwkm(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | designing time dimension for star schema |
Date: | 2014-02-10 16:45:28 |
Message-ID: | CAE+TzGq0yFrHrLwMnD6CdVD2mfuANjBToRn9SJeWai8zyZMxrw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello everybody,
I was wondering if anyone had any experiences they can share when
designing the time dimension for a star schema and the like. I'm
curious about how well it would work to use a timestamp for the
attribute key, as opposed to a surrogate key, and populating the time
dimension with triggers on insert to the fact tables. This is
something that would have data streaming in (as oppose to bulk
loading) and I think we want time granularity to the minute.
A simplified example:
-- Time dimension
CREATE TABLE time (
datetime TIMESTAMP WITH TIME ZONE NOT NULL,
day_of_week SMALLINT NOT NULL
);
CREATE UNIQUE INDEX ON time (datetime);
-- Fact
CREATE TABLE fact(
datetime TIMESTAMP WITH TIME ZONE NOT NULL,
FOREIGN KEY (datetime) REFERENCES time(datetime)
);
-- Function to populate the time dimension
CREATE OR REPLACE FUNCTION decompose_timestamp() RETURNS TRIGGER AS $$
BEGIN
NEW.datetime = date_trunc('minutes', NEW.datetime);
INSERT INTO time (datetime, day_of_week)
VALUES (NEW.datetime, date_part('dow', NEW.datetime));
RETURN NEW;
EXCEPTION
WHEN unique_violation THEN
-- Do nothing if the timestamp already exists in the dimension table.
RETURN new;
END; $$
LANGUAGE 'plpgsql';
CREATE TRIGGER populate_time BEFORE INSERT
ON fact FOR EACH ROW
EXECUTE PROCEDURE decompose_timestamp();
Regards,
Mark
From | Date | Subject | |
---|---|---|---|
Next Message | CS DBA | 2014-02-10 17:20:51 | Re: designing time dimension for star schema |
Previous Message | Wolfgang Keller | 2014-02-10 16:37:26 | Re: client that supports editing views |