為日期維度表設置數據庫

我正在為倉庫目的創建數據庫。我有許多維度表和一個事實表。我創建了單獨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-int 用於關係，並且我在事實表中使用它如下

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 列作為主鍵並將兩者關聯起來。看起來我做錯了，你能告訴我正確的方法嗎？

我實際上是不正確的設計。原因是我們必須分開打破日期維度和表格維度。所以我現在把它們分成以下

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。

引用自：https://dba.stackexchange.com/questions/294973