I brought in the Primary/Secondary/Foreign keys because it does not exist in the Fact/Dimension tables.
The Fact tables contain 6 million records and the dimension tables are tiny. Because some columns don't exist in the Fact and Dimension table I can not update the Foreign Keys in the Fact table to ensure relationship integrity.
e.g Say I have a Fact table containing Apple's Sales; one of the Dimension tables is Apple Type. Since the two tables don't contain an Apple Type column in both I won't be able to enforce referention integrity. If my Apple Sales table contains 6 million + sales, I won't be able to break it down Apple sales by Type.
That is the problem I am sitting with. My fact Table is not able to give me unique Foreign Key columns. I read about a Mapping table.
I would like to join IMETA_ZTRB_MP$F with Dimensions. I have brought in these mapping tables as dimensions(see code and tables attached). I created Primary and Foreign/Secondary keys to join these tables. Currently, I don’t have a unique column within the SAP table and Dimension tables. To be sure that the data align I needed column(s) like that.
a process in achieving this?
I have brought in table key constraints, but because matching columns are missing I am not getting unique foreign keys for these. An example is using this code to update the foreign key values in the Fact/SAP table
” UPDATE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS A
SET "Master_BRACS_Secondary_Key" = B."Primary_ZTBR_TransactionCode"
FROM dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" AS B
WHERE A."ZTBR_TransactionCode" = B."Primary_ZTBR_TransactionCode";”
It is supposed to take primary key values from:
And insert it into
:
The problem is those values in the Foreign/Secondary keys are not unique.
Here is the SQL:
-- Table: system.IMETA_ZTRB_MP$F_ZTBR_TA_BW
-- DROP TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW";
CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"ZTBR_TransactionCode" integer NOT NULL DEFAULT nextval('system."IMETA_ZTBR_TransactionCode_Seq"'::regclass),
"Company_Code" character varying COLLATE pg_catalog."default",
"Posting_Period" integer,
"Fiscal_Year" integer,
"Profit_Center" text COLLATE pg_catalog."default",
"Account_Number" integer,
"Business_Process" character varying COLLATE pg_catalog."default",
"Internal_Order" integer,
"Trading_Partner" text COLLATE pg_catalog."default",
"Amount_in_Company_Code_Currency" numeric,
"Company_Code_Currency" text COLLATE pg_catalog."default",
"BRACS_FA" character varying COLLATE pg_catalog."default",
"Expense_Type" text COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying COLLATE pg_catalog."default",
"CC_Direct" text COLLATE pg_catalog."default",
"Segment_PC" integer,
"CC_Master_FA" text COLLATE pg_catalog."default",
"Region_Secondary_Key" integer,
"Direct_Indirect_Secondary_Key" integer,
"Source_Description_Secondary_Key" integer,
"Entity_Secondary_Key" integer,
"Master_BRACS_Secondary_Key" integer,
"Loaddate" date,
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey" PRIMARY KEY ("ZTBR_TransactionCode"),
CONSTRAINT "IMETA_ZTBR_TransactionCode_unique" UNIQUE ("ZTBR_TransactionCode"),
CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_fkey" FOREIGN KEY ("Master_BRACS_Secondary_Key")
REFERENCES dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" ("Primary_ZTBR_TransactionCode") MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE NO ACTION,
CONSTRAINT fk_entity FOREIGN KEY ("Entity_Secondary_Key")
REFERENCES dim."IMETA_Entity_Mapping" ("Entity_ID") MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
OWNER to apollia;
---
-- Table: dim.IMETA_Master_BRACS_to_SAP_Data_TA_BR_
-- DROP TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_";
CREATE TABLE IF NOT EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
(
"Primary_ZTBR_TransactionCode" integer NOT NULL,
"Level 1" character varying(255) COLLATE pg_catalog."default",
"Level 2" character varying(255) COLLATE pg_catalog."default",
"Level 3" character varying(255) COLLATE pg_catalog."default",
"Acct Type" character varying(255) COLLATE pg_catalog."default",
"Account Desc" character varying(255) COLLATE pg_catalog."default",
"EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default",
"BRACS" character varying(255) COLLATE pg_catalog."default",
"BRACS_DESC"" " character varying(50) COLLATE pg_catalog."default",
"BRACS_DESC" character varying(255) COLLATE pg_catalog."default",
"Loaddate" date,
CONSTRAINT "Primary Key" PRIMARY KEY ("Primary_ZTBR_TransactionCode")
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"
OWNER to apollia;
ZTBR n IMETA_Master_BRACS_to_SAP_Data_TA_BR_.txt
Displaying ZTBR n IMETA_Master_BRACS_to_SAP_Data_TA_BR_.txt.
