Setting up database for date dimension table

All we need is an easy explanation of the problem, so here it is.

I was working on creating database for warehouse purpose. I have many dimension table and one fact table. I have created separate create_date dimension table but It is not working as I want it to. Following is my data dimension table structure. I want to get date and in time hours, minutes and seconds

CREATE SEQUENCE IF NOT EXISTS create_dates_id_seq;
CREATE TABLE "public"."create_dates" (
    "id" int8 NOT NULL DEFAULT nextval('create_dates_id_seq'::regclass),
    "hours" int4,
    "minutes" int4,
    "seconds" int4,
    "date" date,
    "day" int4,
    "day_of_week" int4,
    "month" int4,
    "month_name" text,
    "quarter" int4,
    "quarter_name" text,
    "year" int4,
    PRIMARY KEY ("id")
);

I have used Big-int for relationship and I am using it in fact table as following

CREATE TABLE "public"."performances" (
    "id" int8 NOT NULL DEFAULT nextval('performances_id_seq'::regclass),
    "responsetime" int4,
    "user_id" int8,
    "create_date_id" int8 NOT NULL,
    "created_at" timestamp NOT NULL,
    "updated_at" timestamp NOT NULL,
    CONSTRAINT "fk_rails_7c33413662" FOREIGN KEY ("create_date_id") REFERENCES "public"."create_dates"("id"),
    CONSTRAINT "fk_rails_32824f66a5" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id"),
    PRIMARY KEY ("id")
);

But when I am relating these they are not working in Power BI I have also created relationship but it is not working only shows all data not one month data. I think I need to change this so now I want to make Datetime column as a primary key and relate both. Look like I am doing all wrong, can you advice me correct way to do it.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I was actually incorrect design. The reason is we must break date dimension and table dimension separately. So I have split them now in following

CREATE SEQUENCE IF NOT EXISTS create_dates_id_seq;
CREATE TABLE "public"."create_dates" (
    "id" int8 NOT NULL DEFAULT nextval('create_dates_id_seq'::regclass),
    "date" date,
    "day" int4,
    "day_of_week" int4,
    "month" int4,
    "month_name" text,
    "quarter" int4,
    "quarter_name" text,
    "year" int4,
    PRIMARY KEY ("id")
);

And I created time dimension using following structure

CREATE TABLE "public"."dim_times" (
    "id" int4 NOT NULL,
    "time" time,
    "hour" int2,
    "military_hour" int2,
    "minute" int4,
    "second" int4,
    "minute_of_day" int4,
    "second_of_day" int4,
    "quarter_hour" varchar,
    "am_pm" varchar,
    "day_night" varchar,
    "day_night_abbrev" varchar,
    "time_period" varchar,
    "time_period_abbrev" varchar,
    PRIMARY KEY ("id")
);

and in my performance I have add both tables relationship

CREATE TABLE "public"."performances" (
    "id" int8 NOT NULL DEFAULT nextval('performances_id_seq'::regclass),
    "responsetime" int4,
    "user_id" int8,
    "dim_time_id" int8 NOT NULL,
    "dim_date_id" int8 NOT NULL,
    "created_at" timestamp NOT NULL,
    "updated_at" timestamp NOT NULL,
    CONSTRAINT "fk_rails_7c33413662" FOREIGN KEY ("create_date_id") REFERENCES "public"."create_dates"("id"),
    CONSTRAINT "fk_rails_32824f66a5" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id"),
    PRIMARY KEY ("id")
);

I also added more fields in my date dimension like holidays, weekend information and more details. This fix my issue now it is working. I am now using Integer as a key as no more BigInt needed.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply