What is BETL
- BETL is an ETL generation framework written in TSQL
- Free, open source, GNU GPL
- Reusable building blocks implementing best practices
- ETL Engine (it generates TSQL)
- Time saving (look and see)
- Generic (usable in many different scenarios)
What is BETL NOT
- A modeling method (like e.g. Datavault or Anchor modeling)
- Restrictive (all or nothing)
- A DWH architecture
- Visual (currently it's only TSQL, but you can integrate it in SSIS)
Current Status
- BETL is currently being used by 2 large customers in The Netherlands. The current version is optimized for generating Datavault tables (Hubs, Sats and Links). For each new Customer BETL is extended and modified according to the current needs.
Strong elements of BETL are:
- the meta data engine ( storing meta data about tables and columns).
- guessing. We believe that user input should be minimized. If we can guess for example the target column for a natural foreign key then we will do that. Of course you can always modify the meta data when guessing was not correct.
- dynamic SQL generation using parameters. Bound to the limits of stored procedures we try to develop as modular as possible.
- simple to use and extend. (Most extension work is done on stored procedure push).
- It forces developers to folow certain design guidelines and best practices. E.g. naming conventions, change detection, logging, TSQL Batch insert performance, etc.
How it works
The goal of BETL is making the life of ETL developers easier.
Its a collection of BI best practices which are invoked using the BETL command language.
Suppose we want to move data from A to B. (where A and B are tables or views in a RDBMS)
What we need is the following:
-connection to A (e.g. by using a linked server using an OLEDB driver)
-connection to B
-Some meta data about the transfer that cannot be derived from the source (like transfer type, which columns form a natural key). transfer type tells us which ETL template should be chosen for transfering A to B. the natural keys tells us which columns can be used for change detection.
All we need to do is run this TSQL command:
exec betl.push ‘A’
- we assume that the schema where A lives has a default target schema which will be used by BETL to determine B.
BETL uses extensive logging, so you can see what happens. Debugging is also easy. Just switch on the debugmode and invoke this command in management studio. You will get de TSQL that will be executed.
Read more on best practices here
http://www.biblog.nl/category/bi-design-patterns/Continue with our installation manual or tutorial here
https://betl.codeplex.com/documentation