Storing natural keys in the dimension tables, to aid ETL m..

I'm a beginner at DW and BI. I'm posting to this NG because my current
(and first) data warehouse project is using SQL Server 2005 and my
question is regarding the Kimball book "The Microsoft Data Warehouse
Toolkit" (2006).

The authors are saying to store in your dimension tables the natural-
key counterpart to the primary identity key, in the enterprise data
model, so that you can match on it when looking up surrogate keys
during fact table load (page 267). But this seems like a pollution of
the dimensional model with ETL-support data if the natural keys were
not specified in the data model by a business requirement.

Am I reading this wrong? Thanks for any reply.

-- Josh

"Jay Way" wrote in message

> I'm a beginner at DW and BI. I'm posting to this NG because my current
> (and first) data warehouse project is using SQL Server 2005 and my
> question is regarding the Kimball book "The Microsoft Data Warehouse
> Toolkit" (2006).
>
> The authors are saying to store in your dimension tables the natural-
> key counterpart to the primary identity key, in the enterprise data
> model, so that you can match on it when looking up surrogate keys
> during fact table load (page 267). But this seems like a pollution of
> the dimensional model with ETL-support data if the natural keys were
> not specified in the data model by a business requirement.
>
> Am I reading this wrong? Thanks for any reply.
>
> -- Josh


You will always have a potential natural key in your tables if you eliminate
duplicate rows properly in your ETL process. It wouldn't make much sense as
a business requirement to have rows with different surrogate keys that were
duplicated on all other columns.

--
David Portas