Existing Dimension Input Column Usage Tab

<this is the first tab | next tab (Special Members)>

This tab lists all of the columns that appear on the Existing Dimension input. Shown for each column are:
  • The column name
  • The lineage ID of the column (for informational purposes only)
  • The Usage of the column in the SCD transform (how it participates in the SCD)
  • Whether the dimension table allows nulls in this column

Automated Configuration

When you open the SCD component with an Existing Dimension input already attached, it will attempt to assign a Usage to the following types of columns:
  • Any column that is part of the sort order is assigned a Usage of Business Key.
All of these automated assignments may be overridden by you.

Assigning Usages

The following Usage values can be used: Business Key, Surrogate Key, SCD1, SCD2, SCD2 Effective Date, SCD2 Expiry Date, SCD2 Current Record, Inferred Member Indicator, Audit Key for Last Change, Audit Key for Add, Audit Key for Last SCD1 Change, Audit Key for Last SCD2 Change, or Not Used.
There can be several reasons a Usage you wish to use is not shown in the drop down list:
  • The data type of the column is not permitted for the desired usage. (For example, only boolean columns are permitted for Inferred Member Indicators, and dates for SCD2 Effective Dates.)
  • The usage type is only allowed once on the dimension, and it's already used. (For example, the Surrogate Key usage.)

Business Keys

Identification of at least one Business Key column is required. Business Key columns identify one or more columns that the SCD component can use to determine how Existing Dimension rows get matched up to Source System rows, in order to determine if any changes have occurred in the source system.

Surrogate Key

Only one column may be optionally identified as the Surrogate Key column. If you choose not to inform the component which field is the Surrogate Key, then surrogate key processing is left to you to handle by other means (identity columns, etc...) This can make it somewhat difficult to correctly set the SCD2 and SCD1 changed rows. (This is being addressed in Improve Pass-Through Surrogate Key Support.)

SCD1

Any number of columns may be identified as SCD1 columns. Any change to the values in these columns will result in changes to historical records associated with the business key.

SCD2

Any number of columns may be identified as SCD2 columns. Any change to the values in these columns will result in expiry of the "current" row from the Existing Dimension input, and generation of a new row with the changed values.

SCD2 Effective Date, SCD2 Expiry Date, and SCD2 Current Record

If any columns are identified as SCD2 columns, then one column must be identified as an SCD2 Effective Date column, and one column must be identified as an SCD2 Expiry Date column. Identification of an SCD2 Current Record column is optional.

Inferred Member Indicator

For early-arriving facts/late-arriving members - this column indicates if the member was added to the dimension table by fact processing. If this boolean column is true, then all SCD processing is "bypassed" for this member. If a match to a Source System or Special Members row is detected, then the entire contents of that row will replace the Existing Dimension column values (which are typically NULL or some specified "unknown" value).

Audit Key for Last Change

This is one of the four Auditing columns. This column will get updated with the current Audit Key if the row is sent to the New, SCD1 Update, or SCD2 Expiry outputs.

Audit Key for Add

This is one of the four Auditing columns. This column will get updated for all rows directed to the New output.

Audit Key for Last SCD1 Change

This is one of the four Auditing columns. This column will get updated for all rows directed to the SCD1 output.

Audit Key for Last SCD2 Change

This is one of the four Auditing columns. This column will get updated for all rows directed to the SCD2 expiry output, and those New rows containing the new version of the row.

Not Used

Columns identified as Not Used do not participate in the SCD2 processing, and are removed from the outputs.

<this is the first tab | next tab (Special Members)>

Last edited Apr 12, 2009 at 6:59 PM by toddmcdermid, version 3

Comments

No comments yet.