It’s been almost a year since I discovered LeapFrogBI, and thought it was about time to share this tool with my SSIS Developer brothers and sisters. For the projects I work on that involve end to end data mart creation and population, my deFacto tool for driving agile and rapid development is LeapFrog BI. We use this as part of our A3 (Agile, Accurate, Accelerated) approach to developing BI solutions. Here are three biggest reasons why:
LeapFrog BI falls under the SaaS (Software as a Service) model, and enables me to profile multiple source systems and extract the meta data from those source systems to build my data mart/data warehouse staging area. Just plug into the Source system (Excel file, Flat File, FoxPro File, SQL Server, Oracle DB etc. etc.), and the metadata for tables, columns and data types are extracted and stored in LeapFrogBI. Since a typical Staging area for an ETL subsystem is usally a “clone” of the source system structure, LeapFrogBI uses this meta data to not only create my staging area at a specified target, but also auto generates the SSIS Packages needed to plug into the various source systems, extract the data based on my specified increments, and load my target staging area. If you think through the numbers of source systems and tables (ERP Systems, CRM Systems, line of business applications, on and on), what used to take me weeks to develop and test just the (E)xtract SSIS packages, now takes me hours.
Downstream from my staging are is usually my data mart (or data marts). However, the (T)ransform packages need to be built to apply business rules such as data cleansing, proactive data profiling, calculations, flattening out the table structures etc. I do all of this business logic in a very easy to use web based interface with LeapFrogBI. Again, I don’t open up Visual Studio at all. I use my staging area as my source, and define transforms directly in the LeapFrog BI interface? Why? I spend my time on getting the transformation and business rule logic right, and not in visual studio troubleshooting and debugging SSIS packages. Can I do it in Visual Studio (BIDS, SQL Data Tools)? Sure, and I have many times. But as many of you know, there are definitely more complex pieces of the ETL that deserve my attention over editing column mappings, configuring data source and targets, and trying to translate my business logic into SSIS expressions. With LeapFrogBI, I create the business rules in the interface, and LeapFrog BI generates the SSIS packages for me.
Standard ETL Code Base
LeapFrogBI generates SSIS packages according to generally accepted best practices for coding, performance, error handling, auditing and data lineage. Often I step back into my ETL code base after many months (and sometimes years) have passed since the initial development took place. Many developers may have touched the code base. LeapFrogBI forces standards. The LeapFrogBI interface is used for the package generation, so the packages are generated in the same fashion – every time. That’s piece of mind I like when digging back into an SSIS code base for enhancements, bug fixes, or new data sources.
Accelerated Development Time
Based on the above two reasons I listed as to why I switched to driving my ETL development from source system and derived metadata, I decided to review some of my past SSIS development projects and try and measure the efficiency in development and deployment time experienced by leveraging LeapFrogBI. I handed a source to target mapping (ETL requirements) to two Senior SSIS developers. I let one developer run with the requirements. With the other developer I reviewed a different project I had completed in LeapFrogBI to “show him the ropes”. With skill sets and experience being relatively the same, the second developer had a working ETL populating a star schema in roughly 1/5th the time it took the other developer to build the staging area, star schema, and the associated ETL packages!
Disclaimer on utilizing LeapFrogBI
LeapFrogBI isn’t a complete “paint by number” toolset. A solid understanding of data mart/data warehousing principles as well as a reasonable background in SSIS development is still needed to take advantage of the tool. However, if you could wake up tomorrow and cut your SSIS development time by 80%, I think we would all agree the tool used is a valuable one.
For more info on LeapFrogBI including pricing – check out their site and request a free demo.