How does Oracle BI Enterprise Edition support Slowly Changing Dimensions (SCDs)?

You need to handle the SCD in your data warehouse schema design. The technique you use will probably affect your ETL process startegy. It will also affect how the BI tool querying your data.

OBIEE is not a ETL tool. It provides the tool, DAC – Data Warehouse Administation Console for managing and monitoring the ETL processes, but its focus is really on the logical data modeling and data access. You need to define how a fact table and a dimension table are joined in OBIEE.

How to handle Slowly Changing Dimensions (SCDs) in data model design?

There are multiple methods to handle the slowly changing dimensions. Which technique to use depends on your business requirements. The choice among these three methods are not a technical design decision since their behaviors are different.

Type One: Overwite the old data with new data

Using this method, you do not store the histoy. For example, that say each customer can

What are slowly changing dimensions?

Slowly changing dimensions are used to describe the date effectivity of the data. It describe the dimensions whose attribute value vary over time.

This term is commonly used in the Data Warehousing world. However, the problem exists in the OLTP, relational data modeling as well.

Example:

The sales representative assigned to a customer may change over time. Linda was the salesrep for ABC, inc. before March last year. Kathy later becomes the representative for this account.

You may want to track the data “as is”, “as was”, or both. If you show the year total sales, you can either report as the sales are all generated by Kathy, or actually break the number down between Linda and Kathy.