Hadoop: Questions I Am Asking

I have close to 14 years’ experience with SQL Server for ETL around Data Warehousing. I lead a team of very talented Data Warehouse Developers who have developed and maintain a multi-terabyte data warehouse. We ETL and dimensional model data daily describing tens of thousands orders, millions of dollars of sales, millions of web site visitor metrics and tens of millions of web page views. And we do this each night, and more, and have it all ready for the C-suite execs to drink in with their morning coffee! I’m not saying this to brag (well, maybe a little), but because despite that experience, Hadoop puts me in an alien world where the normal tools of my trade don’t seem to make sense.

At this point in time, the questions I am asking myself are:

• How much of my Data Warehouse environment and processes will eventually be replaced by Hadoop related technologies and processes?
• What ETL processes are best done in Hadoop and which in SQL/SSIS?
• How much of my storage will transfer to Hadoop, Archive, Raw Staged, Operational Stores and Modeled Data?
• How big of Hadoop environment do I need to surpass the power of my current SQL environment?
• Does Hadoop mean adapting new technology to the existing BI strategy or do we need a new BI strategy?

I am tenacious, so it not a matter of “if” but “when” I’ll know which of my old tools will work, how to use new tools and new strategies to conquer the next generation of data challenges.

SSIS Slowly Changing Dimensions

In SQL Server Integration Services 2005 or 2008 there is a Data Flow component called the Slowly Changing Dimension Item (SCD). Unfortunately when you use the SCD wizard to create a type 2 slowly changing dimension you have to choose between using a current indicator or a valid date range technique. You can not have both, at least through the wizard. To add both you will have to edit the package after the wizard is complete.

Complete the Slowly Changing Dimension wizard. I prefer choosing the Single Column / Current Flag option for Historic Attribute Changes during the wizard.

Modify the components in the SDC update branch

1. Add a new derived column with an expression like (DT_DBTIMESTAMP) GETDATE()
2. Modify the OLEDB Command Update statement, i.e.:UPDATE [dbo].[tbl_Cost_Center_Dim] SET [Current_Flag] = ? ,Row_End_Date = ?WHERE [Cost_Center_Code] = ? AND [Current_Flag] = '1'
3. Modify the OLEDB Command, Column Mapping tab, to reflect the re-alignment the new parameter(s) added.

Modify the components in the SDC Insert path. These are the components after the Union All component.

1. Add new derived columns for begin and end effective dates. The end effective date should be a conformed standard future date such as 6/6/2079.
2. Add the new columns to the mapping of the OLEDB Insert Destination component.

In addition to using this process for Row Effective dates it is also useful for using Row Created and Row Updated dates. (Originally posted 10/10/2007)