Postgresql
為日期維度表設置數據庫
我正在為倉庫目的創建數據庫。我有許多維度表和一個事實表。我創建了單獨
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。