0

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.

4
  • Hi, and welcome to dba.se! What are you "relating"? Try and put together a fiddle here (sample) which shows a problem! You could use some sample data to demonstrate what problem you're having! Jun 29 at 12:52
  • Have you defined the relationship between the two tables in the PowerBI data model? Just having the referential integrity in the database itself is not necessarily going to result in PowerBI knowing how the two tables relate.
    – Hannah Vernon
    Jun 30 at 15:04
  • While I hate to ask for an image, it's probably useful to see the two tables in the "Model" tab from PowerBI desktop.
    – Hannah Vernon
    Jun 30 at 15:05
  • well may be next time I dont' any place where I put image to share here. But I resolved it so put answer below, I break dimensions in time + date separately. Jun 30 at 17:34
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.