OWB 11gR2 – Intra Batch Dimension Changes
By David Allan-Oracle on Dec 21, 2009
There are a number of improvements and new features for data warehousing projects related to the OWB dimensional modeling capabilities. The improvements range from improved partitioning capabilities, to richer dimension modeling transformation capabilities to name a few. This post will look at how to handle intra batch dimension member changes.
Before I illustrate the intra batch functionality just want to give a quick summary of some of the changes in the dimensional modeling area;
- support for orphan management for dimensions and cube/facts (handles early arriving facts for example or hierarchy loading) plus slowly changing enhancements to support tracking change intra batch.
- Partitioning enhancements (interval partitioning, virtual column partitioning, system and reference partitions).
- Virtual columns support (column definitions can be calculations)
- More flexible dimensional modeling (removed constraints on surrogate) and enhanced operator support for dimensions (to support such dimensions).
- OLAP 11g support for MOLAP definitions plus cube organized materialized view support
- Derivation of dimensional model to OBIEE - get Answers faster!
Ok, back to the fun stuff...who am I kidding, its all fun right;-)
A request we have seen through the years is to load multiple versions of a dimension member in a single load and record the changes in slowly changing manner. Loading changes across batch loads is straightforward, it is just when the changes are in one load/map execution the problem is a challenge (since the changes are processed as a set). In OWB 11gR2 there is a feature for supporting this. For OWB 10gR2 and 11gR1 a single change of a dimension member is loaded in a load - if multiple rows for a dimension member with the same business key are in the batch, the OWB dimension operator de-duplicates the row set by grouping on the business/natural key and uses the MIN function to select a row. In OWB 11gR2 there is improved support ...
For loading multiple history records for a single dimension within a single load, there is a property that needs set on the dimension operator in 11gR2. The property is the 'Support Multiple History Loading' property and will enable all history to be maintained. There is an additional property on the dimension operator to load the historical records out of order also.
For example if your source looked like the following;
Did you see that the dimension member with natural key 1 has state changes - the revenue type changed from 0 to 1 to 3. With the support for multiple history loading enabled, the map is deployed and then executed - the resultant dimension table would be loaded as follows;
Note each change has been loaded and the effective and expiration date set.
In OWB 11gR2 you can set the property for the dimension operator in mapping using the scripting syntax and property (the property is named SUPPORT_MULTIPLE_TYPE2_HISTORY_RECORDS and has value true or false), this can be done via the OMBPlus panel in the UI or using the command like OMBPlus(.bat or.sh) command processor in batch mode. The OMB commands have been there since 10gR1, and are documented in the scripting reference manual here. The commands are all encompassing and cover all aspects of the whole product.
Back to the dimension loading... Another property mentioned briefly is the property to load dimension member changes out of sequence - this is supported via the property 'Support Out of Order History Loading'.
So imagine we have dimension members loaded as above and we get a source record like the following which is in the middle of the current dimension members.
Using the property above we can support this scenario such that we get all records updated accordingly - we get the record injected in the history and the effective and expiration dates set accordingly, see the resultant table below.
There is an associated cost to this, hence it is switched off by default, as is the support multiple history loading property, but IF you really can't live without it, then there is an easy to use option to enable the behavior. That's it for now, a quick run-through of the intra-batch slowly changing dimension changes in the OWB 11gR2, any feedback or comments please let us know.