Slowly Changing Dimensions

[dsuser]

Hi

Can anyone explain about how slowly changing dimensions are implemented in the DW design. And how to implement surrogate keys.

Any help really appreciated.

Thanks much,
dsuser

[roy]

Hi,
in short a slowly changing dimension is a way of keeping track or changes in data along time.
this helps you to, lets say, know to which department this item belonged to in any given date.
there are several ways coverd in books, in here - use search here or in the tools4datastage.com/forum.

the simplest way, I think, is:
1. use @INROWNUM as you surrogate key (GK = generated key) value for the first load.
2. in next loads check did your key ( the values you need history on ) changed?
if so, you need to perform 2 tasks:
a. close the period for the exisiting (GK) row (i.e. GK = 1 , start_gk_date = 2000-01-01, end_gk_date = 2000-12-31, table's PK (primary key), data....)
b. open a new record with a new G.K. (i.e. GK = @OUTROWNUM + max_old_gk_val, start_gk_date = 2001-01-01, end_gk_date = @NULL ,table's PK (primary key), data...)
3. to achieve the new GK values, as you can see in the parenthasis example of task b. you need to get the max value of the GKs already in the Production table before this job starts or in the beginning of it and simply add it to the @INROWNUM in the derivation of the GK column, using a simple lookup to a hash file that contains 2 columns col1 = dummy key lets say hardcoded 1 for the lookup, col2 = max_gk.

this is in a nut shell.

IHTH (I Hope This Helps)

[ray.wurlod]

Enrol in the DataStage Best Practices (Server) class (DS306). There you will learn good ways to implement all three types of slowly changing dimensions, as well as at least three ways to generate surrogate keys.
I believe that DS306 is available via "e-Learning" as well.