Обсуждение: Ensuring Rifferential Integrity

Поиск
Список
Период
Сортировка

Ensuring Rifferential Integrity

От
Anthony Apollis
Дата:

 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.

Re: Ensuring Rifferential Integrity

От
"Peter J. Holzer"
Дата:
On 2023-09-17 19:24:52 +0200, Anthony Apollis wrote:
>  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.

Can you illustrate this with a simple example? I don't think I
understood what you're trying to say.

> 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.

Foreign key columns aren't normally supposed to be unique. You want to
reference the same thing (e.g. your apple type) from many columns (the
same type of apple will be sold in many stores every day).


> ” 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";”

Isn't that basically the same as

    UPDATE system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
    SET "Master_BRACS_Secondary_Key" = "ZTBR_TransactionCode";

?

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Вложения