MIS 430 Midterm GUIDE — Part 3: SCDs & Modeling Approaches (2.5-2.6)

What are Slowly Changing Dimensions (SCDs) used for?

Slowly Changing Dimensions handle changes to dimension attribute values over time.

What happens in a Type 1 Slowly Changing Dimension?

The existing row is overwritten with the new value. History is lost.

When should SCD Type 1 be used?

When historical accuracy is not important.

If Sidney's title changes from Business Analyst to Senior Business Analyst using Type 1, what happens?

The same row is updated from Business Analyst to Senior Business Analyst; the previous title is not retained.

What happens in a Type 2 Slowly Changing Dimension?

A new row is added for the changed value while the original row remains, preserving full history.

How does SCD Type 2 identify current versus historical records?

A RowIndicator or EffectiveStart/EffectiveEnd dates can distinguish current and historical rows.

What happens to the original row when a Type 2 change occurs?

The original row remains in the dimension and is marked Not Current; a new row is added for the changed value.

What level of history does SCD Type 2 preserve?

Full history.

Which Slowly Changing Dimension type is the most commonly used?

Type 2.

What happens in a Type 3 Slowly Changing Dimension?

A previous-value column is added. The current value is overwritten, and the old current value is stored in the previous-value column.

How much history does SCD Type 3 preserve?

Only one level of history.

How can timestamps be used with SCD Type 3?

Timestamps can be used to indicate when the previous and current values became effective.

What are the three main Data Warehouse Modeling Approaches?

Inmon / Normalized DW, Kimball / Dimensionally Modeled DW, and Independent Data Marts.

What is the Inmon / Normalized Data Warehouse approach?

A fully normalized, enterprise-wide 3NF relational schema, with data marts derived from the central warehouse.

What are key characteristics of the Inmon approach?

It supports enterprise-wide, cross-subject analysis, but direct queries against the normalized warehouse are more complex.

What is the Kimball / Dimensionally Modeled Data Warehouse approach?

Star or constellation schemas are used at the warehouse level, with data marts organized as a bus architecture using conformed dimensions.

What are key characteristics of the Kimball approach?

It is query-friendly, supports direct BI access, and is the most widely adopted approach.

What are Independent Data Marts?

Separate departmental data marts created without a central data warehouse.

What are the advantages and disadvantages of Independent Data Marts?

They can be faster to implement for individual departments, but create silos, inconsistent definitions, and difficult reconciliation.

Why are Independent Data Marts not suitable for enterprise-wide analysis?

They lack a central warehouse and make cross-subject analysis and consistent enterprise-wide definitions difficult.