I find myself starting this blog, sad that I must write it. When it comes to development languages and products I am without a shadow of a doubt firmly two feet and all other associated body parts stood plum on the centre of the Microsoft camp; this has been the case since my late 20’s when I started in software. The only real difference now is that there’s a lot more of me to fit inside the camp nowadays but fit I still do. Of late I find myself heavily involved in more ETL work than true ‘development’ and so my playing field when i get distracted thus comprises off SSIS which I’ve always used (being of the Microsoft camp) and SAP Data Services which is what we sometime use in-house (being an SAP reseller). It is about these two products which I write.
Just recently I have been tasked with creating a data warehouse off the back of one of our application databases, the kind of stuff that we do every day. In this particular instance we for various reasons had elected to use SSIS in favour of Data Services and after a little while (for probably the first time ever) I found myself wishing I was using SAP instead of Microsoft. So what was it that caused this extreme reaction? Well, Type 2 Slowly Changing Dimensions seeing as you ask.
OK. So, what is a Type 2 Slowly Changing Dimensions (SCD)? A slowly changing dimension is a dimensional data table that captures change slowly over time, rather than capturing these changes on a regular schedule. In data warehousing there is a need to track changes in dimension attributes in order to report historical changes at a given point in time. A Type 2 SCD is where we use one table that stores ‘generations’ of records in order to properly enable this ‘point in time reporting’.