BIML, The next milestone for SSIS Developers
Wednesday 24 February 2016, by Nat Sundar
Introduction
Are you a SSIS developer and would like to learn a new skill to improve your employability at work? Then this post will help you to understand the important aspects about BIML and its usage.
What is BIML?
BIML (Business Intelligence Markup Language) is a XML dialect for creating Business Intelligence (BI) solutions. The power of BIML can be unleashed with the help of BIML script.
BimlScript extends the language by enabling authors to embed C# or VB.NET code within Biml, similar to how ASP.NET includes .NET code within static HTML markup
BIML can be used to develop both SSIS and SSAS based solution. Currently Biml supports the representation of relational models, data transformation packages, multi-dimensional models (OLAP), permissions, databases, and flat files.
How does BIML support for Metadata driven ETL development?
Let us assume that the ETL Metadata information is stored in a relational database format. This may contains the information about Data sources, Destinations, Business transformation and Data quality rules.
BIML script can read this Meta data information from the database with the help of BIML. Also the BIML script can read the BIML design pattern and generate a number of SSIS packages.
With the Biml compiler you can add a column to a table and it automatically rewrites everything for you, including database creation scripts, SSIS packages, cubes, documentation, and more.
BIML generated artifacts (SSIS and SSAS) solutions can be manually customized.
How does BIML generate SSIS packages?
The BIML compiler compiles the BIML and BIML Script files and generate the DTSX (SSIS) package.
Can I use BIML without BIML Script?
Yes, BIML is still useful without BIMLScript, but much more powerful with it.
What tools are available in the market to practice BIML?
The following tools can be used to Design and Develop BIML based solution.
- BIDS-Helper (Add-on for BIDS/ SSDT) – Open Source / Free version
- MIST – Commercial IDE Developed by Varigence
Download the latest version of BIDS Helper at the CodePlex website. BIDSHelper is completely free and is a Visual Studio add-in with features that extend and enhance business intelligence development functionality.
What are all the limitation of BIDS Helper and how to overcome?
BIDS helper doesn’t support command line compilation. This is available with MIST (Commercial IDE) and this can be used to integrate the BI solution using continuous integration.
In addition, the MIST has number of features to support enterprise level metadata driven development.
How does BIML accelerate conventional ETL Development?
In Data warehouse projects, we can see commonality between multiple source systems. This means the same kind of flat files / data structure has been loaded multiple times with a help of many SSIS packages. All these packages will have same business transformation implemented.
The development team need to copy and create more and more SSIS packages to meet the business needs. This is repetitive and mundane task. In addition, if there any new requirements need to be captured then we need to make a change to all the SSIS packages.
BIML can resolve all the above mentioned issues by having design pattern and that can be used to generate number of SSIS packages. This slashes the development time considerably and increases the ROI for business stakeholders.
How many days it will take to learn and practice BIML?
It will take approximately few days for an experienced BI professional to understand and start using BIML in workable solution.
Does BIML support previous version of SQL Server?
BIML solutions can be developed and deployed to multiple SQL server versions like SQL 2008, SQL 2008R2 and SQL 2012.
You can also use Biml inside of Visual Studio 2008, 2010 or 2012.
What are all the Benefits of for a SSIS Developer
- As this is based on XML, it is human readable and writable
- More productive during development
- It helps building Design patterns for SSIS and SSAS
- Code reusability / Automate repetitive work
- Learning and Implementing Meta data driven ETL development with BIML is easy and fun
- It works well with Agile development methodologies
- Merging and Diff comparison is easy with any source control
- Easy to share the BIML code across the team and community
- Changes can accommodated quickly, even in a complex ETL system
- It can also be used to reverse engineer all BI solutions to convert into BIML solution.
Conclusion
It is clear that BIML will help to transform the SSIS developer to the next level. I highly recommend you to start allocating few hours a day to learn BIML and its fun. Watch this blog for more detailed tutorials about BIML.