From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Mark Wong <markwkm(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: designing time dimension for star schema |
Date: | 2014-02-11 03:00:13 |
Message-ID: | CAHyXU0xHjCM_aX5vkSsNSB84UHTJuymxC4OoxVkMP5oBnAdssA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Feb 10, 2014 at 8:45 AM, Mark Wong <markwkm(at)gmail(dot)com> wrote:
> 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';
Failure to inline the date/time in dimension tables a terrible
practice IMO. You add a lookup and an expensive subtransaction to
each insert. When querying the fact table you tack on a join for
every query that does not need to be there (for no benefit I can see).
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Nedal Slman | 2014-02-11 09:35:58 | |
Previous Message | Tom Lane | 2014-02-11 02:01:16 | Re: designing time dimension for star schema |