SSIS Model Driven Design – Roelant Vos
The Enterprise Data Warehouse (EDW) has a bad image. An image that seems to be associated with long development times, high complexity, difficulties in maintenance and a falling short on promises in general. It is true that in the past many EDW projects have suffered from stagnation during the data integration (development) phase and to this date, ETL development still takes the lion’s share of the overall development times for a BI/ETL solution. These projects have stalled in a cycle of changes in ETL and data model design and the subsequent unit and acceptance testing. No usable information is presented to the business users while being stuck in this vicious cycle and as a result an image of inability is painted by the Data Warehouse team (and often at high costs).
Why are things this way? One of the main reasons is that the Data Warehouse / Business Intelligence industry defines and works with architectures that do not (can not) live up to their promises. To date there are (still) many Data Warehouse specialists who argue that an EDW is always expensive, complex and monolithic whereas the real message should be that an EDW is in fact driven by business cases, adaptive and quick to deliver.
As one of the main and often tedious components of these solutions (and a prime cause of the delays), it seems hardly practical that ETL as we know it today will still exist in a couple of years. More and more hybrid approaches towards Data Modelling and increased insight in the necessary steps towards data and information management, as well as improvements in the options (APIs) of ETL software will lead to ‘hiding’ the complexities and shifting the focus of the work on data modelling.
This presentation aims to do a conceptual and technical deep dive in capabilities that exist for SSIS to support this model driven design; effectively generating packages based on available metadata and defined patterns. Using the standard BIDS and set of custom scripts I will walk though a series of examples with a focus of simplifying the design and development of the Data Warehouse while explaining the package generation.
About the Speaker
Roelant Vos has been active in the Data Warehouse / Business Intelligence sector for over 12 years and is currently working for Analytics8 as a Principal Consultant for Data Integration/ETL and Data Warehouse architecture in Brisbane. Driven by a deep belief in improving quality and speed of delivery through model-driven design and automation he is a strong advocate of hybrid modelling techniques which are rapidly gaining ground. Roelant has started his career as a consultant for the Cognos suite of Business Intelligence products but quickly adopted ETL and data modelling which resulted in various projects covering almost every combination of BI/DWH related technology. Over the last few years he has been (re-)introduced to Microsoft technologies, was positively impressed, and decided to pursue and specialise further in combining the ETL methodologies with SSIS along with a broader adoption of the Microsoft technologies.