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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.