Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

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:

DW_CUSTOMER

Column Name
CUSTOMER_NUMBER
NAME
REGION_KEY
SALEREP_KEY
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE

DW_ORDER

Column Name
ORDER_KEY
CUSTOMER_NUMBER
TRANSACTION_DATE
ORDER_QTY
ORDER_AMOUNT

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
AND dw_order_transaction_date
  BETWEEN dw_customer.effective_start_date
  AND dw_customer.effective_end_Date

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.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s