Ensuring Refferential Integrity

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 image003

And insert it into

:

image004 (1)

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.