Ensuring referential integrity

Question

I brought in the primary/secondary/foreign keys because it does not exist in the fact/dimension tables.

The fact tables contain 6 million rows 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 relational 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 referential 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:

enter image description here

And insert it into

enter image description here

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