The recent new release of OWB has been an incredible step forward for Oracle Business Intelligence. With this new release all sort of intereseting new features have been added like data profiling, ERP support, schedule integration and so on. One of the most interesting features for BI developer is the new support for history storage. OWB now supports the type 1, 2 and 3 concepts for Slowly Changing Dimensions (SCD). In this article I will give some thoughts of the implementation chosen by Oracle, and of course how to develop exactly what you want in the end.
First off, OWB has implemented the history concepts on table level. Which means that either all your attributes, or none, will be processed by the chosen history type. An important feature request would be to implement the history types on attribute level; instead on table level. Dimensions which combine type 1, 2 and even 3 are all too common…
For type 1; it’s easy. Once you have configured your dimension as a type 1 dimension (in OWB terms; all attributes are type 1) you basically don’t have to do anything more. This works fine.
For type 2; it’s another matter. When configuring your dimension attributes as type 2 attributes by setting the trigger history value you effect that changes will lead to closure of the old record and an insert of the new value. Effective and expiration dates are set correctly (although overlapping) and can be changed to suit your needs. But, if one of your attributes is neither a trigger history, effective date, expiration date or business key then the first problem occurs: changes which hit this attribute will be ignored.
Anyway, in most cases doing nothing (i.e. ignoring a change) isn’t a strategy which leads to success for many datawarehouses, but that is exactly what happens if you don’t configure your attributes in OWB type 2. So if you don’t want all of your attributes to be type 2 you will have to develop a seperate process for these attributes. This will typically result in a mapping with a splitter and two targets: the dimension operator for all your type 2 – trigger history – attributes and one target table operator – the dimension table - which is set to update to realize a type 1 change for these attributes.
The same goes for the type 3 settings.
Basically, the features really do shorten development time but untill OWB features a SCD technique which combines (at least) type 1,2 and 3 you still need to work around the dimension a bit and update directly to the table for the part which isnt fully supported (i.e. a type 2 dimension will need a table target operator for type 1 transaction). This bit is just like implementing these concepts in the previous version of OWB.
November 18th, 2006 at 01:37:41
[...] A couple of days ago I mentioned that I wanted to take a look at SCD2 and 3 handling using OWB10gR2. This was prompted by the fact that up until now I hadn’t really looked in detail at this feature, and I’d been reading a few articles and blog postings that suggested the way it works is a bit odd in the first release. In particular, this blog posting by Roelant Vos suggests that it doesn’t update non-trigger columns in the way you might expect, and corresponding separately with Roelant there does appear to be an issue around how it handles changes to parent-level attributes. [...]
December 6th, 2006 at 01:31:58
I read both your and Mark’s very helpful comments, and decided to investigate in some real depth. To cut to the chase, OWB is not actually buggy, but is very definitely limited in some decidedly idiosyncratic ways. I’ve put together a document on this at http://www.donnapkelly.pwp.blueyonder.co.uk/documents/OWB_10gR2_SCD.pdf
I’ll be adding to that over the next week with a ‘how-to-do-it’ guide for SCD processing in 10gR2.
December 6th, 2006 at 11:39:40
Hello Donna,
Thank you for your article, I’ll look into it soon. Most of the time we end up mapping most of the history functionality ‘the old way’ and use the dimension object for type 2 lowest lvl changes. Technically, with a splitter where one target leads to a regular (type 1) table update, and one to the dimension.
I’ve spoken with Oracle (Netherlands) about this a few times and they acknowledged that it certainly is something that has to be improved. Don’t know when though
I look forward to your ‘how to’ article!
Regards,
Roelant
July 16th, 2007 at 01:39:48
Hi Roelant,
I’ve just completed a really in-depth analysis of the forthcoming release 10.2.0.3, and I’m happy to tell you that all of the problems with SCD processing documented in my previous paper have been resolved!
You can get the detailed test results from my new website at http://www.donnapkelly.com/ but the upshot is: automated SCD 2 processing in the next patch set works a treat! Workarounds are not required.
Cheers,
Donna
April 10th, 2008 at 12:25:10
Hello Donna,
I have gone through your articles and implemented a SCD type2 using OWB.The type1 columns and the type2 column were successfully getting updated. However I have come accross a major performance bottleneck.I have a source table with only a 1000 records and my target has 1.5 million records. I have made a simple mapping using the dimension operator. I have noticed that it takes a very long time to merge the records into the target table. The exact time it took was 5.5 hours.I have created all the necessary indexes in my target table but the query that OWB generates does not use any of the indexes.
I have raised a SR and oracle have stated that this is a bug and had given a patch however the same problem was found after installing the patch. Can you help me resolving this issue
Regards
Abhijit
April 10th, 2008 at 16:03:24
Hi Donna,
I wish my SCD1 would work as fine as expected, unfortunately when I insert a new record , the dimension key and surrogate ID sequence simply dont take the next value, the ID skips ahead for all the previous records. Example I have 3 records already in the dim and a new one if inserted takes ID 7. All the previous records get updated unnecessarily and then the insert happens. Same if I try to implement an update_date column. Please suggest a solution
May 15th, 2008 at 18:36:26
Could you please provide step by step documentation for this. Thanks.
May 14th, 2009 at 21:43:57
Hi Abhijeet, Do you have an answer for your performance issues. I have been struggling with a similar case. The first run of the mapping takes 30 minutes to load with the effective date =sysdate and expiration date null. After updating a few source rows I run the mapping again and it goes for a toss..
Any hints would be appreciated.
10.2.0.3
Thanks