Re: Ensuring Rifferential Integrity

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(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"