Why Bother with Biml?

Also check out an updated post on – More reasons you should adopt Biml

If you haven’t heard of Biml before, it stands for Business Intelligence Markup Language. Basically it allows you to describe Microsoft Business Intelligence objects (SSIS Packges, SSAS Cubes) in XML which are then generated into the actual objects.

At first reading you would be forgiven for thinking that this is just going to add another step in an already long winded process to build a data warehouse. That is what I used to think until I saw it in action a few weeks ago at SQLRally.

What does it look like?

Here is a trivial example which will just generate two project level connection managers, and one SSIS package which has a single Execute SQL task.

1-what-it-looks-like

As you can see, the XML is very expressive which makes it very easy to read and understand. After running the Biml generation tool the following appears (as if by magic) in the Integration Services project.

2-solution-explorer

And the package itself looks like this:

3-the-product

Mixing C# and Biml

What makes Biml really powerful is the ability to mix C# with the Biml code to generate dynamic packages. Take another trivial example:

4-biml-with-c-sharp

You can see the c# code in lines 7, 8 and 19. This Biml script will loop through the <package> element five times and in turn create five packages.

5-solution-with-dyn-packages

So why bother?

In addition to being able to embedded trivial looping mechanisms, you can get a lot more creative and sophisticated. For example, you can execute sql queries and then loop over the results. This would allow you to generate dynamic packages based on some metadata store.

At SQLRally Davide Mauri went into a lot of depth on implementing design patterns with Biml to automate the monkey work, and I think he absolutely right! He has very helpfully posted his slides and demo code on his blog, and I would highly recommend that you have a read through as it is very educational.

What I’m working on

The shop I’m currently working for stores a lot of data in SalesForce, so I’m looking at how I could implement some of the design patterns that Davide has shown with SQL Server as the source system and use SalesForce instead.

Stay tuned! (And let me know if this would be of interest to you).

References

http://bimlscript.com – A great resource of tutorials, from getting started to in-depth

http://www.varigence.com/Products/Biml/Capabilities – These are the original developers, they have their own IDE

http://bidshelper.codeplex.com – This handy addon for BIDS / SSDT BI enables you to compile your Biml into SSIS packages. If you are a Microsoft BI developer you should really already be using this!

Advertisements

2 thoughts on “Why Bother with Biml?

  1. eugeneniemand says:

    I’m actually also in the process of doing exactly what you are doing. I’m using CozyRoc Salesforce SSIS Connectors. How do you handle columns in your Salesforce source connections? Do you define them manually/programmatically or are they implied from the metadata returned by the source connector?

    • KenR says:

      Hi Eugene,
      I’ve actually just written a c# application that connects to SalesForce, lists all of the objects available, lets you select the columns that you want and then creates a table in your staging database and generates the SSIS package to populate it.
      I’ll be posting about it in the coming days so stay tuned.

      Ken

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s