Biml is the operating system for business intelligence. It is a domain specific language that describes BI artifacts: tables, SSAS databases, ETL, and more. The XML tag system provides for the expressiveness of the language, but the true power comes from the native ability to integrate .NET libraries in the generation of Biml objects. This is called BimlScript. With just a few lines of .NET code, C# or VB.NET, you can connect to a database, web service or Excel workbook and use that metadata to drive the generation of your BI artifacts.
As an architect, I find Biml indispensable for delivering consistent, repeatable, cost-effective BI solutions.
Consistency
The wealth of features available in BimlScript for solving problems within the Microsoft Business Intelligence space is staggering. Just looking at SSIS, there are many different ways to solve the same ETL problem. As a simple example, how many times have you seen one person use an OLE DB connection manager and another use an ADO.NET connection manager for the same database? One person uses the OLE DB Command object for updates while another pushes changes to a staging table and uses the MERGE statement. While there is no wrong way to eat a Reese’s, there are certainly better ways to solve problems within the SSIS space. We’ve already talked about why Good Data Integration Patterns Are Good For Business.
Organizations can quantify the cost of a new project but rarely do they look at the cost of on-going maintenance and support. A three-month development project might require care and feeding for five to seven years, with maintenance costs far exceeding the original project cost. If the maintenance and support developers have to spend time getting up to speed because each SSIS package is a unique snowflake, that is going to have a severe impact on their efficiency.
How many versions of SQL Server do you need to support? The same Biml code that generates a SQL Server 2016 SSIS package can be used to generate a SQL Server 2005 package. Until the 2016 release of SQL Server, you might have had 4 different SSIS projects with similar packages, each of which you had to open in a different version of BIDS/SSDT. In these situations, we tend to leave older versions behind as we update logging procedures and implement better integration patterns on newer systems.
To address all of that, it is our duty as architects to ensure we deliver consistent and efficient solutions. Biml is one way to ensure this happens. Instead of handing a junior developer a thick tome of all your best practices, codify it with Biml. This is my pattern for a Type 2 Slowly Changing Dimension, here is how we handle unknown members. You simplify the developer’s life as they only need to ensure they have the correct metadata assembled and your project is successful because you know what your team is delivering before they begin coding.
Metadata driven approach
Metadata is data that describes other data. Within your organization, you already have implicit metadata repositories and you likely have explicit ones as well. Do you have a relational database? Your RDBMS exposes functions and views that allow consumers to know what catalogs, schemas, tables, views, and relationships exist. Further, they expose the columns, data types, and nullability of those fields. That’s a rich metadata store ripe for consumption. If you use extended properties on your databases, tables and columns, now you’ve got a hybrid metadata store.
What about explicit repositories? In organizations that still use the waterfall methodology, it is common to have detailed lineage describing each data element. Do you have a Kimball Bus matrix? In data warehouse projects, often there is an Excel spreadsheet with source and target mappings. Be honest, how often does that document get touched after the first draft? In many organizations, it becomes stale once the electrons have dried.
Change that behavior, make the metadata work for you! Instead of updating that document as one of the last things to happen in a project, use the metadata defined in the various source systems as well as the spreadsheets to drive the creation of your BI artifacts. Biml allows you to take a documentation driven approach toward your development. Use five to ten lines of .NET code to drive the construction of BI artifacts based on the definitions within the metadata store. That’s powerful. As a simple example, we needed to export a few hundred tables for a PolyBase proof of concept. We defined a database connection, used the GetDatabaseSchema method in Biml to generate an in-memory model of the database, and then created an SSIS package for each object that met our criteria. That took about 30 lines of Biml to define the pattern and 10 lines of .NET code to drive the automation, which was about an hour’s worth of work to create all those SSIS packages.
Cost effective
There are always limiting factors in projects but the biggest two are time and money. The Biml and BimlScript features you need for an effective ETL framework are free to download and free to use. All you need is one of the free Visual Studio/SQL Server Data Tools add-ons, BIDS Helper or BimlExpress, and you’ll be up and running with your Biml development. Because the solutions you’re going to build are consistent and they’re built on metadata, the cost of developing solutions is going to decrease because you’re using established and tested patterns against accurate representations of sources.
But what if time is of the essence? Biml can still help you. There are advanced features in the paid product that allow you to get up and running quickly. If you already have patterns that you want to use as a template, you can reverse engineer an existing SSIS package into Biml and go from there.
Adopting this approach not only improves development speed and consistency, but we take on less technical debt. And because we can make updates to multiple packages at once, we can more easily relieve technical debt as we fix bugs and find ways to improve our solutions.
Biml at 3Cloud
We have used Biml as an accelerator for delivery at a number of clients across different industries. We are an official Varigence Consulting Partner and would love to talk to you about how you can leverage Biml to become more effective in your delivery of Business Intelligence. Like what you read and want to know more? Contact us today.
