设置日期维度表的数据库

Question

我在为仓库创建数据库。我有很多维表和一个事实表。我已经创建了单独的create_date维度表，但它并不像我所希望的那样工作。以下是我的数据维表结构。我想得到日期和时间，时间，分钟和秒

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")
);

我在关系中使用了Big，并且我使用它的实证表如下

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")
);

但是当我把它们联系起来的时候，它们不是在Power BI中工作，我也创建了关系，但是它不是工作的，它只显示所有的数据，而不是一个月的数据。我认为我需要改变这一点，所以现在我想将Datetime列作为主键，并将两者关联起来。看上去我做错了，你能给我正确的建议吗？

Answer 1

实际上我的设计是错误的。原因是我们必须分开日期维度和表维度。所以我现在把它们分开

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")
);

我使用以下结构创建了时间维度

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")
);

在我的性能中，我添加了两个表的关系

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")
);

我还在日期维度中添加了更多字段，如假日、周末信息和更多细节。这解决了我的问题，现在它起作用了。我现在使用Integer作为密钥，因为不再需要BigInt。