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: [image: image003]
And insert it into
:
[image: 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.
