Use the Right Tool for the Job

Building a data mart can be broken down into three distinct steps.

Extact data from source system

Transform data to desired structure

Load data into target system (data mart)

There are many tools available to perform these tasks, but generally speaking we have two categories of tools; database engines and data integration tools (SSIS).  Garrett Edmondson did a great job of explaining the difference between ETL & ELT in his latest post.

The ”right tool” in the case of data mart development is the tool that both enables rapid development and well as performs well at runtime.  The performance side is a no brainer in most cases.  The database engine nearly always performs better (loads/transforms more records faster) than SSIS.  However, using the database engine requires that we write many lengthy SQL Scripts.  To make the situation even more unmanageable these SQL Scripts have dependencies upon one another.  Simply changing a field name can require numerous modifications and complicated testing/deployment. 

LeapFrogBI users get the best of both worlds.  The database engine is leveraged for all post stage processes including PSA, transformation, dimension, and fact loads.  Because LeapFrogBI takes a metadata driven approach, developers never write a single line of SQL.  Best of all, making changes is as simple as updating a field in a browser form!