The Biml Book, And Some Thoughts On Biml And SSAS

I know what you’re thinking: why is Chris blogging about a book on Biml when he’s not remotely interested in SSIS? Well, you’re right, SSIS isn’t my thing (though as an SSAS developer it’s important to keep up-to-date on the tools that the SSIS people on your team use) and yes, as you may have guessed, I got a freebie review copy because I’m an Apress author and I’m friends with one of the authors, Andy Leonard. There is, though, a good reason why I want to learn about Biml: you can use it to generate SSAS databases as well as SSIS packages.

First of all, the book itself. “The Biml Book” is a new book written by a team of Biml experts that teaches you pretty much everything you need to know about Biml. I’m always a bit worried by books with a large number of authors because I know how difficult it is to maintain a consistent style, but in this case I couldn’t see any joins. As someone with no previous experience of Biml. I found the book very clear, concise and easy to read. I highly recommend it as an introduction to the topic.

Of course the chapter on using Biml with SSAS was the most interesting for me. The main reason why SSAS people aren’t as excited about Biml as SSIS people are is that we just don’t usually have the same amount of boring, repetitive work that begs to be automated, and we already have AMO and TOM when we do need to automate the creation of SSAS objects. Indeed, I’ve only ever met one person who is using Biml with SSAS. So when would you use Biml and SSAS? The book provides a good, honest answer:

Biml can be a great fit for Analysis Services use cases, but there are some exceptions.

Just as with SSIS, one of the great advantages of Biml is that it easily allows scale-out architectures through automation. This makes it a good choice when it comes to multi-tenancy and/or multi-server environments.

Given the ability to automate structures and deployments through metadata, Biml frameworks can also include cube projects that can be driven with some of the same metadata that was used to build data integration frameworks.

Conversely, SSAS Multidimensional/Tabular can require the specification of additional types of metadata to automate its creation. After all, cubes and tabular models are largely just metadata containers on top of relational structures. If you want to use your own metadata to drive the creation of bespoke SSAS projects that could support any SSAS feature, you essentially need to duplicate the entire SSAS feature set in your metadata store. This will result in complex models that may be very difficult and time-consuming to maintain, potentially leading to longer instead of shorter development and deployment times. In a nutshell, Biml isn’t for all SSAS projects, but for the pattern-heavy, scale-out projects where it does fit, it’s tremendously valuable.

I’ll give you an example of where I think Biml and SSAS make a good match. I’ve worked with several companies who do what I call B2B BI: they create, host and manage Microsoft BI solutions for their customers. They have a standard template solution that connects to a particular type of data source (for example, their customers’ Dynamics databases), builds a data warehouse and then puts SSAS and maybe some reports on top. As a result they end up with multiple copies of the same solution, with the only difference being that each copy contains a different customer’s data – a classic example of a “multi-tenancy and/or multi-server environment” as described in the extract above. This style of BI will become more and more common in the future because cloud-based services like Azure SSAS and Power BI (both now support Azure AD B2B) make it much easier to implement, and I think Biml could play a very important role here: you don’t want to build and manage hundreds of near-identical SSAS databases, and their supporting ETL, manually.