Dynamic Staging – an Interesting view

I’ve long been writing and advocating the need for dynamic data warehousing, which in my definition inlcudes dynamic structuring of the data set.  Well, in a batch world this process that adapts structure needs to start with the staging area.  While this article from an author on SQLServer is not a fully automated process, it’s close.  It is a step in the right direction.  This author talks about “dynamically” changing the stage load code based on data-driven table definitions.  This is not the first time I’ve seen this, some of my friends built ETL generators based on structure, but this is the first time I’ve seen this in SSIS, and to have it so fluid that the ETL doesn’t need to be re-imported every time the structure changes, now that’s interesting.

The article is a fairly good write-up, and I would encourage you to read through it.  I bet that most of you can convert the code to DB2 9.1, Teradata, or even Oracle.  Would love to see that happen, and see what results you get.

http://www.sqlservercentral.com/articles/ETL/71210/

There are several issues left unanswered by this article:

* performance, I have no idea how the performance of this load process pairs up with performance of static structuring, the author doesn’t give any clues there.

* Still a manual structure adaptation, the maintenance person still has to check and add the structural changes to the columns in the database that define the tables.

The next step, would be to build a “structural profiler” that can peer in to the file, and detect the structure – and make the structural changes to the database for you… This is where the REAL fun begins.

None the less, it is an interesting article, I enjoyed it, and I hope you do to.

Cheers,
Dan Linstedt

Tags: , ,

3 Responses to “Dynamic Staging – an Interesting view”

  1. Roelant Vos 2010/10/31 at 11:53 pm #

    Hi Dan,

    I’ve been out for two months but I’m back for extra effort on mapping generation for Data Vault. Thanks for mentioning the article, very useful. It give enough pointers to map my OWB script into SSIS ones…

    Regards,
    Roelant

  2. Roelant Vos 2010/11/01 at 12:03 am #

    One thing I could have added; either through (automated?) profiling or just by simply adding the key information to the metadata would give you enough information to generate the Data Vault using the common steps. I’ll look into it.

  3. dlinstedt 2010/11/01 at 6:53 pm #

    Hi Roelant, Cool. We should chat about this. Thanks for the update.

Leave a Reply

*