Introduction

Business Intelligence Markup Language (Biml) is a programming language for creating data warehouse, business intelligence, and other data solutions. It uses XML in combination with small nuggets of C# or VB code to automatically create huge amounts of SQL scripts, SSIS packages, SSAS cubes, and other objects that your team previously had to build manually - usually with slow, tedious, error-prone dragging and dropping. Perhaps the best part is that there is nothing to install on your server - Biml works entirely with your existing data infrastructure.

Developers

If you are a data developer, especially if you use SSIS or SSAS, Biml takes the tedium out of your day-to-day work. You'll get more done and have more fun doing it. Instead of explaining to managers why seemingly simple tasks take longer than they expect, use Biml automation to exceed their expectations without having to overwork.

Consultants

If you are a consultant, you'll be able to reliably offer more value to your clients, which leads to more closed deals and extended engagements. Once you've written a lot of Biml code, package it up into a framework that you can sell to clients - providing further customer value and ROI, increasing your margins, and creating new recurring revenue streams from framework support and upgrade contracts.

Managers

If you are a manager, you'll be amazed at how much more productive your team (both internal and outsourced) becomes when using Biml. But perhaps more importantly, work becomes more reliable and predictable. Things that seem like they should be easy actually become easy and get done quickly. Difficult things are still difficult, but they get done faster and with lower risk.

Those are some big promises. Let's take a closer look at how Biml delivers.

The Problem with SQL Server, SSIS, and SSAS

Before we discuss what Biml does to make your life easier, let's start with a brief overview of the problems in SQL Server, SSIS, and SSAS development tools that cause many of the difficulties that Biml addresses.

Tedium

The first major problem with data development tools is tedium. This is true across virtually all vendors and technologies, but especially so with SSIS and SSAS. Microsoft Business Intelligence Developer Studio (BIDS) and SQL Server Data Tools (SSDT) are primarily driven by mouse input. Most developer time is spent dragging and dropping on a design surface or filling out forms in dialog boxes. It is slow, it is boring, and it is error prone.

With the standard tools, often your only option is drag and drop and dialog boxes. With Biml, you still have that option, but you can also type and automate.

Repetition

Why does it take so much effort to handle a simple task, such as adding support for a new column in a source system table that you are already processing? A big part of the problem is repetition. Microsoft and other vendors have designed their tools in silos. This eases the development of those tools. And for many customers it doesn't matter, because they are operating in a heterogeneous IT environment where different vendors supply database, ETL, analytics and other software. But if you are using Microsoft for the whole stack, shouldn't you be able to make changes in one place and expect the entire stack to be updated? Shouldn't you be able to avoid changing configuration in 4 or more separate tools - each in potentially multiple locations - just to deal with a single column change?

With the standard tools, you need to repeat yourself. With Biml, you don't.

Lack of Abstraction and Reuse

We've all noticed patterns in our data development. We use the same approach for logging, slowly changing dimensions, inferred member, and a variety of other common data development tasks. With the standard tools, you might have the option (depending on your version) of creating templates that provide very limited code reuse. But modifying those patterns after initial implementation to handle changing business needs is not possible. And the templates are limited to only the highest level patterns, without real support for data flows or other tasks that rely on implementation specific information.

With standard tools, copy/paste is the limit of code reuse. With Biml you can write, modify, and reuse any pattern at any time.

Inconsistency

Largely due to the limitations around abstraction and reuse, SQL Server, SSIS, and SSAS solutions tend to become more fragmented and inconsistent over time. Suppose that you implemented a standard logging pattern across all packages in the first version of your data warehouse. Later, you discover that this pattern did not handle an important but rare scenario. Usually, developers will make targeted changes to the logging pattern just in the affected packages to save time. Of course, that is the right decision and doing it only once won't have major negative impacts on the project. But fast forward a few years after this same story has played out a hundred or more times. Those hundreds of targeted changes have destroyed any real notion of an enforceable pattern across your solution. Large amounts of code have become essentially custom, and subject matter experts are required to engage with almost every change to avoid inadvertently breaking other code.

With standard tools you accumulate technical debt by incrementally deconstructing your own patterns. With Biml, you can modify patterns centrally and reapply them solution-wide in minutes.

How Does Biml Address These Problems

Biml starts by creating a human readable and writable source code. This source code is based on XML, which is widely supported by a variety of tools and vendors. This source code can be organized into separate patterns and reusable code can be placed in separate files. This enables changes to shared code to be applied to the entire solution - just by rebuilding.

The source code is often quicker to write and to navigate using code editor tools than the drag and drop design surfaces of the standard Microsoft tooling. Even better, automation logic can be embedded directly into your Biml code using BimlScript code nuggets. These snippets of C# or VB code enable you to access external metadata to automatically build large amounts of Biml code. For example, you can automatically create staging environments or apply SCD patterns based on column metadata. If you can methodically describe the pattern, you can automate it with BimlScript and perhaps a small amount of metadata supplied by your business analysts and subject matter experts.

But what if your team prefers visual design tools and would rather avoid coding? No problem, the Mist IDE includes visual designers that are much like the standard Microsoft tools - except optimized for greater productivity. They can drag and drop like they have before, and Biml code is written automatically under the hood. Higher level architects and developers can then use Biml automation tools to enhance this code with the enterprise patterns and automation that you need to ensure a reliable and predictable solution.

This might sound like a big change for your organization, but it's probably less than you think. Your developers will have to adapt to using the new tools, but nothing else in your operations workflow needs to change (unless you want to further automate that, too).

With Biml, there is nothing to install on the server. When you build Biml code, the Biml compiler outputs SQL scripts, SSIS files (DTSX, DTPROJ, etc.), SSAS files (CUBE, DIM, DSV, etc.), and other assets that you can deploy to your server exactly as you always have.

Here is an illustration of how the Biml workflow operates:

An Analogy

In many ways, the development philosophy of Biml is similar to the very successful methodologies employed in web development. Just as Biml describes the backend of your data solution, HTML describes the document object model of a webpage. For Biml, it's connections, tables, columns, packages, cubes, etc. For HTML, it's paragraphs, images, hyperlinks, etc.

So at a baseline, Biml provides a markup language that functions much like HTML.

Just like with HTML, you don't want to write all of it by hand. You instead use technologies like ASP.NET, PHP, or Ruby on Rails to embed nuggets of automation code among your HTML elements to generate large amounts of HTML code from a small amount of script logic and metadata. BimlScript works exactly the same way.

It's hard to argue that web development methodologies haven't been widely successful over the past few decades. What Biml does is to bring those same successful methodologies to data development.

Conclusion

With Biml, regardless of your role in the organization, you will see massive improvements to solution delivery time, maintainability, correctness, and much more. To reap these rewards, you do need to make an investment in learning the new technology. Over the course of the lessons that follow, we'll help you do exactly that.