Microsoft Extended ETL Implementations

When looking at the time spent developing a BI solution, the development of the ETL component can consume a large percentage of the project time. Not only because this can be very labour intensive, but mostly because of the importance of the correctness of the data within and between sources and the complexity this can produce.

Based on the experience element61 has with using advanced ETL solutions, and with Microsofts ETL tool SQL Server Integration Service, we produced a standard methodology for creating ETL components. This methodology doesnt only consist of a standard way of using the components available within Integration Services according to industry best practices, but it also goes further than a purely technical approach. Within our methodology element61 also standardized the way logging and error handling will be done during the different ETL processes. This includes build in logic for things like how missing lookups will be handled, how this will affect the load process, how users can track and monitor the ETL process, ... on a daily basis.

All this knowledge and years of experience developing ETL components is not only bundled in the methodology we created, but also in a set of Integration Services templates, as a Microsoft SSIS elementary excel-erator. element61 uses these components when implementing the ETL processes in SSIS in customer projects.

The advantage of using these Integration Services templates:

  • Standardization of development: this ensures that all packages are developed in a uniform way regardless of the person developing.
  • Standardization of look-up handling: all lookups for all customers using SQL Server Integration Services as ETL tool are handled in the same way. Especially for the missing lookups this is very useful.
     
  • Standardization of general logging: all packages produce the same topics concerning logging.
     
  • Standardization of monitoring: Because all logging and handling is done in a standard manner, element61 also has a set of standard reports on top of this logging data. These will provide the developers with the necessary information concerning data quality during development, but can as easily be used by the people monitoring the ETL process once it is deployed.
  • Extensibility:
    although
    these Integration Services templates provide a good starting point when developing an ETL process, no 2 customers are exactly the same. For 80% of all ETL packages, these starter kit packages will be more than sufficient, for the remaining 20% of packages they can be used as a starting point and be extended with new functionality when needed.

Contact us to learn more about Microsoft Extended ETL implementations.