How does Oracle BI Enterprise Edition support Slowly Changing Dimensions (SCDs)?
Posted by Dylan Wan on January 15, 2007
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.
If you use Type One – Overwite the old data with new data, no specical handling is needed. You just join your fact table using the primary – foreign key relationship.
If you Type Two – Add a new record at the time of change, the nature key will not be able to uniquely identify a record. You can add the transaction date from the fact table to join to the dimension table on the phyical layer. For example, you have the following two tables:
You need to include the following where clause to match the correct customer record based on the transaction date of the order.
WHERE dw_order.customer_number = dw_customer.customer_number
Once you define the join clause in the phyical layer, the BI Server will use it when the user query the Orders by Customer, the data will be rolled up to the right sales person.